public static void GetTaskData(string source) { ResetTicketCounts(); using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(source))) { int dtcoll = 0; int heading = 0; dtTaskApm.Clear(); dtTaskOps.Clear(); dtTaskApmEm.Clear(); dtTaskOpsEm.Clear(); dtTaskUnAssigned.Clear(); var myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here var totalRows = myWorksheet.Dimension.End.Row; var totalColumns = myWorksheet.Dimension.End.Column; string[] row = new string[totalColumns]; for (int rowNum = 1; rowNum <= totalRows; rowNum++) //select starting row here { //get values per cell, data is the values from one cell var cell = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString()); foreach (var data in cell) { //creates headings for data tables, first row in excel will be the headings. if (heading != 1) { if (dtTaskApm.Columns.Count != totalColumns) { dtTaskApm.Columns.Add(data); dtTaskOps.Columns.Add(data); dtTaskApmEm.Columns.Add(data); dtTaskOpsEm.Columns.Add(data); dtTaskUnAssigned.Columns.Add(data); } else { heading = 1; } } if (heading == 1) { //Create row to add, dtcol is numbers of columns to be fill need 12 column to create 1 row if (dtcoll != 12) { row[dtcoll] = data; dtcoll++; } //Add the row created to data tables else { try { //Convert excel Updated Date Console.WriteLine(row[7]); updatedDate = Form1.ConvertDates(row[7]); row[7] = updatedDate.ToString("MM/dd/yyyy hh:mm:ss"); } catch (Exception ex) { updatedDate = new DateTime(1990, 01, 01); ex.ToString(); } try { //Convert excel reported Date Console.WriteLine(row[5]); reportedDate = Form1.ConvertDates(row[5]); row[5] = reportedDate.ToString("MM/dd/yyyy hh:mm:ss"); } catch (Exception ex) { reportedDate = new DateTime(1990, 01, 01); ex.ToString(); } try { //Convert excel Closed Date Console.WriteLine(row[11]); closedDate = Form1.ConvertDates(row[11]); row[11] = closedDate.ToString("MM/dd/yyyy hh:mm:ss"); } catch (Exception ex) { closedDate = new DateTime(1990, 01, 01); ex.ToString(); } //if ((Form1.lastDate.Date <= closedDate.Date || row[11] == null || row[11] == "") && (row[10] != "" || row[10] != null) && row[2] != "Cancelled") if (Valid(Form1.lastDate, closedDate, row[11], row[10], row[2])) // Determines if button used is am or pm and if the ticket is have a valid dates { Console.WriteLine(Form1.lastDate.Date.ToString("MM/dd/yyyy") + closedDate.Date + "True !!"); CountTickets(row[2]); // Count Status of the ticket CountPriority(row[4]); // Count the number of priorities //Populating the Unassigned data Table if ((row[3] == "" || row[3] == null) && (row[2] != "Closed Complete" || row[2] != "Resolved")) { dtTaskUnAssigned.Rows.Add(row); } //Populating the APM data Table else if (Form1.ApmList.Contains(row[3].ToString())) { dtTaskApm.Rows.Add(row); if (row[2] == "Closed Complete" || row[2] == "Resolved") { } else { dtTaskApmEm.Rows.Add(row); } } //Populating the Ops data Table else { dtTaskOps.Rows.Add(row); if (row[2] == "Closed Complete" || row[2] == "Resolved") { } else { dtTaskOpsEm.Rows.Add(row); } } } dtcoll = 0; } } } } } //Sort table Updated DESC then sort to Priority dtTaskApm = Form1.SortTables(dtTaskApm); dtTaskApmEm = Form1.SortTables(dtTaskApmEm); dtTaskOps = Form1.SortTables(dtTaskOps); dtTaskOpsEm = Form1.SortTables(dtTaskOpsEm); dtTaskUnAssigned = Form1.SortTables(dtTaskUnAssigned); }