用NPOI操作EXCEL--生成一张工资单
这一节,我们将综合NPOI的常用功能(包括创建和填充单元格、合并单元格、设置单元格样式和利用公式),做一个工资单的实例。先看创建标题行的代码:
其中用到了我们前面讲的设置单元格样式和合并单元格等内容。接下来我们循环创建公司每个员工的工资单:
DataTable dt=GetData();
HSSFRow row;
HSSFCell cell;
HSSFCellStyle celStyle=getCellStyle();
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
HSSFSimpleShape line;
int rowIndex;
for (int i = 0; i < dt.Rows.Count; i++)
{
//表头数据
rowIndex = 3 * (i + 1);
row = sheet1.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("姓名");
cell.CellStyle = celStyle;
cell = row.CreateCell(1);
cell.SetCellValue("基本工资");
cell.CellStyle = celStyle;
cell = row.CreateCell(2);
cell.SetCellValue("住房公积金");
cell.CellStyle = celStyle;
cell = row.CreateCell(3);
cell.SetCellValue("绩效奖金");
cell.CellStyle = celStyle;
cell = row.CreateCell(4);
cell.SetCellValue("社保扣款");
cell.CellStyle = celStyle;
cell = row.CreateCell(5);
cell.SetCellValue("代扣个税");
cell.CellStyle = celStyle;
cell = row.CreateCell(6);
cell.SetCellValue("实发工资");
cell.CellStyle = celStyle;
DataRow dr = dt.Rows[i];
//设置值和计算公式
row = sheet1.CreateRow(rowIndex + 1);
cell = row.CreateCell(0);
cell.SetCellValue(dr["FName"].ToString());
cell.CellStyle = celStyle;
cell = row.CreateCell(1);
cell.SetCellValue((double)dr["FBasicSalary"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(2);
cell.SetCellValue((double)dr["FAccumulationFund"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(3);
cell.SetCellValue((double)dr["FBonus"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(4);
cell.SetCellFormula(String.Format("$B{0}*0.08",rowIndex+2));
cell.CellStyle = celStyle;
cell = row.CreateCell(5);
cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})*0.1",rowIndex+2));
cell.CellStyle = celStyle;
cell = row.CreateCell(6);
cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
cell.CellStyle = celStyle;
//绘制分隔线
sheet1.AddMergedRegion(new Region(rowIndex+2, 0, rowIndex+2, 6));
anchor = new HSSFClientAnchor(0, 125, 1023, 125, 0, rowIndex + 2, 6, rowIndex + 2);
line = patriarch.CreateSimpleShape(anchor);
line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
}
其中为了文件打印为单元格增加了黑色边框的样式(如果不设置边框样式,打印出来后是没有边框的)。另外,注意循环过程中excel中的行号随数据源中的行号变化处理。完整代码如下:
Code
生成的Excel文件样式如下:
![](https://images.cnblogs.com/cnblogs_com/atao/npoi/payroll001.JPG)
//写标题文本
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFCell cellTitle = sheet1.CreateRow(0).CreateCell(0);
cellTitle.SetCellValue("XXX公司2009年10月工资单");
//设置标题行样式
HSSFCellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cellTitle.CellStyle = style;
//合并标题行
sheet1.AddMergedRegion(new Region(0, 0, 1, 6));
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFCell cellTitle = sheet1.CreateRow(0).CreateCell(0);
cellTitle.SetCellValue("XXX公司2009年10月工资单");
//设置标题行样式
HSSFCellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cellTitle.CellStyle = style;
//合并标题行
sheet1.AddMergedRegion(new Region(0, 0, 1, 6));
其中用到了我们前面讲的设置单元格样式和合并单元格等内容。接下来我们循环创建公司每个员工的工资单:
DataTable dt=GetData();
HSSFRow row;
HSSFCell cell;
HSSFCellStyle celStyle=getCellStyle();
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
HSSFSimpleShape line;
int rowIndex;
for (int i = 0; i < dt.Rows.Count; i++)
{
//表头数据
rowIndex = 3 * (i + 1);
row = sheet1.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("姓名");
cell.CellStyle = celStyle;
cell = row.CreateCell(1);
cell.SetCellValue("基本工资");
cell.CellStyle = celStyle;
cell = row.CreateCell(2);
cell.SetCellValue("住房公积金");
cell.CellStyle = celStyle;
cell = row.CreateCell(3);
cell.SetCellValue("绩效奖金");
cell.CellStyle = celStyle;
cell = row.CreateCell(4);
cell.SetCellValue("社保扣款");
cell.CellStyle = celStyle;
cell = row.CreateCell(5);
cell.SetCellValue("代扣个税");
cell.CellStyle = celStyle;
cell = row.CreateCell(6);
cell.SetCellValue("实发工资");
cell.CellStyle = celStyle;
DataRow dr = dt.Rows[i];
//设置值和计算公式
row = sheet1.CreateRow(rowIndex + 1);
cell = row.CreateCell(0);
cell.SetCellValue(dr["FName"].ToString());
cell.CellStyle = celStyle;
cell = row.CreateCell(1);
cell.SetCellValue((double)dr["FBasicSalary"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(2);
cell.SetCellValue((double)dr["FAccumulationFund"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(3);
cell.SetCellValue((double)dr["FBonus"]);
cell.CellStyle = celStyle;
cell = row.CreateCell(4);
cell.SetCellFormula(String.Format("$B{0}*0.08",rowIndex+2));
cell.CellStyle = celStyle;
cell = row.CreateCell(5);
cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})*0.1",rowIndex+2));
cell.CellStyle = celStyle;
cell = row.CreateCell(6);
cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
cell.CellStyle = celStyle;
//绘制分隔线
sheet1.AddMergedRegion(new Region(rowIndex+2, 0, rowIndex+2, 6));
anchor = new HSSFClientAnchor(0, 125, 1023, 125, 0, rowIndex + 2, 6, rowIndex + 2);
line = patriarch.CreateSimpleShape(anchor);
line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
posted on 2011-06-15 09:45 being installed 阅读(461) 评论(0) 编辑 收藏 举报