I was searching for an answer to my current problem on the forums and found similar threads. My issue is somewhat related & I am hoping there might be a solution.
We currently have 2 ways of creating worksheets... we create a 2D object array and use the "InsertArray()" method. We have used this for a lot of our current reporting and with smaller reports it works well.
However, on our larger reports, the performance was not good. A report that previously took ~8 minutes to run with the Excel Interop, not takes closer to an hour. I have read your forums and read that the "InsertDataTable()" method is supposed to be faster. I have attempted to create logic that uses this method, but I have run into an issue where the formulae in my worksheet are not being calculated. This was an issue previously with the "InsertArray()" method, but a call to "workbook.CalculateAllValue()" fixed the problem... it does not work with this new data table approach.
I have included the method we use below, but I cannot provide an example of the worksheet as it has sensitive information.
- Code: Select all
public void AddWorksheetFromDataTable(System.Data.DataTable table)
{
_currentWorksheet = _workBook.Worksheets.Add(table.TableName);
_currentWorksheet.InsertDataTable(table, IncludeHeadings, 1, 1, true);
if (!string.IsNullOrWhiteSpace(Format))
{
SetFormat(1, 1, table.Rows.Count, table.Columns.Count, Format);
}
if (!string.IsNullOrWhiteSpace(Background))
{
_currentWorksheet.GridLinesVisible = false;
CellStyle style = _workBook.Styles.Add("NewStyle");
style.Color = HexToColor(Background);
_currentWorksheet.ApplyStyle(style);
}
if (Autosize)
{
_currentWorksheet.AllocatedRange.AutoFitColumns();
_currentWorksheet.AllocatedRange.AutoFitRows();
}
if (Exists("Sheet1") && !_isAssemblyInfoWritten)
{//this means we now have the newly added worksheet and the classic 'Sheet1' which we will use!
string strVersion = Assembly.GetExecutingAssembly().GetName().Version.ToString();
object[,] dataSheet1 = new object[2, 2];
dataSheet1[0, 0] = $"Model Version:";
dataSheet1[0, 1] = strVersion;
dataSheet1[1, 0] = $"Executed:";
dataSheet1[1, 1] = $"{DateTime.Now:g}";
OverwriteWorksheet("Sheet1", dataSheet1);
_currentWorksheet.Activate();//reselect current
_isAssemblyInfoWritten = true;
}
MoveTabToSpecifiedIndexByWorksheetName(_currentWorksheet.Name, 1, true);
_currentWorksheet = _workBook.Worksheets[table.TableName];
_currentWorksheet.CalculateAllValue();
}
We also call the "workbook.CalculateAllValue()" before saving the document, but when I open the Excel... the formulae have not been calculated. I have to Enable the document for editing, click into the cell formula & hit enter before it is calculated. What could the difference be between these 2 methods you provide?
Thank you!
