public List <WorkingDateData> GetWorkingDateDatasVanPhong(string pathExcelFile, int month) { List <WorkingDateData> getworkingDateDatas = new List <WorkingDateData>(); try { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(pathExcelFile); for (int k = 1; k < 37; k++) { Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[k]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 6; i <= rowCount; i = i + 1) { WorkingDateData data = new WorkingDateData(); data.dept = (xlRange.Cells[i, 2].Value != null) ? xlRange.Cells[i, 2].Value.ToString() : ""; data.ID = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString() : ""; data.Name = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString() : ""; data.col_Dept = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : ""; data.col_date = (xlRange.Cells[i, 7].Value != null) ? xlRange.Cells[i, 7].Value.ToString() : ""; data.WorkingTimeDatas = new List <WorkingTimeData>(); for (int j = 1; j < 32; j++) { data.WorkingTimeDatas.Add(new WorkingTimeData { month = month.ToString(), ngay = j.ToString(), Shift = "day", workingHour = (xlRange.Cells[i, 9 + j].Value != null) ? xlRange.Cells[i, 9 + j].Value.ToString() : "" }); // data.WorkingTimeDatas.Add(new WorkingTimeData { month = month.ToString(), ngay = j.ToString(), Shift = "night", workingHour = (xlRange.Cells[i + 1, 9 + j].Value != null) ? xlRange.Cells[i + 1, 9 + j].Value.ToString() : "" }); } if (data.ID != "") { DataControl dataControl = new DataControl(); dataControl.InsertRowofWorkingDate(data); getworkingDateDatas.Add(data); } } //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); } catch (Exception ex) { Logfile.Output(StatusLog.Error, "List<WorkingDateData> GetWorkingDateDatas(string pathExcelFile, int month)", ex.Message); } return(getworkingDateDatas); }
public List <SpecialList> GetSpecialLists(string pathExcelFile) { List <SpecialList> specialLists = new List <SpecialList>(); try { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(pathExcelFile); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 4; i <= 100; i++) { SpecialList special = new SpecialList(); special.No = (xlRange.Cells[i, 1].Value != null) ? xlRange.Cells[i, 1].Value.ToString() : ""; special.ID = (xlRange.Cells[i, 2].Value != null) ? xlRange.Cells[i, 2].Value.ToString() : ""; special.Name = (xlRange.Cells[i, 3].Value != null) ? xlRange.Cells[i, 3].Value.ToString() : ""; special.Department = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString() : ""; special.from = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString() : ""; special.to = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : ""; special.status = (xlRange.Cells[i, 7].Value != null) ? xlRange.Cells[i, 7].Value.ToString() : ""; if (special.ID.Trim() != "") { DataControl dataControl = new DataControl(); dataControl.InsertRowofSpecialList(special); specialLists.Add(special); } } //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); } catch (Exception ex) { Logfile.Output(StatusLog.Error, "List<SpecialList> GetSpecialLists(string pathExcelFile)", ex.Message); } return(specialLists); }
public List <FingerData> GetFingerDatas(string pathExcelFile) { List <FingerData> specialLists = new List <FingerData>(); try { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(pathExcelFile); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 5; i <= rowCount; i++) { FingerData data = new FingerData(); data.Dept = (xlRange.Cells[i, 3].Value != null) ? xlRange.Cells[i, 3].Value.ToString() :""; data.ID = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString():""; data.Name = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString():""; try { data.DateTime = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : ""; } catch (Exception) { data.DateTime = ""; } // data.DateTime = (xlRange.Cells[i, 6].Value != null) ?(DateTime) xlRange.Cells[i, 6].Value: DateTime.MinValue; data.TimeIn = (xlRange.Cells[i, 9].Value != null) ? xlRange.Cells[i, 9].Value.ToString():""; data.TimeOut = (xlRange.Cells[i, 10].Value != null) ? xlRange.Cells[i, 10].Value.ToString():""; data.DayWorkingTime = (xlRange.Cells[i, 13].Value != null) ? xlRange.Cells[i, 13].Value.ToString():""; data.NightWorkingTime = (xlRange.Cells[i, 14].Value != null) ? xlRange.Cells[i, 14].Value.ToString():""; data.AproveTimeabsent = (xlRange.Cells[i, 17].Value != null) ? xlRange.Cells[i, 17].Value.ToString():""; if (data.ID.Trim() != "") { DataControl dataControl = new DataControl(); dataControl.InsertRowofManufacture(data); specialLists.Add(data); } else { ; } } //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); } catch (Exception ex) { Logfile.Output(StatusLog.Error, "List<FingerData> GetFingerDatas(string pathExcelFile)", ex.Message); } return(specialLists); }