NPoI用于Excel表的导出，导入，可以很方便的操作execl中的样式，格式等
使用NPoI需要引入dll文件，
Npoi.dll和lonic.zip.dll.对于开发者主要使用Npoi.Hssf.userModel空间下的，
HSSfWorkbook,HSSfSheet,HSSfRow,HSSfCell，对应在Npoi.ss.UserModel空间下的，
Iworkbook,ISheet,IRow,ICell,分别对应Excel文件，工作薄，行，列。
1.首先引入dll
2.引入空间
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
3.具体操作步骤：（多个页面数据导入到一个execl中，存在不同的sheet里面，涉及到单元格合并，样式控制等）
3.1 页面中直接调用下载方法---DownloadToExecl()
public FileResult DownloadToExecl()
{
Guid classEndId = new Guid(Request.QueryString["ClassEndId"]);//获取classEndId值
string fileName = "execl的文件名";
MemoryStream stream = new MemoryStream();
try
{
//先实例化HSSFWorkbook，再创建多个sheet并命名，然后把不同sheet的数据源加入stream中，最后再一次性导出
HSSFWorkbook workbook = new HSSFWorkbook(); //创建Workbook
workbook.CreateSheet("第一个Sheet"); //创建sheet1
workbook.CreateSheet("第二个Sheet"); //创建sheet2
workbook.CreateSheet("第三个Sheet"); //创建sheet3
workbook.CreateSheet("第四个Sheet"); //创建sheet4
workbook.CreateSheet("第五个Sheet"); //创建sheet5
workbook.CreateSheet("第六个Sheet"); //创建sheet6
stream = ExportBasicInfoToExcel(classEndId, workbook); //导出到execl中--sheet1
stream = ExportReportInfoToExcel(classEndId, workbook); //导出到execl中--sheet2
stream = ExportCourseToExcel(classEndId, workbook); //导出到execl中--sheet3
stream = ExportKaoqinToExcel(classEndId, workbook); //导出到execl中--sheet4
stream = ExportSummarizeReportToExcel(classEndId, workbook); //导出到execl中--sheet5
stream = ExamScoreToExcel(classEndId, workbook, 5); //导出到execl中--sheet6
stream.Seek(0, SeekOrigin.Begin);
}
catch (Exception ex)
{
LogHelper.Error("DownloadToExecl()", ex.Message);
}
return File(stream, "application/vnd.ms-excel", fileName);
}
3.2 导出数据到execl的具体方法(以sheet1为例)-- ExportBasicInfoToExcel(classEndId, workbook);
public MemoryStream ExportBasicInfoToExcel(Guid classEndId, HSSFWorkbook workbook)
{
MemoryStream stream = new MemoryStream();
Stream fs = File(stream, "application/vnd.ms-excel", "DownloadClassEnd.xls").FileStream;
ISheet sheet = workbook.GetSheetAt(0);//获取sheet
sheet.DefaultRowHeightInPoints = 35;//设置高
sheet.DefaultColumnWidth = 30;//设置宽
//设置第一列显示标题
sheet.CreateRow(0).CreateCell(0).SetCellValue("客户技术培训班级报告");
sheet.CreateRow(1).CreateCell(0).SetCellValue("学员单位");
sheet.CreateRow(2).CreateCell(0).SetCellValue("学员人数");
sheet.CreateRow(3).CreateCell(0).SetCellValue("班 号");
sheet.CreateRow(4).CreateCell(0).SetCellValue("时 长");
sheet.CreateRow(5).CreateCell(0).SetCellValue("班 主 任");
sheet.CreateRow(6).CreateCell(0).SetCellValue("副班主任");
sheet.CreateRow(7).CreateCell(0).SetCellValue("培训项目");
sheet.CreateRow(8).CreateCell(0).SetCellValue("开始日期");
sheet.CreateRow(9).CreateCell(0).SetCellValue("结束日期");
sheet.CreateRow(10).CreateCell(0).SetCellValue("培训地点");
sheet.CreateRow(11).CreateCell(0).SetCellValue("合 同");
T_Op_ClassEnd classEnd = classEndBll.GetModel(classEndId);//获取基本信息
GetApplyInfoById(classEnd.ApplyID, out classCode, out time, out startTime, out endTime);
//设置第二列显示数据
sheet.GetRow(1).CreateCell(1).SetCellValue(classEnd.Company);
sheet.GetRow(2).CreateCell(1).SetCellValue(classEnd.LearnerCount.ToString());
sheet.GetRow(3).CreateCell(1).SetCellValue(classCode);
sheet.GetRow(4).CreateCell(1).SetCellValue(time);
sheet.GetRow(5).CreateCell(1).SetCellValue(classEnd.Instructor1RealName);
sheet.GetRow(6).CreateCell(1).SetCellValue(classEnd.Instructor2RealName);
sheet.GetRow(7).CreateCell(1).SetCellValue(classEnd.ProjectName);
sheet.GetRow(8).CreateCell(1).SetCellValue(startTime);
sheet.GetRow(9).CreateCell(1).SetCellValue(endTime);
sheet.GetRow(10).CreateCell(1).SetCellValue(classEnd.Address);
sheet.GetRow(11).CreateCell(1).SetCellValue(classEnd.Contract);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));//客户技术培训班级报告
//设置标题样式
ICellStyle titleStyle = workbook.CreateCellStyle();
IFont titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗样式
titleFont.FontHeightInPoints = 30; //设置字体大小
titleFont.Color = HSSFColor.Black.Index; //设置字体颜色
titleStyle.SetFont(titleFont);
titleStyle.Alignment = HorizontalAlignment.Center; //居中
titleStyle.VerticalAlignment = VerticalAlignment.Top;//垂直对齐
sheet.GetRow(0).GetCell(0).CellStyle = titleStyle;
//第一列单元格样式
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont(); //字体
font.Boldweight = (short)FontBoldWeight.Bold; //字体加粗样式
font.FontHeightInPoints = 15;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;//水平居中
style.VerticalAlignment = VerticalAlignment.Top;//垂直对齐
for (int i = 1; i <= 11; i++)
{
sheet.GetRow(i).GetCell(0).CellStyle = style;
}
//设置第二列的边框
ICellStyle cellStyle = workbook.CreateCellStyle();
IFont cellFont = workbook.CreateFont();
cellFont.FontHeightInPoints = 15;//设置字体大小
cellFont.Color = HSSFColor.Blue.Index;//设置字体颜色
cellStyle.SetFont(cellFont);
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
for (int i = 1; i <= 11; i++)
{
sheet.GetRow(i).GetCell(1).CellStyle = cellStyle;
sheet.SetColumnWidth(1, 40 * 500); //设置第二列的宽度
}
workbook.Write(fs);//保存文件
return stream;
}
