欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

C#如何将DataTable导出到Excel解决方案

程序员文章站 2023-12-25 11:35:21
...
最近,由于公司项目中需要将系统内用户操作的所有日志进行转存备份,考虑到以后可能还需要还原,所以最后决定将日志数据备份到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中写数据的时候不用考虑这一点,这样调用的时候更为方便


更多C#如何将DataTable导出到Excel解决方案相关文章请关注PHP中文网!

上一篇:

下一篇: