NPOI复制模板另存为新的excel

使用NPOI组件

 

//var table2 = doc.tables[0]; ///直接使用tables无法获取嵌套在里面的table,只能获取最外层的table
//var table3 = table2.GetRow(2).GetCell(0).Tables[0];　　//获取嵌套在表格里面的table

 

excel类

引用

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.IO;

#region 正式导出无bug
    public HSSFWorkbook DataTableToExcelOK(DataTable dt, DataTable dt1, DataTable dt2, string filePath, string tpath, bool isColumnName)
    {
       
        IRow row = null;
        ISheet sheet = null;
        ICell cell = null;
        int startRow = 0;
        IWorkbook workbook = null;
        FileInfo ff = new FileInfo(tpath);
        FileStream fs2 = null;

        if (ff.Exists)
        {
            ff.Delete();
        }
        FileStream fs = new FileStream(tpath, FileMode.Create, FileAccess.ReadWrite);
        HSSFWorkbook x1 = new HSSFWorkbook();
        x1.Write(fs);
        fs.Close();

        FileStream fileRead = new FileStream(filePath, FileMode.Open, FileAccess.Read);//打开模板
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);

        FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);//打开新创建的excel
        HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);

        HSSFSheet CPS = hssfworkbook.GetSheetAt(6) as HSSFSheet;//获取模板的sheet
        
        CPS.CopyTo(book2, "报价体系V1.5", true, true);//将模板复制到新建的excel中

        using (FileStream fileSave = new FileStream(tpath, FileMode.Open, FileAccess.Write))
        {
            book2.Write(fileSave);
            fileSave.Close();

            #region 将数据导入excel中
            using (fs2 = File.OpenRead(tpath))
            {
                // 2007版本  
                if (tpath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs2);
                // 2003版本  
                else if (tpath.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs2);


                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//总行数 
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(3);//表头行  
                            int cellCount = firstRow.LastCellNum;//表头列数  
                            if (isColumnName)
                            {
                                startRow = 3;//如果第一行是列名，则从第二行开始读取
                                if (dt != null && dt.Rows.Count > 0)
                                {

                                    sheet.GetRow(1).GetCell(1).SetCellValue(dt1.Rows[0][0].ToString());//dt1需要填充的表头数据
                                    sheet.GetRow(1).GetCell(4).SetCellValue(dt1.Rows[0][0].ToString());
                                    sheet.GetRow(1).GetCell(7).SetCellValue(dt1.Rows[0][1].ToString());

                                    int rowtbCount = Convert.ToInt32(dt.Rows.Count + 3);//datatable行数 dt内容数据 
                                    int columnCount = dt.Columns.Count;//列数  

                                    for (int i = startRow; i < rowtbCount; i++)
                                    {
                                        // row = sheet.CreateRow(i+1);
                                        row = sheet.CopyRow(i, i + 1);
                                        for (int j = 0; j < columnCount; j++)
                                        {
                                            cell = row.GetCell(j + 2);//excel第二行开始写入数据  
                                            if (j == 7)
                                            {
                                                cell.SetCellValue(dt.Rows[i - 3][j].ToDouble());//dt.Rows[i - 3][j].ToDouble()
                                            }
                                            else
                                            {
                                                cell.SetCellValue(dt.Rows[i - 3][j].ToString());
                                            }
                                        }

                                    }

                                    sheet.GetRow(rowtbCount + 2).GetCell(9).SetCellValue(dt2.Rows[0][3].ToString());
                                    sheet.GetRow(rowtbCount + 2).GetCell(16).SetCellValue(dt2.Rows[0][0].ToString());
                                    sheet.GetRow(rowtbCount + 2).GetCell(18).SetCellValue(dt2.Rows[0][1].ToString());
                                    sheet.GetRow(rowtbCount + 2).GetCell(22).SetCellValue(dt2.Rows[0][2].ToString());//dt2表尾数据

                                    using (fs2 = File.OpenWrite(tpath))
                                    {
                                        workbook.Write(fs2);//向打开的这个xls文件中写入数据  
                                    }
                                }
                            }
                        }
                    }
                }
            }
            #endregion

            return workbook as HSSFWorkbook;
        }
    }
    #endregion

 

控制器

引用

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

#region 按模板导出正确
    public FileResult ImportTemplateExcel(string bom, string ISItem)
    {

        IWorkbook book = new HSSFWorkbook();
        HSSFWorkbook newbook = new HSSFWorkbook();
        NFine.Code.Excel.NPOIExcel2 e = new NPOIExcel2();

        DataTable dt0 = costquoteapp.ImportExcelBom(bom, ISItem);//内容
        DataTable dt1 = costquoteapp.ImportExcelBomHeader(bom, null, null, Convert.ToDateTime("2018-05-05"));//表头
        DataTable dt2 = costquoteapp.ImportExcelSum(bom, ISItem);//表尾

        string fileName = "运营成本模板表V1.0.xls";//客户端保存的文件名
        string filePath = Server.MapPath("~/运营成本表V1.0.xls");//模板路径

        if (!Directory.Exists(Server.MapPath("~/TempFiles")))
        {
            Directory.CreateDirectory(Server.MapPath("~/TempFiles"));
        }
        //将生成的文件保存到服务器临时文件夹中
        //  filepath = Path.Combine(Server.MapPath("~/TempFiles"), fileName);
        //  newbook = e.SheetCopy(fileName, filePath, filePath);
        string files = Server.MapPath("~/TempFiles/内部运营成本表.xls");//新建后的excel位置

        book = e.DataTableToExcelOK(dt0, dt1, dt2, filePath, files, true);
        //写入到客户端
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        book.Write(ms);
        ms.Seek(0, System.IO.SeekOrigin.Begin);
        return File(ms, "application/vnd.ms-excel", filePath);
    }
    #endregion

前台调用方法就好啦

 

XSSFWorkbook  2007版本

HSSFWorkbook 2003版本