0
\$\begingroup\$

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?

\$\endgroup\$
7
  • \$\begingroup\$ 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. \$\endgroup\$
    – t3chb0t
    Mar 1 '17 at 10:00
  • \$\begingroup\$ It says "Application" is does not exist in the context. Is Application.EnableEvents = False is part of csharp? \$\endgroup\$
    – kudlatiger
    Mar 1 '17 at 11:00
  • 4
    \$\begingroup\$ Why would a method called JsonToStream be doing anything at all in excel or data tables? Very misleading! \$\endgroup\$
    – 404
    Mar 1 '17 at 12:19
  • 1
    \$\begingroup\$ I don't think moving formatting to database is the answer. Figure out which line(s) are causing the slowness. \$\endgroup\$
    – paparazzo
    Mar 1 '17 at 19:17
  • 1
    \$\begingroup\$ @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. \$\endgroup\$
    – 404
    Mar 2 '17 at 11:11
1
\$\begingroup\$

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...
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.