工作中的副产品,第一版是07年的
关键是解决了每次生成Excel文件后都会有一个Excel进程杀不掉的问题。
（有些同学说这样还是杀不掉。。呵呵）''根据传入的rst生成一个Excel表格，并保存成一个xls文件Sub CreatexcelFile(ByVal sFileName As String, ByVal rst As ADODB.Recordset)
On Error Resume Next
'' Dim oExcel As Excel.Application
'' Dim oExcelBook As Excel.Workbook
'' Dim oExcelSheet As Excel.Worksheet Dim oExcel
Dim oExcelBook
Dim oExcelSheet
Dim intCol As Long
Dim intRow As Long
Dim intRowAs As Long
If rst Is Nothing Then Exit Sub Set oExcel = CreateObject("Excel.Application")
Set oExcelBook = oExcel.Workbooks.Add
Set oExcelSheet = oExcelBook.Worksheets(1)
With rst
.MoveFirst
'输出内容
Do While Not .EOF
For intCol = 0 To .Fields.Count - 1
oExcelSheet.Cells(intRow + 1, intCol + 1) = .Fields(intCol).Value
Next intCol
.MoveNext
intRow = intRow + 1
Loop
End With
'关闭所有提示
oExcel.AlertBeforeOverwriting = False
oExcel.PromptForSummaryInfo = False
oExcel.ShowStartupDialog = False
oExcelBook.SaveAs sFileName
'自动杀掉Excel进程
'xlAutoOpen=1;xlAutoClose=2
oExcelBook.RunAutoMacros (1) '运行EXCEL启动宏
oExcelBook.RunAutoMacros (2) '运行EXCEL关闭宏
oExcel.Quit Set oExcel = Nothing
Set oExcelBook = Nothing
Set oExcelSheet = Nothing
End Sub
关键是解决了每次生成Excel文件后都会有一个Excel进程杀不掉的问题。
（有些同学说这样还是杀不掉。。呵呵）''根据传入的rst生成一个Excel表格，并保存成一个xls文件Sub CreatexcelFile(ByVal sFileName As String, ByVal rst As ADODB.Recordset)
On Error Resume Next
'' Dim oExcel As Excel.Application
'' Dim oExcelBook As Excel.Workbook
'' Dim oExcelSheet As Excel.Worksheet Dim oExcel
Dim oExcelBook
Dim oExcelSheet
Dim intCol As Long
Dim intRow As Long
Dim intRowAs As Long
If rst Is Nothing Then Exit Sub Set oExcel = CreateObject("Excel.Application")
Set oExcelBook = oExcel.Workbooks.Add
Set oExcelSheet = oExcelBook.Worksheets(1)
With rst
.MoveFirst
'输出内容
Do While Not .EOF
For intCol = 0 To .Fields.Count - 1
oExcelSheet.Cells(intRow + 1, intCol + 1) = .Fields(intCol).Value
Next intCol
.MoveNext
intRow = intRow + 1
Loop
End With
'关闭所有提示
oExcel.AlertBeforeOverwriting = False
oExcel.PromptForSummaryInfo = False
oExcel.ShowStartupDialog = False
oExcelBook.SaveAs sFileName
'自动杀掉Excel进程
'xlAutoOpen=1;xlAutoClose=2
oExcelBook.RunAutoMacros (1) '运行EXCEL启动宏
oExcelBook.RunAutoMacros (2) '运行EXCEL关闭宏
oExcel.Quit Set oExcel = Nothing
Set oExcelBook = Nothing
Set oExcelSheet = Nothing
End Sub
不过很疑惑，为什么会出现Excel进程杀不掉的现象呢？因为你是新建的工作簿啊，而且只是写入了数据，里面应该没有宏的。估计出现这种现象是没有加workbook.close之类的语句造成的。
oExcelBook.close
oExcel.Quit Set oExcelSheet = Nothing
Set oExcelBook = Nothing
Set oExcel = Nothing
.MoveFirst
'字段名
For intCol = 0 To .Fields.Count - 1
oExcelSheet.Cells(intRow + 1, intCol + 1) = .Fields(intCol).name
Next intCol
using Excel = Microsoft.Office.Interop.Excel;Excel.Application myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
myApp.Visible = false; ... myApp.DisplayAlerts = false;
myApp.AlertBeforeOverwriting = false;
//if (!Directory.Exists(myExcelFileFolder)) Directory.CreateDirectory(myExcelFileFolder);
myWorkBook.SaveAs(myExcelFileFolder + "\\" + myExcelFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); myWorkSheet = null;
myWorkSheet2 = null;
myWorkBook = null;
}
catch (Exception ex)
{
...
}
finally
{
myApp.Quit();
myApp = null;
}