C#如何将DataTable导出到Excel解决方案
程序员文章站
2023-12-25 11:35:21
...
最近,由于公司项目中需要将系统内用户操作的所有日志进行转存备份,考虑到以后可能还需要还原,所以最后决定将日志数据备份到Excel中。
下面是我项目当中Excel.cs这个类的全部代码,通过这个类可以很容易地将DataTable中的数据导入到Excel方法中。
首先,必须要下载NPOI.dll这个程序集,
类代码如下:
下面是我项目当中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中写数据的时候不用考虑这一点,这样调用的时候更为方便
更多C#如何将DataTable导出到Excel解决方案相关文章请关注PHP中文网!