• .net NPOI导出EXCEL多个sheet


    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);
                }
            }
  • 相关阅读:
    OSPF Configuration Examples
    enabling ip forwarding
    LeetCode 153. Find Minimum in Rotated Sorted Array
    洛谷 P1059 明明的随机数
    LeetCode 120. Triangle
    洛谷 P1047 校门外的树(待完善)
    C++万能头文件<bits/stdc++.h>的内容与优缺点
    LeetCode 217. Contains Duplicate
    LeetCode 414. Third Maximum Number
    洛谷 P1540 机器翻译
  • 原文地址:https://www.cnblogs.com/wybshyy/p/13783708.html
Copyright © 2020-2023  润新知