Exporting HTML table data to Excel using C#

Question

I have JSON data that I bind to a table:

enter image description here

Now I would like to export this data to Excel, so I have written this code that involves data formatting of an Excel spreadsheet.

private static MemoryStream JsonToStream<T>(string data, string html, string movieReportName) 
{
 IEnumerable<T> serializedData = data.JsonDeserialize<T>().AsEnumerable<T>();
 List<SelectListItem> colOrder = ParseColumnsToList(html);
 DataTable dataTable = serializedData.ToDataTable<T>(colOrder);
 ExcelPackage epackage = new ExcelPackage();
 ExcelWorksheet excel = epackage.Workbook.Worksheets.Add(movieReportName);
 excel.Cells["A1"].LoadFromDataTable(dataTable, true);
 int rowCount = dataTable.Rows.Count;
 int columnCount = dataTable.Columns.Count;
 for (int i = 2; i < rowCount + 2; i++)
 {
 for (int j = 1; j <= columnCount; j++)
 {
 if (value.Contains("%"))
 {
 if ((dataTable.Columns[j - 1].ColumnName != "Movie Code") {
 value = value.Replace("%", "");
 if (value.Contains("."))
 {
 double number = 0.0;
 double.TryParse(value, out number);
 excel.Cells[i, j].Value = number / 100;
 excel.Cells[i, j].Style.Numberformat.Format = "0.00##%";
 }
 }
 }
 }
 }
 MemoryStream ms = new MemoryStream(epackage.GetAsByteArray());
 epackage.Dispose();
 return ms;
 }

This works fine but it runs very slowly. Do you think I have to move data formatting to a database?

Update What are different better option to export large data from HTML to excel?

• t3chb0t: What you need to do is to disable calculation and screen updating for as long as the loops are running. See my answer to this question.
• kudlatiger: It says "Application" is does not exist in the context. Is Application.EnableEvents = False is part of csharp?
• 404: Why would a method called JsonToStream be doing anything at all in excel or data tables? Very misleading!
• paparazzo: I don't think moving formatting to database is the answer. Figure out which line(s) are causing the slowness.
• 404: @codetoshare That doesn't answer the question. This is what I expect the method to do based on its name: take JSON data as input, return it as a stream. Yet it goes off and does stuff in excel?? You're putting too much functionality in this method. Single Responsibility applies to methods too. Your method is doing too many things. A method called JsonToStream should only convert JSON to a stream.

Answer 1

First off let's encapsulate what you are trying to do here.

Create a function that will format the DataTable.

private static DataTable FormatMovieReportTable(DataTable reportTable)
{
 foreach(DataColumn column in reportTable.Columns)
 {
 string colName = column.ColumnName;
 if(colName != "Movie Code")
 {
 foreach(DataRow row in reportTable.Rows)
 {
 string value = row[colName]
 if(value.Contains("%"))
 {
 value = value.Replace("%", "");
 if(value.Contains("."))
 {
 double number = 0.0;
 double.TryParse(value, out number);
 value = (number / 100).ToString("0.00##")
 }
 row[colName] = value;
 }
 }
 }
 }
 return reportTable;
}

Now create a function that will create the DataTable from the string values.

private DataTable JsonToTable<T>(string data, string html, string tableName)
{ 
 DataTable output = null;
 IEnumerable<T> serializedData = data.JsonDeserialize<T>().AsEnumerable<T>();
 List<SelectListItem> colOrder = ParseColumnsToList(html);
 output = serializedData.ToDataTable<T>(colOrder);
 if(output!= null)
 { 
 output = FormatMovieReportTable(output);
 output.TableName = tableName;
 }
 return output
}

And now the excel output..

private void TableToExcel(DataTable table)
{
 ExcelPackage epackage = new ExcelPackage();
 // the ?? is just in case that the table name is null
 // "new sheet" is just some arbitrary default name
 ExcelWorksheet excel = epackage.Workbook.Worksheets.Add(table.TableName ?? "new sheet");
 excel.Cells["A1"].LoadFromDataTable(table, true);
}

From wherever you called the posted function you can separate the logic

private void ThisIsTheCallerMethod()
{
 string data, html, reportName;
 // Other things happen and variables are populated
 DataTable reportTable = JsonToTable<SomeType>(data, html, reportName);
 if(reportTable != null)
 {
 TableToExcel(reportTable);
 }
 // Create the MemoryStream, etc...
}