private void ExcelRead(string filePath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
Book book = new Book();
book.Name = xlRange.Cells[i, 1].Value2.ToString();
book.Category = xlRange.Cells[i, 2].Value2.ToString();
string dateTimeTemp = xlRange.Cells[i, 3].Value2.ToString();
double d = double.Parse(dateTimeTemp);
book.PublishedDate = DateTime.FromOADate(d);
book.Cost = Int32.Parse(xlRange.Cells[i, 4].Value2.ToString());
book.Author = xlRange.Cells[i, 5].Value2.ToString();
book.Publisher = xlRange.Cells[i, 6].Value2.ToString();
book.Identifier = Convert.ToInt32(xlRange.Cells[i, 7].Value2.ToString());
_business.AddBookToDb(book);
_workerImport.ReportProgress((int)(i * 100 / rowCount));
// Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
//add useful things here!
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
