NPOI 根据模板导出Excel

最新推荐文章于 2024-08-14 18:18:10 发布

ChinaLzw

最新推荐文章于 2024-08-14 18:18:10 发布

阅读量6.2k

点赞数

本文为博主原创文章，只为分享而编，欢迎各位学习以及转载。

本文链接：https://blog.csdn.net/qq285679784/article/details/45025063

版权

asp.net web 同时被 3 个专栏收录

169 篇文章 1 订阅

订阅专栏

excel

6 篇文章 0 订阅

订阅专栏

npoi

4 篇文章 0 订阅

订阅专栏

    引用命名空间
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;


    /// <summary>
    /// 导出正式Excel文件，用于导入盘点数据
    /// </summary>
    /// <param name="ds"></param>
    private void ToExcel(DataSet ds)
    {
        string filePath = "";
        bool check = false;
        try
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                string filename = "KQPF_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
                //临时存放路径
                filePath = Server.MapPath("~/upload/" + filename);
                //Excel模版
                string masterPath = Server.MapPath("~/upload/Master/KQPF.xls");
                //复制Excel模版
                File.Copy(masterPath, filePath);

                // 先把文件的属性读取出来 
                FileAttributes attrs = File.GetAttributes(filePath);

                // 下面表达式中的 1 是 FileAttributes.ReadOnly 的值 
                // 此表达式是把 ReadOnly 所在的位改成 0, 
                attrs = (FileAttributes)((int)attrs & ~(1));

                FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                Workbook hssfworkbook = new HSSFWorkbook(file);
                Sheet sheet = hssfworkbook.GetSheet("Sheet1");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //[评分ID],[类型],[日期],[用户],[维修内容],[回访结果],[维修量记分],[服务人员],[满意度评分]
                    Row row = sheet.CreateRow(i + 2);
                    row.CreateCell(0).SetCellValue(dt.Rows[i]["AutoId"].ToString());
                    row.CreateCell(1).SetCellValue(dt.Rows[i]["infoType"].ToString());
                    row.CreateCell(2).SetCellValue(Convert.ToDateTime(dt.Rows[i]["WorkTime"]).ToString("yyyy-MM-dd"));
                    row.CreateCell(3).SetCellValue(dt.Rows[i]["customerName"].ToString());
                    row.CreateCell(4).SetCellValue(dt.Rows[i]["Content"].ToString());
                    row.CreateCell(5).SetCellValue(dt.Rows[i]["Result"].ToString());
                    row.CreateCell(6).SetCellValue(Convert.ToDouble(dt.Rows[i]["pf"]));
                    row.CreateCell(7).SetCellValue(dt.Rows[i]["WorkUserList"].ToString());
                    row.CreateCell(8).SetCellValue(Convert.ToDouble(dt.Rows[i]["mydpf"]));
                }
                sheet.ForceFormulaRecalculation = true;


                using (FileStream filess = File.OpenWrite(filePath))
                {
                    hssfworkbook.Write(filess);
                }

                check = true;

                // 输出副本的二进制字节流
                HttpContext.Current.Response.Charset = "UTF-8"; // 或UTF-7 以防乱码
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.GetEncoding(65001).GetBytes(filename)));
                Response.BinaryWrite(File.ReadAllBytes(filePath));
            }

        }
        catch (Exception ex)
        {
            US.Components.MessageBox.Show(this.Page, "导出失败：" + ex.Message);
        }
        finally
        {
            if (check)
            {
                //删除副本
                File.Delete(filePath);
            }
        }
    }