使用NPOI导出DataTable到Excel

最新推荐文章于 2020-05-14 10:28:34 发布

weixin_30666401

最新推荐文章于 2020-05-14 10:28:34 发布

阅读量97

点赞数

原文链接：http://www.cnblogs.com/zhaohuayang/p/export-data-to-excel.html

版权

　　使用C#对DataTable导出到Excel是我们工作当中比较多用到的场景，微软提供了Microsoft.Office.Interop.Excel组件可以进行操作，但是该组件在数据量大的时候速度很慢，可以参考stackoverflow上的提问（Microsoft.Office.Interop.Excel really slow），所以我又找了一些其它的开源组件，比如著名的OpenXML，但是它不支持.NET 2.0了。终于在stackoverflow上看到有人说NPOI，试了一下，速度果然很快，而且从.NET2.0到4.0都支持，附一下导出的代码吧

using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public class NpoiExport
{
    const int MaximumNumberOfRowsPerSheet = 65500;
    protected HSSFWorkbook workbook { get; set; }

    public NpoiExport()
    {
        this.workbook = new HSSFWorkbook();
    }

    protected ISheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData)
    {
        var sheet = this.workbook.CreateSheet("Sheet1");

        // Create the header row
        var row = sheet.CreateRow(0);

        for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
        {
            var cell = row.CreateCell(colIndex);
            cell.SetCellValue(exportData.Columns[colIndex].ColumnName);
        }

        return sheet;
    }

    public void ExportDataTableToWorkbook(DataTable exportData)
    {
        // Create the header row cell style
        var headerLabelCellStyle = this.workbook.CreateCellStyle();
        //headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
        var headerLabelFont = this.workbook.CreateFont();
        headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
        headerLabelCellStyle.SetFont(headerLabelFont);

        var sheet = CreateExportDataTableSheetAndHeaderRow(exportData);
        var currentNPOIRowIndex = 1;
        var sheetCount = 1;

        for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
        {
            if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
            {
                sheetCount++;
                currentNPOIRowIndex = 1;

                sheet = CreateExportDataTableSheetAndHeaderRow(exportData);
            }

            var row = sheet.CreateRow(currentNPOIRowIndex++);

            for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
            {
                var cell = row.CreateCell(colIndex);
                cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
            }
        }
    }

    public void ExportDataTableToExcel(DataTable exportData, string filePath)
    {
        this.ExportDataTableToWorkbook(exportData);
        using (FileStream sw = File.Create(filePath, 10000, FileOptions.WriteThrough))
        {
            this.workbook.Write(sw);
        }
    }

    
}