Exporting HTML table data to Excel using C#

Question

I have JSON data that I bind to a table:

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?

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...
}