public static bool UpdateDataToExcel(AreaCollectedData data, string path) { try { string newPath = ""; string CurrentExcelFile = ""; IEnumerable <string> folderList = Directory.EnumerateDirectories(path); if (folderList.Count() == 0) { newPath = $"{path}\\{DateTime.Now.ToString("MM_yyyy")}"; Directory.CreateDirectory(newPath); } bool foundFolder = false; foreach (var scheduleFilePath in folderList) { Console.WriteLine(scheduleFilePath); string fname = GetFolderName(scheduleFilePath); if (fname != DateTime.Now.ToString("MM_yyyy")) { continue; } newPath = scheduleFilePath; foundFolder = true; } if (!foundFolder) { CurrentExcelFile = CreateNewReportFile(path, false, false); } else { DirectoryInfo Directorys = new DirectoryInfo(newPath); //Assuming Test is your Folder FileInfo[] Files = Directorys.GetFiles("*.xlsx"); //Getting Text files string ExcelName = DateTime.Now.ToString("dd_MM_yyyy"); if (Files != null) { if (Files.Where(i => i.Name == $"{ExcelName}.xlsx").Count() > 0) { CurrentExcelFile = Files.Where(i => i.Name == $"{ExcelName}.xlsx").First().FullName; } else { CurrentExcelFile = CreateNewReportFile(path, false, false); } } else { CurrentExcelFile = CreateNewReportFile(path, false, false); } } //FileInfo CurrentExcel = new FileInfo(CurrentExcelFile); UpdateExcectlyToExcel(data, CurrentExcelFile); return(true); } catch (Exception ex) { return(false); } }
public static bool UpdateExcectlyToExcel(AreaCollectedData data, string path) { FileInfo CurrentExcel = new FileInfo(path); DateTime Time = DateTime.Now; int currentHour = Time.Hour; try { using (ExcelPackage excelPackage = new ExcelPackage(CurrentExcel)) { for (int i = 0; i < data.CheckAreas.Count; i++) { var area = data.CheckAreas[i]; ExcelWorksheet currentSheet = excelPackage.Workbook.Worksheets[i + 1]; if (area.Exception != Exception_t.RESP_OK) { continue; } for (int checkItem = 0; checkItem < area.ItemDatas.Count; checkItem++) { int currentTime = 0; for (int timeLine = 0; timeLine < 12; timeLine++) { if (currentHour == 0 && timeLine == 0) { currentTime = 0; if (area.ItemDatas[checkItem].Status == 0xFF) { currentSheet.Cells[checkItem + 5, 1].Value = "X"; currentSheet.Cells[checkItem + 5, 1].Style.Font.Bold = true; currentSheet.Cells[checkItem + 5, 2].Value = string.Empty; } else { currentSheet.Cells[checkItem + 5, 1].Value = string.Empty; currentSheet.Cells[checkItem + 5, 2].Value = "X"; currentSheet.Cells[checkItem + 5, 2].Style.Font.Bold = true; } } else { if (currentHour / 2 == timeLine) { currentTime = timeLine * 2 + 2; if (area.ItemDatas[checkItem].Status == 0xFF) { currentSheet.Cells[checkItem + 5, timeLine * 2 + 2].Value = "X"; currentSheet.Cells[checkItem + 5, timeLine * 2 + 2].Style.Font.Bold = true; currentSheet.Cells[checkItem + 5, timeLine * 2 + 1 + 2].Value = string.Empty; } else { currentSheet.Cells[checkItem + 5, timeLine * 2 + 2].Value = string.Empty; currentSheet.Cells[checkItem + 5, timeLine * 2 + 1 + 2].Value = "X"; currentSheet.Cells[checkItem + 5, timeLine * 2 + 1 + 2].Style.Font.Bold = true; } } } } if (area.Person != null) { currentSheet.Cells[area.ItemDatas.Count + 5, currentTime].Value = area.Person.Name; } } } excelPackage.Save(); } return(true); } catch (Exception ex) { return(false); } }