CODEWITHSUNDEEP
c#excelinfragisticsignite-ui
abc123
abc123

Reputation: 262

Export data to excel is not working

I am using Infragistics Excel Export framework for exporting data to excel through ajax post

Here is code at client side

  $.ajax({
        type: 'post',           
        url: '../Common/ExcelExporter',
        data: {
            tbname: parameter, pageNumber: _pageNumber, pageSize: _pageSize
            , exportType: true, exportFormat: true
        },
        success: function (res, status, xhr) {
        alert(JSON.stringify(xhr));
    }
    });

and the code at controller side

public void ExcelExporter(string tbname,int pageNumber, int pageSize, bool exportType, bool exportFormat)
{
    pageNumber++;
    IEnumerable exportdata = dbList.getClass(tbname);
    bool exportAllPages = exportType;

    if (exportdata == null) {
        var obj = Activator.CreateInstance(Assembly.GetExecutingAssembly().GetType(assemblyName + ".Models." + tbname));        

        if (exportAllPages)
        {
           exportdata = (from e in dbList.getClass(tbname).AsQueryable() select e).AsQueryable();
        }
        else
        {
            exportdata = (from e in dbList.getClass(tbname).AsQueryable() select e).AsQueryable().Skip(pageNumber).Take(pageSize);
        }

    WorkbookFormat excelFormat;

    if (exportFormat)
        excelFormat = WorkbookFormat.Excel2007;
    else
        excelFormat = WorkbookFormat.Excel97To2003;

    ExcelExportingModel exportModel = new ExcelExportingModel(excelFormat);
    exportModel.PopulateExcelWorkbook(exportdata);
    SendForDownload(exportModel.ExcelWorkbook, excelFormat);            
}

[SecuritySafeCritical]
private void SendForDownload(Workbook document, WorkbookFormat excelFormat)
{
    string documentFileNameRoot;          
    documentFileNameRoot = string.Format("Document.{0}", excelFormat == WorkbookFormat.Excel97To2003 ? "xls" : "xlsx");           
    Response.Clear();
    Response.AppendHeader("content-disposition", "attachment; filename=" + documentFileNameRoot);
    Response.ContentType = "application/octet-stream";           
    document.SetCurrentFormat(excelFormat);
    document.Save(Response.OutputStream);       
    Response.End();           
}

The code is executing without any error but there is no output.No file is being downloaded. when i checked the response from the controller i am getting this

Output:

{
 "readyState":4,
 "responseText":"PK\u0002\u\<?xml version=\"1.0\" encoding=\"utf-8\"?>Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Type= \"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\" Target=\"docProps/app.xml\" Id=\"rId1\" /><Relationship Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\"Target=\"docProps/core.xml\" Id=\"rId2\" /><Relationship Type= \"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\"Target=\"xl/workbook.xml\" Id=\"rId3\" />/Relationships>PK\u[Content_Types].xmlPK\u0005\u0000",
 "status":200,
 "statusText":"OK"
}

I have taken reference for code from this link

Please help to solve this

Upvotes: 1

Views: 1934

Answers (1)

flytzen
flytzen

Reputation: 7438

@Nilesh is on the right track, but you want to use an actual memory stream, not a temporary file.

Replace 

document.Save(Response.OutputStream);

with 

using (var ms = new MemoryStream())
{
    document.Save(ms);
    ms.Position = 0;
    ms.CopyTo(Response.OutputStream);
}

I have seen this with other Excel writer libraries. The reason is that not all streams are created equal; Some streams, like files and memory streams, allow you to move around in them, using things like the Position property. Other streams can only be read to or written from; In principle when you write to Response.OutputStream each character you write to the stream is sent to the user's browser so you can't "go back" (in practice the response stream may or may not be buffered but that is another discussion). The Excel library needs the ability to navigate inside the stream, hence why it is unable to save directly to the OutputStream.

Do bear in mind that using this approach you will have two copies of the spreadsheet in memory; one in the Excel library and a copy in the memory stream. This shouldn't be a problem unless you are creating lots of large spreadsheets in parallel, but you should be mindful of it.

Upvotes: 1

Related Questions