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 CreateSectionReport()
{
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 strTrace = string.Format("preamble {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
string strReportName = "Section Detail Report";
Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, "Network: " + m_strNetworkName, DateTime.Now.ToLongDateString(), "Page &P", 1);
//oSheet.PageSetup.RightFooter = "Network: " + m_strNetworkName;
//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;
object oEndCell = new object();
DataSet dsPage = null;
DataSet dsGroupNames = null;
DataSet dsGroupDetail = null;
try
{
dsPage = DBOp.QueryPageHeader(strReportName);
dsGroupNames = DBOp.QueryAttributeGroupNames();
dsGroupDetail = DBOp.QueryAttributeByGroup();
}
catch (Exception e)
{
throw e;
}
int nSize = m_nMaxYear - m_nMinYear + 2;
nSize = nSize < 1? 1: nSize;
// Set up column header once, for multiple uses
object[] oColumnHeader = new object[nSize];
oColumnHeader[0] = "ATTRIBUTE_";
int currentYear = m_nMinYear;
for (int i = 1; i < nSize; i++, currentYear++)
{
oColumnHeader[i] = currentYear.ToString();
}
string[] strCols = new string[m_ds.Tables[0].Columns.Count];
int idx = 0;
foreach (DataColumn dataColumn in m_ds.Tables[0].Columns)
{
strCols[idx] = dataColumn.ColumnName.ToString();
idx++;
}
List <string> subList = new List <string>();
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_strNetworkName);
//if (strMinorTitle.IndexOf("@2") > 0) strMinorTitle = strMinorTitle.Replace("@2", m_ds.Tables[0].Rows[0]["FACILITY"].ToString() + ", Section: " + m_ds.Tables[0].Rows[0]["SECTION"].ToString());
int sumCol = 0, numSpacers = dsGroupNames.Tables[0].Rows.Count, numColHeaders = 1;
sumCol = dsGroupDetail.Tables[0].Rows.Count; // number of detail rows
// calculate number of array rows for report
int aryRows = ((dsPage.Tables[0].Rows.Count + 1 + dsGroupNames.Tables[0].Rows.Count + sumCol + (dsGroupNames.Tables[0].Rows.Count * numColHeaders) + numSpacers) * m_ds.Tables[0].Rows.Count);
int aryCurrentRow = 0;
object[,] oData1 = new object[aryRows, nSize];
Report.ClearDataArray(ref oData1);
//string strTrace = string.Format("start LOOP {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
string strFilter;
#region Load data array
//strTrace = string.Format("/tData start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
foreach (DataRow drdata in m_ds.Tables[0].Rows)
{
// Page Header
strMinorTitle = drPage["phText"].ToString();
if (strMinorTitle.IndexOf("@1") > 0)
{
strMinorTitle = strMinorTitle.Replace("@1", m_strNetworkName);
}
if (strMinorTitle.IndexOf("@2") > 0)
{
strMinorTitle = strMinorTitle.Replace("@2", drdata["FACILITY"].ToString() + ", Section: " + drdata["SECTION"].ToString());
}
oData1[aryCurrentRow, 0] = "";
oData1[aryCurrentRow, 1] = strMajorTitle;
aryCurrentRow++;
oData1[aryCurrentRow, 0] = "";
aryCurrentRow++;
oData1[aryCurrentRow, 0] = "";
oData1[aryCurrentRow, 1] = strMinorTitle;
aryCurrentRow += 2;
// Load Group Header
foreach (DataRow dr1 in dsGroupNames.Tables[0].Rows)
{
oData1[aryCurrentRow, 0] = dr1["GROUPING"].ToString();
aryCurrentRow++;
// Load Column Header
for (int i = 0; i < nSize; i++)
{
oData1[aryCurrentRow, i] = oColumnHeader[i];
}
aryCurrentRow++;
//Load Detail Data
strFilter = "GROUPING = '" + dr1["GROUPING"].ToString() + "'";
foreach (DataRow drDetail in dsGroupDetail.Tables[0].Select(strFilter))
{
// Set the Row Label text
oData1[aryCurrentRow, 0] = drDetail["ATTRIBUTE_"].ToString();
// Load the Data values
subList = columnsFromArrayStartingWith(strCols, drDetail["ATTRIBUTE_"].ToString());
PopulateGroupArrayRowAt(ref oData1, subList, drdata, aryCurrentRow);
aryCurrentRow++;
}
aryCurrentRow++;
}
}
//strTrace = string.Format("/tData stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
oEndCell = "A1";
oEndCell = Report.WriteObjectArrayToExcel(oData1, oSheet, oEndCell, false);
Range oR = oSheet.get_Range("A1", "A1");
String strWork = "";
List <string> rangeList = new List <string>();
int sheetRow;
#region set column widths
//strTrace = string.Format("/tColWidth start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
sheetRow = 1;
oR = oSheet.get_Range("A1:A1", Missing.Value);
oR.ColumnWidth = 16;
Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', nSize - 1);
Report.EndRangeList(ref strWork, ref rangeList);
oR = oSheet.get_Range(rangeList.First(), Missing.Value);
oR.ColumnWidth = 9;
rangeList.Clear();
strWork = "";
//strTrace = string.Format("/tColWidth stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region ranges helper Array
// Build a rows array used for building ranges, formatting report, etc.
int [] clearRows = new int[dsGroupNames.Tables[0].Rows.Count + 1];
aryCurrentRow = 1;
clearRows[0] = dsPage.Tables[0].Rows.Count + 1;
foreach (DataRow drGrouping in dsGroupNames.Tables[0].Rows)
{
strFilter = "grouping = '" + drGrouping["grouping"].ToString() + "'";
int filterCount = 0;
foreach (DataRow drDetail in dsGroupDetail.Tables[0].Select(strFilter))
{
filterCount++;
}
int cc = clearRows[aryCurrentRow - 1];
clearRows[aryCurrentRow++] = cc + filterCount + 3;
}
#endregion
#region clear gridlines
//strTrace = string.Format("/tGridlines start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
sheetRow = 0;
// Clear gridlines between data group, pages
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
for (int clearCount = 0; clearCount <= clearRows.GetUpperBound(0); clearCount++)
{
if (clearCount == 0)
{
sheetRow += clearRows[0];
}
else
{
sheetRow += clearRows[clearCount] - clearRows[clearCount - 1];
}
if (clearCount == 0)
{
strWork += "A" + (sheetRow - 3).ToString() + ":I" + sheetRow.ToString() + ",";
}
else
{
Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
}
}
}
// Finish the range List
Report.EndRangeList(ref strWork, ref rangeList);
foreach (string strRange in rangeList)
{
oR = oSheet.get_Range(strRange, Missing.Value);
oR.ClearFormats();
}
rangeList.Clear();
strWork = "";
//strTrace = string.Format("/tGridline stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region pageBreaks
// Add page breaks
//strTrace = string.Format("/tPgBreak start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
sheetRow = clearRows[clearRows.GetUpperBound(0)];
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
oR = oSheet.get_Range("A" + sheetRow.ToString(), Missing.Value);
oR.PageBreak = (int)XlPageBreak.xlPageBreakManual;
sheetRow += clearRows[clearRows.GetUpperBound(0)];
}
//strTrace = string.Format("/tPgBreak stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region pageHeader
// Format page header for each page in report
//strTrace = string.Format("/tPgHeader start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
sheetRow = 1;
idx = 1;
foreach (DataRow drPgHdr in dsPage.Tables[0].Rows)
{
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
//Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', nSize - 2);
Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', 7);
sheetRow += clearRows[clearRows.GetUpperBound(0)];
}
// Finish the range List
Report.EndRangeList(ref strWork, ref rangeList);
foreach (string strRange in rangeList)
{
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
}
rangeList.Clear();
sheetRow = ++idx; // point to the next pageheader line
strWork = "";
}
//strTrace = string.Format("/tPgHeader stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region pageImage
//strTrace = string.Format("/tImage start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
//string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
if (strPath != ".\\")
{
Image img = Image.FromFile(strPath);
System.Windows.Forms.Clipboard.SetDataObject(img, true);
sheetRow = 1;
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
oR = oSheet.get_Range("A" + sheetRow.ToString(), Missing.Value);
oSheet.Paste(oR, img);
sheetRow += (clearRows[clearRows.GetUpperBound(0)]);
}
}
//strTrace = string.Format("/tImage stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region groupHeaders
// Format group headers for each page in report, programmed for single line group header with multiple group headers per page
//strTrace = string.Format("/tGroupHeader start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
aryCurrentRow = 0;
sheetRow = clearRows[0] + 1;
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
aryCurrentRow = 0;
foreach (DataRow drGroupName in dsGroupNames.Tables[0].Rows)
{
Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
aryCurrentRow++;
sheetRow += clearRows[aryCurrentRow] - clearRows[aryCurrentRow - 1];
}
sheetRow += clearRows[0];
}
// Finish the range List
Report.EndRangeList(ref strWork, ref rangeList);
foreach (string strRange in rangeList)
{
DataRow drGroupHdr = dsPage.Tables[0].Rows[0];
Report.FormatHeaders(oR, drGroupHdr, oSheet, "gh", strRange);
}
rangeList.Clear();
strWork = "";
//strTrace = string.Format("/tGroupHeader stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
#region columnHeaders
// Build the range list for the column headers for each page in report
//strTrace = string.Format("/tColHeader start {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
aryCurrentRow = 0;
sheetRow = clearRows[0] + 2;
for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
{
aryCurrentRow = 0;
foreach (DataRow dr1 in dsGroupNames.Tables[0].Rows) // column header is repeated for each group
{
Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
aryCurrentRow++;
sheetRow += clearRows[aryCurrentRow] - clearRows[aryCurrentRow - 1];
}
sheetRow += clearRows[0];
}
// Finish the range List
Report.EndRangeList(ref strWork, ref rangeList);
DataRow drCol = dsPage.Tables[0].Rows[0]; // only one row
foreach (string strRange in rangeList)
{
Report.FormatHeaders(oR, drCol, oSheet, "ch", strRange);
}
//strTrace = string.Format("/tColHeader stop {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
#endregion
//strTrace = string.Format("stop LOOP {0}", DateTime.Now.TimeOfDay);
//System.Diagnostics.Trace.WriteLine(strTrace);
Report.XL.Visible = true;
Report.XL.UserControl = true;
} // end createsectionreport
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 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;
}