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(); }<b>本文来源gao@dai!ma.com搞$代^码!网7</b> /// <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(); } } }

喜欢 (0)赏

[搞代码]

分享 (0)