private void Btn_PrintEstList_Click(object sender, RibbonControlEventArgs e)
{
//1. generate a new worksheet, named "PrintList", if not a estimate list, then do nothing.
//2. reading source sheet line by line
//3. checking if criteria is met
//4. if met then make set value in class
//5. paste class onto the new sheet
//6. format the data that was pasted, set up page break if necessary
//7. call out print form with correct settings.
Excel.Workbook activeworkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
//mute alert
activeworkbook.Application.DisplayAlerts = false;
activeworkbook.Application.ScreenUpdating = false;
Excel.Worksheet worksheet_source, worksheet_target;
worksheet_source = null;
worksheet_target = null;
//check if the workbook is the estimate work book
string sAttr;
bool checker1 = false;
sAttr = ConfigurationManager.AppSettings.Get("Est_List_SourceSheet");
//step 1
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in activeworkbook.Worksheets)
{
if (worksheet.Name.Contains(sAttr))
{
Act_Est_Functions n = new Act_Est_Functions();
worksheet_target = n.WorkSheetRemake("Print List", activeworkbook);
worksheet_source = worksheet;
checker1 = true;
break;
}
}
if (checker1 == false)
{
MessageBox.Show("No Estimate List Worksheet Found");
return;
}
//step 2
int EstDataTitleRow = int.Parse(ConfigurationManager.AppSettings.Get("EstDataTitleRow"));
int DataCount = 0;
Range rRow = worksheet_source.Rows[EstDataTitleRow];
//find out how many headers there are
foreach (Range rCell in rRow.Cells)
{
if (rCell.Value2 == null)
{
break;
}
else
{
//needs to loop this again as there are empty cell fields in the data table, first needs to get cell counts.
//rowData[DataCount] = rCell.ToString();
DataCount++;
//datacount will be 1 bigger than actual data as the way loop was desigend.
//Array.Resize(ref rowData, rowData.Length + 1);
}
}
//step 3 mixed with step 2 a bit
//in c# apparently that int[4] is from [0] to [3], wtf....
//define string array to store data in the row
string[] rowData = new string[DataCount];
Active_Est_List active_Est_List = null;
//read setting of column number for closing date stored at.
//ClosingDate and BudgetUpdateDate needs to be added by 1 as excel column number starts with 1
int intClosingDate = int.Parse(ConfigurationManager.AppSettings.Get("Est_List_BidCloseDate")) + 1;
int intBudgetUpdateDate = int.Parse(ConfigurationManager.AppSettings.Get("Est_List_BudgetUpdateDate")) + 1;
//use newArray to temporarily store the col mapping, so that this function is not getting called when reading each line.
Act_Est_Functions m = new Act_Est_Functions();
var newArray = m.Est_List_Col_Array();
//Initialize where to write data on target sheet.
int worksheet_target_Row = 13;
//today's date
DateTime today = DateTime.Today;
//datatable to store row #, and date (excel date data) for bid close date in the future, for sorting
//emm nope, using 2 single array then sort together...
int[] FutureEst_RowNum = new int[0];
double[] FutureEst_DateArray = new double[0];
int TotalLegitRow = 0;
EstDataTitleRow++;
while (worksheet_source.Cells[EstDataTitleRow, 1].Value2 != null)
{
// if the status is not closed, then the data needs to be pulled
if (worksheet_source.Cells[EstDataTitleRow, 3].Value2 != "Closed" && worksheet_source.Cells[EstDataTitleRow, 3].Value2 != "Did Not Bid")
{
try
{
Array.Resize(ref FutureEst_DateArray, FutureEst_DateArray.Length + 1);
Array.Resize(ref FutureEst_RowNum, FutureEst_RowNum.Length + 1);
FutureEst_RowNum[TotalLegitRow] = EstDataTitleRow;
//pick the later date from either the bid close date or budget update date.
//using value 2 to simply compare the value
var BidCloseDate = worksheet_source.Cells[EstDataTitleRow, intClosingDate].Value2;
var BudgetUpdateDate = worksheet_source.Cells[EstDataTitleRow, intBudgetUpdateDate].Value2;
if (BidCloseDate == null)
{
BidCloseDate = 0;
}
if (BudgetUpdateDate == null)
{
BudgetUpdateDate = 0;
}
if (BidCloseDate < BudgetUpdateDate)
{
intClosingDate = intBudgetUpdateDate; //then reads budget update date instead
}
else if (BidCloseDate == 0)
{
throw new OngGoingEstimateWithNoDate("Row " + EstDataTitleRow.ToString());
}
FutureEst_DateArray[TotalLegitRow] = worksheet_source.Cells[EstDataTitleRow, intClosingDate].Value2;
TotalLegitRow++;
}
catch (DateTimeFormatError error)
{
//MessageBox.Show("DateTimeFormatError" + EstDataTitleRow);
}
catch (RuntimeBinderException error)
{
MessageBox.Show("Date format incorrect - Row: " + EstDataTitleRow + ", program will continue");
FutureEst_DateArray[TotalLegitRow] = 99999999;
TotalLegitRow++;
}
catch (OngGoingEstimateWithNoDate error)
{
FutureEst_DateArray[TotalLegitRow] = 99999999;
TotalLegitRow++;
}
}
EstDataTitleRow++;
}
if (FutureEst_DateArray.Length != 0) //if the array is not empty, then add the header, if empty then prompt and exit program
{
m.Est_Item_Header(worksheet_target);
}
else
{
MessageBox.Show("No Ongoing Estimate Found");
return;
}
//sorting 2 arrays together
Array.Sort(FutureEst_DateArray, FutureEst_RowNum);
worksheet_target.PageSetup.Zoom = false;
worksheet_target.PageSetup.FitToPagesTall = false;
worksheet_target.PageSetup.FitToPagesWide = 1;
//go through the array to pick up the rows.
//internal counter to check on the date, and see if a title needs to be generated
int counter = 0;
foreach (int RowNum in FutureEst_RowNum)
{
//set row
rRow = worksheet_source.Rows[RowNum];
//now read row into the array cell by cell
int i = 0;
foreach (Range rCell in rRow.Cells)
{
string CellText = rCell.Text;//string type to ease combining and avoid error
rowData[i] = CellText;
++i;
if (i >= DataCount)
{
break;
// exit for once reaches the last column, remember DataCount is the size of the array
// from 0 to size-1
// still like wtf...
}
}
//step 5 and step 6 at the same time
//the following needs to be looped till sheet runs out.
active_Est_List = new Active_Est_List(rowData, newArray);
//add date or grey line based on if date equals to previous date
Range range = worksheet_target.Range[worksheet_target.Cells[worksheet_target_Row, 1], worksheet_target.Cells[worksheet_target_Row, 5]];
//check if bid close at the same date, if it is not date with previous one then add header
if (counter == 0 || (FutureEst_DateArray[counter] != FutureEst_DateArray[counter - 1])) // if at beginning of the loop or date is equal to previous date
{
if (FutureEst_DateArray[counter] == 99999999)
{
range.Value = "On Going";
}
else
{
range.Value = active_Est_List.Est_List_BidCloseDate;
range.NumberFormat = "dddd, mmmm d, yyyy";
}
range.Merge();
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
range.Font.Name = "Calibri";
range.Font.Size = 14;
range.Font.Bold = true;
range.Font.Color = ColorTranslator.ToOle(Color.White);
range.Interior.Color = ColorTranslator.FromHtml("#9c88b3");
worksheet_target_Row++;
}
else //if same date then add grey line
{
range.Interior.Color = ColorTranslator.FromHtml("#696969");
range.RowHeight = 4;
worksheet_target_Row++;
}
m.Est_Item_CopyPasteFormat(active_Est_List, worksheet_target, worksheet_target_Row, 1);
worksheet_target_Row += 10;
if ((counter % 5) == 3 || counter == 3) //add page break every 5 items, with exception to the first one, as 2 items added for example and header.
{
worksheet_target.HPageBreaks.Add(worksheet_target.Rows[worksheet_target_Row]);
}
counter++;
}
//formatting page breaks
foreach (VPageBreak vPageBreak in worksheet_target.VPageBreaks)
{
vPageBreak.Delete();
}
worksheet_target.VPageBreaks.Add(worksheet_target.Columns[5]);
worksheet_target.PageSetup.PaperSize = XlPaperSize.xlPaperLetter;
worksheet_target.PageSetup.Orientation = XlPageOrientation.xlPortrait;
worksheet_target.PageSetup.PrintArea = "A1:E" + worksheet_target_Row.ToString();
activeworkbook.Application.DisplayAlerts = true;
activeworkbook.Application.ScreenUpdating = true;
}
public void Est_Item_CopyPasteFormat(Active_Est_List list, Worksheet worksheet, int row, int col) //(class, targetsheet, target row, target col)
{
int i = col; //store the col value
Range range;
double value1, value2;
bool cterm = (list.Est_List_CTerm != "No C-Term");
worksheet.Rows[row++].RowHeight = 4;
//first row
worksheet.Cells[row, col] = list.Est_List_ProjectName;
worksheet.Cells[row, col].Font.Size = 9;
worksheet.Cells[row, ++col] = list.Est_List_Location();
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_EstLead;
worksheet.Cells[row, ++col] = list.Est_List_EstLeadRevDate;
range = worksheet.Cells[row, col];
range.Interior.Color = ColorTranslator.FromHtml("#ffcccc");
worksheet.Rows[row++].Font.Size = 9;
col = i;
//second row
worksheet.Cells[row, col] = list.Est_List_EstValue;
double estvalue;
try
{
estvalue = (worksheet.Cells[row, col].Value2);
estvalue = estvalue / 1000000;
}
catch (Exception error)
{
estvalue = 0.5;
}
worksheet.Cells[row, ++col] = list.Est_List_OwnerName;
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_ChiefEstimator;
worksheet.Cells[row, ++col] = list.Est_List_ChfEstRevDate;
//Review critiria
range = worksheet.Cells[row, col];
if (cterm == true)
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("ChfEstReview1"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("ChfEstReview2"));
}
else
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("ChfEstReview4"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("ChfEstReview5"));
}
RangeReviewColorCoding(range, estvalue, value1, value2);
worksheet.Rows[row++].Font.Size = 9;
col = i;
//third row
worksheet.Cells[row, col] = list.Est_List_SubmissionType;
worksheet.Cells[row, ++col] = list.Est_List_FundSource;
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_SPLPrsdntRevDate;
//Review critiria
range = worksheet.Cells[row, col];
if (cterm == true)
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("SPLPrsReview1"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("SPLPrsReview2"));
}
else
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("SPLPrsReview4"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("SPLPrsReview5"));
}
RangeReviewColorCoding(range, estvalue, value1, value2);
worksheet.Rows[row++].Font.Size = 9;
col = i;
//forth row
worksheet.Cells[row, col] = list.Est_List_Category;
worksheet.Cells[row, ++col] = list.Est_List_Designer;
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_SGCPrsdntRevDate;
//review critiria
range = worksheet.Cells[row, col];
if (cterm == true)
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("SGCPrsReview1"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("SGCPrsReview2"));
}
else
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("SGCPrsReview4"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("SGCPrsReview5"));
}
RangeReviewColorCoding(range, estvalue, value1, value2);
worksheet.Rows[row++].Font.Size = 9;
col = i;
//fifth row
worksheet.Cells[row, col] = list.Est_List_ProjDeliveryContractType();
worksheet.Cells[row, ++col] = list.Est_List_BidCloseDate;
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_OpsMngrRevDate;
//review critiria
range = worksheet.Cells[row, col];
if (cterm == true)
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("OPSMngReview1"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("OPSMngReview2"));
}
else
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("OPSMngReview4"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("OPSMngReview5"));
}
RangeReviewColorCoding(range, estvalue, value1, value2);
worksheet.Rows[row++].Font.Size = 9;
col = i;
//sixth row
worksheet.Cells[row, col] = list.Est_List_EstNumber;
worksheet.Cells[row, ++col] = list.Est_List_PreBidDT();
worksheet.Cells[row, ++col] = "";
worksheet.Cells[row, ++col] = list.Est_List_EstmtrPrpslWrtr;
worksheet.Cells[row, ++col] = list.Est_List_VPCOO;
//review critiria
range = worksheet.Cells[row, col];
if (cterm == true)
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("VPCOOReview1"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("VPCOOReview2"));
}
else
{
value1 = double.Parse(ConfigurationManager.AppSettings.Get("VPCOOReview4"));
value2 = double.Parse(ConfigurationManager.AppSettings.Get("VPCOOReview5"));
}
RangeReviewColorCoding(range, estvalue, value1, value2);
worksheet.Rows[row++].Font.Size = 9;
worksheet.Rows[row++].RowHeight = 4;
col = i;
worksheet.Cells[row, col] = "Notes:";
worksheet.Rows[row].Font.Size = 9;
worksheet.Rows[++row].RowHeight = 4;
}