引用NPOI动态创建EXCEL表

使用前需要引用npoi dll,并引用以下命名空间：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

/***************生成表头部分*******************/  

        //创建工作薄      
        HSSFWorkbook book = new HSSFWorkbook();
        //用工作簿对象创建工作表
        ISheet sheet = book.CreateSheet("统计报表");
        //创建行
        IRow row0 = sheet.CreateRow(0);
        //创建列
        row0.CreateCell(0).SetCellValue("货品信息");
        row0.CreateCell(1);
        row0.CreateCell(2);
        row0.CreateCell(3);
        row0.CreateCell(4).SetCellValue("设计评分(平均分)");
        row0.CreateCell(5);
        //合并单元格,根据单元格下标指定区域合并
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 5));
        
        //指定列宽
        sheet.SetColumnWidth(0, 30 * 150);
        sheet.SetColumnWidth(1, 30 * 150);
        sheet.SetColumnWidth(2, 30 * 150);
        sheet.SetColumnWidth(3, 30 * 150);
        sheet.SetColumnWidth(4, 30 * 150);
        sheet.SetColumnWidth(5, 30 * 150);        
        
        //创建单元格样式
        ICellStyle style = book.CreateCellStyle();
        style.BorderBottom = BorderStyle.Thin;
        style.BorderTop = BorderStyle.Thin;
        style.BorderLeft = BorderStyle.Thin;
        style.BorderRight = BorderStyle.Thin;
        style.Alignment = HorizontalAlignment.Center;
        //指定字体样式
        IFont font = book.CreateFont();
        font.FontHeight = 17 * 17;        
        style.SetFont(font);
        row0.Cells[0].CellStyle = style;
        row0.Cells[1].CellStyle = style;
        row0.Cells[2].CellStyle = style;
        row0.Cells[3].CellStyle = style;
        row0.Cells[4].CellStyle = style;
        row0.Cells[5].CellStyle = style;

        IRow row1 = sheet.CreateRow(1);
        row1.CreateCell(0).SetCellValue("设计系列");
        row1.CreateCell(1).SetCellValue("货品编号");
        row1.CreateCell(2).SetCellValue("货品图片");
        row1.CreateCell(3).SetCellValue("款式");
        row1.CreateCell(4).SetCellValue("分值");
        row1.CreateCell(5).SetCellValue("百分比");
        row1.Cells[0].CellStyle = style;
        row1.Cells[1].CellStyle = style;
        row1.Cells[2].CellStyle = style;
        row1.Cells[3].CellStyle = style;
        row1.Cells[4].CellStyle = style;
        row1.Cells[5].CellStyle = style;
        
        
       
        
        //生成数据部分
        Scoring.BLL.Common commBll = new Scoring.BLL.Common();
        
        //获取指定主题下所有货品系列
        string sql = "select distinct(Series) from Products where questionId=@questionId order by Series desc";
        SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@questionId", questionId) };
        DataSet ds = commBll.ExcuteSql(sql, parm);
        if (ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0)
        {
            int rowCount = 2;//因为前面表头占了两行，所以数据部分从2开始           
            DataTable dt=ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sql = " select p.productNo,p.imgUrl,p.style,"
                      +"(select convert(decimal(18,1), cast(sum(score) as decimal )/count(1)) from Comment c where c.productId=p.id) avgScore "
                      +"from Products p where p.questionId="+questionId+" and p.Series='"+dt.Rows[i][0].ToString()+"' ";
                DataSet datas = commBll.ExcuteSql(sql, null);

                if (datas!=null && datas.Tables.Count>0 && datas.Tables[0].Rows.Count>0)
                {
                    int beginRow = rowCount;
                    int endRow = rowCount;
                    DataTable dt2 = datas.Tables[0];
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        IRow row = sheet.CreateRow(rowCount);
                        row.CreateCell(0);
                        //第0列为合并单元格，只给第0行赋值
                        if (j==0)
                        {
                            row.CreateCell(0).SetCellValue(dt.Rows[i][0].ToString());
                        }
                        else
                        {
                            row.CreateCell(0);
                        }
                        row.CreateCell(1).SetCellValue(dt2.Rows[j]["productNo"].ToString());
                        row.CreateCell(2);
                        //读取图片
                        if (dt2.Rows[j]["imgUrl"]!=DBNull.Value && !string.IsNullOrEmpty(dt2.Rows[j]["imgUrl"].ToString()))
                        {
                            //插入图片
                            byte[] bytes = System.IO.File.ReadAllBytes(HttpContext.Current.Server.MapPath("../" + dt2.Rows[j]["imgUrl"].ToString()));
                            int pictureIdx = book.AddPicture(bytes, PictureType.JPEG);//根据图片格式指定
                            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                            HSSFClientAnchor anchor = new HSSFClientAnchor(2, 2, 1020, 253, 2, (i + 1) * (j + 1) + 1, 2, (i + 1) * (j + 1) + 1);
                            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                            //pict.Resize() ;//恢复原始大小
                            sheet.SetColumnWidth(2, 30 * 150);
                            sheet.GetRow((i + 1) * (j + 1) + 1).Height = 80 * 20;
                        }
                       
                        
                        row.CreateCell(3).SetCellValue(dt2.Rows[j]["style"].ToString());
                        row.CreateCell(4).SetCellValue(dt2.Rows[j]["avgScore"].ToString());
                        if (dt2.Rows[j]["avgScore"]!=DBNull.Value && !string.IsNullOrEmpty(dt2.Rows[j]["avgScore"].ToString()))
                        {
                            row.CreateCell(5).SetCellValue(Math.Round( Convert.ToDecimal(dt2.Rows[j]["avgScore"].ToString())*10,0) +"%");
                        }
                        else
                        {
                            row.CreateCell(5);
                        }
                        
                        ICellStyle style2 = book.CreateCellStyle();
                        style2.BorderBottom = BorderStyle.Thin;
                        style2.BorderTop = BorderStyle.Thin;
                        style2.BorderLeft = BorderStyle.Thin;
                        style2.BorderRight = BorderStyle.Thin;
                        style2.Alignment = HorizontalAlignment.Center;
                        style2.VerticalAlignment = VerticalAlignment.Center;               
                                            
                        row.Cells[0].CellStyle = style2;
                        row.Cells[1].CellStyle = style2;
                        row.Cells[2].CellStyle = style2;
                        row.Cells[3].CellStyle = style2;
                        row.Cells[4].CellStyle = style2;
                        row.Cells[5].CellStyle = style2;
                        rowCount++;//递增，指定下一行下标。
                    }
                    endRow = rowCount - 1;
                    if (dt2.Rows.Count>1)
                    {  
                        sheet.AddMergedRegion(new CellRangeAddress(beginRow, endRow, 0, 0));
                    }
                    
                }
                
            }

        }

//导出

Response.ContentType = "application/x-excel";

string fileName = HttpUtility.UrlEncode("报表.xls");
Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);  //添加http协议报文;

book.Write(Response.OutputStream);
Response.End();