How to Export a Jqgrid Data to Excel Using C#

Exporting data from jqgrid to excel sheet

This problem was occurring due to the presence of UpdatePannel control when this was removed it worked fine.

jqgrid/mvc 3 - Export to excel and raise a file download dialog?

Please change the code as below

Instead of returning View() return new EmptyResult();

also Response.ContentType="application/vnd.ms-excel"

If your code is not working, you can use NPOI for Excel file manipulation

jqgrid + EF + MVC: How to export in excel? Which method you suggest?

As I wrote before (see here and here for example) the best way to export grid data to XML is the usage of Open XML SDK 2.0.

The post of Dr Stephen Walther shows how to create HTML file which can be read by Excel. It's not Excel file and have to be still converted to Excel format. The usage of CSV has even more problems. Depend on the content in the source table the automatic conversion to Excel data types can be absolutely wrong. In one project which I developed for a customer the grid contained information about software products: product name, version, and so on. The software version looks sometime as the date (1.3.1963 for example) and such cells will be wrong converted (in German one use '.' as the separator in the date). As the result one had really hard problems. The usage of CSV with texts having commas inside will be also frequently wrong imported. Even when one quotes the cells having commas (,) and escaped the texts having quotas the import still be wrong especially in the first column. I don't want to explain here the whole history of all attempts and errors, but after all I decide to give up with the usage of CSV and HTML and started to use Open XML SDK 2.0 which allows to create real Excel files with extension XLSX. The way seems me perfect because one don't need any Office
components installed on the server, no additional licenses.

The only restriction is that one should be able to use DocumentFormat.OpenXml.dll, so your server program should run on any Windows operation system. As it's well known, XLSX file is ZIP file which contains some XML files inside. If you still don't know that I recommend you to rename the XLSX file to ZIP file and extract it. The Open XML SDK 2.0 is the library which works with XLSX file like with XML files. So no additional Office components are required.

One can find a lot of information how to use Open XML SDK 2.0 (see here, here and here). Many helpful code examples one cam find directly on the MSDN (see here). Nevertheless the practical usage of Open XML SDK 2.0 is not so easy at least at the first time. So I created a demo from the parts of the code which I used myself.

You can download the demo project from here. The demo is an extension of the demos from the answer and this one.

To export data I use the DataForExcel helper class. It has constructor in the form

DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data,
             string sheetName)

or in a little simplified form

DataForExcel(string[] headers, List<string[]> data, string sheetName)

and the only public method

CreateXlsxAndFillData(Stream stream)

The usage of the class to create Excel file can be like the following

var excelData = new DataForExcel (
    // column Header
    new[]{"Col1", "Col2", "Col3"},
    new[]{DataForExcel.DataType.String, DataForExcel.DataType.Integer,
          DataForExcel.DataType.String},
    new List<string[]> {
        new[] {"a", "1", "c1"},
        new[] {"a", "2", "c2"}
    },
    "Test Grid");
Stream stream = new FileStream ("Test.xlsx", FileMode.Create);
excelData.CreateXlsxAndFillData (stream);
stream.Close();

The usage in the demo from ASP.NET MVC is the following

static readonly string[] HeadersQuestions = {
    "Id", "Votes", "Title"
};
static readonly DataForExcel.DataType[] ColunmTypesQuestions = {
    DataForExcel.DataType.Integer,
    DataForExcel.DataType.Integer,
    DataForExcel.DataType.String
};

public ActionResult ExportAllQuestionsToExcel () {
    var context = new HaackOverflowEntities ();
    var questions = context.Questions;
    questions.MergeOption = MergeOption.NoTracking; // we don't want to update the data

    // to be able to use ToString() below which is NOT exist in the LINQ to Entity
    // we should include in query only the properies which we will use below
    var query = questions.ToList ();
    if (query.Count == 0)
        return new EmptyResult ();
    var data = new List<string[]> (query.Count);
    data.AddRange (query.Select (item => new[] {
        item.Id.ToString(CultureInfo.InvariantCulture),
        item.Votes.ToString(CultureInfo.InvariantCulture),
        item.Title
    }));

    return new ExcelResult (HeadersQuestions, ColunmTypesQuestions, data,
                            "Questions.xlsx", "Questions");
}

where ExcelResult are defined as

public class ExcelResult : ActionResult {
    private readonly DataForExcel _data;
    private readonly string _fileName;

    public ExcelResult (string[] headers, List<string[]> data, string fileName, string sheetName) {
        _data = new DataForExcel (headers, data, sheetName);
        _fileName = fileName;
    }

    public ExcelResult (string[] headers, DataForExcel.DataType[] colunmTypes, List<string[]> data, string fileName, string sheetName) {
        _data = new DataForExcel (headers, colunmTypes, data, sheetName);
        _fileName = fileName;
    }

    public override void ExecuteResult (ControllerContext context) {
        var response = context.HttpContext.Response;
        response.ClearContent();
        response.ClearHeaders();
        response.Cache.SetMaxAge (new TimeSpan (0));

        using (var stream = new MemoryStream()) {
            _data.CreateXlsxAndFillData (stream);

            //Return it to the client - strFile has been updated, so return it. 
            response.AddHeader ("content-disposition", "attachment; filename=" + _fileName);

            // see http://filext.com/faq/office_mime_types.php
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.ContentEncoding = Encoding.UTF8;
            stream.WriteTo (response.OutputStream);
        }
        response.Flush();
        response.Close();
    }
}

To make the code full I have to include the code of the class DataForExcel:

public class DataForExcel {
    public enum DataType {
        String,
        Integer
    }
    private readonly string[] _headers;
    private readonly DataType[] _colunmTypes;
    private readonly List<string[]> _data;
    private readonly string _sheetName = "Grid1";
    private readonly SortedSet<string> _os = new SortedSet<string> ();
    private string[] _sharedStrings;

    private static string ConvertIntToColumnHeader(int index) {
        var sb = new StringBuilder ();
        while (index > 0) {
            if (index <= 'Z' - 'A') // index=0 -> 'A', 25 -> 'Z'
                break;
            sb.Append (ConvertIntToColumnHeader (index / ('Z' - 'A' + 1) - 1));
            index = index % ('Z' - 'A' + 1);
        }
        sb.Append ((char)('A' + index));
        return sb.ToString ();
    }

    private static Row CreateRow(UInt32 index, IList<string> data) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            r.Append (new OpenXmlElement[] { CreateTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private Row CreateRowWithSharedStrings(UInt32 index, IList<string> data) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            r.Append (new OpenXmlElement[] { CreateSharedTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private Row CreateRowWithSharedStrings(UInt32 index, IList<string> data, IList<DataType> colunmTypes) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            if (colunmTypes != null && i < colunmTypes.Count && colunmTypes[i] == DataType.Integer)
                r.Append (new OpenXmlElement[] { CreateNumberCell (ConvertIntToColumnHeader (i), index, data[i]) });
            else
                r.Append (new OpenXmlElement[] { CreateSharedTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private static Cell CreateTextCell(string header, UInt32 index, string text) {
        // create Cell with InlineString as a child, which has Text as a child
        return new Cell (new InlineString (new Text { Text = text })) {
            // Cell properties
            DataType = CellValues.InlineString,
            CellReference = header + index
        };
    }

    private Cell CreateSharedTextCell(string header, UInt32 index, string text) {
        for (var i=0; i<_sharedStrings.Length; i++) {
            if (String.Compare (_sharedStrings[i], text, StringComparison.Ordinal) == 0) {
                return new Cell (new CellValue { Text = i.ToString (CultureInfo.InvariantCulture) }) {
                    // Cell properties
                    DataType = CellValues.SharedString,
                    CellReference = header + index
                };
            }
        }
        // create Cell with InlineString as a child, which has Text as a child
        throw new InstanceNotFoundException();
    }

    private static Cell CreateNumberCell(string header, UInt32 index, string numberAsString) {
        // create Cell with CellValue as a child, which has Text as a child
        return new Cell (new CellValue { Text = numberAsString }) {
            // Cell properties
            CellReference = header + index
        };
    }

    private void FillSharedStringTable(IEnumerable<string> data) {
        foreach (var item in data)
            _os.Add (item);
    }

    private void FillSharedStringTable(IList<string> data, IList<DataType> colunmTypes) {
        for (var i = 0; i < data.Count; i++)
            if (colunmTypes == null || i >= colunmTypes.Count || colunmTypes[i] == DataType.String)
                _os.Add (data[i]);
    }

    public DataForExcel(string[] headers, List<string[]> data, string sheetName) {
        _headers = headers;
        _data = data;
        _sheetName = sheetName;
    }

    public DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data, string sheetName) {
        _headers = headers;
        _colunmTypes = colunmTypes;
        _data = data;
        _sheetName = sheetName;
    }

    private void FillSpreadsheetDocument(SpreadsheetDocument spreadsheetDocument) {
        // create and fill SheetData
        var sheetData = new SheetData ();

        // first row is the header
        sheetData.AppendChild (CreateRow (1, _headers));

        //const UInt32 iAutoFilter = 2;
        // skip next row (number 2) for the AutoFilter
        //var i = iAutoFilter + 1;
        UInt32 i = 2;

        // first of all collect all different strings in OrderedSet<string> _os
        foreach (var dataRow in _data)
            if (_colunmTypes != null)
                FillSharedStringTable (dataRow, _colunmTypes);
            else
                FillSharedStringTable (dataRow);
        _sharedStrings = _os.ToArray ();

        foreach (var dataRow in _data)
            sheetData.AppendChild (_colunmTypes != null
                                      ? CreateRowWithSharedStrings (i++, dataRow, _colunmTypes)
                                      : CreateRowWithSharedStrings (i++, dataRow));

        var sst = new SharedStringTable ();
        foreach (var text in _os)
            sst.AppendChild (new SharedStringItem (new Text (text)));

        // add empty workbook and worksheet to the SpreadsheetDocument
        var workbookPart = spreadsheetDocument.AddWorkbookPart ();
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart> ();

        var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart> ();
        shareStringPart.SharedStringTable = sst;

        shareStringPart.SharedStringTable.Save ();

        // add sheet data to Worksheet
        worksheetPart.Worksheet = new Worksheet (sheetData);
        worksheetPart.Worksheet.Save ();

        // fill workbook with the Worksheet
        spreadsheetDocument.WorkbookPart.Workbook = new Workbook (
                new FileVersion { ApplicationName = "Microsoft Office Excel" },
                new Sheets (
                    new Sheet {
                        Name = _sheetName,
                        SheetId = (UInt32Value)1U,

                        // generate the id for sheet
                        Id = workbookPart.GetIdOfPart (worksheetPart)
                    }
                )
            );
        spreadsheetDocument.WorkbookPart.Workbook.Save ();
        spreadsheetDocument.Close ();
    }

    public void CreateXlsxAndFillData(Stream stream) {
        // Create workbook document
        using (var spreadsheetDocument = SpreadsheetDocument.Create (stream, SpreadsheetDocumentType.Workbook)) {
            FillSpreadsheetDocument (spreadsheetDocument);
        }
    }
}

The above code create new XLSX file directly. You can extend the code to support more data types as String and Integer which I used in the code.

In more professional version of your application you can create some XLSX templates for exporting different tables. In the code you can place the data in the cells instead, so modify the spreadsheet instead of creating. In the way you can create perfect formatted XLSX files. The examples from the MSDN (see here) will help you to implement the way when it will be required.

UPDATED: The answer contains updated code which allows generate Excel documented with more cell formatting.

jqgrid, export to excel (with current filter post data) in an asp.net-mvc site

You could put hidden fields inside the Export to Excel form:

@using (Html.BeginForm(new { action = "ExportToExcel" }))
{
    @Html.Hidden("sidx")
    @Html.Hidden("sord")
    @Html.Hidden("page")
    @Html.Hidden("rows")
    <table id="list"></table>
    <input type="submit" value="Export to Excel" />
}

and populate them upon form submission based on the current values:

$('form').submit(function () {
    var grid = $('#list');
    var sortname = grid.getGridParam('sortname');
    var sortorder = grid.getGridParam('sortorder');
    var page = grid.getGridParam('page');
    var rows = grid.getGridParam('rowNum');
    $('#sidx').val(sortname);
    $('#sord').val(sortorder);
    $('#page').val(page);
    $('#rows').val(rows);
});

This way the ExportToExcel controller action will take those parameters and be able to filter the list.

how to convert a jqgrid data to excel

In the answer you will find the code and the description why I think that one should use Open XML SDK 2.0 on the server side instead of the usage of some kind of conversion in jqGrid (on the client side).

jqgrid Export to Excel roadmap?

see Does jqgrid support exporting to excel " out of the box" or do i need to write some server side code?

I'm not affiliated with the jqGrid team, but I don't think there are any plans in the works to build the Excel export into the native jqGrid JavaScript library. You probably have to buy the php or ASP.NET libraries for that since the Excel generation requires server-side processing.

It wouldn't be too hard to write a JSON-to-CSV converter in JavaScript, but do you really want to ? Also, I can't really think of a way to serve a file to a user purely via JavaScript. This question appears to address that problem using js & Flash Generate some xml in javascript, prompt user to save it

Assuming you want to generate the Excel server-side, your best bet is to either buy the paid product, or write your own code to do it using the same datasource as the grid.

---

Related Topics