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++; } } }