代码包括：
1：导出多个sheet 2：设置单元格格式 3:合并单元格 4：下拉框选项 5:输入数字限制 6：锁定单元格
static void Main(string[] args)
{
//hssf表示2003 xssf表示2007 两者之间有的功能是一样的，比如设置列宽,锁定单元格
//有的功能是不一样的,具体情况具体对待
//实例化一个操作excel工作薄的对象
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet1 = workbook.CreateSheet("sheet1");
//创建行(一行一行的创建,参数代表第几行,不能相同,会覆盖)
IRow row1 = sheet1.CreateRow(0);
//创建单元格(一个一个的创建,参数代表第几个,不能相同,会覆盖)
ICell cell1 = row1.CreateCell(0);
//设置值
cell1.SetCellValue("大家好");
//设置格式
ICell cell2 = row1.CreateCell(1);
cell1.SetCellValue("hahah");
SetCellStyle((HSSFWorkbook)workbook, cell1);
//合并单元格
MergeCell(sheet1, 0, 0, 1, 4);
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 25 * 256);
//创建另一个sheet
ISheet sheet2 = workbook.CreateSheet("sheet2");
SetCellDropdownlist(sheet2);//下拉列表
SetCellInputNumber(sheet2);//只能输入数字
//锁定sheet
ISheet sheet3 = workbook.CreateSheet("sheet3");
//设置密码，锁定表(只要设定就会锁定整个sheet)
sheet3.ProtectSheet("123456");
IRow row3 = sheet3.CreateRow(0);
ICell c1 = row3.CreateCell(0);
ICell c2 = row3.CreateCell(1);
//不锁定
HSSFCellStyle unlocked = (HSSFCellStyle)workbook.CreateCellStyle();
unlocked.IsLocked = false;
//锁定
HSSFCellStyle locked = (HSSFCellStyle)workbook.CreateCellStyle();
locked.IsLocked = true;
c1.SetCellValue("未锁定");
c1.CellStyle = unlocked;
//其实这个设置不设置都一样的，只要加密就是锁定整个sheet
c2.SetCellValue("锁定");
c2.CellStyle = locked;
using (Stream stream = File.OpenWrite(@"D: est2.xls"))
{
//进行保存
workbook.Write(stream);
}
Console.WriteLine("成功");
Console.ReadKey();
}
/// <summary>
/// 设置单元格为下拉框并限制输入值
/// </summary>
/// <param name="sheet"></param>
private static void SetCellDropdownlist(ISheet sheet)
{
//设置生成下拉框的行和列(开始行，结束行，开始列，结束列)
var cellRegions = new CellRangeAddressList(0, 20, 0, 0);
//设置 下拉框内容
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 static void SetCellInputNumber(ISheet sheet)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(0, 10, 1, 1);
//第二个参数int comparisonOperator 参考源码获取
//https://github.com/tonyqus/npoi
//NPOITest项目
DVConstraint constraint = DVConstraint.CreateNumericConstraint(
ValidationType.INTEGER, OperatorType.BETWEEN, "0", "10");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入1~10的数字。");
//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 static 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 static void SetCellStyle(HSSFWorkbook workbook, ICell cell)
{
HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeight = 20 * 20;
ffont.FontName = "宋体";
ffont.Color = HSSFColor.RED.index;
//2007中直接设置就好
//ffont.Color = 2;
fCellStyle.SetFont(ffont);
fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
cell.CellStyle = fCellStyle;
}
参考
http://www.docin.com/p-1151996737.html?qq-pf-to=pcqq.c2c
http://www.cnblogs.com/gossip/p/4307486.html