.net NPOI导出EXCEL多个sheet

最新推荐文章于 2024-07-16 11:27:21 发布

wybshyy

最新推荐文章于 2024-07-16 11:27:21 发布

阅读量2.9k

点赞数

分类专栏： asp.net 文章标签： NPOI excel

本文链接：https://blog.csdn.net/wybshyy/article/details/82898649

版权

asp.net 专栏收录该内容

157 篇文章 1 订阅

订阅专栏

protected void Button3_Click(object sender, EventArgs e)
        {
            DataTable dt = null;
            string tempprovince = "";
            string sql = "";
            List<StringBuilder> listtabler = new List<StringBuilder>();
            string sql2 = "select province from hq_applytry group by province ";
            DataTable dt2 = DbHelp.GetDataTable(sql2);
            HSSFWorkbook book = new HSSFWorkbook();
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            if (dt2.Rows.Count > 0)
            {
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    tempprovince = dt2.Rows[i]["province"].ToString();
                    sql = "select t.* from hq_applytry t where province='" + tempprovince + "' ";
                    dt = DbHelp.GetDataTable(sql);
                    DataTable tempTable = dt;
                    ICellStyle styleTitle = book.CreateCellStyle();
                    styleTitle.WrapText = true;//自动换行
                    //增加sheet
                    HSSFSheet sheet = (HSSFSheet)book.CreateSheet(tempprovince);//Sheet名称
                    sheet.SetColumnWidth(1, 20 * 256);
                    sheet.SetColumnWidth(3, 15 * 256);
                    sheet.SetColumnWidth(10, 30 * 256);
                    
                    HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                    //foreach (DataColumn column in tempTable.Columns)
                    //{
                    //    //创建列
                    //    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    //}
                    headerRow.CreateCell(0).SetCellValue("ID");
                    headerRow.CreateCell(1).SetCellValue("公司名称");
                    headerRow.CreateCell(2).SetCellValue("法人/联系人");
                    headerRow.CreateCell(3).SetCellValue("电话");
                    headerRow.CreateCell(4).SetCellValue("微信号");
                    headerRow.CreateCell(5).SetCellValue("团队人数"); 
                    headerRow.CreateCell(6).SetCellValue("省");
                    headerRow.CreateCell(7).SetCellValue("市/县/区");
                    headerRow.CreateCell(8).SetCellValue("乡/镇/街道");
                    headerRow.CreateCell(9).SetCellValue("推荐人");
                    headerRow.CreateCell(10).SetCellValue("申请理由");
                    headerRow.CreateCell(11).SetCellValue("申请时间");
                    int rowIndex = 1;
                    foreach (DataRow row in tempTable.Rows)
                    {
                        //创建行
                        HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in tempTable.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        }
                        rowIndex++;
                    }
                }
                book.Write(ms);
                byte[] strmByte = ms.ToArray();
                ms.Dispose();
                Response.ClearContent();
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/octet-stream";
                Response.AddHeader("Content-Disposition", "attachment;fileName=" + "加盟"+ DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
                Response.BinaryWrite(strmByte);
            }
        }