Spire.DataExport支持将数据从数据源如数据库, ListView 和 DataTable 等导出到 Word, Excel,RTF, Access, PDF, XPS,HTML, XML, Text,CSV, DBF, SYLK,SQL Server,DIF 和剪贴板等格式。该文章将详细介绍如何使用Spire.DataExport 从DataTable导出数据到Excel, Word, PDF, HTML 等几种常用文件。
第一步:在从DataTable将数据导出到文件前,我们首先需要连接数据库,从数据源获得数据并生成一个DataTable. 获取需要导出的数据.
C#
// Office 07及以上版本
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Data.accdb";
//// Office 07以下版本
//string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=+ path +"
OleDbCommand oleDbCommand = new OleDbCommand(ConnectionString);
oleDbCommand.CommandText = "select * from [Sheet1]";
OleDbDataAdapter ada = new OleDbDataAdapter(oleDbCommand.CommandText, ConnectionString);
DataSet set = new DataSet();
ada.Fill(set);
DataSet dataSet = new System.Data.DataSet();
oleDbCommand.Connection = oleDbConnection;
DataTable datatable = set.Tables[0];
VB.NET
' Office 07及以上版本
Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Data.accdb"
' Office 07以下版本
'string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=+ path +"
Dim oleDbCommand As New OleDbCommand(ConnectionString)
oleDbCommand.CommandText = "select * from [Sheet1]"
Dim ada As New OleDbDataAdapter(oleDbCommand.CommandText, ConnectionString)
Dim [set] As New DataSet()
ada.Fill([set])
Dim dataSet As DataSet = New System.Data.DataSet()
oleDbCommand.Connection = oleDbConnection
Dim datatable As DataTable = [set].Tables(0)
第二步:将数据从DataTable导出到文件。Spire.DataExport支持将数据导出到Word, Excel,RTF,MS Access, PDF, XPS,HTML, XML, Text,CSV, DBF, SYLK,SQL Server,DIF 和剪贴板等格式文件。这里我们以Word, Excel, PDF, HTML, MS Access五种格式为例。
从DataTable导出数据到Excel
C#
Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
worksheet1.DataTable = datatable;
worksheet1.StartDataCol = ((System.Byte)(0));
worksheet1.AutoFitColWidth = true;
cellExport.Sheets.Add(worksheet1);
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport.SaveToFile("DatatoExcel.xls");
VB.NET
Dim cellExport As New Spire.DataExport.XLS.CellExport()
Dim worksheet1 As New Spire.DataExport.XLS.WorkSheet()
worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable
worksheet1.DataTable = datatable
worksheet1.StartDataCol = CByte(0)
worksheet1.AutoFitColWidth = True
cellExport.Sheets.Add(worksheet1)
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport.SaveToFile("DatatoExcel.xls")
从DataTable导出数据到RTF, 并将RTF存为Word格式
C#
Spire.DataExport.RTF.RTFExport rtfExport = new Spire.DataExport.RTF.RTFExport();
rtfExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
rtfExport.DataTable = datatable;
rtfExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
RTFStyle rtfStyle = new RTFStyle();
rtfStyle.FontColor = Color.Blue;
rtfStyle.BackgroundColor = Color.LightGreen;
rtfExport.RTFOptions.DataStyle = rtfStyle;
rtfExport.FileName = "DataToWord.doc";
rtfExport.SaveToFile();
VB.NET
Dim rtfExport As New Spire.DataExport.RTF.RTFExport()
rtfExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable
rtfExport.DataTable = datatable
rtfExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
Dim rtfStyle As New RTFStyle()
rtfStyle.FontColor = Color.Blue
rtfStyle.BackgroundColor = Color.LightGreen
rtfExport.RTFOptions.DataStyle = rtfStyle
rtfExport.FileName = "DataToWord.doc"
rtfExport.SaveToFile()
从DataTable导出数据到PDF
C#
Spire.DataExport.PDF.PDFExport PDFExport = new Spire.DataExport.PDF.PDFExport();
PDFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
PDFExport.DataTable = datatable;
PDFExport.DataEncoding = Spire.DataExport.PDF.PDFEncodingType.UTF8;
PDFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
PDFExport.SaveToFile("DatatoPDF.pdf");
VB.NET
Dim PDFExport As New Spire.DataExport.PDF.PDFExport()
PDFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable
PDFExport.DataTable = datatable
PDFExport.DataEncoding = Spire.DataExport.PDF.PDFEncodingType.UTF8
PDFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
PDFExport.SaveToFile("DatatoPDF.pdf")
从DataTable导出数据到HTML
C#
Spire.DataExport.HTML.HTMLExport HTMLExport = new Spire.DataExport.HTML.HTMLExport();
HTMLExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
HTMLExport.DataTable = datatable;
HTMLExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
HTMLExport.SaveToFile("DatatoHTML.html");
VB.NET
Dim HTMLExport As New Spire.DataExport.HTML.HTMLExport()
HTMLExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable
HTMLExport.DataTable = datatable
HTMLExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
HTMLExport.SaveToFile("DatatoHTML.html")
从DataTable导出数据到MS Access
C#
Spire.DataExport.Access.AccessExport accessExport = new
Spire.DataExport.Access.AccessExport();
accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
accessExport.DataTable = datatable;
accessExport.DatabaseName = "ToMdb.mdb";
accessExport.TableName = "ExportFromDatatable";
accessExport.SaveToFile();
VB.NET
Dim accessExport As New Spire.DataExport.Access.AccessExport()
accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable
accessExport.DataTable = datatable
accessExport.DatabaseName = "ToMdb.mdb"
accessExport.TableName = "ExportFromDatatable"
accessExport.SaveToFile()