16,556
社区成员
发帖
与我相关
我的任务
分享
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Sheet1.xls;Extended Properties=Excel 8.0;")
Dim cmd As New OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()
cmd = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
Me.DataGridView1.DataSource = ds.Tables(0)
cn.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'创建一个 DataSet
Dim myDataSet As DataSet = New DataSet
'连接数据库,得到数据集
Try
' 设定数据连接字符串,此字符串的意思是打开Sql server数据库,服务器名称为本地 ,数据库为temp
Dim strCon As String = " data source=192.168.6.93;initial catalog=ERPV3;persist security info=False;user id=sa;password=sql"
'数据连接代码,对此修改可导入其他类型数据库到Excle表格
Dim myConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strCon)
myConn.Open()
Dim strCom As String = "select * from BW_M_LOC"
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(strCom, myConn)
Dim myAdapter1 As New SqlClient.SqlDataAdapter
myAdapter1.SelectCommand = myCommand
myAdapter1.Fill(myDataSet, "table01")
'关闭此OleDbConnection
myConn.Close()
Catch ey As Exception
MessageBox.Show("连接错误! " + ey.ToString(), "错误")
End Try
Dim table As DataTable = myDataSet.Tables("table01")
'创建一个空的Excel电子表格文档
Dim AppExcel As Excel.Application = New Excel.Application
AppExcel.Application.Workbooks.Add(True)
'读取数据的字段名称,并在产生的Excel表格的第一行显示出来
Dim colIndex As Integer = 0
Dim col As DataColumn = New DataColumn
For Each col In table.Columns
colIndex = colIndex + 1
AppExcel.Cells(1, colIndex) = col.ColumnName
Next
'实现数据集到Excel表格的转换
Dim rowIndex As Integer = 1
Dim row As DataRow
For Each row In table.Rows
rowIndex = rowIndex + 1
colIndex = 0
Dim col1 As DataColumn
For Each col1 In table.Columns
colIndex = colIndex + 1
AppExcel.Cells(rowIndex, colIndex) = row(col1.ColumnName).ToString()
Next
Next
AppExcel.Visible = True
End Sub
'DataSet to hold data
Dim DataTrans As DataSet = New DataSet( "dataroot ")
Dim Products As DataSet = New DataSet( "dataroot ")
Dim DataSupplier As DataSet = New DataSet( "dataroot ")
Dim DataCustomer As DataSet = New DataSet( "dataroot ")
'check to see if the files exist. If they do, delete them
If File.Exists(FileTrans) Then File.Delete(FileTrans) 'if (File.Exists(@FileTrans)) { File.Delete(@FileTrans);}
If File.Exists(FileProducts) Then File.Delete(FileProducts) 'if (File.Exists(@FileTransProducts)) { File.Delete(@FileTransProducts);}
If File.Exists(FileCustomer) Then File.Delete(FileCustomer)
If File.Exists(FileSupplier) Then File.Delete(FileSupplier)
'Open connection to MS Access database
Dim TransactionCN As New OleDb.OleDbConnection
TransactionCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + Server.MapPath( "~ ") + "\DB\Testupdated412.mdb "
TransactionCN.Open()
'Set the DataAdapter to get data from the specific table
Dim BuyingAdapter As New OleDb.OleDbDataAdapter( "Select * from " + TableTrans + " Where Order_No= ' " + GCIOrderRef + " ' ", TransactionCN)
Dim ProductsAdapter As New OleDb.OleDbDataAdapter( "Select * from " + TableProducts + " Where GCI_Order_Ref= ' " + GCIOrderRef + " ' ", TransactionCN)
Dim SupplierAdapter As New OleDb.OleDbDataAdapter( "Select * from " + TableSupplier, TransactionCN)
Dim CustomerAdapter As New OleDb.OleDbDataAdapter( "Select * from " + TableCustomer, TransactionCN)
'Fill the DataSet with the DataAdapter command
BuyingAdapter.Fill(DataTrans, TableTrans)
ProductsAdapter.Fill(Products, TableProducts)
SupplierAdapter.Fill(DataSupplier, TableSupplier)
CustomerAdapter.Fill(DataCustomer, TableCustomer)
'Call the WriteXML methods to output data to a XML file
DataTrans.WriteXml(FileTrans)
Products.WriteXml(FileProducts)
DataSupplier.WriteXml(FileSupplier)
DataCustomer.WriteXml(FileCustomer)
'Clean Up
TransactionCN.Close()
DataTrans = Nothing
BuyingAdapter = Nothing
Products = Nothing
ProductsAdapter = Nothing
DataSupplier = Nothing
SupplierAdapter = Nothing
DataCustomer = Nothing
CustomerAdapter = Nothing
'Open spreadsheet and import XML data into the XML Map
Dim xlApp As Excel.Application
Dim xlWorkbook As Workbook
xlApp = New Excel.Application
xlApp.Visible = True
xlWorkbook = xlApp.Workbooks.Open(SpreadsheetLocation)
xlWorkbook.XmlMaps(ExcelXMLSupplier).Import(FileSupplier)
xlWorkbook.XmlMaps(ExcelXMLCustomer).Import(FileCustomer)
xlWorkbook.XmlMaps(ExcelXMLTrans).Import(FileTrans)
xlWorkbook.XmlMaps(ExcelXMLProducts).Import(FileProducts)
xlWorkbook.SaveAs(OutputFileLocation)
xlApp.Quit()
xlApp = Nothing
'check to see if the files exist. If they do, delete them
If File.Exists(FileTrans) Then File.Delete(FileTrans) 'if (File.Exists(@FileTrans)) { File.Delete(@FileTrans);}
If File.Exists(FileProducts) Then File.Delete(FileProducts) 'if (File.Exists(@FileTransProducts)) { File.Delete(@FileTransProducts);}
If File.Exists(FileCustomer) Then File.Delete(FileCustomer)
If File.Exists(FileSupplier) Then File.Delete(FileSupplier)
'display the hyperlink
If File.Exists(OutputFileLocation) Then
downTrans.Visible = True
OutputFileLocation = OutputFileLocation.Substring(OutputFileLocation.LastIndexOf( "\ ") + 1)
'downTrans.NavigateUrl = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
Dim Url As String = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
'Url = Server.UrlEncode(Url)
'Server.Execute( "sheet/DownExcel.htm ")
'Server.Execute( "/sheet/1.txt ")
Response.Redirect(Url)
End If
End If
End Sub
[code=VB.NET]
Public Sub ExportTransactionSheet(ByVal GCIOrderRef As String, ByVal SpreadsheetLocation As String, ByVal OutputFileLocation As String)
If File.Exists(OutputFileLocation) Then
downTrans.Visible = True
OutputFileLocation = OutputFileLocation.Substring(OutputFileLocation.LastIndexOf( "\ ") + 1)
'downTrans.NavigateUrl = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
Dim Url As String = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
'Url = Server.UrlEncode(Url)
'Server.Execute( "sheet/DownExcel.htm ")
'Server.Execute( "/sheet/1.txt ")
Response.Redirect(Url)
Else
'Dim FileTrans As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempmain.xml "
'Dim FileProducts As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempProd.xml "
'Dim FileCustomer As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempCust.xml "
'Dim FileSupplier As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempSupp.xml "
Dim FileTrans As String = Server.MapPath( "~ ") + "\sheet\ " + "tempmain.xml "
Dim FileProducts As String = Server.MapPath( "~ ") + "\sheet\ " + "tempProd.xml "
Dim FileCustomer As String = Server.MapPath( "~ ") + "\sheet\ " + "tempCust.xml "
Dim FileSupplier As String = Server.MapPath( "~ ") + "\sheet\ " + "tempSupp.xml "
Const TableTrans As String = "Mainform "
Const TableProducts As String = "tbl_UPLOAD_Products "
Const TableCustomer As String = "qry_Excel_CustomerInfo "
Const TableSupplier As String = "qry_Excel_SupplierInfo "
Const ExcelXMLTrans As String = "Mainform_Root_Map "
Const ExcelXMLProducts As String = "Products_Root_Map "
Const ExcelXMLSupplier As String = "dataroot_Map "
Const ExcelXMLCustomer As String = "dataroot_Map1 "
Public Sub ExportTransactionSheet(ByVal GCIOrderRef As String, ByVal SpreadsheetLocation As String, ByVal OutputFileLocation As String)
If File.Exists(OutputFileLocation) Then
downTrans.Visible = True
OutputFileLocation = OutputFileLocation.Substring(OutputFileLocation.LastIndexOf( "\ ") + 1)
'downTrans.NavigateUrl = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
Dim Url As String = System.Configuration.ConfigurationSettings.AppSettings( "downPath ") + OutputFileLocation
'Url = Server.UrlEncode(Url)
'Server.Execute( "sheet/DownExcel.htm ")
'Server.Execute( "/sheet/1.txt ")
Response.Redirect(Url)
Else
'Dim FileTrans As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempmain.xml "
'Dim FileProducts As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempProd.xml "
'Dim FileCustomer As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempCust.xml "
'Dim FileSupplier As String = Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings( "xmlPath ")) + "tempSupp.xml "
Dim FileTrans As String = Server.MapPath( "~ ") + "\sheet\ " + "tempmain.xml "
Dim FileProducts As String = Server.MapPath( "~ ") + "\sheet\ " + "tempProd.xml "
Dim FileCustomer As String = Server.MapPath( "~ ") + "\sheet\ " + "tempCust.xml "
Dim FileSupplier As String = Server.MapPath( "~ ") + "\sheet\ " + "tempSupp.xml "
Const TableTrans As String = "Mainform "
Const TableProducts As String = "tbl_UPLOAD_Products "
Const TableCustomer As String = "qry_Excel_CustomerInfo "
Const TableSupplier As String = "qry_Excel_SupplierInfo "
Const ExcelXMLTrans As String = "Mainform_Root_Map "
Const ExcelXMLProducts As String = "Products_Root_Map "
Const ExcelXMLSupplier As String = "dataroot_Map "
Const ExcelXMLCustomer As String = "dataroot_Map1 "