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