NPOI 多个Excel合并为一个Excel

实现代码

Nuget 中搜索并安装NPOI

添加引用using NPOI.HSSF.UserModel;

            var fullWork = new HSSFWorkbook();//最终的合并excel
            string exportReports = hidExportReports.Value;//需要合并的Excel文件路径
            if (!string.IsNullOrEmpty(exportReports))
            {
                var reportArrary = exportReports.Split(',');
                try
                {                   
                    for (int i = 0; i < reportArrary.Length; i++)
                    {
                        string sheetName = Path.GetFileNameWithoutExtension(reportArrary[i]).Split('_')[1];//获取sheet名称
                        var excelStream = new FileStream(reportArrary[i], FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                        var workbook = new HSSFWorkbook(excelStream);//读取excel文件
                        HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet;  //获取第一个工作表（sheet）
                        sheet.CopyTo(fullWork, sheetName, true, true);//将报表合并至综合报表中
                    }
                    var stream = new MemoryStream();
                    fullWork.Write(stream);
                    byte[] bytes = stream.ToArray();
                    // Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
                    Response.Buffer = true;
                    Response.Clear();
                    Response.ContentType = "application/vnd.ms-excel";
                    String filename = HttpUtility.UrlEncode("综合报表", Encoding.UTF8);
                    Response.AddHeader("content-disposition", "attachment; filename=" + filename + ".xls");
                    Response.BinaryWrite(bytes); // create the file
                    Response.Flush(); // send it to the client to download                   
                }
                catch
                {

                }
                finally
                {
                    //删除excel文件
                    for (int i = 0; i < reportArrary.Length; i++)
                    {
                        File.Delete(reportArrary[i]);
                    }
                }
            }