VB.NET Operate Excel-优快云博客

本文介绍了一种将数据表格转换为Excel文件的方法，包括创建Excel应用程序对象、填充工作表、保存及关闭文件等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Private Sub DataTransferExcel(ByVal tempDataTable As DataTable, ByVal strSaveFilePath As String)

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim rowIndex, colIndex As Integer
rowIndex = 1
colIndex = 0

Dim Col As DataColumn
Dim Row As DataRow

Dim DialogResultObj As DialogResult

Try
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
Catch ex As Exception
MessageBox.Show(ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try

For Each Col In tempDataTable.Columns
colIndex = colIndex + 1
xlApp.Cells(1, colIndex) = Col.ColumnName
Next

For Each Row In tempDataTable.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each Col In tempDataTable.Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = Row(Col.ColumnName)
Next
Next

xlApp.Visible = False
Try
xlBook.Saved = True
xlSheet.SaveAs(strSaveFilePath)
xlApp.UserControl = False

xlSheet = Nothing
xlApp.DisplayAlerts = False
xlBook.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoClose)
xlBook.Close()
xlBook = Nothing
xlApp.Quit()
xlApp.DisplayAlerts = True
xlApp = Nothing
GC.Collect()

DialogResultObj = MessageBox.Show("导出文件成功,是否打开该文件！", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)
If DialogResultObj = DialogResult.OK Then
'Dim ProPath As String
''Dim tempInt As Integer

''tempInt = AppFilePath.Length

'ProPath = AppFilePath & "Excel.exe"
''Shell(ProPath & strSaveFilePath, AppWinStyle.MaximizedFocus)

''Shell("C:\Program Files\Microsoft Office\Office\Excel.exe " & strSaveFilePath)

'Shell("ProPath " & strSaveFilePath)
//本来我是想用shell命令的，但是shell大家用的不多，而且路径Excel.exe难得指定。
Dim xlAppOpen As Excel.Application

Try
xlAppOpen = CType(CreateObject("Excel.Application"), Excel.Application)
xlAppOpen.Workbooks.Open(strSaveFilePath)
Catch ex As Exception
MessageBox.Show(ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try

xlAppOpen.Visible = True

End If
Catch ex1 As Exception
MessageBox.Show("导出文件失败!" & vbCrLf & ex1.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
'xlSheet = Nothing
'xlApp.DisplayAlerts = False
'xlBook.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoClose)
'xlBook.Close()
'xlBook = Nothing
'xlApp.Quit()
'xlApp.DisplayAlerts = True
'xlApp = Nothing
'GC.Collect()
End Try
End Sub