C#将DataTable导入到Excel

最近，由于公司项目中需要将系统内用户操作的所有日志进行转存备份，考虑到以后可能还需要还原，所以最后决定将日志数据备份到Excel中。

下面是我项目当中Excel.cs这个类的全部代码，通过这个类可以很容易地将DataTable中的数据导入到Excel方法中。

首先，必须要下载NPOI.dll这个程序集，下载地址：http://npoi.codeplex.com/releases/view/49524

类代码如下：

using System;

using NPOI.HSSF;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.Collections;
using System.IO;
using System.Data;

namespace BackupAttach
{

    public class Excel
    {
        private HSSFWorkbook _workBook;
        private ISheet _wbSheet = null;
        private DataColumnCollection _columns = null;


        private int _col = 0;    //total columns
        private int _row = 0;    //total rows
        private int _sheet = 0;  //total sheets
        private int _sheetRowNum = 65536;  //each sheet allow rows

        public Excel()
        {
            InstanceWorkBook();
        }

        /// <summary>
        ///  实例方法
        /// </summary>
        /// <param name="sheetRowNum">单个表单允许的最大行数</param>
        public Excel(int sheetRowNum)
        {
            _sheetRowNum = sheetRowNum;
            InstanceWorkBook();
        }

        /// <summary>
        /// 实例方法
        /// </summary>
        /// <param name="columns">表头</param>
        public Excel(DataColumnCollection columns)
        {
            _columns = columns;
            InstanceWorkBook();
        }

        private void InstanceWorkBook()
        {
            /cretate WorkBook
            _workBook = new HSSFWorkbook();

            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "BaiyiTimes";
            _workBook.DocumentSummaryInformation = dsi;

            create a entry of SummaryInformation
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Etimes Secure Document System Log Backup";
            _workBook.SummaryInformation = si;
        }

        private DataColumnCollection GetColumns(DataColumnCollection columns)
        {
            return columns == null || columns.Count == 0 ? _columns : columns;
        }

        private ISheet GetSheet(ISheet sheet)
        {
            return sheet == null ? _wbSheet : sheet;
        }

        private void CreateHeader(ISheet sheet, DataColumnCollection columns)
        {
            _columns = GetColumns(columns);

            /create row of column
            var oRow = sheet.CreateRow(0);

            foreach (DataColumn column in _columns)
            {
                var oCell = oRow.CreateCell(_col);

                var style1 = _workBook.CreateCellStyle();
                style1.FillForegroundColor = HSSFColor.BLUE.index2;
                style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style1.Alignment = HorizontalAlignment.CENTER;
                style1.VerticalAlignment = VerticalAlignment.CENTER;

                var font = _workBook.CreateFont();
                font.Color = HSSFColor.WHITE.index;
                style1.SetFont(font);

                oCell.CellStyle = style1;
                var name = column.ColumnName;
                oCell.SetCellValue(name.ToString());

                _col++;
            }
            / header belong to rows
            _row++;
        }

        private void CreateHeader(ISheet sheet)
        {
            CreateHeader(sheet, null);
        }

        public ISheet CreateSheet()
        {
            return CreateSheet(null);
        }

        public ISheet CreateSheet(DataColumnCollection columns)
        {
            _wbSheet = _workBook.CreateSheet((_sheet + 1).ToString());
            CreateHeader(_wbSheet, columns);
            _sheet++;
            return _wbSheet;
        }

        public void SetRowValue(DataRowCollection rows, ISheet sheet)
        {
            _wbSheet = GetSheet(sheet);
            foreach (DataRow row in rows)
            {
                SetRowValue(row);
            }
        }

        public void SetRowValue(DataRowCollection rows)
        {
            SetRowValue(rows, null);
        }

        public void SetRowValue(DataRow row)
        {
            // create a new sheet
            if (_row % _sheetRowNum == 0)
            {
                CreateSheet();
            }
            var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);
            var obj = string.Empty;
            var cell = 0;
            foreach (DataColumn column in _columns)
            {
                obj = row[column.ColumnName].ToString();
                oRow.CreateCell(cell).SetCellValue(obj);
                cell++;
            }
            _row++;
        }

        public void SetProtectPassword(string password, string username)
        {
            _workBook.WriteProtectWorkbook(password, username);
        }

        public void SaveAs(string filePath)
        {
            if (File.Exists(filePath)) File.Delete(filePath);
            var file = new FileStream(filePath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }
    }
}

下面给出小Demo共参考：

public void DataTableToExcel(DataTable dt,string path)
        {
            //instance excel object
            //Excel excel = new Excel(65536);
            Excel excel = new Excel();

            //create a sheet 
            excel.CreateSheet(dt.Columns);

            //write value into rows
            //excel.SetRowValue(dt.Rows);
            foreach (DataRow row in dt.Rows)
            {
                excel.SetRowValue(row);
            }

            // set excel protected
            excel.SetProtectPassword("etimes2011@", "baiyi");

            // save excel file to local
            excel.SaveAs(path);
        }

缺点：如果要导入到Excel中的数据量较多时（几十万或者几百万行），全部一次性放到DataTable中可能会对内存消耗很大，建议每次导入的数据最好不要超过1000条，可采取分页查询的方式将数据导入Excel中。

优点：1997-2003版本的xls中每个表单最大只支持65536行，2010可以支持1048576行，考虑到客户机上安装的版本不一样，故Excel对象每个表单最大支持65536行，当表单到达最大行数时，Excel对象内部会自动创建新表单，在往Excel中写数据的时候不用考虑这一点，这样调用的时候更为方便