public void Run(string filePath)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
this.watch.Diff("OPENED the merged excel file.");
int rowCount = xlRange.Rows.Count;
int i = rowCount;
int rowId = 1;
while (i > 1)
{
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
DateTime currentRowDate = new DateTime(1900, 1, 1);
currentRowDate = currentRowDate.AddDays(double.Parse(xlRange.Cells[i, 1].Value2.ToString()) - 2);
TransactionExtended tr = new TransactionExtended();
tr.Id = rowId;
tr.AccountingDate = currentRowDate;
tr.TransactionId = this.SetProperty <string>(xlRange.Cells[i, 2]);
tr.Type = this.SetProperty <string>(xlRange.Cells[i, 3]);
tr.Account = this.SetProperty <string>(xlRange.Cells[i, 4]);
tr.AccountName = this.SetProperty <string>(xlRange.Cells[i, 5]);
tr.PartnerAccount = this.SetProperty <string>(xlRange.Cells[i, 6]);
tr.PartnerName = this.SetProperty <string>(xlRange.Cells[i, 7]);
tr.Sum = this.SetProperty <decimal>(xlRange.Cells[i, 8]);
tr.Currency = this.SetProperty <string>(xlRange.Cells[i, 9]);
tr.Message = this.SetProperty <string>(xlRange.Cells[i, 10]);
tr.IsOmitted = this.GetBoolValue(xlRange.Cells[i, 11]);
tr.GroupId = this.SetProperty <string>(xlRange.Cells[i, 12]);
tr.TagIds = this.GetIntList(xlRange.Cells[i, 13]);
this.ExcelSheet.AddNewRow();
this.ExcelSheet.SetLastRow(tr);
rowId++;
}
i--;
}
#region Clean up COM objects
// Cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
this.watch.Diff("GC cleanup init.");
Marshal.ReleaseComObject(xlRange);
this.watch.Diff("Marshal.ReleaseComObject(xlRange)");
Marshal.ReleaseComObject(xlWorksheet);
this.watch.Diff("Marshal.ReleaseComObject(xlWorksheet)");
// Close and release
xlWorkbook.Close();
this.watch.Diff("xlWorkbook.Close()");
Marshal.ReleaseComObject(xlWorkbook);
this.watch.Diff("Marshal.ReleaseComObject(xlWorkbook)");
// Quit and release
xlApp.Quit();
this.watch.Diff("xlApp.Quit()");
Marshal.ReleaseComObject(xlApp);
this.watch.Diff("Marshal.ReleaseComObject(xlApp)");
#endregion
this.watch.Diff("FINISHED, properties are in memory!");
Console.WriteLine();
}
void ReadFile(string filePath, string fileExtension)
{
StringBuilder sb = new StringBuilder();
ISheet sheet;
using (var stream = new FileStream(filePath, FileMode.Open))
{
stream.Position = 0;
if (fileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
this.Header.Add(cell.ToString());
}
//for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
int rowId = 1;
for (int i = sheet.LastRowNum; i > (sheet.FirstRowNum + 1); i--) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
TransactionExtended tr = new TransactionExtended();
tr.Id = rowId;
tr.AccountingDate = row.GetCell(0).DateCellValue;
tr.TransactionId = row.GetCell(1).ToString();
tr.Type = row.GetCell(2).ToString();
tr.Account = row.GetCell(3).ToString();
tr.AccountName = row.GetCell(4).ToString();
tr.PartnerAccount = row.GetCell(5).ToString();
tr.PartnerName = row.GetCell(6).ToString();
tr.Sum = decimal.Parse(row.GetCell(7).ToString());
tr.Currency = row.GetCell(7).ToString();
tr.Message = row.GetCell(9).ToString();
rowId++;
}
}
}
