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