Export to Excel (.xlsx format) - Custom Implementation on JqGrid Grid

by Vijaya Malla

At work I was asked to implement Export to Excel functionality on JqGrid data. What we have at that point is just the JqGrid and two HTML buttons (one for Excel export and PDF export)

I wanted to use the existing content-type and code structure to get the most out of it. Have tried several ways like below

  • Updating the Content-Type
  • Changing the file extension from .xls to .xlsx

and nothing worked. I was either able to successfully export the file but with no data in it or was able to export the file with data, but when opened it would show a warning message which might upset or confuse or scare the users.

I have finally decided to use a 3rd party library and when searching for one I have found EPPLUS a codeplex link for my rescue.

You can follow the links at the Codeplex site and update your code to work for you. Following is how I used this library to resolve the problems I had with the export to excel (.xlsx) format.

Challenges:

Change the export format from .xls to .xlsx

Resolve the below warning message on open.

Export to Excel message: The file you are trying to open, ExportData.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening. This message should not be displayed when exporting to Excel.

Sample Data that is being sent from the JqGrid on button event (Button Click event).

"First Name\tLast Name\tTitle\tAddress\tPhone\tEmail\t\n\"Vijaya\"\t\"Malla\"\t\"Sr. Developer\"\t\"Washington DC \"\t\"(111)-222-2222\"\t\t\"sampleemail@doamin.com\"\t\n\"FirstName1\"\t\"LastName1\"\t\"Jr. Developer\"\t\"Fairfax, VA \"\t\"(222)-333-3333\"\t\t\"sampleemail1@doamin.com\"\t\n\"FirstName2\"\t\"LastName2\"\t\"Developer\"\t\"One Loudoun, VA \"\t\"(222)-333-3333\"\t\t\"sampleemail12@doamin.com\"\t\n\"FirstName3\"\t\"LastName3\"\t\"Rookie Developer\"\t\"Arlington, VA \"\t\"(222)-333-3333\"\t\t\"sampleemail13@doamin.com\"\t\n\"FirstName4\"\t\"LastName4\"\t\"CEO\"\t\"Alexandria, VA \"\t\"(222)-333-3333\"\t\t\"sampleemail14@doamin.com\"\t\n\"FirstName5\"\t\"LastName5\"\t\"CTO\"\t\"Ashburn, VA \"\t\"(222)-333-3333\"\t\t\"sampleemail15@doamin.com\"\t\n\

This is a "\t" delimited text with "\n" to separate a new line. The first set of strings are going to be the columns headings and the rest is the data for the respective columns.

One of the datatypes that the library takes is a DataTable. So it takes DataTable data and creates an Excel out of it. I have done the following to parse and convert the above data to create a DataTable.

        DataTable dt = new DataTable();
        var rows = FileData.Replace("\"", "").Split('\n');
        var columns = rows[0].Split('\t');
        for (int i = 0; i < columns.Length - 1; i++)
        {
            dt.Columns.Add(columns[i]);
        }
        for (int i = 1; i < rows.Length - 1; i++)
        {
            var dr = dt.NewRow();
            var rowData = rows[i].Split('\t');
            dr.ItemArray = rowData.Take(rowData.Length - 1).ToArray();
            dt.Rows.Add(dr);
        }

Very simple parsing, breaking the text into rows and columns. First created the columns part and then parsed each row to get the data array and created a DataRow from it and added each row to the DataTable.

Now that I have the DataTable as input to the library to create my Excel,

I have called the library specific functions to create my xlsx file.

1) I have created variables for the fileName

        var fileName = "SampleExportExcel";
        var exportfileName = HttpUtility.HtmlEncode( fileName +DateTime.Today.ToShortDateString() + ".xlsx");

2) Now that we have file name, let create the excel file.

        if (dataTable != null)
        {
            using (var excelPackage = new ExcelPackage(new MemoryStream()))
            {
                excelPackage.Workbook.Properties.Author = "Vijaya Malla";
                excelPackage.Workbook.Properties.Title = fileName;
                excelPackage.Workbook.Properties.Comments = "Export using EPPLUS library";
                excelPackage.Workbook.Worksheets.Add(fileName);

                workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);

                excelPackage.Save();

                var stream = excelPackage.Stream as MemoryStream;
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.BinaryWrite(stream.ToArray());

                return File(stream, "application/vnd.ms-excel", exportfileName);
            }
        }
        return File(new MemoryStream(), "application/vnd.ms-excel", exportfileName);

If the dataTable is null, then it returns an empty file. Which could be corrupt (I havent tested this part)

If there is data in the dataTable, then we create the Excel file

First of all we need to create an instance of ExcelPackage, which I think is an instance of excel in memory.

Now we have to add a Workbook to the package which will contain WorkSheets.

The first 4 lines within the using is to set properties to the Excel file so that it looks complete when we have a Detail view in Windows Explorer window.

Then we need a Worksheet to host the data, so we create an instance of that within the Workbook and assign the dataTable data to it starting at the first Cell [A1].

That’s all, now we Save() the Excel Package and change the package to a MemoryStream and return it to the browser which will save it to the local machine.

This is how it looks in Windows Explorer

And on open, it doesn’t show the warning dialog window and the data is rendered as below. Success.

And this is the Worksheet name.

Now that we have this, I thought it will be good to add a header above the Columns and also BOLD the column headings as below

First I thought I have to add the header as a column to the Datatable, but that was stupid. Did some research and also added some common sense. I have move the code to add the header from DataTable creation to Excel creation part.

Added this right after the workbook properties set

                var workSheet = excelPackage.Workbook.Worksheets[1];
                var title = workSheet.Cells[1, 1, 1, dataTable.Columns.Count];
                title.Merge = true;
                title.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                title.Value = "Personal Information DataTable";

So basically we are just creating "title" cell, at location: Cell[1,1,1,dataTable.Columns.Count] which in English is A1:F1

And added properties to this Cell like Merge and center the text and finally the cell value.

Since we added the header and it shows up above the column header we also have to update the dataTable render to second row.

From:

workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);

To:

workSheet.Cells["A2"].LoadFromDataTable(dataTable, true);

And also update the cell location

And add the following to bold the header row as below

                workSheet.Cells["A2"].LoadFromDataTable(dataTable, true);
                var columnHeaderRow = workSheet.Cells[2, 1, 2, dataTable.Columns.Count];
                columnHeaderRow.Style.Font.Bold = true;
                columnHeaderRow.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

This would export the data as below

There are other features that EPPLUS library can do, please explore.

Happy Coding..

Thank You

Vijaya Malla.

@vijayamalla