C#如何將DataTable匯出到Excel解決方案

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

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

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

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中寫資料的時候不用考慮這一點，這樣調用的時候更為方便