Export zipped excel file - Looking for best practice

[Julien]

Hi,

I would like to export some excel files in a zip without writing on disk.

I have seen ZipLibrary example and I would like to adapt it with excel (if possible).

The idea is to do something like this in "OnClick" event for exemple (see code bellow)

Thanks in advance for your help

using (MemoryStream stream = new MemoryStream())
{
    using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Create, leaveOpen: true, entryNameEncoding: null))
    {
        //I don't need TXT but just for testing ...
        using (ZipArchiveEntry entry = archive.CreateEntry("text1.txt"))
        {
            StreamWriter writer = new StreamWriter(entry.Open());
            writer.WriteLine("Hello world!");
            writer.Flush();
        }

        //Adding excel file
        using (ZipArchiveEntry entry = archive.CreateEntry("testxlsx.xlsx"))
        {
            //How to handle / edit XLSX file here ?
        }
    }

    Response.Clear();
    Response.AddHeader("content-disposition", "attachment; filename=zipfile.zip");
    Response.ContentType = "application/zip";
    Response.BinaryWrite(stream.ToArray());
    Response.End();
}

[Dimitar]

Hello Julien,

I believe that you can use the approach described here: Create an archive from a list of files.

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

[Julien]

Hi Dimitar,

Thanks for your answer.

As i said in my first post, il would like to do it without writing file on disk.

Do you mean it's impossible to do that ?

Regards,

[Dimitar]

Hi Julien,

No, the example uses FileStreams that can be replaced with MemoryStreams for example (for both the zip and the XLSX file). 

Please let me know if there is anything else I can help you with. 

Regards,
Dimitar
Progress Telerik

[Julien]

Hi again,

Thx, i will try this and come back if necessary :-)

Regards,

[Julien]

Hi Again,

I have replaced FileStream by MemoryStream as you suggested and it runs without error ! Thanks !

The new issue I have now is I can't read the file, Excel says "format or extention is not correct, check if file is not damaged ..."

Is it something regarding compression ?

Find new code bellow

Regards,

using (MemoryStream stream = new MemoryStream())
{
    using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Create, leaveOpen: true, entryNameEncoding: null))
    {
        //I don't need TXT but just for testing ...
        using (ZipArchiveEntry entry = archive.CreateEntry("text1.txt"))
        {
            StreamWriter writer = new StreamWriter(entry.Open());
            writer.WriteLine("Hello world!");
            writer.Flush();
        }

        //Adding excel file
        using (ZipArchiveEntry entry = archive.CreateEntry("testxlsx.xlsx"))
        {
            var entryStream = entry.Open();
            MemoryStream ms = new MemoryStream();

            using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, ms))
            {
                using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("SHEET_TEST"))
                {
                    worksheet.SkipRows(1);
                    using (IRowExporter row = worksheet.CreateRowExporter())
                    {
                        row.SkipCells(1);
                        using (ICellExporter cell = row.CreateCellExporter())
                        {
                            cell.SetValue("TEST CELL VALUE");
                        }
                    }
                }
            }

            ms.CopyTo(entryStream);
            entryStream.Flush();

            ms.Close();
            ms.Dispose();
        }
    }

    Response.Clear();
    Response.AddHeader("content-disposition", "attachment; filename=zipfile.zip");
    Response.ContentType = "application/zip";
    Response.BinaryWrite(stream.ToArray());
    Response.End();
}

[Dimitar]

Hi Julien,

Your approach is correct. One more thing that you need to do is reset the stream position before calling the CopyTo method: 

ms.Seek(0, SeekOrigin.Begin);
ms.CopyTo(entryStream);

Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

[Julien]

Hi,

It works fine, thanks again for your help !

Regards,