npoi 根据需要一次导出多个文件xls(文件全都存放在zip中) 最后导出zip

b/s项目
根据需要一次导出多个文件(文件全都存放在zip中) 最后导出zip
先将文件全部存放在服务器上
然后根据文件路径生成zip 也存在服务器上
最后从服务器导出zip
public FileResult GetatmkeytableExport(string atmid)
        {
            List<Guid> atmids = atmid.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(a => a.TogGuid()).ToList();
            var atmlist = _iAtmAtmService.GetAll().Where(a => atmids.Contains(a.Id)).ToList();
            var atmboxs = _iAtmBoxService.GetAll().Where(a => atmids.Contains(a.ReturnAtmId.Value)).ToList();
            var atmlogs = _AtmDisableLogService.GetAll().Where(a => atmids.Contains(a.AtmId)).ToList();
            string savePath = "/Upload/Reports/ReturnCassette";
            string dtall = DateTime.Now.ToString("yyMMddHHmmssfff");
            string dirPath = Server.MapPath(string.Format("{0}/{1}/", savePath, dtall));
            List<string> cgfilelist = new List<string>();
            //excel
            foreach (var item in atmids)
            {
                var atmboxInids = atmboxs.Where(a => a.ReturnAtmId == item);
                var atmboxInatmid = atmlist.FirstOrDefault(a => a.Id == item);
                var atmlog = atmlogs.FirstOrDefault(a => a.AtmId == item);
                string configurl = System.Configuration.ConfigurationSettings.AppSettings["TempletsPath"].ToString();
                string FileFullPath = Server.MapPath(configurl) + "ReturnCassette.xls";
                if (!System.IO.File.Exists(FileFullPath))
                {
                    return null;
                }
                HSSFWorkbook workbook = new HSSFWorkbook();
                using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read))
                {
                    workbook = (HSSFWorkbook)WorkbookFactory.Create(file);
                }
                HSSFSheet sheettmp = (HSSFSheet)workbook.GetSheetAt(0);
                //sheettmp

                //sheettmp.ShiftRows(10, sheettmp.LastRowNum, hang.Max(a => a.Count()), true, false);
                //NPOIHelper.CopyRow(sheettmp, 10, 10, hang.Max(a => a.Count()));
                //


                ((HSSFRow)sheettmp.GetRow(3)).GetCell(0).SetCellValue("Date / วันที่ _______" + atmlog.OperationTime.Value.ToString("dd/MM/yyyy") + "___________________");
                ((HSSFRow)sheettmp.GetRow(4)).GetCell(0).SetCellValue("Customer / ลูกค้า   _____________" + atmboxInatmid.AtmCustomer.Name + "__________________");
                ((HSSFRow)sheettmp.GetRow(5)).GetCell(0).SetCellValue("Location / สถานที่ ______________" + atmboxInatmid.ATMName + "__________________");
                ((HSSFRow)sheettmp.GetRow(5)).GetCell(0).SetCellValue("ATM Machine number / หมายเลขเครื่องตู้ ATM ___________" + atmboxInatmid.AtmNo + "_____________");
                if (atmboxInids.Count() > 0)
                {
                    var hang = atmboxInids.Select(a => a.SysCurrencyItem).GroupBy(a => a.CurrencyItemName);

                    var row = sheettmp.GetRow(10);
                    NPOIHelper.MyInsertRow(sheettmp, 11, (hang.Max(a => a.Count()) - 1), (HSSFRow)row);

                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(0).SetCellValue(atmboxInatmid.AtmAtmType.Name);
                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(1).SetCellValue(hang.Where(a => a.Key == "1000A").FirstOrDefault() != null ? hang.Where(a => a.Key == "1000A").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(2).SetCellValue(hang.Where(a => a.Key == "1000B").FirstOrDefault() != null ? hang.Where(a => a.Key == "1000B").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(3).SetCellValue(hang.Where(a => a.Key == "500").FirstOrDefault() != null ? hang.Where(a => a.Key == "500").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(4).SetCellValue(hang.Where(a => a.Key == "100").FirstOrDefault() != null ? hang.Where(a => a.Key == "100").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9)).GetCell(5).SetCellValue(hang.Where(a => a.Key == "Reject").FirstOrDefault() != null ? hang.Where(a => a.Key == "Reject").FirstOrDefault().Count() : 0);

                    for (var hang1 = 0; hang1 < hang.Max(a => a.Count()); hang1++)
                    {
                        //((HSSFRow)sheettmp.GetRow(hang1 + 10)).CreateCell(0);
                        ((HSSFRow)sheettmp.GetRow(hang1 + 10)).GetCell(0).SetCellValue("Barcode");
                    }
                    //
                    foreach (var hangitem in hang)  // 1000A 1000B 500 100 Reject 
                    {
                        var data10 = 10;
                        if (hangitem.Key == "1000A")
                        {
                            var i = 0;
                            foreach (var Keyitem in hangitem)
                            {
                                //((HSSFRow)sheettmp.GetRow(data10)).CreateCell(1);
                                ((HSSFRow)sheettmp.GetRow(data10)).GetCell(1).SetCellValue(atmboxInids.Where(a => a.SysCurrencyItemId == Keyitem.Id).Skip(i).FirstOrDefault().AtmBarCode.BarCode);
                                data10++;
                                i++;
                            }
                        }
                        data10 = 10;
                        if (hangitem.Key == "1000B")
                        {
                            var i = 0;
                            foreach (var Keyitem in hangitem)
                            {
                                //((HSSFRow)sheettmp.GetRow(data10)).CreateCell(2);
                                ((HSSFRow)sheettmp.GetRow(data10)).GetCell(2).SetCellValue(atmboxInids.Where(a => a.SysCurrencyItemId == Keyitem.Id).Skip(i).FirstOrDefault().AtmBarCode.BarCode);
                                data10++;
                                i++;
                            }
                        }

                        data10 = 10;
                        if (hangitem.Key == "500")
                        {
                            var i = 0;
                            foreach (var Keyitem in hangitem)
                            {
                                //((HSSFRow)sheettmp.GetRow(data10)).CreateCell(3);
                                ((HSSFRow)sheettmp.GetRow(data10)).GetCell(3).SetCellValue(atmboxInids.Where(a => a.SysCurrencyItemId == Keyitem.Id).Skip(i).FirstOrDefault().AtmBarCode.BarCode);
                                data10++;
                                i++;
                            }
                        }

                        data10 = 10;
                        if (hangitem.Key == "100")
                        {
                            var i = 0;
                            foreach (var Keyitem in hangitem)
                            {
                                //((HSSFRow)sheettmp.GetRow(data10)).CreateCell(4);
                                ((HSSFRow)sheettmp.GetRow(data10)).GetCell(4).SetCellValue(atmboxInids.Where(a => a.SysCurrencyItemId == Keyitem.Id).Skip(i).FirstOrDefault().AtmBarCode.BarCode);
                                data10++;
                                i++;
                            }
                        }

                        data10 = 10;
                        if (hangitem.Key == "Reject")
                        {
                            var i = 0;
                            foreach (var Keyitem in hangitem)
                            {
                                //((HSSFRow)sheettmp.GetRow(data10)).CreateCell(5);
                                ((HSSFRow)sheettmp.GetRow(data10)).GetCell(5).SetCellValue(atmboxInids.Where(a => a.SysCurrencyItemId == Keyitem.Id).Skip(i).FirstOrDefault().AtmBarCode.BarCode);
                                data10++;
                                i++;
                            }
                        }
                    }


                    //((HSSFRow)sheettmp.GetRow(9+hang.Max(a => a.Count())).GetCell(0).SetCellValue(atmboxInatmid.AtmAtmType.Name);
                    //((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(1).SetCellValue(hang.Where(a => a.Key == "1000A").FirstOrDefault().Count());
                    //((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(2).SetCellValue(hang.Where(a => a.Key == "1000B").FirstOrDefault().Count());
                    //((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(3).SetCellValue(hang.Where(a => a.Key == "500").FirstOrDefault().Count());
                    //((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(4).SetCellValue(hang.Where(a => a.Key == "100").FirstOrDefault().Count());
                    //((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(5).SetCellValue(hang.Where(a => a.Key == "Reject").FirstOrDefault().Count());

                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(1).SetCellValue(hang.Where(a => a.Key == "1000A").FirstOrDefault() != null ? hang.Where(a => a.Key == "1000A").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(2).SetCellValue(hang.Where(a => a.Key == "1000B").FirstOrDefault() != null ? hang.Where(a => a.Key == "1000B").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(3).SetCellValue(hang.Where(a => a.Key == "500").FirstOrDefault() != null ? hang.Where(a => a.Key == "500").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(4).SetCellValue(hang.Where(a => a.Key == "100").FirstOrDefault() != null ? hang.Where(a => a.Key == "100").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(5).SetCellValue(hang.Where(a => a.Key == "Reject").FirstOrDefault() != null ? hang.Where(a => a.Key == "Reject").FirstOrDefault().Count() : 0);
                    ((HSSFRow)sheettmp.GetRow(9 + 2 + (hang.Max(a => a.Count()).ToInt()))).GetCell(6).SetCellValue(atmboxInids.Count());

                }

                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                workbook.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);

                if (!System.IO.Directory.Exists(dirPath))
                {
                    System.IO.Directory.CreateDirectory(dirPath);
                }
                string path = dirPath + atmboxInatmid.AtmNo + "_" + dtall + ".xls";
                if (System.IO.File.Exists(path))
                {
                    System.IO.File.Delete(path);
                }
                FileStream fs = new FileStream(path, FileMode.CreateNew);
                fs.Write(ms.ToArray(), 0, ms.ToArray().Length);
                fs.Close();
                cgfilelist.Add(path);
            }
            //打包文件
            var fileName = "ReturnCassette" + "_" + dtall + ".zip";
            string downloadpath = dirPath + fileName;
            if (cgfilelist.Count > 0)
            {
                var filedata = Common.ZipUtil.CreateZip(cgfilelist).ToArray();
                if (filedata != null)
                {
                    if (!System.IO.Directory.Exists(dirPath))
                    {
                        System.IO.Directory.CreateDirectory(dirPath);
                    }
                    if (System.IO.File.Exists(downloadpath))
                    {
                        System.IO.File.Delete(downloadpath);
                    }
                    FileStream fs = new FileStream(downloadpath, FileMode.CreateNew);
                    fs.Write(filedata, 0, filedata.Length);
                    fs.Close();
                }
            }
            string outfileName = "ReturnCassette_" + dtall + ".zip";
            return File(downloadpath, "application/octet-stream", outfileName);
            //string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff
            //string fileName = "Summary_Report_CPC" + dateTime + ".xls";
            ////return File(ms, "application/vnd.ms-excel", fileName);
            //var byt = new byte[1];
            //return File(byt, "application/vnd.ms-excel", fileName);
        }



public static MemoryStream CreateZip(List<string> listPath, int level = 5)
        {
            MemoryStream mstream = new MemoryStream();
            using (ZipOutputStream zipstream = new ZipOutputStream(mstream))  //zip 流
            {
                zipstream.SetLevel(level);
                Crc32 crc = new Crc32();
                foreach (var path in listPath)
                {
                    FileStream fs = File.Open(path, FileMode.Open);
                    //重置流的位置  
                    fs.Position = 0L;
                    byte[] buffer = new byte[fs.Length];
                    fs.Read(buffer, 0, buffer.Length);

                    //ZIP文件条目  
                    ZipEntry entry = new ZipEntry(Path.GetFileName(path));   // zip中每个文件
                    entry.DateTime = DateTime.Now;
                    entry.Size = fs.Length;
                    fs.Close();

                    crc.Reset();
                    crc.Update(buffer);
                    //冗余校验码  
                    entry.Crc = crc.Value;

                    zipstream.PutNextEntry(entry);
                    zipstream.Write(buffer, 0, buffer.Length);
                }
                //ZipOutputStream关闭后不关闭mstream  
                zipstream.IsStreamOwner = false;
            }
            //重置流的位置  
            mstream.Position = 0L;
            return mstream;    // 返回流
        }