I am having a performance issue when I am trying to generate an excel report using a template, then load data from a xml file. Here is my source code:
private void CreateReportWorkBook()
{
xml = XDocument.Load(@"D:\tfs\files\Test.xml");
try
{
if (xml != null)
{
lblMessage.Visible = false;
var rowArray = xml.Descendants("r").ToArray();
_rows = rowArray.Count();
//Reads the Excel template file into a fileStream and pass it as parameter to workbook constructor.
if (string.IsNullOrEmpty(_templatePath) || !File.Exists(_templatePath))
throw new Exception("Templete file not found.");
_fileStream = new FileStream(_templatePath, FileMode.Open, FileAccess.Read);
_myWorkBook = new Workbook();
_myWorkBook.LoadFromStream(_fileStream);
_myWorkSheet = _myWorkBook.Worksheets[0];
_myWorkSheet.Name = _sheetName;
//read data row by row from xml file and insert into new excel file
for (int i = 0; i < _rows; i++)
{
//As the first row (index:0) in the excel template contains the report Heading. Hence, adding 1 to the index number and starting from next row.
int rowNumber = _isDocumentTypePDF ? i + 3 : i + 1;
int rowCount = _isDocumentTypePDF ? _myWorkSheet.Range.RowCount + 2 : _myWorkSheet.Range.RowCount;
//Gets the row count from the template : If the count is less than or equal to the total row number of the xml, then it inserts a new row into excel file.
if (rowCount <= rowNumber)
{
_myWorkSheet.InsertRow(rowNumber + 1, 1, InsertOptionsType.FormatAsAfter);
}
//Gets total number of columns from xml file.
int columnsCount = rowArray[i].Nodes().Count();
if (_myWorkSheet.Columns.Count() < columnsCount)
{
columnsCount = _myWorkSheet.Columns.Count();
}
for (int j = 0; j < columnsCount; j++)
{
var value = rowArray[i].Element("c" + (j + 1)).Value;
//Checks if the Data Format received from the template file is of type "Number", thats holds only numeric data, and applies equivalent NPOI Data Format for the particular cell.
if (_myWorkSheet.Rows[rowNumber].Cells[j].NumberFormat.Contains("0") && Regex.Matches(value, @"[a-zA-Z]").Count == 0 && (int.TryParse(value, out _tempInt) || double.TryParse(value, out _tempDouble) || float.TryParse(value, out _tempFloat)))
{
var value1 = _tempInt > 0 ? _tempInt : _tempFloat > 0 ? _tempFloat : _tempDouble > 0 ? _tempDouble : 0;
_myWorkSheet.Rows[rowNumber].Cells[j].NumberValue = value1;
}
//Checks if the Data Format for the cell is of type 'Date', and applies equivalent NPOI Data Format for the particular cell.
else if (_myWorkSheet.Rows[rowNumber].Cells[j].NumberFormat.Contains("d") && Regex.Matches(value, @"[a-zA-Z]").Count == 0 && !string.IsNullOrEmpty(value))
{
var date = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
var dateVal = date.Parse(value);
_myWorkSheet.Rows[rowNumber].Cells[j].DateTimeValue = dateVal;
}
else
{
if (!string.IsNullOrEmpty(value))
{
_myWorkSheet.Rows[rowNumber].Cells[j].Text = value;
}
}
}
}
_ms = new MemoryStream();
_myWorkBook.SaveToStream(_ms,Spire.Xls.FileFormat.Version2007);
if (_isDocumentTypePDF)
GeneratePDFReportDocument();
}
else
{
throw new Exception(Constants.REPORT_DATA_XMLFILE_NOT_AVAILABLE);
}
}
catch (Exception ex)
{
string message = string.Format("{0} <br/> Error Message: {1}", Constants.REPORT_EXCEL_WORKBOOK_EXCEPTION, ex.Message);
throw new Exception(message);
}
}
The issue is: it is very slow processing the data. I have a source xml file with 560 rows of data into, it takes about 1 min 45 seconds to run through the above code to generate a excel file. Could you please give me some insight on what am I doing wrong?
I attached both template file and the xml data source file with this post. Please feel free to take a look.
Thanks!