因工作需要，需要导出每个人在魔噩一个时间区间内的所有排班，并支持修改后在上传的功能，使用到了 NPOI 程序，决定选择使用 NPOI 导出 EXCEL 最主要的一点原因就是，NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。
下面大致介绍一下 NOPI 的优势：
1、您可以完全免费使用该框架
2、包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)
3、专业的技术支持服务(24*7全天候) (非免费)
4、支持处理的文件格式包括xls, xlsx, docx.
5、采用面向接口的设计架构( 可以查看 NPOI.SS 的命名空间)
6、同时支持文件的导入和导出
7、基于.net 2.0 也支持xlsx 和 docx格式(当然也支持.net 4.0)
8、来自全世界大量成功且真实的测试Cases
9、大量的实例代码
11、你不需要在服务器上安装微软的Office，可以避免版权问题。
12、使用起来比Office PIA的API更加方便，更人性化。
13、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。
14、不仅仅对与Excel可以进行操作，对于doc、ppt文件也可以做对应的操作
NPOI之所以强大，并不是因为它支持导出Excel，而是因为它支持导入Excel，并能“理解”OLE2文档结构，这也是其他一些Excel读写库比较弱的方面。通常，读入并理解结构远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在Excel读写库中，也体现在pdf读写库中，目前市面上大部分的pdf库仅支持生成，不支持导入。
话不多说，直接步入正题：
我这里是 ASP.NET 开发中的项目;
我这里是已在开发中的项目，所以前面什么怎么新建项目等等就不介绍了，直接从引用 NPOI 开始讲起；
首先：添加如下引用 ：
若是没有事先下载好的 dll ,也可以在 “工具” -> “NuGet 程序包管理器” -> “管理解决方案的 NeGet程序包(N)…” 在弹出的 “管理 NeGet 程序包” 框选择 “联机”，然后再右上角的搜索框中搜索 “NPOI“ 如下图，找到之后就可以直接安装了，
引用了 NPOI 的程序集之后，在代码中引入对应的命名空间：
using NPOI;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using NPOI.OpenXml4Net;
using NPOI.OpenXmlFormats;
后面直接上代码，对应代码都有注释：
//创建 Excel 工作薄
//NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");
***// 这里需要注意一点，使用的是HSSF类，只支持2007以前的excel（文件扩展名为xls），而XSSF支持07以后的(.xls,xlsx)，所在导入之前要把excel文件降到07，而我们这里直接用的 XSSF 导出兼容性的 excel***
//设置标题样式
ICellStyle style1 = book.CreateCellStyle();
style1.Alignment = HorizontalAlignment.Center;
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.Alignment = HorizontalAlignment.Center; //设置居中
style1.VerticalAlignment = VerticalAlignment.Center; //垂直居中
IFont font = book.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeight = 200;
font.FontName = "宋体"; //设置字体
style1.SetFont(font);
// 设置内容样式
ICellStyle style2 = book.CreateCellStyle();
style2.BorderBottom = BorderStyle.Thin;
style2.BorderLeft = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
style2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style2.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");// 单元格设置文本格式
IFont font2 = book.CreateFont();
font2.FontName = "宋体"; //设置字体
font2.FontHeightInPoints = (short)10;
style2.SetFont(font2);
//设置内容的字体颜色，某些单元格需要根据单元格内容设置字体
ICellStyle rosterStyle = book.CreateCellStyle();
rosterStyle.BorderBottom = BorderStyle.Thin;
rosterStyle.BorderLeft = BorderStyle.Thin;
rosterStyle.BorderRight = BorderStyle.Thin;
rosterStyle.BorderTop = BorderStyle.Thin;
rosterStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
**rosterStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");// 单元格设置文本格式**
IFont rosterFont = book.CreateFont();
rosterFont.FontName = "宋体"; //设置字体
rosterFont.FontHeightInPoints = (short)10;
...... 中间省略部分字体样式设置代码 ......
//写标题行
// 根据需求，只有前三列的标题是固定存在的，后面的列都是根据数据库返回的数据动态生成的，所以有一个 for 循环 CreateCell()
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
row1.Height = 600;
ICell cell1 = row1.CreateCell(0);
cell1.CellStyle = style1;
cell1.SetCellValue("Employee ID");
ICell cell2 = row1.CreateCell(1);
cell2.CellStyle = style1;
cell2.SetCellValue("Name");
ICell cell3 = row1.CreateCell(2);
cell3.CellStyle = style1;
cell3.SetCellValue("Position");
TimeSpan ts = DateTime.Parse(c.EndDate.ToString()) - DateTime.Parse(c.BgnDate.ToString());
var columnMaxIndex = ts.Days + 1;
DateTime columnBgnDate = DateTime.Parse(c.BgnDate.ToString());
for (var i = 3; i < columnMaxIndex + 3; i++)
{
ICell cell = row1.CreateCell(i);
cell.CellStyle = style1;
cell.SetCellValue(columnBgnDate.Year.ToString() + "/" + columnBgnDate.Month.ToString().PadLeft(2, '0') + "/" + columnBgnDate.Day.ToString().PadLeft(2, '0'));
sheet.SetColumnWidth(i, 15 * 256); //设置列宽
columnBgnDate = columnBgnDate.AddDays(1);
}
//下拉框数据源
var ArrayRosterSetup = new List<string>();
var ArraryRosterSetupFormat = new List<string>();
var strRosterSetup = "* ";
foreach (var roster in rosterSetup)
{
ArrayRosterSetup.Add(roster.roster_code); //下拉框数据源
ArraryRosterSetupFormat.Add(roster.roster_code + "|" + roster.ros_type + "|" + roster.color_id.ToString());
strRosterSetup = strRosterSetup + roster.ros_type + " - " + roster.ros_type_desc + ", ";
}
strRosterSetup = strRosterSetup.Remove(strRosterSetup.LastIndexOf(','), 1);//移除最后一个 ','
strRosterSetup = strRosterSetup.Length > 0 ? strRosterSetup + "." : ""; //最后行的描述内容
#region 写数据
//写数据
var rowIndex = 1;
fields.GroupBy(r => r.emp_id).ToList().ForEach(entity => {
try
{
var emp_id = entity.FirstOrDefault().emp_id;
var historyInfo = historyService.GetAllBy(r => r.emp_id == emp_id && r.eff_date <= end_date).OrderByDescending(r => r.empn_no).OrderByDescending(r => r.eff_date).OrderByDescending(r => r.eff_date_seq).FirstOrDefault();
var empn_no = historyInfo.empn_no;
var leave = service.select_ta_roster_overrding_leave(bgn_date, end_date, entity.FirstOrDefault().emp_id, empn_no);
NPOI.SS.UserModel.IRow rowData = sheet.CreateRow(rowIndex);
rowData.Height = 360;
var rowEmpId = rowData.CreateCell(0);
rowEmpId.SetCellValue(entity.FirstOrDefault().emp_id);
rowEmpId.CellStyle = style2;
var rowName = rowData.CreateCell(1);
rowName.SetCellValue(entity.FirstOrDefault().name);
rowName.CellStyle = style2;
var rowPosition = rowData.CreateCell(2);
rowPosition.SetCellValue(entity.FirstOrDefault().position_desc);
rowPosition.CellStyle = style2;
// Roster Date
var columnDataBgnDate = DateTime.Parse(c.BgnDate.ToString());
for (var j = 3; j < columnMaxIndex + 3; j++)
{
ICell cellDate = rowData.CreateCell(j);
var rosterData = entity.Where(r => r.roster_date == columnDataBgnDate).FirstOrDefault();
var leaveData = leave.Where(r => r.lv_date == columnDataBgnDate);
var rosterType = string.Empty;
var rosterCode = string.Empty;
if (rosterData != null)
{
if (rosterData.is_leave == "Y") // 当天已请假，不能 Overrding
{
#region is Leave
var leaveIndex = 0;
var lv_date_half = "";
leaveData.ToList().ForEach(lv =>
{
if (lv.lv_date_half == " ") // lv_date_half 为空时时两个空格
{
leaveIndex = 2;
}
else
{
lv_date_half = lv.lv_date_half;
leaveIndex++;
}
});
if (leaveIndex > 1)
{
rosterCode = "LEAVE";
rosterFont.Color = HSSFColor.Black.Index;
leaveStyle.SetFont(rosterFont);
cellDate.CellStyle = leaveStyle;
}
else
{
rosterCode = rosterData.roster_code;
//添加批注
echr.CreateComment((XSSFSheet)sheet, cellDate, j + 1, rowIndex, j + 2, rowIndex + 2, lv_date_half);
//单元格添加下拉框数据
echr.SetCellDropdownList((XSSFSheet)sheet, rowIndex, rowIndex, j, j, ArrayRosterSetup.ToArray());
}
#endregion
}
else
{
#region 存在 roster
rosterType = rosterData.roster_type;
rosterCode = rosterData.roster_code;
var color_id = rosterData.color_id;
if (color_id == 1)
{
rosterFont.Color = HSSFColor.Blue.Index;
rosterStyle.SetFont(rosterFont);
cellDate.CellStyle = rosterStyle;
}
else if (color_id == 2)
{
rosterFont.Color = HSSFColor.Green.Index;
rosterStyle.SetFont(rosterFont);
cellDate.CellStyle = rosterStyle;
}
else if (color_id == 3)
{
rosterFont.Color = HSSFColor.Tan.Index;
rosterStyle.SetFont(rosterFont);
cellDate.CellStyle = rosterStyle;
}
else if (color_id == 4)
{
rosterFont.Color = HSSFColor.DarkRed.Index;
rosterStyle.SetFont(rosterFont);
cellDate.CellStyle = rosterStyle;
}
else
{
cellDate.CellStyle = style2;
}
#endregion
//单元格添加下拉框数据
echr.SetCellDropdownList((XSSFSheet)sheet, rowIndex, rowIndex, j, j, ArrayRosterSetup.ToArray());
}
}
else
{
cellDate.CellStyle = style2;
//单元格添加下拉框数据
echr.SetCellDropdownList((XSSFSheet)sheet, rowIndex, rowIndex, j, j, ArrayRosterSetup.ToArray());
}
cellDate.SetCellValue(rosterCode);
columnDataBgnDate = columnDataBgnDate.AddDays(1);
}
rowIndex = rowIndex + 1;
}
catch (Exception e)
{
ExceptionHandler(oReturn, e);
}
});
#endregion
#region 单元格添加下拉框数据
//echr.SetCellDropdownList((XSSFSheet)sheet, 1, rowMaxIndex, 3, columnMaxIndex + 2, ArrayRosterSetup.ToArray());
#endregion
#region 设置单元格条件样式
echr.SetConditionalFormat(sheet, 1, rowMaxIndex, 3, columnMaxIndex + 2, ArraryRosterSetupFormat.ToArray());
#endregion
#region 按需求写入下拉框描述内容（最后一行内容）
NPOI.SS.UserModel.IRow rowFooter = sheet.CreateRow(rowIndex + 1);
rowFooter.Height = 360;
ICell cellFooter = rowFooter.CreateCell(rowMaxIndex + 1);
var cellFoo = rowFooter.CreateCell(0);
cellFoo.SetCellValue(strRosterSetup);
cellFoo.CellStyle = styleFooter;
#endregion
#region 按需求写入 LEAVE OR WORK & LEAVE 描述内容
NPOI.SS.UserModel.IRow rowFooter2 = sheet.CreateRow(rowIndex + 2);
rowFooter2.Height = 360;
ICell cellFooter2 = rowFooter2.CreateCell(rowMaxIndex + 2);
var cellFoo2 = rowFooter2.CreateCell(0);
cellFoo2.SetCellValue("* LEAVE and WORK & LEAVE mean that employees take leave on that day, CANNOT change it.");
cellFoo2.CellStyle = styleFooter;
#endregion
#region 按需求写入 描述信息
NPOI.SS.UserModel.IRow rowFooter3 = sheet.CreateRow(rowIndex + 3);
rowFooter3.Height = 360;
ICell cellFooter3 = rowFooter3.CreateCell(rowMaxIndex + 3);
var cellFoo3 = rowFooter3.CreateCell(0);
cellFoo3.SetCellValue("* About half a day's leave, please see the Note.");
cellFoo3.CellStyle = styleFooter;
#endregion
FileStream streamFile = new FileStream(fileUrl, FileMode.Create);
System.IO.MemoryStream streamMemory = new System.IO.MemoryStream();
book.Write(streamMemory);
byte[] data = streamMemory.ToArray();
// 开始写入
streamFile.Write(data, 0, data.Length);
// 清空缓冲区、关闭流
streamFile.Flush();
streamFile.Close();
book = null;
streamMemory.Close();
streamMemory.Dispose();
/// <summary>
/// NPOI 单元格添加下拉框
/// </summary>
/// <param name="sheet">ISheet</param>
/// <param name="firstRow">起始行号</param>
/// <param name="lastRow">终止行号</param>
/// <param name="firstcol">起始列号</param>
/// <param name="lastcol">终止列号</param>
/// <param name="vals">下拉框数据</param>
public static void SetCellDropdownList(XSSFSheet sheet,int firstRow,int lastRow, int firstcol, int lastcol, string[] vals)
{
//设置生成下拉框的行和列
var cellRegions = new NPOI.SS.Util.CellRangeAddressList(firstRow, lastRow, firstcol, lastcol);
//设置 下拉框内容
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(vals);
//绑定下拉框和作用区域，并设置错误提示信息
XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(constraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate.ShowErrorBox = true;
sheet.AddValidationData(dataValidate);
//CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstcol, lastcol);
//IDataValidation validate = dvHelper.CreateValidation(constraint, regions);
//validate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
//validate.ShowErrorBox = true;
//sheet.AddValidationData(validate);
}
/// <summary>
/// NPOI 单元格条件样式
/// </summary>
/// <param name="sheet">ISheet</param>
/// <param name="firstRow">起始行号</param>
/// <param name="lastRow">终止行号</param>
/// <param name="firstcol">起始列号</param>
/// <param name="lastcol">终止列号</param>
public static void SetConditionalFormat(ISheet sheet, int firstRow, int lastRow, int firstcol, int lastcol,string[] vals)
{
XSSFSheetConditionalFormatting hscf = (XSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
foreach (var roster in vals)
{
var rosterList = roster.Split('|');
var roster_code = rosterList[0];
var roster_type = rosterList[1];
var color_id = rosterList[2];
XSSFConditionalFormattingRule rule = (XSSFConditionalFormattingRule)hscf.CreateConditionalFormattingRule(
ComparisonOperator.Equal,
"\"" + roster_code + "\"", // 中文引号需要转义
null
);
if (color_id == "1")
{
rule.CreateFontFormatting().FontColorIndex = HSSFColor.Blue.Index;
}
else if (color_id == "2")
{
rule.CreateFontFormatting().FontColorIndex = HSSFColor.Green.Index;
}
else if (color_id == "3")
{
rule.CreateFontFormatting().FontColorIndex = HSSFColor.Tan.Index;
}
else if (color_id == "4")
{
rule.CreateFontFormatting().FontColorIndex = HSSFColor.DarkRed.Index;
}
else {
rule.CreateFontFormatting().FontColorIndex = HSSFColor.Black.Index;
}
CellRangeAddress[] ranges = { new CellRangeAddress(firstRow, lastRow, firstcol, lastcol) };
hscf.AddConditionalFormatting(ranges, rule);
}
}
/// <summary>
/// NPOI 给单元格添加批注
/// </summary>
/// <param name="sheet">ISheet</param>
/// <param name="cell">ICell</param>
/// <param name="firstcol">起始列</param>
/// <param name="firstRow">起始行</param>
/// <param name="lastcol">结束列</param>
/// <param name="lastRow">结束行</param>
/// <param name="vals">批注内容</param>
public static void CreateComment(ISheet sheet,ICell cell, int firstcol,int firstRow, int lastcol, int lastRow, string vals)
{
//添加批注
XSSFDrawing draw = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFComment comment = (XSSFComment)draw.CreateCellComment(new XSSFClientAnchor(255, 125, 1023, 150, 0, 0, 2, 2));//批注显示定位
comment.String = new XSSFRichTextString("LEAVE Note \nHave a Leave at " + vals + "."); //\n 换行
cell.CellComment = comment;//将批注给予单元格
}
批注主要有三个属性需要设置，一个是批注的位置和大小、一个是批注的文本、还有一个是批注的作者，
批注的位置和大小，在Excel中是与单元格密切相关的，NPOI中通过HSSFClientAnchor或者XSSFClientAnchor的实例来表示，它的构造函数比较复杂，有8个参数，它们分别是
XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
参数解析：
dx1: 第1个单元格中x轴的偏移量
dy1: 第1个单元格中y轴的偏移量
dx2: 第2个单元格中x轴的偏移量
dy2: 第2个单元格中y轴的偏移量
col1: 第1个单元格的列号
row1: 第1个单元格的行号
col2: 第2个单元格的列号
row2: 第2个单元格的行号
需要注意一点就是：后四个参数 col1,row1,col2,row2 要根据实际的批注所在单元格的位置传参，而我这里是写死的 0,0,2,2 是因为我这里每次都是新增一个单元格然后直接写批注，而不是在已存在的 excel 中批量加批注；
批注参考网址：https://blog.csdn.net/lovestj/article/details/85292591
最后导出 excel 的效果图：
上图中实现的效果有：
1.动态添加列，从第 D 列开始，后面的列都是动态添加的；
2.从第 D 列开始，所有单元格的字体颜色，格式等都是根据内容设置的；
3.添加下拉框，并添加下拉数据，实现下拉框手动输入数据验证，若是输入内容不存在下拉框，会提示：
4.根据条件给单元格添加批注；
537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
