As I wrote earlier (see here and here ), the best way to export XML grid data is to use the Open XML SDK 2.0 .
Dr. Steven Walter's post shows how to create an HTML file that can be read in Excel. It is not an Excel file and should be converted to Excel format. Using CSV is even more of a problem. Depending on the contents in the source table, automatic conversion to Excel data types may be completely wrong. In one project that I developed for a client, the grid contained information about software products: product name, version, etc. The software version looks like a date (for example, 1.3.1963), and such cells will not be correctly converted (in German “.” Is used as a separator in the date). As a result, I had really serious problems. Using CSV with texts with commas inside will also often be mistakenly imported. Even when someone is quoting cells with commas ( , ) and avoiding texts having quotas, the import is still erroneous, especially in the first column. I don’t want to explain the whole history of all attempts and errors here, but in the end I decided to stop using CSV and HTML and started using Open XML SDK 2.0, which allows you to create real Excel files with the .xlsx extension. It seems to me that I'm perfect, because that I don’t need any office components installed on the server, no additional licenses.
The only limitation is that you must use DocumentFormat.OpenXml.dll , so your server program must run on any Windows operating system. As is well known, an XLSX file is a ZIP file that contains some XML files inside. If you still do not know that I recommend that you rename the XLSX file to a ZIP file and extract it. The Open XML SDK 2.0 is a library that works with an XLSX file, such as XML files. Therefore, no additional Office components are required.
You can find a lot of information on how to use the Open XML SDK 2.0 (see here , here and here ). Many useful code examples found with one fist directly on MSDN (see here ). However, the practical use of the Open XML SDK 2.0 is not so simple, at least for the first time. So I created a demo from parts of the code that I used myself.
You can download the demo project from here . A demo is an extension of the demos from the answer and that .
To export data, I use the helper class DataForExcel . It has a constructor in the form
DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data, string sheetName)
or in a slightly simplified form
DataForExcel(string[] headers, List<string[]> data, string sheetName)
and the only public method
CreateXlsxAndFillData(Stream stream)
Using a class to create an Excel file might look like this
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();
Use in a demo from ASP.NET MVC is as follows
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;
where ExcelResult 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);
To make the code complete, I must include the code for the DataForExcel class:
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 creates a new xlsx file. You can extend the code to support more data types like String and Integer , which I used in the code.
In a more professional version of your application, you can create several XLSX templates to export different tables. In code, you can put data in cells instead, so change the table, not create it. In a way to create perfect xlsx formatted files. Examples from MSDN (see here ) will help you implement the path when you need it.
UPDATED : The response contains updated code that allows you to generate Excel with a lot of formatting cells.