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中写数据的时候不用考虑这一点，这样调用的时候更为方便