C#调用NPOI创建Excel文档

你的名字 2021-07-04 16:08 460阅读 0赞

#### C\#调用NPOI自动创建Excel文档（一） ####

所以，要实现的效果是：读取数据库所有记录，记录中有几个高端类就让Excel列宽等于其数量值，其它类别同理。除了控制循环读取的次数外，最为关键的就是准确得出每个要合并的类别单元格在Excel中的起始位置和结束位置。核心算法如下：

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();
    }

--------------------

#### C\#调用NPOI创建Excel文档单元格写入问题一则 ####

想从数据库里读数据并写入到Excel文件中，C\#代码是这样写的。

private void SetContent(HSSFWorkbook hssfworkbook)
    { 
      ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
      hssfworkbook.CreateSheet("Sheet2");
    
      OleDbConnection mycon = null;
      OleDbDataReader myReader = null;
      try
      { 
        string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../Product.mdb;";
        mycon = new OleDbConnection(strcon);
        mycon.Open();
        string sql = "SELECT DISTINCT ProductType FROM ProductInfor";
        OleDbCommand mycom = new OleDbCommand(sql, mycon);
        myReader = mycom.ExecuteReader();
    
        int loopCount = 0;   
        while (myReader.Read())
        { 
          IRow row = sheet.CreateRow(1);    //创建行对象 --注意这点
          string productType = myReader.GetString(0);
          //ICell cell = row.CreateCell(loopCount); //创建单元格对象 
          //cell.SetCellValue(productType); //设置单元格内容 
    
       	 row.CreateCell(loopCount).SetCellValue(productType);
    
        loopCount++;
     	}
    
      }
      finally
      { 
      	mycon.Close();
      }
    }

这样做之后发现最后生成的Excel文件只有第二行的一个单元格内有数据。比如查到的数据记录有5条，那么只有这一行的第五个单元格内有数据，前面四个单元格内都没有。

问题出在NPOI的`CreateRow()`方法。这个方法创建行的时候默认会先把这行数据全部清空。所以上述代码在创建完成的Excel文件中就只有一个单元格内有数据。解决方法是：`在逐个读取数据库记录前先创建行`。所以，调整`IRow row = sheet.CreateRow(1); //创建行对象 --注意这点`的位置就行了。如此调整。

private void SetContent(HSSFWorkbook hssfworkbook)
    {
      ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
      hssfworkbook.CreateSheet("Sheet2");
    
      OleDbConnection mycon = null;
      OleDbDataReader myReader = null;
      try
      {
        string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../Product.mdb;";
        mycon = new OleDbConnection(strcon);
        mycon.Open();
        string sql = "SELECT DISTINCT ProductType FROM ProductInfor";
        OleDbCommand mycom = new OleDbCommand(sql, mycon);
        myReader = mycom.ExecuteReader();
    
        IRow row = sheet.CreateRow(1);    //创建行对象  --调整到这里
        int loopCount = 0;   
        while (myReader.Read())
        {
          string productType = myReader.GetString(0);
          //ICell cell = row.CreateCell(loopCount);   //创建单元格对象  
          //cell.SetCellValue(productType);         //设置单元格内容  
    
       	 row.CreateCell(loopCount).SetCellValue(productType);
    
        loopCount++;
     	}
    
      }
      finally
      {
      	mycon.Close();
      }
    }

--------------------

#### C\#调用NPOI创建Excel文档合并后的单元格写入数据问题一则 ####

C\#调用NPOI创建Excel文档。第二行设定了3个单元格，每个单元格列宽为3（跨3列）。现在想向这三个单元格内填充数据。C\#代码如下：

#region 合并单元格
      pinTypeNumForLastLoop = result; 
      sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, (loopCount + 1) * result - 1));
      sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, loopCount*pinTypeNumForLastLoop, (loopCount+1)*result-1));
      sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, loopCount * pinTypeNumForLastLoop, (loopCount + 1) * result - 1));
    #endregion
    
    #region 往单元格填充值
    	row1.CreateCell(loopCount * pinTypeNumForLastLoop).SetCellValue(productType);
    #endregion
    loopCount++；

其中，`loopCount`代表读到的数据个数，有多少个数据，上述代码就循环几次。`pinTypeNumForLastLoop`是上个单元格的列宽。

需要注意的是：NPOI给合并后的单元格填充数据，单元格的位置仍然按照原列标计算。合并单元格后列表并未改变。意思是说：假如第一行有3个单元格，合并后每个单元格跨度为3，那么第一个单元格的位置是（0，0），第二个单元格的位置是（0，3），第三个单元格的位置是（0，6）。而不是（0，0），（0，1），（0，2）。（下标从0开始）

--------------------

#### C\#调用NPOI创建Excel文档样式设置方法总结 ####

##### 一、描边与居中 #####

#region 描边与居中-style
      ICellStyle style1 = hssfworkbook.CreateCellStyle();
      style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
      style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
      style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
      style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
      style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
      style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
    #endregion

##### 二、描边，居中，加粗 #####

#region 描边，居中，加粗-style
      ICellStyle style2//Style2为继承了Style1的新样式
      = hssfworkbook.CreateCellStyle();
      style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//Style1的样式
      style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
      style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
      style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
      style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
      style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
      IFont font1 = hssfworkbook.CreateFont();
      font1.Boldweight = (short)FontBoldWeight.Bold;
      style2.SetFont(font1);
    #endregion

##### 三、描边，居中，加粗，设字体大小 #####

#region 描边，居中，加粗，设字体大小-style
      ICellStyle style3//Style3为继承了Style2的新样式
      = hssfworkbook.CreateCellStyle();
      style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//Style1的样式
      style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
      style3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
      style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
      style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
      style3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
      IFont font2 = hssfworkbook.CreateFont();
      font2.Boldweight = (short)FontBoldWeight.Bold;
      font2.FontHeightInPoints = 14;
      style3.SetFont(font2);
    #endregion

##### 四、给单元格描边 #####

#region 描边与居中
      CellRangeAddress regionAll = new CellRangeAddress(0, sheet.LastRowNum, 0, endLoc);//从第一行第一列到最后一行最后一列
      for (int i = regionAll.FirstRow; i <= regionAll.LastRow; i++)
      {
        IRow row = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
        for (int j = regionAll.FirstColumn; j <= regionAll.LastColumn; j++)
        {
          ICell singleCell = HSSFCellUtil.GetCell(row, j);
    
          if (i == regionAll.FirstRow)//第一行单元格用style3
          {
          	singleCell.CellStyle = style3;
          }
          else if (i == regionAll.FirstRow + 1 || j == regionAll.FirstColumn)//第二行用style2;第一列用style2
          {
          	singleCell.CellStyle = style2;
          }
          else//其它行列用style1
          {
          	singleCell.CellStyle = style1;
          }
        }
      }
    #endregion

实际应用需要给创建好的表格描边。只需先确定好整个表格的区域，然后根据这个区域的行列值循环为每个单元格描边即可。