DataTable 导出Excel 之 NPOI

NPOI 强大的地方就是Excel里面90%功能，都能实现。坑爹的是需要引用5个DLL

?

官方例子:

static void Main(string[] args)

{

IWorkbook workbook = new XSSFWorkbook();

ISheet worksheet = workbook.CreateSheet("Sheet1");

?

for (int rownum = 0; rownum < 10000; rownum++)

{

IRow row = worksheet.CreateRow(rownum);

for (int celnum = 0; celnum < 20; celnum++)

{

ICell Cell = row.CreateCell(celnum);

Cell.SetCellValue("Cell: Row-" + rownum + ";CellNo:" + celnum);

}

}

?

FileStream sw = File.Create("test.xlsx");

workbook.Write(sw);

sw.Close();

}

?

自定义导出数据,设置字体,列宽,表头

?

?

DataTable dt =//

IWorkbook workbook = new XSSFWorkbook();

ISheet worksheet = workbook.CreateSheet("fulu");

?

//字体设置

IFont Font = workbook.CreateFont();

Font.FontName = "华文细黑";

ICellStyle CellStyle = workbook.CreateCellStyle();

CellStyle.SetFont(Font);

?

IFont FontHead = workbook.CreateFont();

FontHead.FontName = "华文细黑";

FontHead.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

ICellStyle CellHeadStyle = workbook.CreateCellStyle();

CellHeadStyle.SetFont(FontHead);

?

#region 定义DataTable要显示的数据（如果全部显示，忽略这种方法）

?

List<ExcelFormat> formatList = new List<ExcelFormat>();

int n = 0;

ExcelFormat item = new ExcelFormat();

item.ID = n;

item.ColTitle = "订单编号";

item.ColName = "OrderNo";

item.ColWidth = 20;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "购买时间";

item.ColName = "BuyTime";

item.ColWidth = 20;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "购买数量";

item.ColName = "BuyNum";

item.ColWidth = 10;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "订单金额";

item.ColName = "Payment";

item.ColWidth = 10;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "商品编号";

item.ColName = "ProductId";

item.ColWidth = 15;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "商品名称";

item.ColName = "ProductName";

item.ColWidth = 40;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = ++n;

item.ColTitle = "充值账号";

item.ColName = "ChargeAccount";

item.ColWidth = 20;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = n++;

item.ColTitle = "FL订单编号";

item.ColName = "FLOrderNo";

item.ColWidth = 20;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = n++;

item.ColTitle = "订单状态";

item.ColName = "OrderStatus";

item.ColWidth = 15;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = n++;

item.ColTitle = "买家地区";

item.ColName = "UserIP";

item.ColWidth = 20;

formatList.Add(item);

?

item = new ExcelFormat();

item.ID = n++;

item.ColTitle = "备注";

item.ColName = "Remark";

item.ColWidth = 40;

formatList.Add(item);

?

#endregion

?

//设置每一列的宽度

foreach (ExcelFormat format in formatList)

{

worksheet.SetColumnWidth(format.ID, format.ColWidth * 256);

}

?

//写入表头，从第行开始, 华文细黑+加粗

IRow rowhead = worksheet.CreateRow(0);

foreach (ExcelFormat format in formatList)

{

ICell Cell = rowhead.CreateCell(format.ID);

Cell.SetCellValue(format.ColTitle);

Cell.CellStyle = CellHeadStyle;

}

?

//写入行，从第行开始，华文细黑

for (int rownum = 1; rownum <= dt.Rows.Count; rownum++)

{

IRow row = worksheet.CreateRow(rownum);

foreach (ExcelFormat format in formatList)

{

ICell Cell = row.CreateCell(format.ID);

Cell.SetCellValue(dt.Rows[rownum - 1][format.ColName].ToString());

Cell.CellStyle = CellStyle;

}

}

?

//保存到Excel

string filename = string.Format("fulu{0}.xlsx", DateTime.Now.ToLongDateString());

FileStream sw = File.Create(filename);

workbook.Write(sw);

sw.Close();

?

MessageBox.Show("导出成功");

?

?

?

class ExcelFormat

{

public int ID { get; set; }

public string ColTitle { get; set; }

public string ColName { get; set; }

public int ColWidth { get; set; }

}

?

DataTable 导出Excel 之 NPOI