C#將DataTable導入到Excel

最近，由於公司項目中需要將系統內用戶操作的所有日志進行轉存備份，考慮到以後可能還需要還原，所以最後決定將日志數據備份到Excel中。

下面是我項目當中Excel.cs這個類的全部代碼，通過這個類可以很容易地將DataTable中的數據導入到Excel方法中。

首先，必須要下載NPOI.dll這個程序集，
類代碼如下：

[csharp]
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共參考：
[csharp] 
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中寫數據的時候不用考慮這一點，這樣調用的時候更為方便