这是我写的一个方法,也找了不少资料,参数是,dataset,要生成的excel的路径,根据strColumnName 这一列的值生成不同的excel文件名称为1.xls,2.xsl....... 希望对你有帮助.要先添加对excel的引用,在引用中的com中选 Public Sub CreateExcel(ByVal _strExcelPath As String, ByVal _ExcelDataSet As DataSet, ByVal _strColumnName As String) Dim xlSheet As Excel.Worksheet Dim xlBook As Excel.Workbook Dim xlDBTable As Excel.DataTable Dim strPath As String Try 'define an arry list Dim arry As ArrayList arry = New ArrayList Dim dr As DataRow Dim i As Integer = 0 Dim strAll As String 'first,get the deffent column's value 'then send the values into a arrylist 'define a dataview in order to sort Dim dv As DataView dv = New DataView(_ExcelDataSet.Tables(0)) 'sort by the column dv.Sort = _strColumnName Dim tmp As String Dim drv As DataRowView For Each drv In dv If tmp <> drv.Row(_strColumnName) Then tmp = Convert.ToString(drv.Row(_strColumnName)) arry.Add(tmp) End If Next Dim k As Integer = 0 For k = 0 To arry.Count - 1 Dim xlApp As New Excel.Application xlBook = xlApp.Workbooks.Add xlSheet = xlBook.Worksheets("sheet1") 'the file's full name strPath = _strExcelPath & "\" & k + 1 & ".xls" Dim col As DataColumn Dim colIndex As Integer = 0 'For Each col In _ExcelDataSet.Tables(0).Columns ' colIndex = colIndex + 1 ' 'set the cell's column name ' xlApp.Cells(1, colIndex) = col.ColumnName 'Next Dim row As DataRow Dim rowIndex As Integer = 0 'base on the arrylist's value,circle For Each row In _ExcelDataSet.Tables(0).Select(_strColumnName & "='" & Convert.ToString(arry(k)) & "'") 'add the cell's value rowIndex = rowIndex + 1 colIndex = 0 For Each col In _ExcelDataSet.Tables(0).Columns colIndex = colIndex + 1 xlApp.Cells(rowIndex, colIndex) = row(col.ColumnName) Next Next xlBook.SaveAs(strPath, xlApp.ActiveWorkbook.FileFormat, "", "", xlApp.ActiveWorkbook.ReadOnlyRecommended, xlApp.ActiveWorkbook.CreateBackup, Excel.XlSaveAsAccessMode.xlShared.xlShared, xlApp.ActiveWorkbook.ConflictResolution, False, "", "") 'kill the process xlBook.Close() xlApp.Quit() xlApp = Nothing Next Catch ex As Exception MsgBox(ex.Message) End Try End Sub
直接用数据库定时导出就好了.
你要实在想用程序来做,说说你做的是winform还是webform
1.采用数据库的方式,定时调度. 2.B/S建议用Window Service. 3.C/S 定时器即可~
至于读取、生成、保存Excel,网上例子就很多了。
不是手动保存,那位大侠知道的指导下,谢谢。要是有什么原代码就太感谢了。
然后一个WorkBook
然后添加一个sheet
然后就是操作Rang() getRang SetText等等.以前做过.不复杂的
希望对你有帮助.要先添加对excel的引用,在引用中的com中选
Public Sub CreateExcel(ByVal _strExcelPath As String, ByVal _ExcelDataSet As DataSet, ByVal _strColumnName As String)
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim xlDBTable As Excel.DataTable
Dim strPath As String Try 'define an arry list
Dim arry As ArrayList
arry = New ArrayList
Dim dr As DataRow
Dim i As Integer = 0
Dim strAll As String
'first,get the deffent column's value
'then send the values into a arrylist
'define a dataview in order to sort
Dim dv As DataView
dv = New DataView(_ExcelDataSet.Tables(0))
'sort by the column
dv.Sort = _strColumnName
Dim tmp As String
Dim drv As DataRowView
For Each drv In dv
If tmp <> drv.Row(_strColumnName) Then
tmp = Convert.ToString(drv.Row(_strColumnName))
arry.Add(tmp)
End If
Next Dim k As Integer = 0
For k = 0 To arry.Count - 1
Dim xlApp As New Excel.Application
xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets("sheet1") 'the file's full name
strPath = _strExcelPath & "\" & k + 1 & ".xls"
Dim col As DataColumn
Dim colIndex As Integer = 0
'For Each col In _ExcelDataSet.Tables(0).Columns
' colIndex = colIndex + 1
' 'set the cell's column name
' xlApp.Cells(1, colIndex) = col.ColumnName
'Next Dim row As DataRow
Dim rowIndex As Integer = 0
'base on the arrylist's value,circle
For Each row In _ExcelDataSet.Tables(0).Select(_strColumnName & "='" & Convert.ToString(arry(k)) & "'")
'add the cell's value
rowIndex = rowIndex + 1
colIndex = 0
For Each col In _ExcelDataSet.Tables(0).Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = row(col.ColumnName) Next Next xlBook.SaveAs(strPath, xlApp.ActiveWorkbook.FileFormat, "", "", xlApp.ActiveWorkbook.ReadOnlyRecommended, xlApp.ActiveWorkbook.CreateBackup, Excel.XlSaveAsAccessMode.xlShared.xlShared, xlApp.ActiveWorkbook.ConflictResolution, False, "", "") 'kill the process
xlBook.Close()
xlApp.Quit()
xlApp = Nothing Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub