C#调用NPOI自动创建Excel文档(一)
最终要实现的效果是这样的:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5iM5MTM4kDOzMDMzE2MzEmNzYzX5UjN0gTM4AzLcFTMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
以我的项目为例。我的数据库是这样设计的:
try
{
string sql1 = "SELECT DISTINCT ProductType FROM ProductInfor";
OleDbCommand mycom1 = new OleDbCommand(sql1, mycon);
myReader1 = mycom1.ExecuteReader();
int loopCount = 0;//-----------这里是关键点
int lastMergedCellWidth = 0;//记录上一个合并了的单元格的宽度-----------这里是关键点
IRow row1 = sheet.CreateRow(1); //创建行对象-行2
int startLoc = 0;//-----------这里是关键点
int endLoc = 0;//-----------这里是关键点
while (myReader1.Read())
{
string productType = myReader1.GetString(0);
string sql2 = //得到ProductType对应的PinType个数
"SELECT COUNT(*) FROM ProductInfor WHERE ProductType='"+productType+"'";
OleDbCommand mycom2 = new OleDbCommand(sql2, mycon);
OleDbDataReader myReader2 = mycom2.ExecuteReader();
int result = 0;
while (myReader2.Read())
{
result=myReader2.GetInt32(0);
}
startLoc += lastMergedCellWidth;//-----------这里是关键点
endLoc += result;//-----------这里是关键点
lastMergedCellWidth = result;//-----------这里是关键点
#region 合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, (loopCount + 1) * result));//合并第一行-----------这里是关键点
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, startLoc+1, endLoc));//合并第二行-产品类型行-----------这里是关键点
#endregion
#region 填充产品类型行
row1.CreateCell(0).SetCellValue("产品类型");//-----------这里是关键点
row1.CreateCell(startLoc+1).SetCellValue(productType);//填充第二行合并后的单元格-----------这里是关键点
#endregion
loopCount++;//-----------这里是关键点
}
}
finally
{
mycon.Close();
}