NPOI导出Excel2007

来源：互联网发布：java高性能并发框架编辑：程序博客网时间：2024/06/05 17:35

public void Export_ProjectList(DataTable dt, HttpRequestBase Request)
        {
            string strModelFile = "";
            strModelFile = Request.PhysicalApplicationPath + "ProjectList.xls";//模板地址
            XSSFWorkbook workbook = null;
            FileStream file = new FileStream(strModelFile, FileMode.Open, FileAccess.Read);
            workbook = new XSSFWorkbook(file);//如果导出2003 则是HSSFWorkbook
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("ProjectList");//可以是ISheet Sheet=null;
            if (sheet == null)
            {
                return;
            }
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.WrapText = true;
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //设置详细信息
            if (dt != null && dt.Rows.Count > 0)
            {
                IRow dataRowtitle = sheet.CreateRow(0);
                dataRowtitle.Height = 500;
                dataRowtitle.CreateCell(0).SetCellValue("序号");
                dataRowtitle.CreateCell(1).SetCellValue("项目编号");
                dataRowtitle.CreateCell(2).SetCellValue("项目名称");
                dataRowtitle.CreateCell(3).SetCellValue("项目内容");
                dataRowtitle.CreateCell(4).SetCellValue("行政区");
                dataRowtitle.CreateCell(5).SetCellValue("所属分公司");
                dataRowtitle.CreateCell(6).SetCellValue("联系人");
                dataRowtitle.CreateCell(7).SetCellValue("联系人电话");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + 1);
                    dataRow.Height = 450;

                    for (int j = 0; j < 8; j++)
                    {
                        ICell iCell = dataRow.CreateCell(j);
                        iCell.CellStyle = cellStyle;
                        switch (j)
                        {
                            case 0:
                                iCell.SetCellValue(i + 1);//序号
                                break;
                            case 1:
                                iCell.SetCellValue(dt.Rows[i]["PROJECTCODE"] == null ? "" : String.Format("{0:d}", dt.Rows[i]["PROJECTCODE"]));//项目编号
                                break;
                            case 2:
                                iCell.SetCellValue(dt.Rows[i]["NAME"].ToString());//项目名称
                                break;
                            case 3:
                                iCell.SetCellValue(dt.Rows[i]["CONTENT"].ToString());//项目内容
                                break;
                            case 4:
                                iCell.SetCellValue(GetListName(GFun.SafeToInt32(dt.Rows[i]["DISTRICT"])));//行政区
                                break;
                            case 5:
                                iCell.SetCellValue(GetListName(GFun.SafeToInt32(dt.Rows[i]["BRANCH"])));//所属分公司
                                break;
                            case 6:
                                iCell.SetCellValue(dt.Rows[i]["CONTACTS"].ToString());//联系人
                                break;
                            case 7:
                                iCell.SetCellValue(dt.Rows[i]["PHONENUM"].ToString());//联系人电话
                                break;
                        }

                    }
                }
            }
            string fileName = string.Empty;
            //sheet.GetRow(0).GetCell(0).SetCellValue("项目信息");//标题
            SetExportFileName(fileName + "查询结果导出.xlsx", workbook);
        }
        public static void SetExportFileName(string sFileName, XSSFWorkbook workbook)
        {
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            sFileName = System.Web.HttpUtility.UrlEncode(sFileName, System.Text.Encoding.UTF8);
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + sFileName);

// 指定返回的是一个不能被客户端读取的流，必须被下载
System.Web.HttpContext.Current.Response.ContentType = "application/ms-Excel";

            // 把文件流发送到客户端
            workbook.Write(System.Web.HttpContext.Current.Response.OutputStream);
            // 停止页面的执行
            System.Web.HttpContext.Current.Response.End();
        }

0 0