Can able to generate an Excel Report and push it to Client machine without Saving the file in Server

Question

Currently I m getting data from table and converting it to a excel workbook , then save it in the server space and then using Response class push the file to the client machine. the code works fine. But i have an idea of not storing the file in physical storage as it increases server storage and directly pushing it to the client machine. Kindly advice if it is possible in any way. Thanks in advance.

protected  void GenerateExcelFile()
{
    string data = null;
    int i = 0;
    int j = 0;

    Application xlApp;
    Workbook xlWorkBook;
    Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
    DataSet ds = new DataSet();
    ds = GetTableData();// Returns dataset
    for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
    {
        xlWorkSheet.Cells[1, k + 1] = ds.Tables[0].Columns[k].ColumnName;
    }
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
        {
            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
            xlWorkSheet.Cells[i + 2, j + 1] = data;
        }
    }

    xlWorkBook.SaveAs("c:\\csharp.net-informations.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);


    string FileName = "Report.xls";
    string filepath = "c:\\csharp.net-informations.xls";
    WebClient req = new WebClient();
    HttpResponse response = HttpContext.Current.Response;
    FileInfo file = new FileInfo(filepath);
    response.Clear();
    response.ClearContent();
    response.ClearHeaders();
    response.Buffer = true;
    Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName);
    Response.AddHeader("Content-Length", file.Length.ToString());
    Response.ContentType = ReturnExtension(file.Extension.ToLower());
    Response.TransmitFile(file.FullName);
    response.End();

pushing to the client machine means downloading into the browser, Am i right? — Vinay Pratap Singh Bhadauria, Sep 23 '15 at 07:03
Yes downloading into client browser. My doubt is instead of saving as a file in server and then picking it in response class is there any possibility to connect these steps directly (possibly any temp storage, or getting as bytes from workbook class) — Ramkumar, Sep 23 '15 at 07:34
this is possible, wait for me to post an answer after i have my lunch — Vinay Pratap Singh Bhadauria, Sep 23 '15 at 07:35

score 0 · Answer 1 · answered Sep 23 '15 at 07:15

I use this and this directly download excel in client PC

            System.IO.StringWriter objStringWriter1 = new System.IO.StringWriter();
            System.Web.UI.WebControls.GridView gridView1 = new System.Web.UI.WebControls.GridView();
            System.Web.UI.HtmlTextWriter objHtmlTextWriter1 = new System.Web.UI.HtmlTextWriter(objStringWriter1);

            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);

            gridView1.DataSource = tempDataTable;
            gridView1.DataBind();
            gridView1.RenderControl(objHtmlTextWriter1);

            gridView1.Dispose();
            HttpContext.Current.Response.Write(objStringWriter1.ToString());

            HttpContext.Current.Response.End();

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

If you are creating server side Excel sheet then you must have to save it before send it to client.

If you don't want to save it in server then you can simply send response in list format then you can create excel sheet in client side . Create Excel sheet in Javascript

edited May 23 '17 at 12:15

Community

1
1

answered Sep 23 '15 at 07:18

Shrinath

364
1
13

score 0 · Answer 3 · answered Feb 26 '16 at 23:50

Use the OOXML library instead of the Microsoft.Office.Interop.Excel. The library package is called EPPlus. This is how you set it up in your project:

From Visual Studio's Package Manager Console, type: Install-Package EPPlus (this will install the library and the reference as needed)
Add this using statement (using OfficeOpenXml;) to your code

Here is some sample code for using the OOXML library. You will need to create an ExcelPackage:

    ExcelPackage pck = new ExcelPackage();
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("exported data");
    // Excel sheet headings
    ws.Cells[1, 1].Value = "Column 1 title";
    ws.Cells[1, 2].Value = "Column 2 title";

Now use the following code to save the excel file on the client:

        Byte[] fileBytes = pck.GetAsByteArray(); // convert excel data to bytes

        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=\"fileName1.xlsx\"");
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 
        Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
        Response.BinaryWrite(fileBytes);
        Response.End();

Can able to generate an Excel Report and push it to Client machine without Saving the file in Server

3 Answers3