Excel结构:工作薄.xls->工作表sheet->单元格Cell.
使用NPOI导出Excel速度挺快。
微软内置的COM组件,需要依赖office,而且发布到IIS权限配置很麻烦。而且效率较低。
NPOI导出Excel主要C#代码:
写入Excel文件类:
public static class XlsWriter
{
public static IWorkbook Writer(XlsWorkSet workSet)
{
if (workSet == null || workSet.Sheets == null || workSet.Sheets.Length == 0)
throw new ArgumentNullException();
// 工作簿
IWorkbook work = RenderWorkBook(workSet.Version);
for (var i = 0; i < workSet.Sheets.Length; i += 1)
{
var layer = workSet.Sheets[i];
ISheet workSheet = RenderSheet(work, layer);
DataTable dataRow = layer.Rows;
for (int row = 0; row < dataRow.Rows.Count; row += 1)
{
IRow sheetRow = workSheet.CreateRow(row + layer.titleRowIndex);
for (int cols = 0; cols < layer.Fields.Length; cols += 1)
{
ICell cell = sheetRow.CreateCell(cols);
DataSetter(dataRow.Rows[row][layer.Fields[cols].FieldName], cell, layer.Fields[cols]);
}
}
AutoFitBodyWidth(workSheet, layer);
}
return work;
}
public static byte[] ToByte(this IWorkbook work)
{
using (MemoryStream ms = new MemoryStream())
{
work.Write(ms);
return ms.ToArray();
}
}
private static ISheet RenderSheet(IWorkbook work, XlsWorkSheet sheet)
{
ISheet workSheet = work.CreateSheet(sheet.SheetName);
if (sheet.Fields == null || sheet.Fields.Length == 0)
return workSheet;
IRow sheetRow = workSheet.CreateRow(0);
var format = work.CreateDataFormat();
XlsField field = null;
for (int i = 0; i < sheet.Fields.Length; i += 1)
{
field = sheet.Fields[i];
ICell cell = sheetRow.CreateCell(i);
cell.SetCellValue(field.Title);
ICellStyle style1 = work.CreateCellStyle();
IFont font1 = work.CreateFont();
field.HeaderS.Initilize(style1,