Excel file is corrupted after generating multiple excel files using EPPLUS and Zipped using DotNetZip from an ActionResult in C#

Question

I need to export multiple excel containing data from database. I'm using EPPLUS to do that. Since I can't return multiple file from an ActionResult I've to ZIP the excel files and then return that ZIP file. I've successfully zipped it and downloaded the zip file but when I try to open any excel file in the zip file it says my excel file is corrupted. If there is a single excel file in the zip file it works fine.

Here is my code-

public ActionResult ExportExcel()
        {
            try
            {
                projects="project1,project2";
                var memoryStream = new MemoryStream();
                var zip = new ZipFile();
                var arrProject = projects.Split(',');
                foreach (var pro in arrProject)
                {
                    var v = GetProjectData(pro);//method to get data from database
                    //Construct DataTable
                    var dt = new DataTable();
                    dt.Columns.Add("Model", typeof(string));
                    dt.Columns.Add("Color", typeof(string));
                    dt.Columns.Add("BarCode", typeof(long));
                    dt.Columns.Add("BarCode2", typeof(long));
                    //Load data to DataTable
                    foreach (var item in v)
                    {
                        var row = dt.NewRow();
                        row["Model"] = item.Model;
                        row["Color"] = item.Color;
                        row["BarCode"] = Int64.Parse(item.BarCode);
                        row["BarCode2"] = Int64.Parse(item.BarCode2);
                        dt.Rows.Add(row);
                    }
                    //transfer data from DataTable to worksheet
                    using (var package = new ExcelPackage())
                    {
                        var worksheet = package.Workbook.Worksheets.Add("IMEI");
                        worksheet.Cells["A1"].LoadFromDataTable(dt, PrintHeaders: true);
                        for (var col = 1; col < dt.Columns.Count + 1; col++)
                        {
                            worksheet.Column(col).AutoFit();
                        }
                        zip.AddEntry(pro + ".xlsx", package.GetAsByteArray());
                        zip.Save(memoryStream);
                    }
                }
                return File(memoryStream.ToArray(), System.Net.Mime.MediaTypeNames.Application.Zip, "report.zip");
            }
            catch (Exception ex)
            {
                
            }
            
        }

Answer 1

The problem with your code at line zip.Save(memoryStream); which is being saving on each file.

Move that line before return statement would make one zip file containing all files. Convert to as follows.

zip.Save(memoryStream);
return File(memoryStream.ToArray(), System.Net.Mime.MediaTypeNames.Application.Zip, "report.zip");