如何将数据表导出到vb.net中的xls文件 [英] how to export datatable to xls file in vb.net
问题描述
亲爱的所有人,
我有一个数据表,我想将这个数据表的值导出到.xls文件(Excel文件)而不参考excel程序集,因为我没有安装MS Excel我的电脑,但我开放办公室软件来处理xls文件。
所以任何人都可以告诉我如何将我的数据表导出为.xls文件格式。
问候,
BlueSathish
我可以推荐ClosedXml。它是开源的,易于使用..
您可以从 codeplex 并在项目中添加引用。
var wb = < span class =code-keyword> new XLWorkbook();
wb.Worksheets.Add(yourDataTable);
wb.SaveAs( myExcelFile.xlsx);
PS .. ClosedXml仅支持.xlsx!
Imports Microsoft
Imports Microsoft.Office.Interop
并添加引用dllMicrosoft.Office.Interop.Excel
导出数据的功能如下:
''' < 摘要 >
< span class =code-summarycomment>''' 将Excel文件写为给定文件名和给定数据。
''' < / summary >
''' < param < span class =code-summarycomment> name =a_sFilename > 完整档案创建Excel文件的名称。< / param >
''' < ; param name =a_sData< /秒pan> > 要填充excel shhet的数据集中的数据。< / param >
''' < param name =a_sFileTitle > Excel文件的标题。< / param >
''' < param name =a_sErrorMessage> 输出参数包含错误消息。< / param >
''' < 返回 > bool < / returns >
公共 功能 ExportToExcel( ByVal a_sFilename 作为 字符串, ByVal a_sData As DataSet, ByVal a_sFileTitle < span class =code-keyword>作为 字符串, ByRef a_sErrorMessage 作为 字符串)作为 Boolean
a_sErrorMessage = 字符串 .Empty
Dim bRetVal 作为 布尔 = 错误
Dim dsDataSet As DataSet = Nothing
尝试
dsDataSet = a_sData
Dim xlObject As Excel.Application = Nothing
Dim xlWB As Excel.Workbook = Nothing
Dim xlSh As Excel.Worksheet = Nothing
< span class =code-keyword> Dim rg As Excel.Range = Nothing
尝试
xlObject = 新 Excel.Application()
xlObject。 AlertBeforeOverwriting = False
xlObject.DisplayAlerts = False
' '这个添加一个新的woorkbook,你可以从文件中打开工作簿
xlWB = xlObject。 Workbooks.Add(Type.Missing)
xlWB.SaveAs(a_sFilename, 56 ,Missing.Value,Missing.Value,Missing.Value,Missing.Value,_
Excel.XlSaveAsAccessMode.xlNoChange,Missing .Value,Missing.Value,Missing.Value,Missing.Value)
xlSh = DirectCast (xlObject.ActiveWorkbook.ActiveSheet,Excel.Worksheet )
' Dim sUpperRange As String =A1
' Dim sLastCol As String =AQ
' Dim sLowerRange As String = sLastCol +(dsDataSet.Tables(0).Rows.Count + 1).ToString()
对于 j = 0 到 dsDataSet.Tables( 0 )。列。计数 - 1
xlSh.Cells( 1 ,j + 1 )= _
dsDataSet.Tables( 0 )。列(j).ToString()
xlSh。单元格( 1 ,j + 1 )。Font.Bold = 真
下一步
对于 i = 1 dsDataSet.Tables( 0 ) .Rows.Count
对于 j = 0 dsDataSet.Tables( 0 )。Columns.Count - 1
xlSh .Cells(i + 1 ,j + 1 )= _
dsDataSet.Tables( 0 )。行(i - 1 )(j).ToString()
下一步
下一页
xlSh.Columns.AutoFit()
' rg = xlSh.Range(sUpperRange,sLowerRange)
' rg.Value2 = GetData( dsDataSet.Tables(0))
' xlSh.Range(A1 ,sLastCol& 1)。Font.Bold = True
' xlSh.Range(A1 ,sLastCol&1)。HorizontalAlignment = XlHAlign.xlHAlignCenter
' xlSh.Range (sUpperRange,sLowerRange).EntireColumn.AutoFit()
如果 String .IsNullOrEmpty(a_sFileTitle)< span class =code-keyword>然后
xlObject.Caption = 无标题
其他
xlObject.Caption = a_sFileTitle
结束 如果
xlWB.Save()
bRetVal = True
< span class =code-keyword> Catch ex As System.Runtime.InteropServices.COMException
如果 ex.ErrorCode = -2147221164 那么
a_sErrorMessage = 导出时出错:请安装Microsoft Office(Excel)以使用导出到Excel功能。
ElseIf ex.ErrorCode = -2146827284 然后
a_sErrorMessage = 导出错误:Excel仅允许工作表中最多65,536行。
否则
a_sErrorMessage =((< span class =code-string> 导出错误:& ex.Message)+ Environment.NewLine& 错误:)+ ex.ErrorCode
结束 如果
Catch ex 作为异常
a_sErrorMessage = 导出错误:& ex.Message
最后
尝试
如果 xlWB IsNot Nothing 然后
xlWB.Close( Nothing , Nothing , Nothing )
结束 如果
xlObject.Workbooks.Close()
xlObject.Quit()
如果 rg IsNot Nothing 然后
Marshal.ReleaseComObject(rg)
结束 如果
如果 xlSh IsNot Nothing 然后
Marshal.ReleaseComObject(xlSh)
End 如果
如果 xlWB IsNot Nothing 然后
Marshal.ReleaseComObject(xlWB)
结束 如果
如果 xlObject IsNot 没什么 然后
Marshal.ReleaseComObject (xlObject)
结束 如果
Catch
结束 尝试
xlSh = 没什么
xlWB = 没什么
xlObject = 没什么
' 强制最终清理!
GC.Collect()
GC.WaitForPendingFinalizers()
结束 尝试
Catch ex As 异常
a_sErrorMessage = 导出错误:& ex.Message
结束 尝试
返回 bRetVal
结束 功能
试试这个代码
快乐编码!
:)
公共函数GridExport(ByVal DGV As DataGridView)As Boolean
如果DGV .RowCount > 0 然后
Dim filename As String = Application.StartupPath& \ Export_ + Format(现在, hhmmss)。ToString + 。xls
DGV.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
DGV.SelectAll()
IO.File.WriteAllText(filename,DGV.GetClipboardContent()。GetText.TrimEnd)
DGV.ClearSelection()
Process.Start(文件名)
返回True
否则
返回False
结束如果
结束函数
Dear All,
I'm having one datatable, i want to export this datatable's values to .xls file(Excel file) without referring the excel assemblies because i didn't install MS Excel in my pc but instead i've Open office software to process xls files.
So anyone give me the idea how to export my Datatable to .xls file format.
Regards,
BlueSathish
I can recommend ClosedXml. It's open source and easy to use..
You download it from codeplex and add reference in your project.
var wb = new XLWorkbook(); wb.Worksheets.Add(yourDataTable); wb.SaveAs("myExcelFile.xlsx");
PS.. ClosedXml only supports .xlsx!
Imports Microsoft Imports Microsoft.Office.Interop
and add reference dll "Microsoft.Office.Interop.Excel"
function to export data is given below
''' <summary> ''' Write Excel file as given file name with given data. ''' </summary> ''' <param name="a_sFilename">full file name for create excel file.</param> ''' <param name="a_sData">data in dataset to be fill in excel shhet.</param> ''' <param name="a_sFileTitle">Title of Excel file.</param> ''' <param name="a_sErrorMessage">output parameter contains error message if error occurrs.</param> ''' <returns>bool</returns> Public Function ExportToExcel(ByVal a_sFilename As String, ByVal a_sData As DataSet, ByVal a_sFileTitle As String, ByRef a_sErrorMessage As String) As Boolean a_sErrorMessage = String.Empty Dim bRetVal As Boolean = False Dim dsDataSet As DataSet = Nothing Try dsDataSet = a_sData Dim xlObject As Excel.Application = Nothing Dim xlWB As Excel.Workbook = Nothing Dim xlSh As Excel.Worksheet = Nothing Dim rg As Excel.Range = Nothing Try xlObject = New Excel.Application() xlObject.AlertBeforeOverwriting = False xlObject.DisplayAlerts = False ''This Adds a new woorkbook, you could open the workbook from file also xlWB = xlObject.Workbooks.Add(Type.Missing) xlWB.SaveAs(a_sFilename, 56, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _ Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value) xlSh = DirectCast(xlObject.ActiveWorkbook.ActiveSheet, Excel.Worksheet) 'Dim sUpperRange As String = "A1" 'Dim sLastCol As String = "AQ" 'Dim sLowerRange As String = sLastCol + (dsDataSet.Tables(0).Rows.Count + 1).ToString() For j = 0 To dsDataSet.Tables(0).Columns.Count - 1 xlSh.Cells(1, j + 1) = _ dsDataSet.Tables(0).Columns(j).ToString() xlSh.Cells(1, j + 1).Font.Bold = True Next For i = 1 To dsDataSet.Tables(0).Rows.Count For j = 0 To dsDataSet.Tables(0).Columns.Count - 1 xlSh.Cells(i + 1, j + 1) = _ dsDataSet.Tables(0).Rows(i - 1)(j).ToString() Next Next xlSh.Columns.AutoFit() 'rg = xlSh.Range(sUpperRange, sLowerRange) 'rg.Value2 = GetData(dsDataSet.Tables(0)) 'xlSh.Range("A1", sLastCol & "1").Font.Bold = True 'xlSh.Range("A1", sLastCol & "1").HorizontalAlignment = XlHAlign.xlHAlignCenter 'xlSh.Range(sUpperRange, sLowerRange).EntireColumn.AutoFit() If String.IsNullOrEmpty(a_sFileTitle) Then xlObject.Caption = "untitled" Else xlObject.Caption = a_sFileTitle End If xlWB.Save() bRetVal = True Catch ex As System.Runtime.InteropServices.COMException If ex.ErrorCode = -2147221164 Then a_sErrorMessage = "Error in export: Please install Microsoft Office (Excel) to use the Export to Excel feature." ElseIf ex.ErrorCode = -2146827284 Then a_sErrorMessage = "Error in export: Excel allows only 65,536 maximum rows in a sheet." Else a_sErrorMessage = (("Error in export: " & ex.Message) + Environment.NewLine & " Error: ") + ex.ErrorCode End If Catch ex As Exception a_sErrorMessage = "Error in export: " & ex.Message Finally Try If xlWB IsNot Nothing Then xlWB.Close(Nothing, Nothing, Nothing) End If xlObject.Workbooks.Close() xlObject.Quit() If rg IsNot Nothing Then Marshal.ReleaseComObject(rg) End If If xlSh IsNot Nothing Then Marshal.ReleaseComObject(xlSh) End If If xlWB IsNot Nothing Then Marshal.ReleaseComObject(xlWB) End If If xlObject IsNot Nothing Then Marshal.ReleaseComObject(xlObject) End If Catch End Try xlSh = Nothing xlWB = Nothing xlObject = Nothing ' force final cleanup! GC.Collect() GC.WaitForPendingFinalizers() End Try Catch ex As Exception a_sErrorMessage = "Error in export: " & ex.Message End Try Return bRetVal End Function
try this code
Happy Coding!
:)
Public Function GridExport(ByVal DGV As DataGridView) As Boolean If DGV.RowCount > 0 Then Dim filename As String = Application.StartupPath & "\Export_" + Format(Now, "hhmmss").ToString + ".xls" DGV.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText DGV.SelectAll() IO.File.WriteAllText(filename, DGV.GetClipboardContent().GetText.TrimEnd) DGV.ClearSelection() Process.Start(filename) Return True Else Return False End If End Function
这篇关于如何将数据表导出到vb.net中的xls文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!