//public void Create()
//{
// HSSFWorkbook book = new HSSFWorkbook();
// ISheet sheet = book.CreateSheet("Sheet1");
// IRow row = sheet.CreateRow(20);//index代表多少行
// row.HeightInPoints = 35;//行高
// ICell cell = row.CreateCell(0);//创建第一列
// cell.SetCellValue("设置单元格的值");
//}
/// <summary>
/// 导出基本操作示例方法
/// </summary>
public static void ExportExcel()
{
//初始化一个新的HSSFWorkbook实例
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//设置excel必须的文件属性（该属性用来存储 如作者、标题、标记、备注、主题等信息，右键可查看的属性信息）
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.DocumentSummaryInformation = dsi;
hssfworkbook.SummaryInformation = si;
//新建一个Workbook默认都会新建3个Sheet(标准的Excel文件有3个Sheet)。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet");
// hssfworkbook.CreateSheet("Sheet1");
// hssfworkbook.CreateSheet("Sheet2");
// hssfworkbook.CreateSheet("Sheet3");
//建创行
Row row1 = sheet.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
row1.Height = 500;
CellStyle s = hssfworkbook.CreateCellStyle();
s.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
s.FillPattern = FillPatternType.SOLID_FOREGROUND;
//第一列
Cell cell1 = row1.CreateCell(0);
cell1.CellStyle = s;
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 20;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
cell1.CellStyle.SetFont(font);
cell1.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cell1.SetCellValue("《大明宫保护办月报月计划》——( 行政管理部)6月工作计划");
cell1.CellStyle.BorderBottom = CellBorderType.THIN;
cell1.CellStyle.BorderLeft = CellBorderType.THIN;
cell1.CellStyle.BorderRight = CellBorderType.THIN;
cell1.CellStyle.BorderTop = CellBorderType.THIN;
sheet.SetColumnWidth(0, 4 * 256);
//第二列
Cell cell2 = row1.CreateCell(1);
cell2.CellStyle = s;
sheet.SetColumnWidth(1, 12 * 256);
//第三列
Cell cell3 = row1.CreateCell(2);
cell3.CellStyle = s;
sheet.SetColumnWidth(2, 20 * 256);
//第四列
Cell cell4 = row1.CreateCell(3);
cell4.CellStyle = s;
sheet.SetColumnWidth(3, 25 * 256);
//第五列
Cell cell5 = row1.CreateCell(4);
cell5.CellStyle = s;
sheet.SetColumnWidth(4, 35 * 256);
//第六列
Cell cell6 = row1.CreateCell(5);
cell6.CellStyle = s;
sheet.SetColumnWidth(5, 20 * 256);
//第七列
Cell cell7 = row1.CreateCell(6);
cell7.CellStyle = s;
sheet.SetColumnWidth(6, 20 * 256);
//第八列
Cell cell8 = row1.CreateCell(7);
cell8.CellStyle = s;
sheet.SetColumnWidth(7, 20 * 256);
//第9列
Cell cell9 = row1.CreateCell(8);
cell9.CellStyle = s;
sheet.SetColumnWidth(8, 20 * 256);
//第10列
Cell cell10 = row1.CreateCell(9);
cell10.CellStyle = s;
sheet.SetColumnWidth(9, 20 * 256);
//第11列
Cell cell11 = row1.CreateCell(10);
cell11.CellStyle = s;
sheet.SetColumnWidth(10, 20 * 256);
CellRangeAddress r = new CellRangeAddress(0, 0, 0, 10);
sheet.AddMergedRegion(r);
//sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, 10));
CreateRow2(hssfworkbook, sheet);
CreateRow3_4(hssfworkbook, sheet);
_1stModule _1StModule = new _1stModule()
{
_1stModuleName = "管理工作"
};
List <_2ndModule> _2NdModules = new List <_2ndModule>();
_2ndModule _2NdModule = new _2ndModule {
_2ndModuleName = "与战略地图要求相关"
};
DataItem item = new DataItem
{
Work = "保护办十年工作总结",
Result = "30日前完成保护办十年工作总结（总结部分）",
_1stWeek = "根据主要领导意见进行修改",
_2ndWeek = "进行修改",
_3rdWeek = "进行修改",
_4thWeek = "完成总结",
PersonInCharge = "雷博",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与制度、流程、标准、工具相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与企业文化相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与团队建设相关"
};
item = new DataItem
{
Work = "组织公文写作培训",
Result = "30日前完成培训",
_1stWeek = "",
_2ndWeek = "与培训老师确定时间和内容",
_3rdWeek = "与培训老师确定时间和内容",
_4thWeek = "完成培训",
PersonInCharge = "王倩",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_1StModule._2ndModules = _2NdModules;
CreateRowsByModules(_1StModule, sheet, hssfworkbook);
//把这个HSSFWorkbook实例写入文件
FileStream file = new FileStream("Example1.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
private static void CreateRowsByModules(_1stModule module, HSSFSheet sheet, HSSFWorkbook hssfworkbook)
{
int offset = 4;
for (int i = 0; i < module._2ndModules.Count; i++)
{
for (int j = 0; j < module._2ndModules[i].datas.Count; j++)
{
offset++;
Row row2 = sheet.CreateRow(offset);
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
CellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.WrapText = true;
//设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
var cell = row2.CreateCell(3);
cell.SetCellValue(module._2ndModules[i].datas[j].Work);
cell.CellStyle = style;
cell = row2.CreateCell(4);
cell.SetCellValue(module._2ndModules[i].datas[j].Result);
cell.CellStyle = style;
cell = row2.CreateCell(5);
cell.SetCellValue(module._2ndModules[i].datas[j]._1stWeek);
cell.CellStyle = style;
cell = row2.CreateCell(6);
cell.SetCellValue(module._2ndModules[i].datas[j]._2ndWeek);
cell.CellStyle = style;
cell = row2.CreateCell(7);
cell.SetCellValue(module._2ndModules[i].datas[j]._3rdWeek);
cell.CellStyle = style;
cell = row2.CreateCell(8);
cell.SetCellValue(module._2ndModules[i].datas[j]._4thWeek);
cell.CellStyle = style;
cell = row2.CreateCell(9);
cell.SetCellValue(module._2ndModules[i].datas[j].PersonInCharge);
cell.CellStyle = style;
cell = row2.CreateCell(10);
cell.SetCellValue(module._2ndModules[i].datas[j].Penaty);
cell.CellStyle = style;
}
}
}
