private void PlaceColorLegend(_Worksheet oSheet, int startingCol, int startingRow) { string strRange = Report.GetColumnLetter(startingCol + 2) + startingRow.ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + startingRow.ToString();; Range oR = oSheet.get_Range(strRange, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGray); oR.MergeCells = true; oR.Value2 = "Legend"; oR.Font.Size = 11; oR.Font.Bold = true; oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 1).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue); oR.MergeCells = true; oR.Value2 = "Feasible treatment not funded/selected"; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 2).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 2).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen); oR.MergeCells = true; oR.Value2 = "Treatment selected per RoadCare logic"; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 3).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 3).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon); oR.MergeCells = true; oR.Value2 = "Treatment selected as Committed Project"; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); //strRange = Report.GetColumnLetter(startingCol + 2) + startingRow.ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 3).ToString(); //oR.Borders.LineStyle = XlLineStyle.xlContinuous; //oR.Borders.Weight = XlBorderWeight.xlThin; //oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); }
public void CreateBudgetPerReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; Report.SheetPageSetup(oSheet, "Budget Per " + m_strBudgetPer, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); Range oR = oSheet.get_Range("A1", "A1"); object oEndCell = new object(); string strFilter = ""; string strReportName = "Budget Per District Report"; // keep this constant to avoid having to add // a row to the Reports Table for each permutation // of this "Budget Per [attribute]" style report // QUICK TEST of COUNTY attribute... //m_strBudgetPer = "County"; //oSheet.Name = "Budget Per " + m_strBudgetPer; int sheetRow; int ndx; DataSet dsPage = null, dsSimulations = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsSimulations = DBOp.QuerySimulations(m_strSimulationID); } catch (Exception e) { throw e; // Circular reference, address later //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message); } Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); if (strMajorTitle.IndexOf("@1") > 0) { strMajorTitle = strMajorTitle.Replace("@1", m_strBudgetPer); // stuff the attribute into title } #region default column widths // set some column widths oR = oSheet.get_Range("A1:A1", Missing.Value); oR.ColumnWidth = 18; #endregion #region place agency graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion #region write Major Title int aryCols = 2; int aryRows = 3; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); oData[0, 1] = strMajorTitle; sheetRow = 4; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region get Budget Order DataSet dsInvestments = DBOp.QueryInvestments(m_strSimulationID); // get the budgetorder string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(','); #endregion #region get Budget Years switch (DBMgr.NativeConnectionParameters.Provider) { case "MSSQL": strFilter = "SELECT DISTINCT [Year_] FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY [Year_]"; break; case "ORACLE": strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_"; break; default: throw new NotImplementedException("TODO: Create ANSI implementation for CreateBudgetPerReport()"); //break; } DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter); int numYears = dsBudgetYears.Tables[0].Rows.Count; #endregion #region get Number of Districts List <string> strJuris = DBOp.GetJurisdiction(m_strNetworkID, m_strBudgetPer); strJuris.Sort(); int numDistricts = strJuris.Count; #endregion aryCols = numYears + 1; aryRows = numDistricts + 3; #region build Column Headers array // Set up column header once, for multiple uses object[] oColumnHeader = new object[aryCols]; oColumnHeader[0] = m_strBudgetPer; ndx = 1; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { oColumnHeader[ndx++] = dr["Year_"].ToString(); } #endregion #region build Budget tables object[,] oTotalBudget = new object[aryRows, aryCols]; // Annual Total Budget array Report.Resize2DArray(ref oData, aryRows, aryCols); int totalRow = oData.GetUpperBound(0); string strRange, strGroups, strColHdrs, strTotals, strGrids, strMoney; strColHdrs = strGrids = strGroups = strMoney = strRange = strTotals = ""; // ranges needed for formatting spreadsheet List <string> groupList = new List <string>(); List <string> columnList = new List <string>(); List <string> totalsList = new List <string>(); List <string> gridsList = new List <string>(); List <string> moneyList = new List <string>(); Report.ClearDataArray(ref oTotalBudget); sheetRow = 4; int nCol, nGridStart, nGridEnd; double nTmp = 0d; foreach (string strBudget in strBudgetOrder) { oEndCell = "A" + sheetRow.ToString(); Report.BuildRange(ref strGroups, ref groupList, sheetRow, 'A', aryCols - 1); Report.ClearDataArray(ref oData); ndx = 0; oData[ndx, 0] = "Annual " + strBudget + " Budget"; // Group Header oTotalBudget[ndx, 0] = "Annual Total Budget"; sheetRow++; ndx++; oData[totalRow, 0] = oTotalBudget[totalRow, 0] = "Total"; Report.BuildRange(ref strColHdrs, ref columnList, sheetRow, 'A', aryCols - 1); nGridStart = sheetRow; for (int i = 0; i < aryCols; i++) { oData[ndx, i] = oColumnHeader[i]; // Column Header oTotalBudget[ndx, i] = oColumnHeader[i]; } sheetRow++; ndx++; foreach (string strDistrict in strJuris) { nCol = 1; oData[ndx, 0] = oTotalBudget[ndx, 0] = strDistrict; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { Hashtable hBudgets = DBOp.GetBudgetTotals(m_strNetworkID, m_strSimulationID, dr["Year_"].ToString(), strBudget, m_strBudgetPer, m_strBudgetPer + " = " + strDistrict); string strHash = (string)hBudgets[strDistrict]; double nBudget = Convert.ToDouble(strHash); if (oTotalBudget[ndx, nCol].ToString() == "") { oTotalBudget[ndx, nCol] = 0d; // initialize array element } if (oData[totalRow, nCol].ToString() == "") { oData[totalRow, nCol] = 0d; // initalize array element } if (oTotalBudget[totalRow, nCol].ToString() == "") { oTotalBudget[totalRow, nCol] = 0d; // initalize array element } // Accumulate total budget array nTmp = (double)oTotalBudget[ndx, nCol]; nTmp += nBudget; oTotalBudget[ndx, nCol] = nTmp; nTmp = (double)oTotalBudget[totalRow, nCol]; nTmp += nBudget; oTotalBudget[totalRow, nCol] = nTmp; // accumulate fiscal year budget nTmp = (double)oData[totalRow, nCol]; nTmp += nBudget; oData[totalRow, nCol] = nTmp; // accumulate fiscal year budget oData[ndx, nCol++] = nBudget; // store budget value } ndx++; sheetRow++; } Report.BuildRange(ref strTotals, ref totalsList, sheetRow, 'A', aryCols - 1); strGrids = "A" + nGridStart.ToString() + ":" + Report.GetColumnLetter(aryCols) + sheetRow.ToString(); gridsList.Add(strGrids); strMoney = "B" + (nGridStart + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + sheetRow.ToString(); moneyList.Add(strMoney); sheetRow += 2; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); } // print the annual total budget array oEndCell = "A" + sheetRow.ToString(); Report.BuildRange(ref strGroups, ref groupList, sheetRow, 'A', aryCols - 1); Report.BuildRange(ref strColHdrs, ref columnList, sheetRow + 1, 'A', aryCols - 1); Report.BuildRange(ref strTotals, ref totalsList, sheetRow + aryRows - 1, 'A', aryCols - 1); nGridStart = sheetRow + 1; nGridEnd = sheetRow + aryRows - 1; strGrids = "A" + nGridStart.ToString() + ":" + Report.GetColumnLetter(aryCols) + nGridEnd.ToString(); gridsList.Add(strGrids); strMoney = "B" + (nGridStart + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + nGridEnd.ToString(); moneyList.Add(strMoney); oEndCell = Report.WriteObjectArrayToExcel(oTotalBudget, oSheet, oEndCell, false, false); Report.EndRangeList(ref strGroups, ref groupList); Report.EndRangeList(ref strColHdrs, ref columnList); Report.EndRangeList(ref strTotals, ref totalsList); #endregion #region format pageheader // PAGEHEADER strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); #endregion #region format groupheader foreach (string s in groupList) { Report.FormatHeaders(oR, drPage, oSheet, "gh", s); //oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; //oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; //oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; //oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; } #endregion #region format columnheader foreach (string s in columnList) { Report.FormatHeaders(oR, drPage, oSheet, "ch", s); } #endregion #region format totals rows foreach (string s in totalsList) { Report.FormatHeaders(oR, drPage, oSheet, "ch", s); } #endregion #region format grid data foreach (string s in gridsList) { oR = oSheet.get_Range(s, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; } #endregion #region format Money cells foreach (string s in moneyList) { oR = oSheet.get_Range(s, Missing.Value); oR.NumberFormat = "$#,##0"; oR.ColumnWidth = 15; } #endregion #region create column charts int top = 15; //bool bHasLegend = true; //string strCategoryAxis = "Fiscal Year"; string strTitle = ""; string sheetName; int budgetIndex = 0; Range oSourceData = oSheet.get_Range(moneyList[0], Missing.Value); int left = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet); foreach (string s in moneyList) { int nPos = s.IndexOf(":"); string sTmp = Report.Left(s, nPos + 2); string sRight = s.Substring(nPos + 2); nTmp = int.Parse(sRight) - 1; oSourceData = oSheet.get_Range((sTmp + nTmp.ToString()), Missing.Value); sheetName = "='Budget Per " + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5"; if (budgetIndex < strBudgetOrder.Count()) { strTitle = "Annual " + strBudgetOrder[budgetIndex++] + " Budget Per " + m_strBudgetPer; } else { strTitle = "Annual Total Budget Per " + m_strBudgetPer; } Report.CreateColClusterBarGraph(left, top, 425, 315, oSheet, oSourceData, sheetName, strTitle, 12, "Fiscal Year", 11, strJuris, "", 11, XlRowCol.xlRows); top += 330; // magic number } // end moneyList #endregion Report.XL.Visible = true; Report.XL.UserControl = true; } // end CreateBudgetPerReport
public void CreateInputSummaryReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; Report.SheetPageSetup(oSheet, "Input Summary", 50d, 20d, 10d, "Database: " + DBMgr.NativeConnectionParameters.Database, DateTime.Now.ToLongDateString(), "Page &P", 1); Range oR = oSheet.get_Range("A1", "A1"); object oEndCell = new object(); string strReportName = "Input Summary Report"; int sheetRow; DataSet dsPage = null, dsNetwork = null, dsSimulations = null, dsPriority = null, dsPriorityFund = null; DataSet dsTarget = null, dsDeficient = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsNetwork = DBOp.GetNetworkDesc(NetworkId); dsSimulations = DBOp.QuerySimulations(SimulationId); dsPriority = DBOp.QueryPriority(SimulationId); dsPriorityFund = DBOp.QueryPriorityFund(SimulationId); dsTarget = DBOp.QueryTargets(SimulationId); dsDeficient = DBOp.QueryDeficients(SimulationId); } catch (Exception e) { throw e; } Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; DataRow drNetwork = dsNetwork.Tables[0].Rows[0]; DataRow drSimulations = dsSimulations.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); string strTemp, strFilter; #region report graphic // Place report graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion #region default column widths // set some column widths oR = oSheet.get_Range("A1:A1", Missing.Value); oR.ColumnWidth = 21; oR = oSheet.get_Range("B:D", Missing.Value); oR.ColumnWidth = 13.4; oR = oSheet.get_Range("E:H", Missing.Value); oR.ColumnWidth = 7; #endregion int ndx; // calculate the size for the array int aryCols = 2; int aryRows = 22; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); Hashtable titles = new Hashtable(); #region General Info titles.Add("General Information", new Titles("General Information", 4, 1, false, "SECTION", 0)); sheetRow = 4; // GENERAL INFO oData[0, 1] = strMajorTitle; oData[3, 0] = "General Information"; oData[4, 0] = "Database:"; oData[4, 1] = DBMgr.NativeConnectionParameters.Database; oData[5, 0] = "Network Name:"; oData[5, 1] = Network; oData[6, 0] = "Network Description:"; oData[6, 1] = drNetwork["Description"].ToString(); oData[7, 0] = "Simulation Name:"; oData[7, 1] = drSimulations["Simulation"].ToString(); oData[8, 0] = "Simulation Description:"; oData[8, 1] = drSimulations["Comments"].ToString(); oData[9, 0] = "Created By:"; oData[9, 1] = drSimulations["Username"].ToString(); oData[10, 0] = "Created On:"; strTemp = drSimulations["Date_created"].ToString(); oData[10, 1] = Report.Left(strTemp, 9); sheetRow = 13; #endregion #region Analysis // ANALYSIS titles.Add("Analysis", new Titles("Analysis", sheetRow, 1, false, "SECTION", 0)); oData[12, 0] = "Analysis"; oData[13, 0] = "Optimization:"; oData[13, 1] = drSimulations["Analysis"].ToString(); oData[14, 0] = "Budget:"; oData[14, 1] = drSimulations["Budget_constraint"].ToString(); oData[15, 0] = "Weighting:"; oData[15, 1] = drSimulations["Weighting"].ToString(); oData[16, 0] = "Benefit:"; oData[16, 1] = drSimulations["Benefit_variable"].ToString(); oData[17, 0] = "Benefit Limit:"; oData[17, 1] = drSimulations["Benefit_limit"].ToString(); oData[18, 0] = "Jurisdiction Criteria:"; oData[18, 1] = drSimulations["Jurisdiction"].ToString(); #endregion #region Priority // PRIORITY sheetRow = 21; titles.Add("Priority", new Titles("Priority", sheetRow, 1, false, "GROUP", 0)); oData[20, 0] = "Priority"; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); int nCol; aryRows = DBMgr.ExecuteScalar("SELECT Count(*) FROM Priority WHERE simulationID = " + SimulationId) + 1; DataSet dsInvestments = DBOp.QueryInvestments(SimulationId); // get the budgetorder string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(','); aryCols = strBudgetOrder.Count() + 2; Hashtable criteraColumns = new Hashtable(); // used in formatting below criteraColumns.Add("Priority", aryCols); criteraColumns.Add("Target", 5); criteraColumns.Add("Deficient", 5); criteraColumns.Add("Performance", 4); criteraColumns.Add("Feasibility", 1); criteraColumns.Add("Cost", 3); criteraColumns.Add("Consequence", 3); Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); sheetRow++; titles.Add("Priority_col", new Titles("Priority", sheetRow, aryCols, true, "COLUMN", aryRows - 1)); sheetRow++; oData[0, 0] = "Priority"; ndx = 1; foreach (string str in strBudgetOrder) { oData[0, ndx++] = str; } oData[0, ndx] = "Criteria"; ndx = 1; foreach (DataRow dr in dsPriority.Tables[0].Rows) { oData[ndx, 0] = dr["PriorityLevel"].ToString(); oData[ndx, aryCols - 1] = dr["Criteria"].ToString(); //oData[ndx, 4] = dr["Criteria"].ToString(); nCol = 1; foreach (string str in strBudgetOrder) { strFilter = "priorityID = " + dr["priorityID"].ToString() + " and budget = '" + str + "'"; foreach (DataRow dr1 in dsPriorityFund.Tables[0].Select(strFilter)) { oData[ndx, nCol] = dr1["funding"].ToString(); } nCol++; } sheetRow++; ndx++; } oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString())); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion #region Target // TARGET sheetRow += 2; aryCols = 5; aryRows = dsTarget.Tables[0].Rows.Count + 2; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); titles.Add("Target", new Titles("Target", sheetRow - 1, aryCols, false, "GROUP", 0)); titles.Add("Target_col", new Titles("Attribute", sheetRow, aryCols, true, "COLUMN", aryRows - 2)); oData[0, 0] = "Target"; oData[1, 0] = "Attribute"; oData[1, 1] = "Name"; oData[1, 2] = "Year"; oData[1, 3] = "Target"; oData[1, 4] = "Criteria"; ndx = 2; foreach (DataRow dr in dsTarget.Tables[0].Rows) { nCol = 0; oData[ndx, nCol++] = dr["Attribute_"].ToString(); oData[ndx, nCol++] = dr["TargetName"].ToString(); oData[ndx, nCol++] = dr["Years"].ToString(); oData[ndx, nCol++] = dr["TargetMean"].ToString(); oData[ndx++, nCol++] = dr["Criteria"].ToString(); sheetRow++; } oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion #region Deficient // DEFICIENT aryCols = 5; aryRows = dsDeficient.Tables[0].Rows.Count + 2; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); sheetRow += 2; titles.Add("Deficient", new Titles("Deficient", sheetRow, aryCols, false, "GROUP", 0)); titles.Add("Deficient_col", new Titles("Attribute", sheetRow + 1, aryCols, true, "COLUMN", aryRows - 2)); sheetRow++; oData[0, 0] = "Deficient"; oData[1, 0] = "Attribute"; oData[1, 1] = "Name"; oData[1, 2] = "Deficient Level"; oData[1, 3] = "Allowed Deficient (%)"; oData[1, 4] = "Criteria"; ndx = 2; foreach (DataRow dr in dsDeficient.Tables[0].Rows) { nCol = 0; oData[ndx, nCol++] = dr["Attribute_"].ToString(); oData[ndx, nCol++] = dr["DeficientName"].ToString(); oData[ndx, nCol++] = dr["Deficient"].ToString(); oData[ndx, nCol++] = dr["PercentDeficient"].ToString(); oData[ndx++, nCol++] = dr["Criteria"].ToString(); sheetRow++; } oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion // deficient #region Investments // INVESTMENTS aryCols = 2; aryRows = 5; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); sheetRow += 2; titles.Add("Investments", new Titles("Investment", sheetRow, 1, false, "SECTION", 0)); oData[0, 0] = "Investment"; oData[1, 0] = "Start Year:"; oData[1, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[1].ToString(); oData[2, 0] = "Analysis Period:"; oData[2, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[2].ToString(); oData[3, 0] = "Inflation Rate:"; oData[3, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[3].ToString(); oData[4, 0] = "Discount Rate:"; oData[4, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[4].ToString(); oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); sheetRow += aryRows; #endregion #region Budget // Budget switch (DBMgr.NativeConnectionParameters.Provider) { case "MSSQL": aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT [Year_]) FROM YearlyInvestment WHERE simulationID = " + SimulationId) + 2; break; case "ORACLE": aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT Year_) FROM YearlyInvestment WHERE simulationID = " + SimulationId) + 2; break; default: throw new NotImplementedException("TODO: Create ANSI implementation for CreateInputSummaryReport()"); //break; } aryCols = strBudgetOrder.Count() + 1; criteraColumns.Add("Budget", aryCols); sheetRow += 1; titles.Add("Budget", new Titles("Budget", sheetRow, aryCols, false, "GROUP", 0)); sheetRow++; titles.Add("Years", new Titles("Years", sheetRow, aryCols, false, "COLUMN", aryRows - 2)); sheetRow++; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); oData[0, 0] = "Budget"; oData[1, 0] = "Years"; ndx = 1; foreach (string str in strBudgetOrder) { oData[1, ndx++] = str; } ndx = 2; DataSet dsYearlyInvestment = DBOp.QueryYearlyInvestment(SimulationId); strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE SimulationID = " + SimulationId; DataSet dsYearlyYears = DBMgr.ExecuteQuery(strFilter); foreach (DataRow dr in dsYearlyYears.Tables[0].Rows) { oData[ndx, 0] = dr["Year_"].ToString(); nCol = 1; foreach (string str in strBudgetOrder) { strFilter = "Year_ = " + dr["Year_"].ToString() + " AND Budgetname = '" + str + "'"; foreach (DataRow dr1 in dsYearlyInvestment.Tables[0].Select(strFilter)) { oData[ndx, nCol] = dr1["Amount"].ToString(); } nCol++; } ndx++; sheetRow++; } oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion #region Performance //PERFORMANCE aryRows = DBMgr.ExecuteScalar("SELECT Count(*) FROM Performance WHERE simulationID = " + SimulationId) + 2; aryCols = 4; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); sheetRow += 1; titles.Add("Performance", new Titles("Performance", sheetRow, aryCols, false, "SECTION", 0)); sheetRow++; titles.Add("Performance_col", new Titles("Attribute", sheetRow, aryCols, true, "COLUMN", aryRows - 2)); sheetRow++; oData[0, 0] = "Performance"; oData[1, 0] = "Attribute"; oData[1, 1] = "Equation Name"; oData[1, 2] = "Equation"; oData[1, 3] = "Criteria"; DataSet dsPerformance = DBOp.QueryPerformance(SimulationId); ndx = 2; foreach (DataRow dr in dsPerformance.Tables[0].Rows) { nCol = 0; oData[ndx, nCol++] = dr["Attribute_"].ToString(); oData[ndx, nCol++] = dr["EquationName"].ToString(); oData[ndx, nCol++] = dr["Equation"].ToString(); oData[ndx++, nCol++] = dr["Criteria"].ToString(); sheetRow++; } oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion #region Treatment // TREATMENT DataSet dsTreatments = DBOp.QueryTreatments(SimulationId); DataSet dsFeasibility = DBOp.QueryFeasibility(SimulationId); DataSet dsCosts = DBOp.QueryCosts(SimulationId); DataSet dsConsequences = DBOp.QueryConsequences(SimulationId); int blankLines = 5, grpHeaders = 3, colHeaders = 3, title = 1, preamble = 5; aryRows = (blankLines + grpHeaders + colHeaders + title + preamble) * dsTreatments.Tables[0].Rows.Count; aryRows += dsFeasibility.Tables[0].Rows.Count + dsCosts.Tables[0].Rows.Count + dsConsequences.Tables[0].Rows.Count; aryCols = 3; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); sheetRow++; titles.Add("Treatment", new Titles("Treatment", sheetRow, aryCols, false, "SECTION", 0)); sheetRow++; int treatNum = 1; // keep track of where each treatment section begins string strGrid = ""; List <string> gridList = new List <string>(); oData[0, 0] = "Treatment"; ndx = 2; sheetRow++; foreach (DataRow dr in dsTreatments.Tables[0].Rows) { titles.Add("Treatment" + treatNum.ToString(), new Titles("Name", sheetRow, 1, false, "BOLD", 4)); oData[ndx, 0] = "Treatment Name:"; oData[ndx++, 1] = dr["Treatment"].ToString(); sheetRow++; oData[ndx, 0] = "Description:"; oData[ndx++, 1] = dr["Description"].ToString(); sheetRow++; oData[ndx, 0] = "Budget:"; oData[ndx++, 1] = dr["Budget"].ToString(); sheetRow++; oData[ndx, 0] = "Years Before Any:"; sheetRow++; oData[ndx++, 1] = dr["BeforeAny"].ToString(); sheetRow++; oData[ndx, 0] = "Years Before Same:"; oData[ndx++, 1] = dr["BeforeSame"].ToString(); sheetRow++; //Feasibility ndx++; int count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Feasibility WHERE TreatmentID = " + dr["TreatmentID"].ToString()); titles.Add("Feasibility" + treatNum.ToString() + "grp", new Titles("Feasibility", sheetRow, 1, false, "GROUP", 0)); oData[ndx++, 0] = "Feasibility"; sheetRow++; titles.Add("Feasibility" + treatNum.ToString() + "col", new Titles("Criteria", sheetRow, 1, true, "COLUMN", count)); oData[ndx++, 0] = "Criteria"; sheetRow++; if (count == 0) { aryRows += 2; ndx++; sheetRow++; } else { //aryRows += count + 1; //Report.Resize2DArrayKeepData(ref oData, aryRows, aryCols); strFilter = "TreatmentID = " + dr["TreatmentID"].ToString(); foreach (DataRow dr1 in dsFeasibility.Tables[0].Select(strFilter)) { Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7); //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ","; //string s = "A" + sheetRow.ToString() + ":H" + sheetRow.ToString(); //oR = oSheet.get_Range(s, Missing.Value); //oR.NumberFormat = "@"; oData[ndx++, 0] = dr1["Criteria"].ToString(); sheetRow++; } } ndx++; sheetRow++; // Costs titles.Add("Cost" + treatNum.ToString() + "grp", new Titles("Cost", sheetRow, 1, false, "GROUP", 0)); oData[ndx++, 0] = "Cost"; sheetRow++; count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Costs WHERE TreatmentID = " + dr["TreatmentID"].ToString()); titles.Add("Cost" + treatNum.ToString() + "col", new Titles("Cost", sheetRow, 3, true, "COLUMN", count)); oData[ndx, 0] = "Cost"; oData[ndx, 1] = "Units"; oData[ndx++, 2] = "Criteria"; sheetRow++; if (count == 0) { ndx++; sheetRow++; } else { strFilter = "TreatmentID = " + dr["TreatmentID"].ToString(); foreach (DataRow dr1 in dsCosts.Tables[0].Select(strFilter)) { Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7); //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ","; //string s = "A" + sheetRow.ToString() + ":H" + sheetRow.ToString(); //oR = oSheet.get_Range(s, Missing.Value); //oR.NumberFormat = "@"; oData[ndx, 0] = dr1["Cost_"]; oData[ndx, 1] = dr1["Unit"].ToString(); oData[ndx++, 2] = dr1["Criteria"].ToString(); sheetRow++; } } ndx++; sheetRow++; // Consequences titles.Add("Consequence" + treatNum.ToString() + "grp", new Titles("Consequence", sheetRow, 1, false, "GROUP", 0)); oData[ndx++, 0] = "Consequence"; sheetRow++; count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Consequences WHERE TreatmentID = " + dr["TreatmentID"].ToString()); titles.Add("Consequence" + treatNum.ToString() + "col", new Titles("Consequence", sheetRow, 3, true, "COLUMN", count)); oData[ndx, 0] = "Attribute"; oData[ndx, 1] = "Change"; oData[ndx++, 2] = "Criteria"; sheetRow++; if (count == 0) { ndx++; sheetRow++; } else { strFilter = "TreatmentID = " + dr["TreatmentID"].ToString(); foreach (DataRow dr1 in dsConsequences.Tables[0].Select(strFilter)) { Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7); //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ","; string s = "B" + sheetRow.ToString() + ":B" + sheetRow.ToString(); oR = oSheet.get_Range(s, Missing.Value); oR.NumberFormat = "@"; oData[ndx, 0] = dr1["Attribute_"].ToString(); oData[ndx, 1] = dr1["Change_"].ToString(); oData[ndx++, 2] = dr1["Criteria"].ToString(); sheetRow++; } } ndx++; sheetRow++; treatNum++; } // end foreach oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2); oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion // Apply formatting List <string> sectionList = new List <string>(); List <string> groupList = new List <string>(); List <string> columnList = new List <string>(); List <string> criteriaList = new List <string>(); List <string> boldList = new List <string>(); //List<string> gridList = new List<string>(); string strSection = "", strGroup = "", strColumn = "", strCriteria = "", strBold = ""; #region pageheader // PAGEHEADER string strRange = "B1:H1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); #endregion #region bold // Set bold text at known locations object o = titles["Investments"]; if (o != null) { Titles bold = (Titles)o; int nTmp = bold.Row; strRange = "A" + (nTmp + 1).ToString() + ":A" + (nTmp + 4).ToString(); strRange += ",A1:A11,A14:A19"; oR = oSheet.get_Range(strRange, Missing.Value); oR.Font.Bold = true; oR.Font.Size = 11; } #endregion #region Format headers // Format headers foreach (DictionaryEntry de in titles) { Titles t = (Titles)de.Value; int nSize = t.NumberOfColumns < 9 ? 8 : t.NumberOfColumns; if (t.TitleType == "SECTION") { Report.BuildRange(ref strSection, ref sectionList, t.Row, 'A', nSize - 1); } else if (t.TitleType == "GROUP") { if ((string)de.Key == "Budget") { nSize = t.NumberOfColumns; } Report.BuildRange(ref strGroup, ref groupList, t.Row, 'A', nSize - 1); } else if (t.TitleType == "BOLD") { strBold += ("A" + t.Row.ToString() + ":A" + (t.Row + t.NumberOfDataRows).ToString() + ","); } else if (t.TitleType == "COLUMN") { if ((string)de.Key == "Years") { nSize = t.NumberOfColumns; } Report.BuildRange(ref strColumn, ref columnList, t.Row, 'A', nSize - 1); if (t.Criteria) { string s = Report.GetColumnLetter(t.NumberOfColumns); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; int asciiValue = (int)asciiChar; asciiValue += nSize - t.NumberOfColumns; asciiChar = (char)asciiValue; strCriteria = Report.Left(s, 1) + t.Row.ToString() + ":" + asciiChar.ToString() + t.Row.ToString(); criteriaList.Add(strCriteria); for (int i = 1; i <= t.NumberOfDataRows; i++) { int gridrow = t.Row + i; strCriteria = Report.Left(s, 1) + gridrow.ToString() + ":" + asciiChar.ToString() + gridrow.ToString(); criteriaList.Add(strCriteria); } } } } Report.EndRangeList(ref strSection, ref sectionList); Report.EndRangeList(ref strGroup, ref groupList); Report.EndRangeList(ref strColumn, ref columnList); Report.EndRangeList(ref strCriteria, ref criteriaList); Report.EndRangeList(ref strBold, ref boldList); Report.EndRangeList(ref strGrid, ref gridList); foreach (string s in boldList) { oR = oSheet.get_Range(s, Missing.Value); oR.Font.Bold = true; } foreach (string s in sectionList) { oR = oSheet.get_Range(s, Missing.Value); oR.MergeCells = true; oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy); oR.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); oR.Font.Size = 13; oR.Font.Bold = true; } foreach (string s in groupList) { oR = oSheet.get_Range(s, Missing.Value); oR.MergeCells = true; oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy); oR.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); oR.Font.Size = 11; oR.Font.Bold = true; oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; } foreach (string s in columnList) { oR = oSheet.get_Range(s, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.VerticalAlignment = XlVAlign.xlVAlignBottom; oR.WrapText = true; oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); oR.Font.Size = 11; oR.Font.Bold = true; oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; } foreach (string s in criteriaList) { oR = oSheet.get_Range(s, Missing.Value); oR.MergeCells = true; oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.VerticalAlignment = XlVAlign.xlVAlignBottom; oR.WrapText = true; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; } foreach (string s in gridList) { oR = oSheet.get_Range(s, Missing.Value); oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; } #endregion #region Wide cells // Format wide cells strRange = "B6:H6,B7:H7,B8:H8,B9:H9,B10:H10,B19:H19"; oR = oSheet.get_Range(strRange, Missing.Value); oR.MergeCells = true; oR.WrapText = true; oR.VerticalAlignment = XlVAlign.xlVAlignTop; #endregion Report.XL.Visible = true; Report.XL.UserControl = true; }
public void CreateTotalBudgetReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; Report.SheetPageSetup(oSheet, "Total Budget", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); Range oR = oSheet.get_Range("A1", "A1"); object oEndCell = new object(); string strReportName = "Total Budget Report", strFilter = ""; int sheetRow; int ndx; DataSet dsPage = null, dsSimulations = null, dsInvestments = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsSimulations = DBOp.QuerySimulations(m_strSimulationID); dsInvestments = DBOp.QueryInvestments(m_strSimulationID); // get the budgetorder } catch (Exception e) { throw e; } if (m_bDebug) { WriteLogEntry("DataSet OK"); } Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(','); #region default column widths // set some column widths oR = oSheet.get_Range("A1:A1", Missing.Value); oR.ColumnWidth = 18; #endregion if (m_bDebug) { WriteLogEntry("Default column widths OK"); } #region place agency graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion if (m_bDebug) { WriteLogEntry("Place agency graphic OK"); } int aryCols = 2; int aryRows = 3; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); oData[0, 1] = strMajorTitle; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #region budget int nFY = 1, nBudgetTotal = 1; switch (DBMgr.NativeConnectionParameters.Provider) { case "MSSQL": aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT [Year_]) FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID) + 2; break; case "ORACLE": aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT Year_) FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID) + 2; break; default: throw new NotImplementedException("TODO: Create ANSI implementation for CreateTotalBudgetReport()"); //break; } aryCols = strBudgetOrder.Count() + nFY + nBudgetTotal; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); oData[0, 0] = "Total Budget - in Millions"; oData[1, 0] = "FY"; sheetRow = 6; ndx = 1; foreach (string str in strBudgetOrder) { oData[1, ndx++] = str; } oData[1, ndx] = "Total"; // add total column ndx = 2; DataSet dsYearlyInvestment = DBOp.QueryYearlyInvestment(m_strSimulationID); strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE SimulationID = " + m_strSimulationID; DataSet dsYearlyYears = DBMgr.ExecuteQuery(strFilter); int nCol = 0; double budgetTotal; foreach (DataRow dr in dsYearlyYears.Tables[0].Rows) { oData[ndx, 0] = dr["Year_"].ToString(); nCol = 1; budgetTotal = 0; foreach (string str in strBudgetOrder) { strFilter = "Year_ = " + dr["Year_"].ToString() + " AND Budgetname = '" + str + "'"; foreach (DataRow dr1 in dsYearlyInvestment.Tables[0].Select(strFilter)) { oData[ndx, nCol] = Convert.ToDouble(dr1["Amount"].ToString()) / 1000000; // show millions budgetTotal += double.Parse(dr1["Amount"].ToString()); // sum the budgets for a fiscal year } nCol++; } oData[ndx, nCol] = budgetTotal / 1000000; ndx++; sheetRow++; } oEndCell = "A4"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion if (m_bDebug) { WriteLogEntry("Budget OK"); } #region format pageheader // PAGEHEADER string strRange = "B1:G1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); #endregion if (m_bDebug) { WriteLogEntry("Format pageheader OK"); } #region format groupheader strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4"; oR = oSheet.get_Range(strRange, Missing.Value); oR.MergeCells = true; oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy); oR.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); oR.Font.Size = 11; oR.Font.Bold = true; oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; #endregion if (m_bDebug) { WriteLogEntry("Format groupheader OK"); } #region format columnheader strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5"; oR = oSheet.get_Range(strRange, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.VerticalAlignment = XlVAlign.xlVAlignBottom; if (m_bDebug) { WriteLogEntry("Format columnheader VerticalAlignment OK"); } //oR.WrapText = true; oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); oR.Font.Size = 11; oR.Font.Bold = true; oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous; if (m_bDebug) { WriteLogEntry("Format columnheader EdgeTop OK"); } oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; if (m_bDebug) { WriteLogEntry("Format columnheader EdgeBottom OK"); } oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; //if (m_bDebug) WriteLogEntry("Format columnheader InsideHorizontal OK"); //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; //if (m_bDebug) WriteLogEntry("Format columnheader InsideVertical OK"); #endregion if (m_bDebug) { WriteLogEntry("Format columnheader OK"); } #region format grid data strRange = "A6:A" + (sheetRow - 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.NumberFormat = "$#,##0.0"; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value); //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.ColumnWidth = 13.4; #endregion if (m_bDebug) { WriteLogEntry("Format grid data OK"); } #region create column chart strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString(); Range oSourceData = oSheet.get_Range(strRange, Missing.Value); //B5:E10 int left = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet); string strTitle = "Yearly Budget Distribution ($ Millions)"; string sheetName = "='Total Budget'!$A$6:$A$" + (sheetRow - 1).ToString(); List <string> listBudgets = new List <string>(); foreach (string s in strBudgetOrder) { listBudgets.Add(s); } listBudgets.Add("Total"); Report.CreateColClusterBarGraph(left, 30, 425, 315, oSheet, oSourceData, sheetName, strTitle, 12, "Fiscal Year", 11, listBudgets, "", 11, XlRowCol.xlColumns); #endregion if (m_bDebug) { WriteLogEntry("Create column chart OK"); } Report.XL.Visible = true; Report.XL.UserControl = true; }
public void CreateDetailedResultsReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; string strReportName = "Detailed Results Report"; Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); //oSheet.PageSetup.RightFooter = m_strNetwork + " - " + m_strSimulation; //oSheet.PageSetup.LeftFooter = DateTime.Now.ToLongDateString(); //oSheet.PageSetup.CenterFooter = "Page &P"; //oSheet.PageSetup.FirstPageNumber = 1; //oSheet.PageSetup.LeftMargin = 50d; //oSheet.PageSetup.RightMargin = 20d; //oSheet.Columns.Font.Size = 10; //oSheet.Name = strReportName; Range oR = oSheet.get_Range("A1", "A1"); object oEndCell = new object(); string strFilter = ""; int sheetRow; int ndx; DataSet dsPage = null, dsResults = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsResults = DBOp.QueryReport(m_strNetworkID, m_strSimulationID); } catch (Exception e) { throw e; // Circular reference, address later //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message); } Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); drPage = dsPage.Tables[0].Rows[2]; string strMinorTitle = drPage["phText"].ToString(); if (strMinorTitle.IndexOf("@1") > 0) { strMinorTitle = strMinorTitle.Replace("@1", m_strNetwork + " (" + m_strNetworkID + ")"); } if (strMinorTitle.IndexOf("@2") > 0) { strMinorTitle = strMinorTitle.Replace("@2", m_strSimulation + " (" + m_strSimulationID + ")"); } #region default column widths // set some column widths oR = oSheet.get_Range("A1:B1", Missing.Value); oR.ColumnWidth = 15; #endregion #region place agency graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion #region write Major Title int aryCols = 2; int aryRows = 3; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); oData[0, 1] = strMajorTitle; oData[2, 1] = strMinorTitle; sheetRow = 4; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region get Budget Years strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_"; DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter); int numYears = dsBudgetYears.Tables[0].Rows.Count; #endregion aryCols = numYears + 2; //aryRows = dsResults.Tables[0].Rows.Count / 5 + 2; aryRows = dsResults.Tables[0].Rows.Count / numYears + 2; #region build Column Headers array Report.Resize2DArray(ref oData, aryRows + 1, aryCols); Report.ClearDataArray(ref oData); object[] oColumnHeader = new object[aryCols]; oColumnHeader[0] = "Facility"; oColumnHeader[1] = "Section"; ndx = 2; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { oColumnHeader[ndx++] = dr["Year_"].ToString(); } #endregion #region build Results table ndx = 0; oData[ndx, 0] = "Treatment Distribution per Section"; // Group Header sheetRow = 5; oEndCell = "A" + sheetRow.ToString(); sheetRow++; ndx++; for (int i = 0; i < aryCols; i++) { oData[ndx, i] = oColumnHeader[i]; // Column Header } sheetRow++; ndx++; int rowsToColumns = 0, nCol = 2; string strGreen = "", strBlue = "", strRed = ""; List <string> greenList = new List <string>(); List <string> blueList = new List <string>(); List <string> redList = new List <string>(); foreach (DataRow dr in dsResults.Tables[0].Rows) { string sYear = dr["Years"].ToString(); if (rowsToColumns < numYears) { if (rowsToColumns == 0) { nCol = 2; if (ndx == 132) { string testString = dr["Facility"].ToString(); } oData[ndx, 0] = dr["Facility"].ToString(); oData[ndx, 1] = dr["Section"].ToString(); } } switch (DBMgr.NativeConnectionParameters.Provider) { case "MSSQL": // Test for Treatment selected per RoadCare logic (GREEN) if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0); oData[ndx, nCol] = dr["Treatment"].ToString(); } // Test for Feasible treatment not funded/selected (BLUE) else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0); } // Test for Treatment selected as Committed Project (RED) else if (dr["Iscommitted"].ToString().ToUpper() == "TRUE") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0); oData[ndx, nCol] = dr["Treatment"].ToString(); } break; case "ORACLE": // Test for Treatment selected per RoadCare logic (GREEN) if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0); oData[ndx, nCol] = dr["Treatment"].ToString(); } // Test for Feasible treatment not funded/selected (BLUE) else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0); } // Test for Treatment selected as Committed Project (RED) else if (dr["Iscommitted"].ToString() == "1") { string s = Report.GetColumnLetter(nCol + 1); char[] chr = s.ToCharArray(0, 1); char asciiChar = chr[0]; Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0); oData[ndx, nCol] = dr["Treatment"].ToString(); } break; default: throw new NotImplementedException("TODO: develop ANSI version of CreateDetailedResultsReport()"); //break; } nCol++; if (rowsToColumns + 1 >= numYears) { rowsToColumns = 0; ndx++; sheetRow++; } else { rowsToColumns++; } } oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true); #endregion #region format pageheader // PAGEHEADER string strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); strRange = "B2:" + Report.GetColumnLetter(aryCols) + "2"; drPgHdr = dsPage.Tables[0].Rows[1]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); strRange = "B3:" + Report.GetColumnLetter(aryCols) + "3"; drPgHdr = dsPage.Tables[0].Rows[2]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); // Place a color legend at top and bottom of report PlaceColorLegend(oSheet, aryCols, 3); PlaceColorLegend(oSheet, 1, sheetRow + 1); #endregion #region format groupheader strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5"; Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange); #endregion #region format columnHeader strRange = "A6:" + Report.GetColumnLetter(aryCols) + "6"; Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); #endregion #region apply color Report.EndRangeList(ref strGreen, ref greenList); Report.EndRangeList(ref strBlue, ref blueList); Report.EndRangeList(ref strRed, ref redList); foreach (string s in greenList) { oR = oSheet.get_Range(s, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen); } foreach (string s in blueList) { oR = oSheet.get_Range(s, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue); } foreach (string s in redList) { oR = oSheet.get_Range(s, Missing.Value); oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon); } #endregion #region default column widths // set some column widths oR = oSheet.get_Range("A1:B1", Missing.Value); oR.ColumnWidth = 15; strRange = "C1:" + Report.GetColumnLetter(aryCols) + "1"; oR = oSheet.get_Range(strRange, Missing.Value); oR.ColumnWidth = 12; #endregion #region make bold strRange = "A7:B" + (sheetRow - 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.Font.Bold = true; #endregion Report.XL.Visible = true; Report.XL.UserControl = true; }
public void CreateTotalLaneMileYearsPerConditionReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; string strReportName = "Lane Mile Years Per Condition"; object oEndCell = new object(); string strFilter = ""; int sheetRow; int ndx; DataSet dsPage = null, dsSimulations = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsSimulations = DBOp.QuerySimulations(m_strSimulationID); } catch (Exception e) { throw e; // Circular reference, address later //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message); } if (m_useLaneMiles) { Report.SheetPageSetup(oSheet, "Total Lane-Mile Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); } else { Report.SheetPageSetup(oSheet, "Total VML Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); } Range oR = oSheet.get_Range("A1", "A1"); Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); if (strMajorTitle.IndexOf("@1") > 0) { strMajorTitle = strMajorTitle.Replace("@1", m_strBudgetPer); // stuff the attribute into title } if (!m_useLaneMiles) { strMajorTitle = strMajorTitle.Replace("Lane-Miles", "Vehicle Lane-Miles"); } #region default column widths // set some column widths oR = oSheet.get_Range("A1:A1", Missing.Value); oR.ColumnWidth = 18; #endregion #region place agency graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion #region write Major Title int aryCols = 2; int aryRows = 3; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); oData[0, 1] = strMajorTitle; sheetRow = 4; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region get Budget Years strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + " ORDER BY Year_"; DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter); int numYears = dsBudgetYears.Tables[0].Rows.Count; #endregion int numConditions = 5, nTables = 2, rowSpacer = 2; aryCols = numYears + 1; aryRows = nTables * (numConditions + 3) + rowSpacer; #region build Column Headers array // Set up column header once, for multiple uses object[] oColumnHeader = new object[aryCols]; oColumnHeader[0] = "Condition"; //m_strBudgetPer; ndx = 1; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { oColumnHeader[ndx++] = dr["Year_"].ToString(); } #endregion #region build Condition List // REVIST THIS REGION. BUILD LIST FROM THE DATABASE WHEN POSSIBLE xyzzy List <string> listCondition = new List <string>(); listCondition.Add("Very Good"); listCondition.Add("Good"); listCondition.Add("Fair"); listCondition.Add("Mediocre"); listCondition.Add("Poor"); #endregion #region build Budget and Percentage tables Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); int totalRow, nCol, budgetStartRowNdx, rowCounter = 0, acceptableRow; Hashtable laneMilesPerCondition; double nTmp, nTmp1; ndx = 0; oData[ndx, 0] = "Lane-miles per Condition Category"; // Group Header sheetRow = 4; totalRow = 2 + numConditions; acceptableRow = oData.GetUpperBound(0) - 1; oEndCell = "A" + sheetRow.ToString(); sheetRow++; ndx++; oData[totalRow, 0] = "Total"; for (int i = 0; i < aryCols; i++) { oData[ndx, i] = oColumnHeader[i]; // Column Header } sheetRow++; ndx++; budgetStartRowNdx = 2; // percentRowNdx is used to pull a value out of oData then use it to calculate the percent table List <string> listAttributes; string strCriteria; string strLeftSide = "", strConnector = "", strRightSide = ""; double nBudget = 0d; foreach (string s in listCondition) { oData[ndx, 0] = s; nCol = 1; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { ConditionCriteria(s, ref strLeftSide, ref strConnector, ref strRightSide); strCriteria = m_strBudgetPer + "_" + dr["Year_"].ToString() + strLeftSide; if (strConnector != "") { strCriteria += strConnector + m_strBudgetPer + "_" + dr["Year_"].ToString() + strRightSide; } strFilter = " YEARS = " + dr["Year_"].ToString() + " AND " + strCriteria; //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString(); //strFilter = " YEARS = " + dr["Year"].ToString(); //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString(); laneMilesPerCondition = DBOp.GetPercentagePerStringAttribute(m_strNetworkID, m_strSimulationID, m_strBudgetPer.ToUpper(), dr["Year_"].ToString(), "AREA", strFilter, "REMAINING_LIFE", m_useLaneMiles, out listAttributes); nBudget = 0d; foreach (DictionaryEntry de in laneMilesPerCondition) { string entry = (string)de.Value; if (entry != "") { nBudget += Convert.ToDouble(entry); } } //string strHash = (string)laneMilesPerCondition[s.ToUpper()]; //double nBudget = Convert.ToDouble(strHash); if (oData[totalRow, nCol].ToString() == "") { oData[totalRow, nCol] = 0d; // initalize array element } nTmp = (double)oData[totalRow, nCol]; nTmp += nBudget; oData[totalRow, nCol] = nTmp; oData[ndx, nCol++] = nBudget; } ndx++; sheetRow++; } sheetRow += 2; ndx += 2; if (m_useLaneMiles) { oData[ndx, 0] = "Lane-Mile Years Distribution (%) per Condition Category"; } else { oData[ndx, 0] = "Vehicle Lane-Mile Years Distribution (%) per Condition Category"; } ndx++; sheetRow++; for (int i = 0; i < aryCols; i++) { oData[ndx, i] = oColumnHeader[i]; // Column Header } sheetRow++; ndx++; rowCounter = 1; foreach (string s in listCondition) { oData[ndx, 0] = s; nCol = 1; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { nTmp = (double)oData[budgetStartRowNdx, nCol]; nTmp1 = (double)oData[totalRow, nCol]; double nPercent = nTmp / nTmp1; oData[ndx, nCol] = nPercent; if (rowCounter <= 3) { // sum the % Acceptable if (oData[acceptableRow, nCol].ToString() == "") { oData[acceptableRow, nCol] = 0d; // initalize array element } nTmp = (double)oData[acceptableRow, nCol]; nTmp += nPercent; oData[acceptableRow, nCol] = nTmp; } else { // sum the % Unacceptable if (oData[acceptableRow + 1, nCol].ToString() == "") { oData[acceptableRow + 1, nCol] = 0d; // initalize array element } nTmp = (double)oData[acceptableRow + 1, nCol]; nTmp += nPercent; oData[acceptableRow + 1, nCol] = nTmp; } nCol++; } ndx++; sheetRow++; budgetStartRowNdx++; rowCounter++; } oData[acceptableRow, 0] = "%Acceptable"; sheetRow++; oData[acceptableRow + 1, 0] = "%Unacceptable"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region format pageheader // PAGEHEADER string strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); #endregion #region format groupheader int startingRow = 4; int offSet = startingRow + numConditions + 2 + rowSpacer; strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4"; Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange); strRange = "A" + offSet.ToString() + ":" + Report.GetColumnLetter(aryCols) + offSet.ToString(); Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange); #endregion #region format columnHeader strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5" + ", A" + (offSet + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (offSet + 1).ToString(); Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); #endregion #region format totals row strRange = "A" + (totalRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString(); Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); strRange = "A" + (acceptableRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 4).ToString(); Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); strRange = "A" + (acceptableRow + 5).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 5).ToString(); Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); #endregion #region format grid data strRange = "A5:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString(); strRange += ",A" + (acceptableRow - numConditions + startingRow - 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; #endregion #region format Lane Mile cells strRange = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.NumberFormat = "#,##0.0"; oR.ColumnWidth = 15; #endregion #region format Percent cells strRange = "B" + (acceptableRow - numConditions + startingRow).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.NumberFormat = "0.0%"; #endregion #region create column charts strRange = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + startingRow - 1).ToString(); Range oSourceData = oSheet.get_Range(strRange, Missing.Value); int left = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet); if (m_useLaneMiles) { Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total Lane-Mile Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5", "Annual Total Lane-Mile Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows); } else { Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total VML Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5", "Annual Total VLM Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows); } #endregion Report.XL.Visible = true; Report.XL.UserControl = true; }
public void CreateLaneMilesPerTreatmentReport() { Report.XL.Visible = false; Report.XL.UserControl = false; Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook(); Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; string tabName = "Lane-Miles Per"; if (!m_useLaneMiles) { tabName = "VLM Per"; } Report.SheetPageSetup(oSheet, tabName + m_strBudgetPer, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1); Range oR = oSheet.get_Range("A1", "A1"); object oEndCell = new object(); string strFilter = ""; string strReportName = "Lane-Miles Per Treatment Report"; string strRange; int sheetRow; int ndx, nCol; DataSet dsPage = null, dsSimulations = null; try { dsPage = DBOp.QueryPageHeader(strReportName); dsSimulations = DBOp.QuerySimulations(m_strSimulationID); } catch (Exception e) { throw e; // Circular reference, address later //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message); } if (m_useLaneMiles) { strReportName = "Vehicle Lane-Miles Per Treatment Report"; } Cursor c = Cursor.Current; Cursor.Current = new Cursor(Cursors.WaitCursor.Handle); DataRow drPage = dsPage.Tables[0].Rows[0]; string strMajorTitle = drPage["phText"].ToString(); if (!m_useLaneMiles) { strMajorTitle = "Vehicle " + strMajorTitle; } #region default column widths // set some column widths oR = oSheet.get_Range("A1:A1", Missing.Value); oR.ColumnWidth = 18; #endregion #region place agency graphic //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString(); string strPath = ".\\" + drPage["reportGraphicFile"].ToString(); Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet); #endregion #region write Major Title int aryCols = 2; int aryRows = 3; object[,] oData = new object[aryRows, aryCols]; Report.ClearDataArray(ref oData); oData[0, 1] = strMajorTitle; sheetRow = 4; oEndCell = "A1"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region get Budget Years DataSet dsBudgetYears = DBOp.QueryBudgetYears(m_strSimulationID); int numYears = dsBudgetYears.Tables[0].Rows.Count; #endregion #region get Treatments List <string> treatmentList = DBOp.GetTreatments(m_strNetworkID, m_strSimulationID); treatmentList.Remove("No Treatment"); int numTreatments = treatmentList.Count(); #endregion aryCols = numYears + 1; aryRows = numTreatments + 3; Report.Resize2DArray(ref oData, aryRows, aryCols); Report.ClearDataArray(ref oData); #region build the Lane-Miles table Hashtable laneMilesPerTreatment; // get the number of districts List <string> strJuris = DBOp.GetJurisdiction(m_strNetworkID, "DISTRICT"); strJuris.Sort(); int numDistricts = strJuris.Count; sheetRow = 4; ndx = 1; if (m_useLaneMiles) { oData[0, 0] = "Annual Total Lane-Miles"; } else { oData[0, 0] = "Annual Total Vehicle Lane-Miles"; } oData[1, 0] = m_strBudgetPer; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { oData[1, ndx++] = dr["Year_"]; } sheetRow++; ndx = 2; nCol = 0; foreach (string s in treatmentList) { oData[ndx++, nCol] = s; sheetRow++; } oData[ndx, nCol] = "Total"; sheetRow++; int totalRow = oData.GetUpperBound(0); double nTotal = 0d, nMiles; List <string> listAttributes; foreach (DataRow dr in dsBudgetYears.Tables[0].Rows) { ndx = 2; nCol++; nTotal = 0d; foreach (string s in treatmentList) { strFilter = "TREATMENT = '" + s + "' AND YEARS = " + dr["Year_"].ToString(); laneMilesPerTreatment = DBOp.GetPercentagePerStringAttribute(m_strNetworkID, m_strSimulationID, "DISTRICT", "", "AREA", strFilter, m_useLaneMiles, out listAttributes); nMiles = sumLaneMiles(laneMilesPerTreatment, numDistricts); // have to sum the districts to get the total nTotal += nMiles; oData[ndx++, nCol] = nMiles; oData[totalRow, nCol] = nTotal; } } oEndCell = "A4"; oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false); #endregion #region format pageheader // PAGEHEADER strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1"; DataRow drPgHdr = dsPage.Tables[0].Rows[0]; Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange); #endregion #region format groupheader strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4"; Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange); #endregion #region format columnHeader strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5"; Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); #endregion #region format totals row strRange = "A" + (sheetRow).ToString() + ":" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString(); Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange); #endregion #region format grid data strRange = "A6:" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.HorizontalAlignment = XlHAlign.xlHAlignCenter; oR.Borders.LineStyle = XlLineStyle.xlContinuous; oR.Borders.Weight = XlBorderWeight.xlThin; oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous; oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous; #endregion #region set column width strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString(); oR = oSheet.get_Range(strRange, Missing.Value); oR.NumberFormat = "#,##0.0"; oR.ColumnWidth = 12; #endregion #region create column charts string strTitle = ""; strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString(); Range oSourceData = oSheet.get_Range(strRange, Missing.Value); int left = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet); //strRange = "B6:F13"; //oSourceData = oSheet.get_Range((sTmp + nTmp.ToString()), Missing.Value); strTitle = "Annual Lane-Miles Per " + m_strBudgetPer; if (!m_useLaneMiles) { strTitle = "Annual Vehicle Lane-Miles Per " + m_strBudgetPer; } string sheetName = "='Lane-Miles Per" + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5"; if (!m_useLaneMiles) { sheetName = "='VLM Per" + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5"; } Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, sheetName, strTitle, 12, "Fiscal Year", 11, treatmentList, "", 11, XlRowCol.xlRows); #endregion Report.XL.Visible = true; Report.XL.UserControl = true; } // end CreateBudgetPerTreatmentReport