NPOI操作EXCEL导出

c#导出:
//加载模板
string readFile = Server.MapPath(“./xxx.xls”);
HSSFWorkbook WorkBook;
//打开模板到文件流
using (FileStream sFile = new FileStream(
readFile, FileMode.Open, FileAccess.Read))
{
//将文件流中的模板加载到工作薄
WorkBook = new HSSFWorkbook(sFile);
}
//设置内容
HSSFSheet sheet = WorkBook.GetSheet(“Sheet1”);
CellRangeAddress rangeDocMemo =
new CellRangeAddress(numRow, numRow, 0, colRow - 1);
sheet.AddMergedRegion(rangeDocMemo);
sheet.GetRow(numRow).GetCell(0).SetCellValue(
string.Format(“说明:{0}”, dr[“DOCUMENT_MEMO”].ToString()));
sheet.GetRow(numRow).GetCell(0).CellStyle =
CellStyle(WorkBook, “left”);
//设置响应头（文件名和文件格式）
//设置响应类型EXCEL
Response.ContentType = “application/vnd.ms-excel”;
//设置下载的EXCEL文件名
Response.AddHeader(“Content-Disposition”,
string.Format(“attachment;filename={0}”, “xxx.xls”));
Response.Clear();
//写入客户端
using (MemoryStream mstr = new MemoryStream())
{
WorkBook.Write(mstr);
Response.BinaryWrite(mstr.GetBuffer());
Response.End();
}
=============样式设置============================
HSSFWorkbook WorkBook;
HSSFCellStyle cellStyle = book.CreateCellStyle();
//上框
cellStyle.BorderTop = HSSFBorderFormatting.BORDER_THIN;
//右框
cellStyle.BorderRight = HSSFBorderFormatting.BORDER_THIN;
//左框
cellStyle.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
//下框
cellStyle.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
cellStyle.WrapText = true; //换行
//设置打印标题
ISheet=workBook.CreateSheet(“T”);
Tsheet.RepeatingRows = new NPOI.SS.Util.CellRangeAddress(0, 4, 0,int.MaxValue); //1-4 row
Tsheet.RepeatingColumns = new NPOI.SS.Util.CellRangeAddress(0, int.MaxValue, 0, 8); //1-8 column
Tsheet.PrintSetup.Scale = 100; //縮放

*字体设置***********************
HSSFFont font = book.CreateFont();
font.FontName = “Microsoft JhengHei”; //设置字体
//font.FontHeightInPoints = (short)11; //设置大小
font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; //加粗
cellStyle.SetFont(font);//设置字体

---

//居左
cellStyle.Alignment = HSSFCellStyle.ALIGN_LEFT;
//居右
cellStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
//居中
cellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
//垂直居中
cellStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;

注意:用poi导出excel，对于大报表或字体较多的报表，打开excel时会出现本工作薄不能再使用其他新字体的错误，这其实是程序的问题，
应该是生成excel表格单元时每次都new了HSSFFont对象，导致HSSFFont对象太多，这可能是poi的bug，解决方法只需将相同字体的表格单元用同一个HSSFFont对象，
不要每次都new就行了