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

  • Question

  • User696604810 posted

    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;
            }

    Saturday, June 6, 2020 2:26 AM

Answers

  • User-939850651 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2020 10:11 AM

All replies

  • User303363814 posted

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

    Saturday, June 6, 2020 10:46 PM
  • User-939850651 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2020 10:11 AM