private void writeAuditFileToExcel(string dataPath2020) /*********************************************************************************************************************** ** Write the audit information to an excel file ***********************************************************************************************************************/ { int totalCompleted = 1; int totalToDo = ParentForm.AuditDoor.Count + 1; ParentForm.Invoke(new BuildAuditDoorChange(BuildAuditDoorOnChange), "Opening Excelclose", totalCompleted, totalToDo); ParentForm.Invoke(new BuildAuditEventsChange(BuildAuditEventsOnChange), "Waiting on Excel...", 0, 100); DoorSht = new Worksheet[ParentForm.AuditDoor.Count]; int sheetNum = 1; Excel excel = new Excel(dataPath2020, sheetNum, ParentForm); /*********************************************************************************************************************** ** Create and open the excel file. This is the longest task in the process ***********************************************************************************************************************/ DoorSht[sheetNum - 1] = excel.Open(dataPath2020, sheetNum); int i = 0; excel.renameSheet(sheetNum, ParentForm.AuditDoor[i].DoorID.ToString("0000")); /*********************************************************************************************************************** ** Process each door in the list ***********************************************************************************************************************/ int doorNum = -1; for (i = 0; i < ParentForm.AuditDoor.Count; i++) { /*********************************************************************************************************************** ** if the user clicked abort, this is where we leave. ***********************************************************************************************************************/ if (ParentForm.AbortAuditReport) { ParentForm.AbortAuditReport = false; excel.Close(SaveChanges: false); excel.Kill(); GC.Collect(); // Garbage Collect - Needed to make Excel quit in the background GC.WaitForPendingFinalizers(); ParentForm.Invoke(new BuildAuditDoorChange(BuildAuditDoorOnChange), null, 100, 100); ParentForm.Invoke(new BuildAuditEventsChange(BuildAuditEventsOnChange), null, 100, 100); return; } ParentForm.Invoke(new BuildAuditDoorChange(BuildAuditDoorOnChange), "Processing door " + (i + 1).ToString() + " of " + ParentForm.AuditDoor.Count.ToString(), totalCompleted++, totalToDo); int r = 1; int c = 1; doorNum++; AuditExcelRow tmpExcelRow = new AuditExcelRow(); /*********************************************************************************************************************** ** The first time through, the worksheet was created by the excel.open above. ** Every other time, creat a new sheet for the door information. ***********************************************************************************************************************/ if (i > 0) { sheetNum++; DoorSht[sheetNum - 1] = excel.newSheet(ParentForm.AuditDoor[i].DoorID.ToString("0000"), DoorSht[sheetNum - 2]); } /*********************************************************************************************************************** ** Write the site and door name ** If the Site and door don't come in the audit file, look it up based on the user selection and the lock information list ***********************************************************************************************************************/ if (ParentForm.AuditDoor[doorNum].site.ToUpper().Trim() == "SITE") { excel.WriteCell(r, c, SharedSiteData.site); } else { excel.WriteCell(r, c, ParentForm.AuditDoor[doorNum].site); } r++; if (ParentForm.AuditDoor[doorNum].DoorName.ToUpper().Trim() == "DOOR") { string DoorByID; int index = Lock.FindIndex(a => a.ID == ParentForm.AuditDoor[doorNum].DoorID.ToString("0000")); if (index > -1) { DoorByID = SharedDoorData.LockListPtr[index].Name; } else { DoorByID = ParentForm.AuditDoor[doorNum].DoorName; } excel.WriteCell(r, c, DoorByID); } else { excel.WriteCell(r, c, ParentForm.AuditDoor[doorNum].DoorName); } r = r + 2; /*********************************************************************************************************************** ** Write the column headings and color the cells gray ***********************************************************************************************************************/ var hdrStrt = (r : r, c : c); excel.WriteCell(r, c, "Date"); c++; excel.WriteCell(r, c, "Time"); c++; excel.WriteCell(r, c, "User"); c++; excel.WriteCell(r, c, "Name"); c++; excel.WriteCell(r, c, "Action"); excel.GrayCell(hdrStrt.r, hdrStrt.c, r, c); r++; /*********************************************************************************************************************** ** Put all the door event information in an array (will write the whole array to excel...speeeedddd) ***********************************************************************************************************************/ ParentForm.AuditRows.Clear(); DoorEventsCompleted = 0; DoorEventsToDo = ParentForm.AuditDoor[i].UserTxn.Count; ExcelRow = new string[ParentForm.AuditDoor[i].UserTxn.Count, AuditDoorColumns]; int UpdateStatusBarEvery = ParentForm.AuditDoor[i].UserTxn.Count / 20; for (int j = 0; j < ParentForm.AuditDoor[i].UserTxn.Count; j++) { c = 0; ExcelRow[j, c] = ParentForm.AuditDoor[doorNum].UserTxn[j].txnDateTime.ToString("MM/dd/yyyy"); c++; ExcelRow[j, c] = ParentForm.AuditDoor[doorNum].UserTxn[j].txnDateTime.ToString("h:m:ss tt", System.Globalization.CultureInfo.InvariantCulture); c++; /*********************************************************************************************************************** ** If the code is zero, there is ne way to identify a user ***********************************************************************************************************************/ if (int.Parse(ParentForm.AuditDoor[doorNum].UserTxn[j].userCode) == 0) { ExcelRow[j, c] = "N/A"; } else { ExcelRow[j, c] = ParentForm.AuditDoor[doorNum].UserTxn[j].userCode; } c++; /*********************************************************************************************************************** ** TryGetValue is at least 10 times faster than a Try/Catch clause ***********************************************************************************************************************/ string HoldUsrNm = ""; ParentForm.UserDictionary.TryGetValue(int.Parse(ParentForm.AuditDoor[doorNum].UserTxn[j].userCode), out HoldUsrNm); if (HoldUsrNm == null) { ExcelRow[j, c] = "N/A"; } else { ExcelRow[j, c] = HoldUsrNm; } c++; ExcelRow[j, c] = ParentForm.AuditDoor[doorNum].UserTxn[j].Txn; /*********************************************************************************************************************** ** Update the progress bar every x records - let the user know something good is happening ***********************************************************************************************************************/ int UpdateStatusBar = j % UpdateStatusBarEvery; if (UpdateStatusBar == 0) { DoorEventsCompleted = DoorEventsCompleted + UpdateStatusBarEvery; ParentForm.Invoke(new BuildAuditEventsChange(BuildAuditEventsOnChange), "Processing door event " + j.ToString() + " of " + ParentForm.AuditDoor[i].UserTxn.Count.ToString(), DoorEventsCompleted, DoorEventsToDo); } } /*********************************************************************************************************************** ** Write the whole array to excel, add borders and adjust the column width ***********************************************************************************************************************/ c = 1; excel.WriteArray(r, c, ExcelRow); // Add borfers. r-1 includes the header row excel.Borders(r - 1, c, r + ExcelRow.GetLength(0) - 1, c + ExcelRow.GetLength(1) - 1); // Autofit the columns excel.ColumnWidth(FromCol: 1, ToCol: AuditDoorColumns, Width: 0); r += ParentForm.AuditDoor[i].UserTxn.Count; excel.RowToRepeatAtTop(1, 3); excel.FitToPage(1); excel.PageNumbers(); } /*********************************************************************************************************************** ** Save the file, make Excel visible to the user, wrap up the progress dialog. ***********************************************************************************************************************/ excel.Activate(DoorSht[0]); excel.SaveAS(); //excel.Visible(true); excel.Close(SaveChanges: false); excel.Kill(); GC.Collect(); // Garbage Collect - Needed to make Excel quit in the background GC.WaitForPendingFinalizers(); ParentForm.Invoke(new BuildAuditEventsChange(BuildAuditEventsOnChange), "Starting Excel on Audit Report File", DoorEventsToDo - 5, DoorEventsToDo); Process.Start(dataPath2020); System.Threading.Thread.Sleep(4000); ParentForm.Invoke(new BuildAuditDoorChange(BuildAuditDoorOnChange), null, 100, 100); ParentForm.Invoke(new BuildAuditEventsChange(BuildAuditEventsOnChange), null, 100, 100); }
public void WriteTheReport(object sender, DoWorkEventArgs e) { /*********************************************************************************************************************** ** Write the data to Excel ***********************************************************************************************************************/ reportPathAndFileName = reportPath + DateTime.Now.ToString("yyyy-MM-dd") + @" Eplex Report.xlsx"; int SheetNum = 1; ParentForm.DoorUserAbort = false; ////System.Windows.Forms.Application.UseWaitCursor = true; ////System.Windows.Forms.Application.DoEvents(); //if (InvokeRequired) //{ int totalCompleted = 50; int totalToDo = 100; ParentForm.Invoke(new WriteReportChange(WriteReportOnChange), "Opening Excel", totalCompleted, totalToDo); //} excel = new Excel(reportPathAndFileName, SheetNum, ParentForm); UserSht = excel.Open(reportPathAndFileName, SheetNum); excel.renameSheet(1, "Users"); if (WasAbortRequested()) { return; } WriteUsersToExcel(excel); if (WasAbortRequested()) { return; } WriteDoorsToExcel(excel); if (WasAbortRequested()) { return; } excel.Activate(UserSht); excel.SaveAS(); //excel.Visible(true); excel.Close(SaveChanges: false); excel.Kill(); // excel = null; // Needed to make Excel quit in the background GC.Collect(); // Garbage Collect - Needed to make Excel quit in the background GC.WaitForPendingFinalizers(); totalCompleted = totalToDo - 5; ParentForm.Invoke(new WriteReportChange(WriteReportOnChange), "Copying dated backup to common report file", totalCompleted, totalToDo); totalCompleted = totalToDo - 1; string outFile = PutACopyInUsersRegistryLocation(reportPathAndFileName); ParentForm.Invoke(new WriteReportChange(WriteReportOnChange), "Starting Excel on Print File", totalCompleted, totalToDo); Process.Start(outFile); System.Threading.Thread.Sleep(4000); ParentForm.Invoke(new WriteReportChange(WriteReportOnChange), null, totalCompleted, totalToDo); // Tells the process box to close //System.Windows.Forms.Application.UseWaitCursor = false; }