NPOI 生成Excel (单元格合并、设置单元格样式:字段,颜色、设置单元格为下拉框并限制输入值、设置单元格只能输入数字等)

NPIO源码地址:https://github.com/tonyqus/npoi

NPIO使用参考:源码中的 NPOITest项目

下面代码包括:

1、包含多个Sheet的Excel

2、单元格合并

3、设置单元格样式:字段,颜色

4、设置单元格为下拉框并限制输入值

5、设置单元格只能输入数字

 //
// GET: /Excel/
public ActionResult Write()
{
var workbook = new HSSFWorkbook();//从流内容创建Workbook对象
ISheet sheet = ((HSSFWorkbook)workbook).CreateSheet("sheetOne");//创建工作表 IRow row = sheet.CreateRow();//在工作表中添加一行
ICell cell = row.CreateCell();//在行中添加一列
cell.SetCellValue("test");//设置列的内容
setCellStyle(workbook, cell);
mergeCell(sheet, , , , ); sheet = ((HSSFWorkbook)workbook).CreateSheet("sheet2");//创建工作表
setCellDropdownlist(sheet);
setCellInputNumber(sheet); string filePath = Server.MapPath("~/ExportFiles/test.xls");
FileStream fs = new FileStream(filePath, FileMode.Create);
workbook.Write(fs);
fs.Close(); return null;
} /// <summary>
/// 设置单元格为下拉框并限制输入值
/// </summary>
/// <param name="sheet"></param>
private void setCellDropdownlist(ISheet sheet)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(, , , ); //设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "itemA", "itemB", "itemC" }); //绑定下拉框和作用区域,并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate);
} /// <summary>
/// 设置单元格只能输入数字
/// </summary>
/// <param name="sheet"></param>
private void setCellInputNumber(ISheet sheet)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(, , , ); //第二个参数int comparisonOperator 参考源码获取
//https://github.com/tonyqus/npoi
//NPOITest项目
DVConstraint constraint = DVConstraint.CreateNumericConstraint(
ValidationType.INTEGER, OperatorType.BETWEEN, "", ""); HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入1~100的数字。");
//dataValidate.PromptBoxTitle = "ErrorInput"; sheet.AddValidationData(dataValidate);
} /// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCell"></param>
/// <param name="lastCell"></param>
private void mergeCell(ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
{
sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));//2.0使用 2.0以下为Region
} /// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="cell"></param>
private void setCellStyle(HSSFWorkbook workbook, ICell cell)
{
HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeight = * ;
ffont.FontName = "宋体";
ffont.Color = HSSFColor.Red.Index;
fCellStyle.SetFont(ffont); fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
cell.CellStyle = fCellStyle;
}
上一篇:设置c++中cout输出的字体颜色


下一篇:Java实现zip压缩文件的解压