How to export JQgrid data to Excel using C #?

Question

I have done some research, but most of the solutions are for MVC. I just use Asp.net 3.5. How can I achieve this by pressing a button. Should I include any library or something else. Please, help..

Answer 1

The code I posted in can be practically without any changes in any ASP.NET code written in C #. The auxiliary class DataForExcel (see DataForExcel.cs file) has a constructor

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

or a slightly more advanced version

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

which allow you to specify which columns have a numeric data type. The List<string[]> data parameter is the data that you want to export to Excel. The string[] headers parameter specifies the data for the first line of output.

The DataForExcel class has only one public method.

 public void CreateXlsxAndFillData(Stream stream) 

which populate the stream binary representation of the resulting Excel .XLSX file.

To return binary data from your ASP.NET method (e.g. ASHX handler), you just need to do the same thing as ExecuteResult from my answer:

• create a memory stream using using (var stream = new MemoryStream()) {...}
• create a DataForExcel object necessary for export to Excel var dataExcel = new DataForExcel (new []{"Id", "Votes", "Title"}, "Questions.xlsx", "Name or Sheet");
• response.AddHeader ("content-disposition", "attachment; filename=Questions.xlsx");
• response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
• response.ContentEncoding = Encoding.UTF8;
• stream.WriteTo (response.OutputStream);
• response.Flush();

All.

UPDATED . I modified the previously published code to make it very easy to create an Excel file (in .xlsx format) from a grid. If you are using the OpenXML SDK 2.0 , you can use .NET 3.5. OpenXML SDK 2.5 requires .NET 4.0 or higher.

The proposed code allows you to convert string[][] data into binary Excel data and write the results to Stream. You can use a MemoryStream , as I described earlier, to return Excel from any ASP.NET application.

The proposed code contains an ExportToExcel static class with one open static method FillSpreadsheetDocument , which can be used as follows

 var data = new[] { new [] {"Tom", "30", "x", "", "1974-06-16"}, new [] {"Margarita", "34", "x", "x", "1978-10-02"}, new [] {"Bob", "7", "", "", "2005-06-26"}, new [] {"Oleg", "48", "x", "x", "1964-09-11"}, new [] {"Frank", "29", "", "x", "1983-01-28"} }; using (var stream = new FileStream("Test.xlsx", FileMode.Create)) { ExportToExcel.FillSpreadsheetDocument(stream, new[] { new ColumnModel { Type = DataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" }, new ColumnModel { Type = DataType.Integer, Header = "Age" }, new ColumnModel { Type = DataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = DataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = DataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left } }, data, "Friends"); } 

He creates "Test.xlsx" with one sheet of "Friends" that looks like

The width of the columns will not be set, but in two clicks (select all and double-click between the columns), the user can set the width of the columns to the optimal width, as in the one built above. All cells have formatted data (there is no "common" format). I used integers, date and blank lines. You can easily create columns with center alignment or right justification texts.

You can easily change the code to use different text formats. This is just an example of how to create a real Excel document with formatted cells.

Visual Studio 2008 working project you can download from here . Below you will find the source code from the demo:

 using System; using System.Collections.Generic; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Text; using System.IO; using System.Globalization; namespace ExportToExcel { public enum ExcelCellDataType { String, Integer, Date } public enum HorizontalAlignment { Left, Center, Right } public class ColumnModel { public ExcelCellDataType Type { set; get; } public HorizontalAlignment Alignment { set; get; } public string Header { set; get; } public bool IsRotatedHeader { set; get; } } public enum OutputCellFormat: uint { Text, Integer, Date, TextHeader, TextHeaderRotated, TextCenter, TextRight } public static class ExportToExcel { private static StringBuilder ConvertIntToColumnHeader (uint iCol) { var sb = new StringBuilder(); while (iCol > 0) { if (iCol <= 'Z' - 'A') // iCol=0 -> 'A', 25 -> 'Z' break; sb.Append(ConvertIntToColumnHeader(iCol / ('Z' - 'A' + 1) - 1)); iCol = iCol % ('Z' - 'A' + 1); } sb.Append((char)('A' + iCol)); return sb; } private static string GetCellReference (uint iRow, uint iCol) { return ConvertIntToColumnHeader(iCol).Append(iRow).ToString(); } private static Row CreateColumnHeaderRow (uint iRow, IList<ColumnModel> colunmModels) { var r = new Row { RowIndex = iRow }; for (var iCol = 0; iCol < colunmModels.Count; iCol++) { var styleIndex = colunmModels[iCol].IsRotatedHeader ? (UInt32Value)(uint)(OutputCellFormat.TextHeaderRotated + 1) : (UInt32Value)(uint)(OutputCellFormat.TextHeader + 1); r.Append(new OpenXmlElement[] { // create Cell with InlineString as a child, which has Text as a child new Cell(new InlineString(new Text { Text = colunmModels[iCol].Header })) { DataType = CellValues.InlineString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } return r; } private static UInt32Value GetStyleIndexFromColumnModel (ColumnModel colunmModel) { switch (colunmModel.Type) { case ExcelCellDataType.Integer: return (uint)(OutputCellFormat.Integer) + 1; case ExcelCellDataType.Date: return (uint)(OutputCellFormat.Date) + 1; } switch (colunmModel.Alignment) { case HorizontalAlignment.Center: return (uint)(OutputCellFormat.TextCenter) + 1; case HorizontalAlignment.Right: return (uint)(OutputCellFormat.TextRight) + 1; default: return (uint)(OutputCellFormat.Text) + 1; } } private static string ConvertDateToString (string date) { DateTime dt; string text = date; // default results of conversion if (DateTime.TryParse(date, out dt)) text = dt.ToOADate().ToString(CultureInfo.InvariantCulture); return text; } private static Row CreateRow (UInt32 iRow, IList<string> data, IList<ColumnModel> colunmModels, IDictionary<string, int> sharedStrings) { var r = new Row { RowIndex = iRow }; for (var iCol = 0; iCol < data.Count; iCol++) { var styleIndex = (uint)(OutputCellFormat.Text) + 1; string text = data[iCol] ?? String.Empty; if (colunmModels != null && iCol < colunmModels.Count) { styleIndex = GetStyleIndexFromColumnModel(colunmModels[iCol]); switch (colunmModels[iCol].Type) { case ExcelCellDataType.Integer: r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = text }) { StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); continue; case ExcelCellDataType.Date: r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = ConvertDateToString(text) }) { StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); continue; } } // default format is text if (String.IsNullOrEmpty(text) || !sharedStrings.ContainsKey(text)) { // create Cell with InlineString as a child, which has Text as a child r.Append(new OpenXmlElement[] { new Cell(new InlineString(new Text { Text = text })) { DataType = CellValues.InlineString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } else { r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = sharedStrings[text].ToString(CultureInfo.InvariantCulture) }) { DataType = CellValues.SharedString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } } return r; } private static void FillSpreadsheetDocument (SpreadsheetDocument spreadsheetDocument, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) { if (columnModels == null) throw new ArgumentNullException("columnModels"); if (data == null) throw new ArgumentNullException("data"); // add empty workbook and worksheet to the SpreadsheetDocument var workbookPart = spreadsheetDocument.AddWorkbookPart(); var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>(); // create styles for the header and columns workbookStylesPart.Stylesheet = new Stylesheet( new Fonts( // Index 0 - The default font. new Font( new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ), // Index 1 - The bold font. new Font( new Bold(), new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ) ), new Fills( // Index 0 - required, reserved by Excel - no pattern new Fill(new PatternFill { PatternType = PatternValues.None }), // Index 1 - required, reserved by Excel - fill of gray 125 new Fill(new PatternFill { PatternType = PatternValues.Gray125 }), // Index 2 - no pattern text on gray background new Fill(new PatternFill { PatternType = PatternValues.Solid, BackgroundColor = new BackgroundColor { Indexed = 64U }, ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" } }) ), new Borders( // Index 0 - The default border. new Border( new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder() ), // Index 1 - Applies a Left, Right, Top, Bottom border to a cell new Border( new LeftBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder() ) ), new CellFormats( // Index 0 - The default cell style. If a cell does not have a style iCol applied it will use this style combination instead new CellFormat { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }, // Index 1 - Alignment Left, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Left }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 2 - Interger Number new CellFormat { NumberFormatId = (UInt32Value)1U, // "0" - integer format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 3 - Interger Date new CellFormat { NumberFormatId = (UInt32Value)14U, // "14" - date format mm-dd-yy - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 4 - Text for headers new CellFormat(new Alignment { Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 5 - Text for headers rotated new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center, TextRotation = (UInt32Value)90U }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 6 - Alignment Center, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 7 - Alignment Right, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Right }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true } ) ); workbookStylesPart.Stylesheet.Save(); // create and fill SheetData var sheetData = new SheetData(); // first row is the header uint iRow = 1; sheetData.AppendChild(CreateColumnHeaderRow(iRow++, columnModels)); //iRow++; // skip one row for the filter // first of all collect all different strings var sst = new SharedStringTable(); var sharedStrings = new SortedDictionary<string, int>(); foreach (var dataRow in data) for (var iCol = 0; iCol < dataRow.Length; iCol++) if (iCol >= columnModels.Count || columnModels[iCol].Type != ExcelCellDataType.Integer) { string text = (columnModels[iCol].Type == ExcelCellDataType.Date ? dataRow[iCol] : ConvertDateToString(dataRow[iCol])) ?? String.Empty; if (!String.IsNullOrEmpty(text) && !sharedStrings.ContainsKey(text)) { sst.AppendChild(new SharedStringItem(new Text(text))); sharedStrings.Add(text, sharedStrings.Count); } } var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); shareStringPart.SharedStringTable = sst; shareStringPart.SharedStringTable.Save(); foreach (var dataRow in data) sheetData.AppendChild(CreateRow(iRow++, dataRow, columnModels, sharedStrings)); // 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, Id = workbookPart.GetIdOfPart(worksheetPart) // generate the id for sheet }) ); spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } public static void FillSpreadsheetDocument (Stream stream, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) { using (var spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { FillSpreadsheetDocument(spreadsheetDocument, columnModels, data, sheetName); } } } class Program { static void Main () { var data = new[] { new [] {"Tom", "30", "x", null, "1974-06-16"}, new [] {"Margarita", "34", "x", "x", null}, new [] {"Bob", "7", "", "", "2005-06-26"}, new [] {"Oleg", null, "x", "x", "1964-09-11"}, new [] {"Frank", "29", "", "x", "1983-01-28"} }; using (var stream = new FileStream("Test.xlsx", FileMode.Create)) { ExportToExcel.FillSpreadsheetDocument(stream, new[] { new ColumnModel { Type = ExcelCellDataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" }, new ColumnModel { Type = ExcelCellDataType.Integer, Header = "Age" }, new ColumnModel { Type = ExcelCellDataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = ExcelCellDataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = ExcelCellDataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left } }, data, "Friends"); } } } } 

Answer 2

php + jqgrid + export to Excel

Although this is a PHP solution, its core seems to be in Javascript if that helps.

Answer 3

You can use the Office XML format (http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats). The only drawback is the output of the XML file, not binary. However, it opens and behaves exactly the same as a regular .xls file.

What I did before is to reverse engineer Excel in the format I need, then save it as an .xml format, open it in Notepad ++ and find out the header, the table (converted from the grid) and finally the footer

Example:

Header (can be hardcoded)

 <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>phpGrid.com</Author> <Created></Created> <LastSaved></LastSaved> <Version></Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>768</WindowHeight> <WindowWidth>1024</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>0</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom" /> <Borders/> <Font ss:FontName="Arial" ss:Size="8" /> <Interior/> <NumberFormat /> <Protection /> </Style> <Style ss:ID="sHyperlink" ss:Name="Hyperlink"> <Font ss:Color="#0000FF" ss:Underline="Single" /> </Style> <Style ss:ID="sDate"> <NumberFormat ss:Format="Short Date"/> </Style> <Style ss:ID="sNumber"> <NumberFormat/> </Style> <Style ss:ID="sHeader"> <Font ss:Family="Arial" ss:Bold="1" /> </Style> <Style ss:ID="sDecimal"> <NumberFormat ss:Format="Fixed"/> </Style> </Styles><Worksheet ss:Name="Sheet1"> 

Body (this is a table converted from a datagrid. You need to dynamically generate it)

 <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="328" x:FullColumns="1" x:FullRows="1"><Row><Cell ss:StyleID="sHeader"><Data ss:Type="String">Order No.</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Order Date</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Shipped Date</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">status</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">comments</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Customer No.</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">2010-08-20 00:00:00</Data></Cell><Cell><Data ss:Type="String"></Data></Cell><Cell><Data ss:Type="String">Open</Data></Cell><Cell><Data ss:Type="String">foo</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">123456</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">10100</Data></Cell><Cell><Data ss:Type="String">2003-01-06 00:00:00</Data></Cell><Cell><Data ss:Type="String">2003-01-10 00:00:00</Data></Cell><Cell><Data ss:Type="String">Shipped</Data></Cell><Cell><Data ss:Type="String">foo</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">363</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">10101</Data></Cell><Cell><Data ss:Type="String">2003-01-18 00:00:00</Data></Cell><Cell><Data ss:Type="String">2003-01-22 00:00:00</Data></Cell><Cell><Data ss:Type="String">Shipped</Data></Cell><Cell><Data ss:Type="String">Check on availability.</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">128</Data></Cell></Row></Table> 

Footer (can be hardcoded)

 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Print> <ValidPrinterInfo /> <HorizontalResolution>800</HorizontalResolution> <VerticalResolution>0</VerticalResolution> </Print> <Selected /> <Panes> <Pane> <Number>3</Number> <ActiveRow>1</ActiveRow> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook> 

You can simply copy the header and footer code, only the body will be generated dynamically. If you need alignment and other styles. Repeat the same “reverse” engineering method and find out what needs to be added to XML.

One of the advantages that I have found this way is that you do not need to rely on the API, because literally everything can be aligned on a string. He also works in different programming languages. It may not be as flexible or powerful as the API, but for a datagrid, only with tables, rows and columns. At least for me.