.net中的Webform使用NPOI导出excel

.net中的Webform使用NPOI导出excel

private void ExprotExcelGuanXiaYan(DataSet ds, ZF.Model.UserInfo uModel)
        {
            try
            {
                //行号
                int rowsNum = 1;
                //页码
                int sheetnum = 2;

                DataTable dtable = ds.Tables[0];
                if (dtable != null)
                {
                    Response.ContentType = "application/vnd.ms-excel";
                    string fileName = HttpUtility.UrlEncode("AA商户身份证号" + DateTime.Now.ToString("yyyyMMdd") + ".xls");
                    //添加http协议报文;
                    Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);

                    //创建一个xls；
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    //创建一个Sheet页  
                    HSSFSheet sheet = workbook.CreateSheet("身份证号1") as HSSFSheet;

                    HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                    sheet.SetColumnWidth(0, 5500);
                    sheet.SetColumnWidth(1, 18000);

                    //创建列头
                    HSSFCell cell0 = headerRow.CreateCell(0) as HSSFCell;
                    cell0.SetCellValue("商户号");
                    HSSFCell cell1 = headerRow.CreateCell(1) as HSSFCell;
                    cell1.SetCellValue("身份证号");

                    foreach (DataRow dr in dtable.Rows)
                    {
                        //每页的行数
                        //if (rowsNum % 60 == 1 && rowsNum != 1)
                        if (rowsNum % 50000 == 1 && rowsNum != 1)
                        {
                            //超过页的指定行数时，创建一个新的Sheet页
                            sheet = workbook.CreateSheet("身份证号" + sheetnum) as HSSFSheet;

                            headerRow = sheet.CreateRow(0) as HSSFRow;
                            sheet.SetColumnWidth(0, 5500);
                            sheet.SetColumnWidth(1, 18000);

                            cell0 = headerRow.CreateCell(0) as HSSFCell;
                            cell0.SetCellValue("商户号");
                            cell1 = headerRow.CreateCell(1) as HSSFCell;
                            cell1.SetCellValue("身份证号");

                            rowsNum = 1;
                            sheetnum++;
                        }
                        //根据字段名找出ID
                        string MERCODE = dr["MERCODE"].ToString();
                        string LEGALIDCARD = dr["LEGALIDCARD"].ToString();

                        ShaHelper sh = new ShaHelper();
                        string LEGALIDCARDStr = sh.SHA256Encrypt(LEGALIDCARD);

                        /******************以上代码对应数据库表中的字段*********************/
                        HSSFRow row = sheet.CreateRow(rowsNum) as HSSFRow;
                        row.CreateCell(0, CellType.STRING).SetCellValue(MERCODE);
                        row.CreateCell(1, CellType.STRING).SetCellValue(LEGALIDCARDStr);
                        /******************以上代码对应Excel文件的列************************/
                        rowsNum++;
                    }
                    //输出到流中
                    workbook.Write(Response.OutputStream);
                }
            }
            catch (Exception e)
            {
                LogsManager.Info("AA商户身份证号导出异常" + e.Message + e.Source + e.StackTrace);
            }
            finally
            {
                if (null != Response.OutputStream)
                {
                    Response.OutputStream.Dispose();
                    Response.OutputStream.Close();
                }
            }
        }

 

翻译

搜索

复制