How to compress more than one Excel files with type xlsx to one file as zip Archive ?

2020-06-06

Question

_{Saturday, June 6, 2020 2:26 AM}

I working on c# 5 app create more than excel files xlsx

but I face Issue I cannot compress these files to one file as zip archive .

meaning if result of code below of three files as abc.xlsx and autod.xlsx and mog.xlsx as examples

then create all theses file to one file as zip archive with any name but check file creating by zip for these files

not exist before .

my code as below

static void Main(string[] args)
        {
            DataTable dataTable = GetData();

            var res = from row in dataTable.AsEnumerable()
                      group row by row["Files"] into g
                      select g;

            foreach (var item in res)
            {
                CreateWorkbook(item.Key.ToString(), item.AsEnumerable().CopyToDataTable());
            }
        }

        public static void CreateWorkbook(String file, DataTable dt)
        {
            string fileName = file + ".xlsx";
            XLWorkbook workbook;
            if (!File.Exists(fileName))
            {
                workbook = new XLWorkbook();
            }
            else
            {
                workbook = new XLWorkbook(fileName);
            }
            
            var res = from row in dt.AsEnumerable()
                      group row by row["Tab"] into g
                      select g;

            foreach (var item in res)
            {
                DataTable dataTable = item.AsEnumerable().CopyToDataTable();
                workbook.Worksheets.Add(dataTable, dataTable.Rows[0].Field<string>("Tab"));
            }

            workbook.SaveAs(fileName);
        }
        public static DataTable GetData() 
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("PartId", typeof(int));
            dataTable.Columns.Add("Company", typeof(string));
            dataTable.Columns.Add("Files", typeof(string));
            dataTable.Columns.Add("Tab", typeof(string));
            dataTable.Columns.Add("Module", typeof(int));

            dataTable.Rows.Add(1222,"micro","Abc","source",1);
            dataTable.Rows.Add(1321, "silicon", "Abc", "Types", 3);
            dataTable.Rows.Add(1444, "cd2", "AutoD", "Rev", 10);
            dataTable.Rows.Add(1321, "cd3", "AutoD", "source", 11);
            dataTable.Rows.Add(1541,"mtvscro", "AutoD", "Rev", 12);
            dataTable.Rows.Add(9811, "tvs2", "Mog", "Dal", 6);
            dataTable.Rows.Add(1901, "tvs3", "Mog", "Mondo", 6);
            dataTable.Rows.Add(2111, "toyo", "Mog", "Pingo", 7);

            return dataTable;
        }

All replies (2)

_{Wednesday, June 10, 2020 10:11 AM ✅Answered}

Hi, ahmedbarbary

I used the sample code you provided and modified it. Now it can finish saving the excel file on the server and download the compressed zip file from the browser.

More details, please refer below code:

downLoadZip.aspx

<body>
    <form id="downLoadZip" runat="server">
        <div>
            <br />
            <asp:Button ID="btnDownload" runat="server" Text="Download" OnClick="DownloadFiles" />
        </div>
    </form>
</body>

downLoadZip.aspx.cs

public partial class downloadZip : System.Web.UI.Page
    {
        public static string path=null;
        protected void Page_Load(object sender, EventArgs e){
            path = Server.MapPath("/Files/");
        }

        public static DataTable GetData()
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("PartId", typeof(int));
            dataTable.Columns.Add("Company", typeof(string));
            dataTable.Columns.Add("Files", typeof(string));
            dataTable.Columns.Add("Tab", typeof(string));
            dataTable.Columns.Add("Module", typeof(int));

            dataTable.Rows.Add(1222, "micro", "Abc", "source", 1);
            dataTable.Rows.Add(1321, "silicon", "Abc", "Types", 3);
            dataTable.Rows.Add(1444, "cd2", "AutoD", "Rev", 10);
            dataTable.Rows.Add(1321, "cd3", "AutoD", "source", 11);
            dataTable.Rows.Add(1541, "mtvscro", "AutoD", "Rev", 12);
            dataTable.Rows.Add(9811, "tvs2", "Mog", "Dal", 6);
            dataTable.Rows.Add(1901, "tvs3", "Mog", "Mondo", 6);
            dataTable.Rows.Add(2111, "toyo", "Mog", "Pingo", 7);

            return dataTable;
        }

        public static void CreateWorkbook(String file, DataTable dt)
        {
            string fileName = path + file + ".xlsx";
            if (File.Exists(fileName)) {
                File.Delete(fileName);
            }
            XLWorkbook workbook = new XLWorkbook();

            var res = from row in dt.AsEnumerable()
                      group row by row["Tab"] into g
                      select g;

            foreach (var item in res)
            {
                DataTable dataTable = item.AsEnumerable().CopyToDataTable();
                workbook.Worksheets.Add(dataTable, dataTable.Rows[0].Field<string>("Tab"));
            }

            workbook.SaveAs(fileName);
        }
        protected void DownloadFiles(object sender, EventArgs e)
        {
            DataTable dataTable = GetData();

            var res = from row in dataTable.AsEnumerable()
                      group row by row["Files"] into g
                      select g;

            foreach (var item in res)
            {
                CreateWorkbook(item.Key.ToString(), item.AsEnumerable().CopyToDataTable());
            }

            using (ZipFile zip = new ZipFile())
            {
                String[] files = Directory.GetFiles(path);
                string fileUniqueName = ".xlsx";
                foreach (string file in files)
                {
                    if (file.Contains(fileUniqueName.ToString()))
                    {
                        zip.AddFile(file);
                    }
                }
                Response.Clear();
                Response.BufferOutput = false;
                string zipName = String.Format("Zip_{0}.zip", "Excels");
                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "attachment; filename=" + zipName);
                zip.Save(Response.OutputStream);
                Response.End();
            }
        }
    }

I create a folder to save these excel files.

Just like this:

When I click button in the browser, it will download these excel files as a zip file.

Hope this can help you.

Best regards,

Xudong Peng.

_{Saturday, June 6, 2020 10:46 PM}

I think there is no zip file created because you don't have any code which attempts to create a zip file.

Share via

How to compress more than one Excel files with type xlsx to one file as zip Archive ?

Question

All replies (2)

Additional resources