利用Spire.DataExport将数据(Database/DataTable)导成各种文件

借助Spire.DataExport可以很方便的将Database或者DataTable里的数据导成XLS, PDF and MS Word, HTML, MS ,XML, PDF, DBF, SQL Script, SYLK, DIF, CSV文件或者剪切板里而且机器上并不需要安装Microsoft Excel ,Microsoft Access ,Adobe Acrobat.转换后的数据可以输出到File,HttpResponse,Stream里.

当前的免费Spire.DataExport对日期类型的数据存在一个Bug:导出的数据会丢失时间部分,这个问题在以后的版本可能会解决掉,在没修正这个Bug前需要采取别的措施,比喻将时间类型转换成字符串类型.

如果要从Database里查询数据并输出成文件,需要指定相应的导出格式对象实例的DataSource属性为ExportSource.SqlCommand,并为Columns和SQLCommand属性赋相应的值.如果直接将DataTable输出成文件,则需要指定DataSource属性为ExportSource.DataTable.DataSource的默认值为ExportSource.SqlCommand.

要用Spire.DataExport组件,项目里需要引用Spire.License.dll,Spire.DataExport.ResourceMgr.dll,Spire.DataExport.dll.

以下示例将一个DataTable输出成XLS文件:

static
 
void
 ExportToXLS(DataTable dbTable, 
string
 strFileName)
{
       CellExport cellExport 
=
 
new
 CellExport();

        cellExport.ActionAfterExport 
=
 ActionType.None;
        cellExport.AutoFitColWidth 
=
 
true
;
        cellExport.DataFormats.CultureName 
=
 
"
en-US
"
;
        cellExport.DataFormats.Currency 
=
 
"
#,###,##0.00
"
;
        cellExport.DataFormats.DateTime 
=
 
"
yyyyhhmm HHmmss
"
;
        cellExport.DataFormats.Float 
=
 
"
#,###,##0.00
"
;
        cellExport.DataFormats.Integer 
=
 
"
#,###,##0
"
;
        cellExport.DataFormats.Time 
=
 
"
H:mm
"
;
        cellExport.SheetOptions.AggregateFormat.Font.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.CustomDataFormat.Font.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.DefaultFont.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.FooterFormat.Font.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.HeaderFormat.Font.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.HyperlinkFormat.Font.Color 
=
 CellColor.Blue;
        cellExport.SheetOptions.HyperlinkFormat.Font.Name 
=
 
"
Arial
"
;
        cellExport.SheetOptions.HyperlinkFormat.Font.Underline 
=
 XlsFontUnderline.Single;
        cellExport.SheetOptions.NoteFormat.Alignment.Horizontal 
=
 HorizontalAlignment.Left;
        cellExport.SheetOptions.NoteFormat.Alignment.Vertical 
=
 VerticalAlignment.Top;
        cellExport.SheetOptions.NoteFormat.Font.Bold 
=
 
true
;
        cellExport.SheetOptions.NoteFormat.Font.Name 
=
 
"
Tahoma
"
;
        cellExport.SheetOptions.NoteFormat.Font.Size 
=
 8F;
        cellExport.SheetOptions.TitlesFormat.Font.Bold 
=
 
true
;
        cellExport.SheetOptions.TitlesFormat.Font.Name 
=
 
"
Arial
"
;

        cellExport.DataSource 
=
 ExportSource.DataTable;
        cellExport.DataTable 
=
 dbTable;
        cellExport.FileName 
=
 strFileName;
        cellExport.SaveToFile();
 }

以下示例将一个DataTable输出成PDF文件:

static
 
void
 ExportToPDF(DataTable dbTable, 
string
 strFileName)
{
        PDFExport pdfExport 
=
 
new
 PDFExport();
        pdfExport.ActionAfterExport 
=
 ActionType.OpenView;
        pdfExport.DataFormats.CultureName 
=
 
"
en-US
"
;
        pdfExport.DataFormats.Currency 
=
 
"
#,###,##0.00000
"
;
        pdfExport.DataFormats.DateTime 
=
 
"
yyyy-M-d HH:mm
"
;
        pdfExport.DataFormats.Float 
=
 
"
#,###,##0.00
"
;
        pdfExport.DataFormats.Integer 
=
 
"
#,###,##0
"
;
        pdfExport.DataFormats.Time 
=
 
"
H:mm
"
;
        pdfExport.PDFOptions.PageOptions.Format 
=
 PageFormat.User;
        pdfExport.PDFOptions.PageOptions.Height 
=
 
11.67
;
        pdfExport.PDFOptions.PageOptions.MarginBottom 
=
 
0.78
;
        pdfExport.PDFOptions.PageOptions.MarginLeft 
=
 
1.17
;
        pdfExport.PDFOptions.PageOptions.MarginRight 
=
 
0.57
;
        pdfExport.PDFOptions.PageOptions.MarginTop 
=
 
0.78
;
        pdfExport.PDFOptions.PageOptions.Width 
=
 
8.25
;

        pdfExport.DataSource 
=
 ExportSource.DataTable;
        pdfExport.DataTable 
=
 dbTable;

        pdfExport.FileName 
=
 strFileName;
        pdfExport.SaveToFile();
}

以下示例将一个DataTable输出成HTML文件:

 

static
 
void
 ExportToHTML(DataTable dbTable, 
string
 strFileName)
        {
            HTMLExport htmlExport 
=
 
new
 HTMLExport();
            htmlExport.ActionAfterExport 
=
 ActionType.None;
            htmlExport.DataFormats.CultureName 
=
 
"
en-US
"
;
            htmlExport.DataFormats.Currency 
=
 
"
#,###,##0.00
"
;
            htmlExport.DataFormats.DateTime 
=
 
"
yyyy-M-d H:mm
"
;
            htmlExport.DataFormats.Float 
=
 
"
#,###,##0.00
"
;
            htmlExport.DataFormats.Integer 
=
 
"
#,###,##0
"
;
            
//
htmlExport.DataFormats.Time = "H:mm";


            htmlExport.HtmlStyle 
=
 HtmlStyle.MSMoney;
            htmlExport.HtmlTextOptions.Font 
=
 
new
 System.Drawing.Font(
"
Arial
"
, 8F);

            htmlExport.DataSource 
=
 ExportSource.DataTable;
            htmlExport.DataTable 
=
 dbTable;

            htmlExport.FileName 
=
 strFileName;
            htmlExport.SaveToFile();
        }