/// <summary> /// build a xref of the columns in the log file /// </summary> /// <param name="dataWorksheet">The data worksheet.</param> /// <returns>Dictionary<System.String, System.Int32>.</returns> private static Dictionary <string, int> BuildColumnNameXref(SpreadsheetGear.IWorksheet dataWorksheet) { Dictionary <string, int> colNameXref = new Dictionary <string, int>(); IRange usedRange = dataWorksheet.UsedRange; IRange usedColumns = usedRange.Columns; int columnCount = usedColumns.ColumnCount; string columnName = string.Empty; for (int colIndex = 0; colIndex <= columnCount - 1; colIndex++) { try { columnName = dataWorksheet.Cells[0, colIndex].Text; if (!string.IsNullOrEmpty(columnName)) { colNameXref.Add(dataWorksheet.Cells[0, colIndex].Text, colIndex); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } } return(colNameXref); }
public PreparedImage(Workbook workbook, string worksheetName) { this.workbook = workbook; spreadworksheet = InitializationSpreedsheetGearWorksheet(worksheetName); cells = spreadworksheet.Cells; ApplyDeaultTitle(worksheetName); }
private void CreaHoja(string nombreHoja) { worksheet = workbook.Worksheets.Add(); cells = worksheet.Cells; worksheet.Name = nombreHoja; rowIndex = 1; counterPage++; }
/// <summary> /// We need a objective way to compare the performance between two runs using different tuning constants /// This approach calculates the sum of the "error area" between the target and the actual /// The area is calculated as the difference * step time /// We keep track of the positive (target > actual) and negative (target < actual) error separately /// Generally these values are displayed in the graph title. /// </summary> /// <param name="dataWorksheet"></param> /// <param name="elapsedDeltaColumnIdx"></param> /// <param name="targetColumnIdx"></param> /// <param name="actualColumnIdx"></param> /// <param name="graphName"></param> /// <returns></returns> private static (decimal posErr, decimal negErr) CalcAreaDelta(SpreadsheetGear.IWorksheet dataWorksheet, int elapsedDeltaColumnIdx, int targetColumnIdx, int actualColumnIdx, string graphName) { decimal totalPositiveAreaDelta = 0; decimal totalNegativeAreaDelta = 0; decimal thisLoopAreaDelta = -0; int maxRows = dataWorksheet.UsedRange.RowCount; decimal lastLoopElapsedTimeInMS = 0; decimal thisLoopElapsedTimeInMS = 0; decimal targetValue = 0; decimal actualValue = 0; int newColumnIdx = dataWorksheet.UsedRange.ColumnCount; dataWorksheet.Cells[0, newColumnIdx].Value = $"{graphName} Error Area"; for (int rowIndex = 1; rowIndex < maxRows; rowIndex++) { thisLoopElapsedTimeInMS = decimal.Parse(dataWorksheet.Cells[rowIndex, elapsedDeltaColumnIdx].Text); targetValue = decimal.Parse(dataWorksheet.Cells[rowIndex, targetColumnIdx].Text); actualValue = decimal.Parse(dataWorksheet.Cells[rowIndex, actualColumnIdx].Text); if (targetValue == 0) { continue; } thisLoopAreaDelta = Math.Round(((targetValue - actualValue) * thisLoopElapsedTimeInMS), 2); if (targetValue > actualValue) { totalPositiveAreaDelta += thisLoopAreaDelta; } else { totalNegativeAreaDelta += thisLoopAreaDelta; } dataWorksheet.Cells[rowIndex, newColumnIdx].Value = $"{totalPositiveAreaDelta} | {totalNegativeAreaDelta}"; // snapshot for next loop lastLoopElapsedTimeInMS = thisLoopElapsedTimeInMS; } // round result return(totalPositiveAreaDelta, totalNegativeAreaDelta); }
private void CorteHoja(string nombreHoja) { rowIndex++; if (rowIndex >= configExcel.CorteHoja) { worksheet = workbook.Worksheets.Add(); cells = worksheet.Cells; worksheet.Name = nombreHoja + headerColumns[counterPage]; rowIndex = 1; counterPage++; } else { rowIndex++; } }
public void PrintWareHouses(List <WareHousesDTO> wareHouseList, StoreNamesDTO storeName) { storeNameDTO = storeName; SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook(); SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0]; SpreadsheetGear.IRange cells = worksheet.Cells; Dictionary <string, byte> HeaderColumn = new Dictionary <string, byte>(); cellList = wareHouseList; int line = storeNameDTO.LineCount ?? 0; int column = storeNameDTO.ColumnCount ?? 0; int cell = storeNameDTO.CellCount ?? 0; int k = 0; int startPosition = 1; int currentPosition = 3; byte startHeaderPosition = 1; # region Header
/// <summary> /// In some scenarios (Telop PID Testing) we may enable in %VBUS mode and some time later transition to VELOCITY mode. /// The PID constants column may not be populated or valid until we gp into Velocity mode, so... /// scan down the MODE column until it is Velocity then format & grab the PID values from that row. /// </summary> /// <param name="dataWorksheet"></param> /// <param name="pidGainsColumnIdx"></param> /// <param name="controlModeColumnIdx"></param> /// <returns></returns> private static string GetPIDGains(SpreadsheetGear.IWorksheet dataWorksheet, int pidGainsColumnIdx, int controlModeColumnIdx) { int maxRows = dataWorksheet.UsedRange.RowCount; string controlMode = string.Empty; // scan down the control mode column looking for the 1st row that is "Velocity", grab the PID gains value from that row for (int rowIndex = 1; rowIndex < maxRows; rowIndex++) { controlMode = dataWorksheet.Cells[rowIndex, controlModeColumnIdx].Text; switch (controlMode.ToLower()) { case "velocity": return(dataWorksheet.Cells[rowIndex, pidGainsColumnIdx].Text); default: return(@"N/A Open Loop"); } } return(string.Empty);; }
/// <summary> /// Perform Fill Percent Summary calculation /// </summary> /// <param name="subgroupText"></param> public void FillPercentSummaryLog(string subgroupText) { DataView dvMain = null; // dvMain.RowFilter = string.Empty; try { //Apply Language Setting ApplyLanguageSettings(); //Raise event for wait cursor this.RaiseEventSetCursor(false); int i; int j; int k = 0; string sFilter = string.Empty; int CtrFillFrom = 0; int CtrFillTo = 0; int iSelCount = Step1SelectionDataTable.Rows.Count; int iRecordCount = 0; string TempStringForAddress = string.Empty; String[,] msArray = new String[1, 9]; // Log sheet array // Indicator and Subgroup val NIds will be used for SSel_NId //string[] sSel_NId; string Sel_Indicator_NId = string.Empty; string Sel_Subgroup_NId = string.Empty; ArrayList sSel_Indicator_NId = new ArrayList(); ArrayList sSel_Subgroup_Val_NId = new ArrayList(); //Variable for fill data value and formula string sChageValue = string.Empty; string sAddress = string.Empty; string sNum = string.Empty; string sDen = string.Empty; string sMinRange = string.Empty; string sMaxRange = string.Empty; SpreadsheetGear.IRange oRngStart; SpreadsheetGear.IRange oRngTotal; int iDataCol = 0; bool SupressArea = false; //Dim NumTotal, DenTotal As Single double NumTotal; double DenTotal; int FirstNumIndex; // Raise Event for initialize Progress Bar RaiseEventSetProgress(1); // Copy log file to temp location if (File.Exists(LogFilePath)) { File.Copy(LogFilePath, TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } // Getting workbook using BAL DIExcel this.DIExcel = new DIExcel(TempLogFilePath, System.Globalization.CultureInfo.CurrentCulture); // Getting worksheets 1.Summery sheet 2. LogSheet SummarySheet = DIExcel.GetWorksheet(0); LogSheet = DIExcel.GetWorksheet(1); // Setting Lang based Name of worksheets SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; RaiseEventSetProgress(10); // Filling General info of summery sheet SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_Percent (" + TimeStamp + ").xls"; SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES; SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue; SummarySheet.Cells[7, 2].Value = LangStrings.NUMERATOR; SummarySheet.Cells[7, 2 + Step1SelectionDataTable.Rows.Count + 1].Value = LangStrings.DENOMINATOR; SummarySheet.Cells[7, 2 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count + 1].Value = LangStrings.TARGET; SummarySheet.Cells[10, 0].Value = LangStrings.AREAID; SummarySheet.Cells[10, 1].Value = LangStrings.AREANAME; // Raise Event to notify progress RaiseEventSetProgress(25); //*** Get Distinct Selected IndicatorNId & Subgroup_Val_NId From Step1 and Step2 and fill in summary sheet //Loop for all Indicator row selected in step1 for (i = 0; i <= Step1SelectionDataTable.Rows.Count - 1; i++) { // Set selected indicator and subgroup val name in excel c10 and c11 and so on //If second indicator and sg are selected at step 1 its values goes in d10 and d11 SummarySheet.Cells[9, 2 + i].Value = Step1SelectionDataTable.Rows[i][Indicator.IndicatorName].ToString(); // indicator text SummarySheet.Cells[10, 2 + i].Value = Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupVal].ToString(); // subgroupVal text sSel_Indicator_NId.Add(Step1SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString()); sSel_Subgroup_Val_NId.Add(Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString()); } // Set total caption SummarySheet.Cells[10, 2 + i].Value = LangStrings.TOTAL; //-------------------------------------------------------------------- //Working with step2 selection (Selected Denominator).Entering them in excel sheet for (i = 0; i <= Step2SelectionDataTable.Rows.Count - 1; i++) { SummarySheet.Cells[9, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = Step2SelectionDataTable.Rows[i][Indicator.IndicatorName].ToString(); // indicator text SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupVal].ToString(); // update distinct indicator NId if (sSel_Indicator_NId.Contains(Step2SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString()) == false) { sSel_Indicator_NId.Add(Step2SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString()); } // update distinct indicator NId and subgroup NIDs if (sSel_Subgroup_Val_NId.Contains(Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString()) == false) { sSel_Subgroup_Val_NId.Add(Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString()); } } SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = LangStrings.TOTAL; i += 1; SummarySheet.Cells[9, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = DataEntrySheetInfo.Indicator; SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = DESheetInformation.Unit;// DESUnitText; //*** Get the data view with all filters applied dvMain = this.PresentationData; //*** Filter records for selected Indicator / Subgroup in calculates step1 if (dvMain.RowFilter == "") { dvMain.RowFilter = Indicator.IndicatorNId + " IN (" + string.Join(",", (string[])sSel_Indicator_NId.ToArray(typeof(string))) + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + string.Join(",", (string[])sSel_Subgroup_Val_NId.ToArray(typeof(string))) + ")"; } else { dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " IN (" + String.Join(",", (string[])sSel_Indicator_NId.ToArray(typeof(string))) + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + string.Join(",", (string[])sSel_Subgroup_Val_NId.ToArray(typeof(string))) + ")"; } sFilter = dvMain.RowFilter; //Fill Unique Area dvMain.Sort = Area.AreaID + " ASC, " + Timeperiods.TimePeriod + " DESC"; CtrFillFrom = 12;//13; for (i = 0; i <= dvMain.Count - 1; i++) { if (dvMain[i][Area.AreaID].ToString() != SummarySheet.Cells[iRecordCount + CtrFillFrom - 1, 0].Value + "") { SummarySheet.Cells[iRecordCount + CtrFillFrom, 0].Value = dvMain[i][Area.AreaID].ToString(); SummarySheet.Cells[iRecordCount + CtrFillFrom, 1].Value = dvMain[i][Area.AreaName].ToString(); iRecordCount += 1; } } // Getting Last Fill Column. CtrFillTo = CtrFillFrom + iRecordCount - 1; //Raise Event to notify Progress RaiseEventSetProgress(35); //*** Set Data Array for Log Sheet simultaneously msDataArray = new String[iRecordCount, 6]; msArray = new String[(iRecordCount * (Step1SelectionDataTable.Rows.Count + Step2SelectionDataTable.Rows.Count + 2)) + 2, 9]; msArray[0, 0] = LangStrings.AREAID; msArray[0, 1] = LangStrings.AREANAME; msArray[0, 2] = LangStrings.ELEMENT; msArray[0, 3] = LangStrings.INDICATOR; msArray[0, 4] = LangStrings.SUBGROUP; msArray[0, 5] = LangStrings.TIME; msArray[0, 6] = LangStrings.DATAVALUE; msArray[0, 7] = LangStrings.UNIT; msArray[0, 8] = LangStrings.SOURCE; k += 2; //Fill Data Values and Formula for each records for (i = CtrFillFrom; i <= CtrFillTo; i++) { //*** Fill Datavalue for Numerator Indicator against each AreaID NumTotal = 0; DenTotal = 0; FirstNumIndex = 0; for (j = 0; j <= Step1SelectionDataTable.Rows.Count - 1; j++) { iDataCol = 3 + j; dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'"; // Getting Indicator and subgroup Val NId Sel_Indicator_NId = Step1SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString(); Sel_Subgroup_NId = Step1SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString(); dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + Sel_Indicator_NId + " AND " + SubgroupVals.SubgroupValNId + " =" + Sel_Subgroup_NId; if (dvMain.Count > 0) { if (FirstNumIndex == 0) { FirstNumIndex = k; } try { SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString(); NumTotal += Convert.ToDouble(dvMain[0][Data.DataValue].ToString()); } catch (Exception ex) { } if (SupressArea == false) { msArray[k, 0] = dvMain[0][Area.AreaID].ToString(); msArray[k, 1] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - 12, 0] = dvMain[0][Timeperiods.TimePeriod].ToString(); msDataArray[i - 12, 1] = dvMain[0][Area.AreaID].ToString(); msDataArray[i - 12, 2] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - 12, 3] = dvMain[0][Data.DataValue].ToString(); //msDataArray[i - 13, 4] = cbo1_3_Subgroup.Text; msDataArray[i - 12, 4] = subgroupText; msDataArray[i - 12, 5] = dvMain[0][IndicatorClassifications.ICName].ToString(); SupressArea = true; } msArray[k, 2] = LangStrings.NUMERATOR; msArray[k, 3] = dvMain[0][Indicator.IndicatorName].ToString(); msArray[k, 4] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); msArray[k, 5] = dvMain[0][Timeperiods.TimePeriod].ToString(); msArray[k, 6] = dvMain[0][Data.DataValue].ToString(); msArray[k, 7] = dvMain[0][Unit.UnitName].ToString(); msArray[k, 8] = dvMain[0][IndicatorClassifications.ICName].ToString(); k += 1; } } //*** Fill Datavalue for Denominator Indicator against each AreaID for (j = 0; j <= Step2SelectionDataTable.Rows.Count - 1; j++) { iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + j; dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'"; //sSel_NId = lv1_2_Selected.Items(j).Tag.ToString.Split("_"); Sel_Indicator_NId = Step2SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString(); Sel_Subgroup_NId = Step2SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString(); dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + Sel_Indicator_NId + " AND " + SubgroupVals.SubgroupValNId + " =" + Sel_Subgroup_NId; if (dvMain.Count > 0) { SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString(); try { DenTotal += Convert.ToDouble(dvMain[0][Data.DataValue].ToString()); } catch (Exception ex) { } if (SupressArea == false) { msArray[k, 0] = dvMain[0][Area.AreaID].ToString(); msArray[k, 1] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - 12, 0] = dvMain[0][Timeperiods.TimePeriod].ToString(); msDataArray[i - 12, 1] = dvMain[0][Area.AreaID].ToString(); msDataArray[i - 12, 2] = dvMain[0][Area.AreaName].ToString(); //msDataArray[i - 13, 4] = cbo1_3_Subgroup.Text; msDataArray[i - 12, 3] = subgroupText; msDataArray[i - 12, 4] = dvMain[0][IndicatorClassifications.ICName].ToString(); SupressArea = true; } msArray[k, 2] = LangStrings.DENOMINATOR; //*** Bugfix Feb 06 Hardcoded Numerator instead of Denominator msArray[k, 3] = dvMain[0][Indicator.IndicatorName].ToString(); msArray[k, 4] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); msArray[k, 5] = dvMain[0][Timeperiods.TimePeriod].ToString(); msArray[k, 6] = dvMain[0][Data.DataValue].ToString(); msArray[k, 7] = dvMain[0][Unit.UnitName].ToString(); msArray[k, 8] = dvMain[0][IndicatorClassifications.ICName].ToString(); k += 1; } } if (FirstNumIndex > 0) { msArray[k, 2] = LangStrings.PERCENT; msArray[k, 3] = msArray[FirstNumIndex, 3]; msArray[k, 4] = msArray[FirstNumIndex, 4]; msArray[k, 5] = msArray[FirstNumIndex, 5]; if (DenTotal != 0) msArray[k, 6] = Convert.ToString(Math.Round((100 * NumTotal / DenTotal), (int)DecimalUpDownControlValue)); msArray[k, 7] = LangStrings.PERCENT; msArray[k, 8] = msArray[FirstNumIndex, 8]; } k += 2; SupressArea = false; } //*** Set the formula for Numerator Total sChageValue = ""; for (j = 0; j <= Step1SelectionDataTable.Rows.Count - 1; j++) { iDataCol = 3 + j; if (Strings.Trim(sChageValue) == "") { sChageValue = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", ""); } else { TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", ""); sChageValue = sChageValue + " + " + TempStringForAddress; } } sChageValue = "IF(" + sChageValue + "=\"\",\"\"," + sChageValue + ")"; sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address; SummarySheet.Range[sAddress].Formula = "= " + sChageValue; TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempStringForAddress]; string TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol].Address.Replace("$", ""); if (TempStringForAddress != TempStringForAddress2) { sMaxRange = TempStringForAddress + ":" + TempStringForAddress2; oRngTotal = SummarySheet.Range[sMaxRange]; AutoFillRange(oRngStart, oRngTotal); } //*** Set the formula for Denominator Total sChageValue = ""; for (j = 0; j <= Step2SelectionDataTable.Rows.Count - 1; j++) { iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + j; if (Strings.Trim(sChageValue) == "") { sChageValue = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", ""); } else { TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", ""); sChageValue = sChageValue + " + " + TempStringForAddress; } } sChageValue = "IF(" + sChageValue + "=\"\",\"\"," + sChageValue + ")"; sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address; SummarySheet.Range[sAddress].Formula = "= " + sChageValue; TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address.Replace("$", ""); TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempStringForAddress]; if (TempStringForAddress != TempStringForAddress2) { sMaxRange = TempStringForAddress + ":" + TempStringForAddress2; oRngTotal = SummarySheet.Range[sMaxRange]; //oRngStart.AutoFill(Destination = oRngTotal); AutoFillRange(oRngStart, oRngTotal); } //*********************** Set the formula for Percent ********************************** sChageValue = ""; iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count + 1; sNum = SummarySheet.Cells[CtrFillFrom, 2 + Step1SelectionDataTable.Rows.Count].Address.Replace("$", ""); sDen = SummarySheet.Cells[CtrFillFrom, 2 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count].Address.Replace("$", ""); sChageValue = "IF(OR(" + sNum + "=\"\" ," + sDen + "=\"\"),\"\",ROUND((" + sNum + " * 100 / " + sDen + ")," + SummarySheet.Cells[6, 1].Address + "))"; sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address; // Set Formula SummarySheet.Range[sAddress].Formula = "= " + sChageValue; //Apply formula to range TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", ""); TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol - 1].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempStringForAddress]; if (TempStringForAddress != TempStringForAddress2) { sMaxRange = TempStringForAddress + ":" + TempStringForAddress2; oRngTotal = SummarySheet.Range[sMaxRange]; this.AutoFillRange(oRngStart, oRngTotal); } //*** Setting the Font try { SummarySheet.Range[0, 0, CtrFillTo, iDataCol - 1].Font.Name = FontName; SummarySheet.Range[0, 0, CtrFillTo, iDataCol - 1].Font.Size = (float)FontSize; SummarySheet.Range[7, 0, CtrFillTo, iDataCol - 1].Columns.AutoFit(); SummarySheet.Range[CtrFillFrom, 2, CtrFillTo, iDataCol - 1].HorizontalAlignment = HAlign.Right; //xlRight SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = TitleFontSize; SummarySheet.Range[7, 0, 7, iDataCol - 1].Font.Bold = true; } catch (Exception ex) { } //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_Percent (" + TimeStamp + ").xls"; CtrFillFrom = 8;// 9 in Excel; //*** Set Data LogSheet.Range[CtrFillFrom, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Value = msArray; //*** Autofit LogSheet.Range[CtrFillFrom - 2, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Columns.AutoFit(); LogSheet.Range[CtrFillFrom, 5, CtrFillFrom + msArray.GetLength(0) - 1, 5].HorizontalAlignment = HAlign.Left; //-4131; //xlLeft 'Time column //*** Set Font LogSheet.Range[0, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Font.Name = FontName; LogSheet.Range[0, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Font.Size = (float)FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = TitleFontSize; //****************************************************************************************** // Activate forst sheet and save workbook DIExcel.GetWorksheet(0).Cells[0, 0].Activate(); DIExcel.Save(); this.RaiseEventSetProgress(100); //*** hide progress bar this.RaiseEventHideProgressBar(); // Dispose worksheets SummarySheet = null; LogSheet = null; dvMain.RowFilter = string.Empty; } catch (Exception ex) { } finally { this.RaiseEventSetCursor(true); } // Raise event to show workbook Show workbook inside panel this.RaiseEventCalculateStepCompleted("pnl1_4"); }
/// <summary> /// Perform Composite Index Calculations /// </summary> /// <param name="compositeIndexStep2DataTable"></param> /// <param name="scaleMinDecimalValue">nud3_3_Min.Value</param> /// <param name="scaleMaxDecimalValue">nud3_3_Max.Value;</param> /// <param name="showSubgroup"></param> /// <param name="step3SubgroupText"></param> public void FillCompositeIndexSummaryLog(DataTable compositeIndexStep2DataTable, decimal scaleMinDecimalValue, decimal scaleMaxDecimalValue, bool showSubgroup, String step3SubgroupText) { // Info required Step1DataTable,scaleMinDecimalValue,scaleMaxDecimalValue // ScaleMinValue, //CompositeIndexStep2DataTable having columns Indicator ,Percent weight, HighIsGood // showSubgroup //cbo3_3_Subgroup.Text as step3SubgroupText int i = 0; int j = 0; int k = 0; string sFilter = string.Empty; int iCtrFillFrom = 0; int iCtrFillTo = 0; int iSelCount = Step1SelectionDataTable.Rows.Count; int iRecordCount = 0; string[] sSel_NId = new string[2]; string sSel_Indicator_NId = ""; string sSel_Subgroup_Val_NId = ""; string TempFromAddressString = string.Empty; string TempToAddressString = string.Empty; string[,] msArray = new string[1, 8]; try { //Set cursor to wait cursor this.RaiseEventSetCursor(false); // Apply Language Settings ApplyLanguageSettings(); //*** Set Progress bar this.RaiseEventSetProgress(1); // Get temp log file if (File.Exists(this.LogFilePath)) { File.Copy(this.LogFilePath, this.TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } //open file and get worksheets this.DIExcel = new DIExcel(TempLogFilePath); SummarySheet = DIExcel.GetWorksheet(0); LogSheet = DIExcel.GetWorksheet(1); SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; //*************************** SHOW INDEX ****************************************** // Raise Event to notify progess this.RaiseEventSetProgress(10); //*** enter lang based info in summery sheet SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_Composite Index (" + TimeStamp + ").xls"; SummarySheet.Cells[6, 3].Value = LangStrings.DECIMALPLACES; SummarySheet.Cells[6, 4].Value = this.DecimalUpDownControlValue; SummarySheet.Cells[6, 0].Value = LangStrings.SCALE_MIN; SummarySheet.Cells[6, 1].Value = scaleMinDecimalValue; SummarySheet.Cells[7, 0].Value = LangStrings.SCALE_MAX; SummarySheet.Cells[7, 1].Value = scaleMaxDecimalValue; SummarySheet.Cells[9, 0].Value = LangStrings.PERCENT_WEIGHT; SummarySheet.Cells[10, 0].Value = LangStrings.HIGHISGOOD; for (i = 2; i <= iSelCount * 2; i += 2) { SummarySheet.Cells[9, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][1].ToString();// dg3_2_Weight[i / 2 - 1, 1).ToString(); SummarySheet.Cells[10, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][2].ToString(); SummarySheet.Cells[10, i - 1].HorizontalAlignment = HAlign.Right; //xlRight SummarySheet.Cells[13, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][0].ToString(); SummarySheet.Cells[13, i].Value = LangStrings.INDEX; } SummarySheet.Cells[13, 0].Value = Area.AreaName; SummarySheet.Cells[13, i - 1].Value = "Score"; SummarySheet.Cells[13, i].Value = "Composite Index"; SummarySheet.Cells[13, i + 1].Value = Area.AreaID; this.RaiseEventSetProgress(25); //*** Get Selected Indicator/I_S NId for (i = 0; i <= iSelCount - 1; i++) { sSel_NId[0] = Step1SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString(); sSel_NId[1] = Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString(); if (i == 0) { if (showSubgroup) { sSel_Indicator_NId = sSel_NId[0]; sSel_Subgroup_Val_NId = sSel_NId[1]; } else { sSel_Indicator_NId = sSel_NId[0]; } } else { if (showSubgroup) { sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0]; sSel_Subgroup_Val_NId = sSel_Subgroup_Val_NId + "," + sSel_NId[1]; } else { sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0]; } } } //*** Get the data view with all filters applied DataView dvMain = this.PresentationData; //*** Filter records for selected Indicator / Subgroup in calculates step1 if (showSubgroup) { sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + sSel_Subgroup_Val_NId + ")"; } else { sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ")"; } if (dvMain.RowFilter == "") { dvMain.RowFilter = sFilter; } else { dvMain.RowFilter += " AND " + sFilter; } sFilter = dvMain.RowFilter; //Fill Unique Area dvMain.Sort = " Area_ID ASC"; iCtrFillFrom = 16; for (i = 0; i <= dvMain.Count - 1; i++) { if (dvMain[i][Area.AreaID].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, iSelCount * 2 + 3].Value + "") { SummarySheet.Cells[iRecordCount + iCtrFillFrom, 0].Value = dvMain[i][Area.AreaName]; SummarySheet.Cells[iRecordCount + iCtrFillFrom, iSelCount * 2 + 3].Value = dvMain[i][Area.AreaID]; iRecordCount += 1; } } iCtrFillTo = iCtrFillFrom + iRecordCount - 1; //Update progress this.RaiseEventSetProgress(35); //*** Set Data Array for Log Sheet simultaneously msDataArray = new string[iRecordCount, 6]; msArray = new string[(iRecordCount * iSelCount) + iRecordCount + 3, 8]; msArray[0, 0] = LangStrings.AREAID; msArray[0, 1] = LangStrings.AREANAME; msArray[0, 2] = LangStrings.INDICATOR; msArray[0, 3] = LangStrings.SUBGROUP; msArray[0, 4] = LangStrings.TIME; msArray[0, 5] = LangStrings.DATAVALUE; msArray[0, 6] = LangStrings.UNIT; msArray[0, 7] = LangStrings.SOURCE; k += 2; //Fill Data Values and Formula for each records string sStaticValue = string.Empty; string sScoreStaticValue = ""; string sHiddenDivision = ""; string sChageValue = string.Empty; string sAddess = string.Empty; string sMinRange = string.Empty; string sMaxRange = string.Empty; IRange oRngStart; IRange oRngTotal; int iDataCol = 0; bool bSupressArea = false; //*** varibales for setting Avg Data Value for missing Data int[] arrDataCount = new int[iSelCount]; //float[] arrDataSum = new float[iSelCount]; double[] arrDataSum = new double[iSelCount]; string[] arrCellId = new string[iSelCount]; for (i = iCtrFillFrom; i <= iCtrFillTo; i++) { //*** Fill Datavalue for all selected indecator against each AreaID for (j = 0; j <= iSelCount - 1; j++) { iDataCol = (j + 1) * 2; dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, iSelCount * 2 + 3].Value.ToString())) + "'"; //sSel_NId = lv3_1_Selected.Items(j).Tag.ToString.Split("_"); sSel_NId[0] = Step1SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString(); sSel_NId[1] = Step1SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString(); if (showSubgroup) { dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + sSel_NId[0] + " AND " + SubgroupVals.SubgroupValNId + " =" + sSel_NId[1]; } else { dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + sSel_NId[0]; } if (dvMain.Count == 0) { SummarySheet.Cells[i, iDataCol - 1].Value = 0; SummarySheet.Cells[i, iDataCol - 1].NumberFormat = "0.00"; SummarySheet.Cells[i, iDataCol - 1].Font.Bold = true; SummarySheet.Cells[i, iDataCol - 1].Font.ColorIndex = 15; arrCellId[j] += i + "," +Convert.ToInt32(iDataCol-1) + ";"; } else { SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString(); if (bSupressArea == false) { msArray[k, 0] = dvMain[0][Area.AreaID].ToString(); msArray[k, 1] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - iCtrFillFrom, 0] = dvMain[0][Timeperiods.TimePeriod].ToString(); msDataArray[i - iCtrFillFrom, 1] = dvMain[0][Area.AreaID].ToString(); msDataArray[i - iCtrFillFrom, 2] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - iCtrFillFrom, 4] = step3SubgroupText; //dvMain[0]["Subgroup_Val") msDataArray[i - iCtrFillFrom, 5] = dvMain[0][IndicatorClassifications.ICName].ToString(); bSupressArea = true; } msArray[k, 2] = dvMain[0][Indicator.IndicatorName].ToString(); msArray[k, 3] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); msArray[k, 4] = dvMain[0][Timeperiods.TimePeriod].ToString(); msArray[k, 5] = dvMain[0][Data.DataValue].ToString(); msArray[k, 6] = dvMain[0][Unit.UnitName].ToString(); msArray[k, 7] = dvMain[0][IndicatorClassifications.ICName].ToString(); arrDataCount[j] += 1; arrDataSum[j] += Convert.ToDouble(dvMain[0][Data.DataValue]); k += 1; } if (i == iCtrFillFrom) { //*** Build and Set Formula For Index Columns sStaticValue = SummarySheet.Cells[10, iDataCol - 1].Address; //*** Extract the Alphabet part from cell address. Accomodate condition that cell address may be like A1 or AA1 TempFromAddressString = SummarySheet.Cells[0, iDataCol - 1].Address.Replace("$", ""); sChageValue = TempFromAddressString;// Strings.Replace(SummarySheet.Cells[1, iDataCol].Address, "$", ""); sChageValue = (Strings.Len(sChageValue) == 3 ? Strings.Mid(sChageValue, 1, 2) : Strings.Mid(sChageValue, 1, 1)); sChageValue = sChageValue + Convert.ToString(iCtrFillFrom + 1); sMinRange = "Min(" + SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address + ")"; sMaxRange = "Max(" + SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address + ")"; sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol].Address; SummarySheet.Range[sAddess].Formula = "= IF((" + sMaxRange + " - " + sMinRange + ") = 0" + "," + 1 + "," + "IF(" + sStaticValue + "," + "(" + sChageValue + " - " + sMinRange + ")" + " / " + "(" + sMaxRange + " - " + sMinRange + ")," + " " + "(" + sChageValue + " - " + sMaxRange + ")" + " / " + "(" + sMinRange + " - " + sMaxRange + ")))"; TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempFromAddressString]; TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol].Address.Replace("$", ""); if (TempFromAddressString != TempToAddressString) { sMaxRange = TempFromAddressString + ":" + TempToAddressString; oRngTotal = SummarySheet.Range[sMaxRange]; this.AutoFillRange(oRngStart, oRngTotal); } //*** Build Formula for Score Column if (Strings.Trim(sScoreStaticValue) == "") { //TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address.Replace("$", ""); sScoreStaticValue = TempFromAddressString + "*" + SummarySheet.Cells[9, iDataCol - 1].Address; sHiddenDivision = SummarySheet.Cells[9, iDataCol - 1].Address; } else { sScoreStaticValue = sScoreStaticValue + " + " + TempFromAddressString + "*" + SummarySheet.Cells[9, iDataCol - 1].Address; sHiddenDivision = sHiddenDivision + " + " + SummarySheet.Cells[9, iDataCol - 1].Address; } } } //*** Set Formaula for all Index Colunns against each AreaID if (i == iCtrFillFrom) { //*** Set Formula for Score Column sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address; SummarySheet.Range[sAddess].Formula = "= (" + sScoreStaticValue + ") / " + "(" + sHiddenDivision + ")"; TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address.Replace("$", ""); TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempFromAddressString]; if (TempFromAddressString != TempToAddressString) { sMaxRange = TempFromAddressString + ":" + TempToAddressString; oRngTotal = SummarySheet.Range[sMaxRange]; //oRngStart.AutoFill(Destination = oRngTotal); this.AutoFillRange(oRngStart, oRngTotal); } //*** Set Formula for Composite Index string sHiddenColumn; sHiddenColumn = (Strings.Len(Strings.Mid(sMaxRange, 1, Strings.InStr(1, sMaxRange, ":", CompareMethod.Text) - 1)) == 4 ? Strings.Mid(sMaxRange, 1, 2) : Strings.Mid(sMaxRange, 1, 1)); sMinRange = "Min(" + SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address + ")"; sMaxRange = "Max(" + SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address + ")"; sStaticValue = "IF((" + sMaxRange + " - " + sMinRange + ") = 0" + ", " + "$B$8" + "," + "$B$7" + " + " + "( (" + sHiddenColumn + 17 + " - " + sMinRange + ") / (" + sMaxRange + " - " + sMinRange + ") ) * (" + "$B$8" + " - " + "$B$7" + "))"; //*** Set formula for Decimal Precision based on Cells(7, 5) value. //*** Rounding should be cell address based so that if user makes changes inside excel it should reflect sStaticValue = "ROUND(" + sStaticValue + "," + SummarySheet.Cells[6, 4].Address + ")"; sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].Address; SummarySheet.Range[sAddess].Formula = "= " + sStaticValue; SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].NumberFormat = "General"; TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].Address.Replace("$", ""); TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol + 2].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempFromAddressString]; if (TempFromAddressString != TempToAddressString) { sMaxRange = TempFromAddressString + ":" + TempToAddressString; oRngTotal = SummarySheet.Range[sMaxRange]; this.AutoFillRange(oRngStart, oRngTotal); } } k += 1; bSupressArea = false; } //*** Set Avg data value for Missing Data string[] sCellId; string[] sRowCol; float MissingData=0.0f; for (i = 0; i <= iSelCount - 1; i++) { if ((arrCellId[i] != null)) { // Get Missing Data : DataSum/ DataCount try { MissingData = (float)arrDataSum[i] / arrDataCount[i]; } catch (Exception) { } sCellId = arrCellId[i].ToString().Split(';'); for (j = 0; j <= sCellId.Length - 1; j++) { if (sCellId[j].ToString().Trim() != "") { sRowCol = sCellId[j].Split(','); SummarySheet.Cells[Convert.ToInt32(sRowCol[0]), Convert.ToInt32(sRowCol[1])].Value = MissingData; } } } } //*** Setting the Font SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Name = this.FontName;// goUserPreference.Language.FontName; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Size = (float)this.FontSize; //goUserPreference.Language.FontSize; SummarySheet.Range[6, 0, iCtrFillTo, iDataCol + 3].Columns.AutoFit(); SummarySheet.Range[13, 1, 14, iSelCount * 2 + 3].HorizontalAlignment = HAlign.Right;// -4152; //xlRight SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize; //*** Progress //moStatusBar.progressBar.Value = 45; this.RaiseEventSetProgress(45); //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_Composite Index (" + TimeStamp + ").xls"; iCtrFillFrom = 8; //9; //*** Set Data LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Value = msArray; //*** Autofit LogSheet.Range[iCtrFillFrom - 2, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Columns.AutoFit(); //*** Set Font LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 2, 7].Font.Name = this.FontName; //goUserPreference.Language.FontName; LogSheet.Range[1, 1, iCtrFillFrom + msArray.GetLength(0) - 2, 8].Font.Size = (float)this.FontSize; //goUserPreference.Language.FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize; //*** hide progress bar this.RaiseEventSetProgress(100); DIExcel.Save(); this.RaiseEventHideProgressBar(); this.RaiseEventStep3_4Completed(); SummarySheet = null; LogSheet = null; dvMain.RowFilter = string.Empty; } catch (Exception ex) { } finally { this.RaiseEventSetCursor(true); } }
/// <summary> /// Perform 100Minus Summary calculation /// </summary> public void Fill100MinusSummaryLog() { try { // Apply Language settings ApplyLanguageSettings(); RaiseEventSetCursor(false); //Excel.Worksheet int i = 0; int iAreaCount = 0; int iCtrFillFrom; int iCtrFillTo; int iDataCol; string TempStringForEndCellAddress = string.Empty; string sAddress = string.Empty; DataView dvMain; System.Collections.Specialized.StringCollection oSubgroup = new System.Collections.Specialized.StringCollection(); //*** Initialize Progress Bar this.RaiseEventSetProgress(1); // Get calculate file to temp location if (File.Exists(this.LogFilePath)) { File.Copy(this.LogFilePath, this.TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } // Opening and getting workbook this.DIExcel = new DIExcel(this.TempLogFilePath); // Getting worksheet SummarySheet = DIExcel.GetWorksheet(0); LogSheet = DIExcel.GetWorksheet(1); SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; //*************************** SHOW INDEX ****************************************** RaiseEventSetProgress(10); //*** Get the data view with all filters applied dvMain = this.PresentationData; //dvMain.RowFilter = string.Empty; //if (string.IsNullOrEmpty(dvMain.RowFilter)) //{ dvMain.RowFilter = Indicator.IndicatorNId + " = " + Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId].ToString(); //} //else //{ // dvMain.RowFilter += Indicator.IndicatorNId + " = " + Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId].ToString(); //} dvMain.Sort = Area.AreaID + " ASC"; //DataRowView drv; //msDataArray = new string[dvMain.Count - 1, 5]; msDataArray = new string[dvMain.Count, 6]; // string[,] msSummaryArray = new string[dvMain.Count-1 + 3, 8]; int summaryArrayLengh = 1; if (dvMain.Count>0) { summaryArrayLengh = dvMain.Count + 3; } string[,] msSummaryArray = new string[summaryArrayLengh, 8]; msSummaryArray[0, 0] = LangStrings.AREAID; msSummaryArray[0, 1] = LangStrings.AREANAME; msSummaryArray[0, 2] = Step1SelectionDataTable.Rows[0][0].ToString();// lv2_1_Selected.Items(0).SubItems(0).Text; msSummaryArray[0, 3] = LangStrings.VAR_WIZARD_NAME; msSummaryArray[0, 4] = LangStrings.SUBGROUP; msSummaryArray[0, 5] = LangStrings.TIME; msSummaryArray[0, 6] = LangStrings.UNIT; msSummaryArray[0, 7] = LangStrings.SOURCE; string PrevAreaId = ""; i += 2; foreach (DataRowView drv in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID if (PrevAreaId != drv[Area.AreaID].ToString()) { iAreaCount += 1; PrevAreaId = drv[Area.AreaID].ToString(); msSummaryArray[i, 0] = drv[Area.AreaID].ToString(); msSummaryArray[i, 1] = drv[Area.AreaName].ToString(); } msSummaryArray[i, 2] = drv[Data.DataValue].ToString(); msSummaryArray[i, 3] = System.Convert.ToString(100 - System.Convert.ToDouble(drv["Data_Value"].ToString())); msSummaryArray[i, 4] = drv[SubgroupVals.SubgroupVal].ToString(); if (oSubgroup.Contains(drv[SubgroupVals.SubgroupValNId].ToString()) == false) { oSubgroup.Add(drv[SubgroupVals.SubgroupValNId].ToString()); } msSummaryArray[i, 5] = drv[Timeperiods.TimePeriod].ToString(); msSummaryArray[i, 6] = drv[Unit.UnitName].ToString(); msSummaryArray[i, 7] = drv[IndicatorClassifications.ICName].ToString(); msDataArray[i - 2, 0] = drv[Timeperiods.TimePeriod].ToString(); msDataArray[i - 2, 1] = drv[Area.AreaID].ToString(); msDataArray[i - 2, 2] = drv[Area.AreaName].ToString(); msDataArray[i - 2, 4] = drv[SubgroupVals.SubgroupVal].ToString(); msDataArray[i - 2, 5] = drv[IndicatorClassifications.ICName].ToString(); i += 1; } string[,] msLogArray = new string[dvMain.Count - 1 + iAreaCount + 3, 9]; //*** Data Array for Log Sheet msLogArray[0, 0] = LangStrings.AREAID; msLogArray[0, 1] = LangStrings.AREANAME; msLogArray[0, 2] = LangStrings.INDICATOR; msLogArray[0, 3] = LangStrings.SUBGROUP; msLogArray[0, 4] = LangStrings.TIME; msLogArray[0, 5] = LangStrings.DATAVALUE; msLogArray[0, 6] = LangStrings.UNIT; msLogArray[0, 7] = LangStrings.SOURCE; msLogArray[0, 8] = LangStrings.VAR_WIZARD_NAME; PrevAreaId = ""; i = 1; dvMain.GetEnumerator().Reset(); foreach (DataRowView dvRow in dvMain) { if (PrevAreaId != dvRow[Area.AreaID].ToString()) { i += 1; PrevAreaId = dvRow[Area.AreaID].ToString(); msLogArray[i, 0] = dvRow[Area.AreaID].ToString(); msLogArray[i, 1] = dvRow[Area.AreaName].ToString(); } msLogArray[i, 2] = dvRow[Indicator.IndicatorName].ToString(); msLogArray[i, 3] = dvRow[SubgroupVals.SubgroupVal].ToString(); msLogArray[i, 4] = dvRow[Timeperiods.TimePeriod].ToString(); msLogArray[i, 5] = dvRow[Data.DataValue].ToString(); msLogArray[i, 6] = dvRow[Unit.UnitName].ToString(); msLogArray[i, 7] = dvRow[IndicatorClassifications.ICName].ToString(); msLogArray[i, 8] = Convert.ToString(100 - Convert.ToDouble(dvRow[Data.DataValue])); i += 1; } //*** Special Handling for Subgroup in 100 Minus this.DESheetInformation.Subgroup = new string[oSubgroup.Count]; this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count]; for (i = 0; i <= oSubgroup.Count - 1; i++) { string sSql = string.Empty; // sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i]); sSql = this.DBQueries.SubgroupVals.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i]); dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView; this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); if (dvMain.Count > 0) { this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString(); } else { this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString(); } } SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_100 Minus (" + TimeStamp + ").xls"; SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES; SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue; this.RaiseEventSetProgress(25); //************************ Set Data in summary sheet******************** SummarySheet.Range[8, 0, 8 + msSummaryArray.GetLength(0) - 1, 7].Value = msSummaryArray; this.RaiseEventSetProgress(35); //Fill Data Values and Formula for each records string sChangeValue; string sMinRange; string sMaxRange; IRange oRngStart; IRange oRngTotal; //*** Set the formula for 100 Minus iCtrFillFrom = 10; iCtrFillTo = iCtrFillFrom + msSummaryArray.GetLength(0) - 2 - 2; if (iCtrFillTo<iCtrFillFrom) { iCtrFillTo = iCtrFillFrom; } // -2 for header row and a blank row iDataCol = 4; sChangeValue = ""; TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillFrom, 2].Address.Replace("$", ""); sChangeValue = "ROUND(100 - " + TempStringForEndCellAddress + "," + SummarySheet.Cells[6, 1].Address + ")"; sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address; // Set Formula SummarySheet.Range[sAddress].Formula = "= " + sChangeValue; // Apply formula for all rows sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", ""); oRngStart = SummarySheet.Range[sAddress]; TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", ""); if (sAddress != TempStringForEndCellAddress) { sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", ""); TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", ""); sMaxRange = sAddress + ":" + TempStringForEndCellAddress; oRngTotal = SummarySheet.Range[sMaxRange]; AutoFillRange(oRngStart, oRngTotal); } iDataCol = 8; //*** Setting the Font SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize; try { SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit(); SummarySheet.Range[iCtrFillFrom, 4, iCtrFillTo, 4].HorizontalAlignment = HAlign.Left; // -4131; } catch (Exception ex) { } //xlLeft 'Time column SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize; //Raise event to notify Progress this.RaiseEventSetProgress(45); //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_100 Minus (" + TimeStamp + ").xls"; iCtrFillFrom = 8; //*** Set Data LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Value = msLogArray; //NOte1 : Index change completed //*** Autofit LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Columns.AutoFit(); LogSheet.Range[iCtrFillFrom, 4, iCtrFillFrom + msLogArray.GetLength(0) - 1, 4].HorizontalAlignment = HAlign.Left;// -4131; //xlLeft 'Time column //*** Set Font LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Font.Name = this.FontName; LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Font.Size = (float)this.FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize; //****************************************************************************************** // Save Workbook DIExcel.GetWorksheet(0).Cells[0, 0].Activate(); DIExcel.Save(); this.RaiseEventSetProgress(100); //Raise event for hiding progress bar this.RaiseEventHideProgressBar(); // Dispose SummarySheet = null; LogSheet = null; dvMain.RowFilter = string.Empty; } catch (Exception ex) { } finally { RaiseEventSetCursor(true); } // RaiseEvent step completed this.RaiseEventCalculateStepCompleted("pnl2_3"); }
/// <summary> /// Get Data From Summary sheet for creation of DES /// </summary> public void GetDESheetDataFromSummarySheet(System.Globalization.CultureInfo currentCulture) { //System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread; //System.Globalization.CultureInfo originalCulture = thisThread.CurrentCulture; try { // set current thread culture to english //thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); int i = 0; int j = 0; int iDataCol = 0; //this.DIExcel = new DIExcel(TempLogFilePath); this.DIExcel = new DIExcel(TempLogFilePath, currentCulture); IWorksheet SummarySheet = DIExcel.GetWorksheet(0); //Excel.Worksheet switch (ApplicationWizardType) { case WizardType.Percent: //*** Decimal if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value)) { this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value); this.DecimalUpDownControlValue = this.DESheetInformation.Decimals; } iDataCol = 3 + Step1SelectionDataTable.Rows.Count + Step2SelectionDataTable.Rows.Count; //*** Indicator & Unit SetDESheetIUSGuid(SummarySheet.Cells[9, iDataCol + 1].Text, SummarySheet.Cells[10, iDataCol + 1].Text, ""); // raise to set step3 indicator and unit text RaiseEventSetStep3IndicatorAndUnit(DESheetInformation.Indicator, this.DESheetInformation.Unit); //*** Data Values for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { //msDataArray(i, 3) = SummarySheet.Cells[i + 12, iDataCol].value; msDataArray[i, 3] = SummarySheet.Cells[i + 12, iDataCol + 1].Text; } break; case WizardType.HundredMinus: //*** Decimal if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value)) { this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value); this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals; } //*** Data Values iDataCol = 3; for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { msDataArray[i, 3] = SummarySheet.Cells[i + 10, iDataCol].Value.ToString(); } break; case WizardType.CompositeIndex: //*** Decimal if (Information.IsNumeric(SummarySheet.Cells[6, 4].Value)) { this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 4].Value); this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals; } //*** Data Values iDataCol = 2 + (this.Step1SelectionDataTable.Rows.Count * 2) ; //*** 1 for Score Col //*** Handling Excel Sorting for Composite Index string[] ArrAreaId = new string[msDataArray.GetLength(0)]; for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { ArrAreaId[i] = msDataArray[i, 1]; } string[,] sTempDataArray = new string[msDataArray.GetLength(0), msDataArray.GetLength(1)]; int ArrIndex; for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { ArrIndex = Array.IndexOf(ArrAreaId, SummarySheet.Cells[i + 16, iDataCol+1].Value); for (j = 0; j <= msDataArray.GetLength(1) - 1; j++) { if (j == 3) { sTempDataArray[i, 3] = SummarySheet.Cells[i + 16, iDataCol].Value.ToString(); } else { sTempDataArray[i, j] = msDataArray[ArrIndex, j]; } } } msDataArray = null; msDataArray = sTempDataArray; break; case WizardType.SubTotal: // No decimal Combo //Data Values //Subtotal Step 5 iDataCol = 3; for (i = 0; i <= (msDataArray.GetLength(0) / this.DESheetInformation.SubgroupGUID.Length) - 1; i++) { for (j = 0; j <= this.DESheetInformation.SubgroupGUID.Length - 1; j++) { msDataArray[i + j, 3] = SummarySheet.Cells[i + 11, (iDataCol + j) - 1].Value.ToString(); } } break; case WizardType.TransformUnit: ////*** Decimal if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value)) { this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value); this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals; } //*** Data Values iDataCol = 5; for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { msDataArray[i, 3] = SummarySheet.Cells[i + 10, iDataCol].Value.ToString(); } break; case WizardType.UserDefinedFormula: //*** Decimal if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value)) { this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value); this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals; } //*** Data Values iDataCol = 2 + this.SelectedNids.Count; for (i = 0; i <= msDataArray.GetLength(0) - 1; i++) { msDataArray[i, 3] = SummarySheet.Cells[i + 11, iDataCol].Value.ToString(); } break; } SummarySheet = null; } catch (Exception ex) { } // Reset Original culture //thisThread.CurrentCulture = originalCulture; }
/// <summary> /// Perform Calculations for User defind formula /// </summary> /// <param name="FullFormulaText">txt6_1FullFormula text in UI application</param> /// <param name="formulaText">txt6_1Formula text</param> /// <param name="showSubgroup">mbShowsubgrop</param> /// <param name="step2SubgroupText">cbo6_2_Subgroup.Text</param> /// <param name="selNId2">arraylist miSelNId2 'Used for storing NID of Sel. Indicator </param> /// <param name="sIdentifierChar">msIdentifierChar</param> public void FillUserDefinedFormulaSummaryLog(string FullFormulaText, string formulaText, bool showSubgroup, string step2SubgroupText, ArrayList selNId2, StringCollection sIdentifierChar) { System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread; System.Globalization.CultureInfo originalCulture = thisThread.CurrentCulture; try { thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); this.RaiseEventSetCursor(false); IWorksheet SummarySheet; IWorksheet LogSheet; int i = 0; int j = 0; int k = 0; string sFilter = string.Empty; int iCtrFillFrom = 0; int iCtrFillTo = 0; int iRecordCount = 0; string[] sSel_NId; string sSel_Indicator_NId = ""; string sSel_Subgroup_Val_NId = ""; DataView dvMain; string[,] msArray = new string[1, 8]; // For holding data //Variables for set formula string sChageValue = string.Empty; string sAddress = string.Empty; string sMinRange = string.Empty; string sMaxRange = string.Empty; IRange oRngStart; IRange oRngTotal; string TempFillFromString = string.Empty; string TempFillToString = string.Empty; this.RaiseEventSetProgress(1); //*** Initialize Progress Bar // Copy Log File tempalate to temp location if (File.Exists(this.LogFilePath)) { File.Copy(this.LogFilePath, this.TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } // Apply language settings ApplyLanguageSettings(); // Opening and getting workbook this.DIExcel = new DIExcel(TempLogFilePath); SummarySheet = DIExcel.GetWorksheet(0); LogSheet = DIExcel.GetWorksheet(1); // Setting name for worksheets SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; //*************************** SHOW INDEX ****************************************** this.RaiseEventSetProgress(10); // Getting presentation data in dvMain Dataview dvMain = this.PresentationData; //*** Data Array for Log Sheet // Filling Fixed columns of summery sheet with lang based strings SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = this.TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_User Defined Formula (" + this.TimeStamp + ").xls"; SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES; SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue; //nud6_2_Decimals.Value; //"Decimal Value" SummarySheet.Cells[9, 0].Value = LangStrings.AREAID; SummarySheet.Cells[9, 1].Value = LangStrings.AREANAME; SummarySheet.Cells[9, 2 + this.SelectedNids.Count].Value = FullFormulaText; this.RaiseEventSetProgress(25); //*** Get Selected I_S NId // SelectedNid is an arraylist passed by clinet application for (i = 0; i <= this.SelectedNids.Count - 1; i++) { // Split selected Nid to get indicator ad subgroup val NId sSel_NId = this.SelectedNids[i].ToString().Split('_'); if (i == 0) { sSel_Indicator_NId = sSel_NId[0]; sSel_Subgroup_Val_NId = sSel_NId[1]; } // Get comma delimited list of selected NIds else { sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0]; sSel_Subgroup_Val_NId = sSel_Subgroup_Val_NId + "," + sSel_NId[1]; } SummarySheet.Cells[8, 2 + i].Value = this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][0].ToString(); // lv6_1_Available.Items(selNId2[i]).SubItems(0).Text; SummarySheet.Cells[9, 2 + i].Value = this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][1].ToString();// lv6_1_Available.Items(selNId2[i]).SubItems(1).Text; } //*** Filter records for selected Indicator / Subgroup in calculates step1 . using mbshowSubgroup if (showSubgroup) { sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + sSel_Subgroup_Val_NId + ")"; } else { sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ")"; } //*** Bugfix 21 Dec 2006 Data Filters not applied if (dvMain.RowFilter == "") { } else { sFilter = dvMain.RowFilter + " AND " + sFilter; } //Fill Unique Area dvMain.Sort = Area.AreaID + " ASC"; iCtrFillFrom = 11;// 12; for (i = 0; i <= dvMain.Count - 1; i++) { //if (SummarySheet.Cells[iRecordCount + iCtrFillFrom , 0].Value ==null || dvMain[i]["Area_ID"].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom-1 , 0].Value.ToString() + "") if (SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, 0].Value == null || dvMain[i]["Area_ID"].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, 0].Value.ToString() + "") { SummarySheet.Cells[iRecordCount + iCtrFillFrom, 0].Value = dvMain[i][Area.AreaID].ToString(); SummarySheet.Cells[iRecordCount + iCtrFillFrom, 1].Value = dvMain[i][Area.AreaName].ToString(); iRecordCount += 1; } } iCtrFillTo = iCtrFillFrom + iRecordCount - 1; this.RaiseEventSetProgress(35); //*** Set Data Array for Log Sheet simultaneously msDataArray = new string[iRecordCount, 6]; msArray = new string[(iRecordCount * this.SelectedNids.Count) + iRecordCount + 3, 8]; msArray[0, 0] = LangStrings.AREAID; msArray[0, 1] = LangStrings.AREANAME; msArray[0, 2] = LangStrings.INDICATOR; msArray[0, 3] = LangStrings.SUBGROUP; msArray[0, 4] = LangStrings.TIME; msArray[0, 5] = LangStrings.DATAVALUE; msArray[0, 6] = LangStrings.UNIT; msArray[0, 7] = LangStrings.SOURCE; k += 2; //*** Fill Data Values and Formula for each Area int iDataCol; bool bSupressArea = false; for (i = iCtrFillFrom; i <= iCtrFillTo; i++) { for (j = 0; j <= this.SelectedNids.Count - 1; j++) { iDataCol = 3 + j; sSel_NId = this.SelectedNids[j].ToString().Split('_'); dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'"; dvMain.RowFilter += " AND " + Indicator.IndicatorNId + "=" + sSel_NId[0] + " AND Subgroup_Val_NId =" + sSel_NId[1]; if (dvMain.Count > 0) { SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString(); //SummarySheet.Cells[i, iDataCol - 1].Value =dvMain[0]["Data_Value"].ToString().Replace(",","."); if (bSupressArea == false) { msArray[k, 0] = dvMain[0][Area.AreaID].ToString(); msArray[k, 1] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - iCtrFillFrom, 0] = dvMain[0][Timeperiods.TimePeriod].ToString(); msDataArray[i - iCtrFillFrom, 1] = dvMain[0][Area.AreaID].ToString(); msDataArray[i - iCtrFillFrom, 2] = dvMain[0][Area.AreaName].ToString(); msDataArray[i - iCtrFillFrom, 4] = step2SubgroupText; msDataArray[i - iCtrFillFrom, 5] = dvMain[0][IndicatorClassifications.ICName].ToString(); bSupressArea = true; } msArray[k, 2] = dvMain[0][Indicator.IndicatorName].ToString(); msArray[k, 3] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); msArray[k, 4] = dvMain[0][Timeperiods.TimePeriod].ToString(); msArray[k, 5] = dvMain[0][Data.DataValue].ToString(); msArray[k, 6] = dvMain[0][Unit.UnitName].ToString(); msArray[k, 7] = dvMain[0][IndicatorClassifications.ICName].ToString(); k += 1; } } k += 1; bSupressArea = false; } //*** Set Formula //*** Set the formula for User Defined Formula // Total no of data columns iDataCol = 3 + this.SelectedNids.Count; sChageValue = formulaText; //txt6_1_Formula.Text; for (i = 0; i <= sIdentifierChar.Count - 1; i++) { sChageValue = sChageValue.Replace(sIdentifierChar[i], "_" + sIdentifierChar[i] + "_"); } for (i = 0; i <= this.SelectedNids.Count - 1; i++) { TempFillFromString = SummarySheet.Cells[iCtrFillFrom, 2 + i].Address.Replace("$", ""); // sChageValue = sChageValue.Replace("_" + lv6_1_Available.Items(selNId2[i]).SubItems(2).Text + "_", TempFillFromString); sChageValue = sChageValue.Replace("_" + this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][2].ToString() + "_", TempFillFromString); } sChageValue = "IF(TYPE(" + sChageValue + ")=16,\"\",ROUND(" + sChageValue + "," + SummarySheet.Cells[6, 1].Address + "))"; sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address; SummarySheet.Range[sAddress].Formula = "= " + sChageValue; TempFillFromString = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", ""); oRngStart = SummarySheet.Range[TempFillFromString]; TempFillToString = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", ""); if (TempFillFromString != TempFillToString) { sMaxRange = TempFillFromString + ":" + TempFillToString; oRngTotal = SummarySheet.Range[sMaxRange]; this.AutoFillRange(oRngStart, oRngTotal); } //*** Setting the Font SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Name = this.FontName; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Size = (float)this.FontSize; SummarySheet.Range[6, 0, iCtrFillTo, iDataCol + 3].Columns.AutoFit(); SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize; //*** Progress this.RaiseEventSetProgress(45); //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = this.TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_User Defined Formula (" + this.TimeStamp + ").xls"; iCtrFillFrom = 8;//9; //*** Set Data LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Value = msArray; //*** Autofit LogSheet.Range[iCtrFillFrom - 2, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Columns.AutoFit(); //*** Set Font LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Font.Name = this.FontName; LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Font.Size = (float)this.FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize; //****************************************************************************************** this.RaiseEventSetProgress(100); this.DIExcel.GetWorksheet(0).Cells[0, 0].Activate(); DIExcel.SaveAs(TempLogFilePath); //*** hide progress bar this.RaiseEventHideProgressBar(); SummarySheet = null; LogSheet = null; dvMain.RowFilter = string.Empty; } catch (Exception ex) { } finally { this.RaiseEventSetCursor(true); } thisThread.CurrentCulture = originalCulture; this.RaiseEventCalculateStepCompleted("pnl6_3"); }
/// <summary> /// Perform Transform Unit Calculations /// </summary> /// <param name="step2UnitText">Name Of the Unit in step 2</param> /// <param name="step2ConversionTable"></param> public void FillTransformUnitSummaryLog(String step2UnitText, DataTable step2ConversionTable) { try { this.RaiseEventSetCursor(false); IWorksheet SummarySheet; IWorksheet LogSheet; int i = 0; int iAreaCount = 0; int iCtrFillFrom = 0; int iCtrFillTo = 0; int iDataCol = 0; StringCollection oSubgroup = new StringCollection(); string sTempFillFromString = string.Empty; string sTempFillToString = string.Empty; // Initialize Progress Bar this.RaiseEventSetProgress(1); // Copy Log file to temp location if (File.Exists(this.LogFilePath)) { File.Copy(this.LogFilePath, this.TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } ApplyLanguageSettings(); // Opening and getting workbook this.DIExcel = new DIExcel(TempLogFilePath); SummarySheet = DIExcel.GetWorksheet(0); LogSheet = DIExcel.GetWorksheet(1); SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; //*************************** SHOW INDEX ****************************************** this.RaiseEventSetProgress(10); //data view with all filters applied DataView dvMain = this.PresentationData; // Apply Data Filters using selected Ind NId if (dvMain.RowFilter == "") { dvMain.RowFilter = Indicator.IndicatorNId + " = " + this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]; } else { dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " = " + this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]; ; } dvMain.Sort = Area.AreaID + " ASC, " + Unit.UnitName + " Asc"; StringDictionary ColUnit = new StringDictionary(); //foreach (DataRowView Drv in (DataView)dg5_2_Conversion.DataSource) foreach (DataRowView Drv in step2ConversionTable.DefaultView) { ColUnit.Add(Drv[Unit.UnitNId].ToString(), Drv["Factor"].ToString()); } msDataArray = new string[dvMain.Count, 6]; string[,] msSummaryArray = new string[dvMain.Count + 2, 9]; msSummaryArray[0, 0] = LangStrings.AREAID; msSummaryArray[0, 1] = LangStrings.AREANAME; msSummaryArray[0, 2] = LangStrings.UNIT; msSummaryArray[0, 3] = LangStrings.DATAVALUE; msSummaryArray[0, 4] = LangStrings.CONVERSION_FACTOR; msSummaryArray[0, 5] = step2UnitText;// txt5_2_Unit.Text; msSummaryArray[0, 6] = LangStrings.SUBGROUP; msSummaryArray[0, 7] = LangStrings.TIME; msSummaryArray[0, 8] = LangStrings.SOURCE; string PrevAreaId = ""; i += 2; foreach (DataRowView Drv in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID if (PrevAreaId != Drv[Area.AreaID].ToString()) { iAreaCount += 1; PrevAreaId = Drv[Area.AreaID].ToString(); } msSummaryArray[i, 0] = Drv[Area.AreaID].ToString(); msSummaryArray[i, 1] = Drv[Area.AreaName].ToString(); msSummaryArray[i, 2] = Drv[Unit.UnitName].ToString(); msSummaryArray[i, 3] = Drv[Data.DataValue].ToString(); msSummaryArray[i, 4] = ColUnit[Drv[Unit.UnitNId].ToString()].ToString(); msSummaryArray[i, 6] = Drv[SubgroupVals.SubgroupVal].ToString(); if (oSubgroup.Contains(Drv[SubgroupVals.SubgroupValNId].ToString()) == false) { oSubgroup.Add(Drv[SubgroupVals.SubgroupValNId].ToString()); } msSummaryArray[i, 7] = Drv[Timeperiods.TimePeriod].ToString(); msSummaryArray[i, 8] = Drv[IndicatorClassifications.ICName].ToString(); msDataArray[i - 2, 0] = Drv[Timeperiods.TimePeriod].ToString(); msDataArray[i - 2, 1] = Drv[Area.AreaID].ToString(); msDataArray[i - 2, 2] = Drv[Area.AreaName].ToString(); msDataArray[i - 2, 4] = Drv[SubgroupVals.SubgroupVal].ToString(); msDataArray[i - 2, 5] = Drv[IndicatorClassifications.ICName].ToString(); i += 1; } ColUnit = null; string[,] msLogArray = new string[dvMain.Count + iAreaCount + 2, 8]; //*** Data Array for Log Sheet msLogArray[0, 0] = LangStrings.AREAID; msLogArray[0, 1] = LangStrings.AREANAME; msLogArray[0, 2] = LangStrings.INDICATOR; msLogArray[0, 3] = LangStrings.SUBGROUP; msLogArray[0, 4] = LangStrings.TIME; msLogArray[0, 5] = LangStrings.DATAVALUE; msLogArray[0, 6] = LangStrings.UNIT; msLogArray[0, 7] = LangStrings.SOURCE; PrevAreaId = ""; i = 1; dvMain.GetEnumerator().Reset(); foreach (DataRowView Drv in dvMain) { if (PrevAreaId != Drv[Area.AreaID].ToString()) { i += 1; PrevAreaId = Drv[Area.AreaID].ToString(); msLogArray[i, 0] = Drv[Area.AreaID].ToString(); msLogArray[i, 1] = Drv[Area.AreaName].ToString(); } msLogArray[i, 2] = Drv[Indicator.IndicatorName].ToString(); msLogArray[i, 3] = Drv[SubgroupVals.SubgroupVal].ToString(); msLogArray[i, 4] = Drv[Timeperiods.TimePeriod].ToString(); msLogArray[i, 5] = Drv[Data.DataValue].ToString(); msLogArray[i, 6] = Drv[Unit.UnitName].ToString(); msLogArray[i, 7] = Drv[IndicatorClassifications.ICName].ToString(); i += 1; } //SubgroupNId in Step 5 - Transform Unit //*** Special Handling for Subgroup in Transform Unit this.DESheetInformation.Subgroup = new string[oSubgroup.Count]; this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count]; for (i = 0; i <= oSubgroup.Count - 1; i++) { //-- Using DAL library for getting dataView string sSql; sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i].ToString()); dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView; this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); if (dvMain.Count > 0) { this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString(); } else { this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString(); } } SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = this.TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_Transform Unit (" + this.TimeStamp + ").xls"; SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES; SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue;// nud5_3_Decimals.Value; SummarySheet.Cells[7, 2].Value = Step1SelectionDataTable.Rows[0][0].ToString();// lv5_1_Selected.Items(0).SubItems(0).Text; this.RaiseEventSetProgress(25); iCtrFillTo = 8 + msSummaryArray.GetLength(0) - 1; iDataCol = 9; //*** Set Data SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Value = msSummaryArray; //*** Set Alignment SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit(); SummarySheet.Range[7, 0, iCtrFillTo, iDataCol - 1].HorizontalAlignment = HAlign.Left;// -4131; //xlLeft SummarySheet.Range[8, 3, iCtrFillTo, 5].HorizontalAlignment = HAlign.Right; //xlRight this.RaiseEventSetProgress(35); //*** Set Formula for Transform Unit string sChageValue; string sAddress; string sMinRange; string sMaxRange; IRange oRngStart; IRange oRngTotal; string TempString = string.Empty; iCtrFillFrom = 10; iDataCol = 6; sChageValue = ""; sTempFillFromString = SummarySheet.Cells[iCtrFillFrom, 3].Address.Replace("$", ""); TempString = SummarySheet.Cells[iCtrFillFrom, 4].Address.Replace("$", ""); sChageValue = "ROUND(" + sTempFillFromString + "*" + TempString + "," + SummarySheet.Cells[6, 1].Address + ")"; sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address; SummarySheet.Range[sAddress].Formula = "= " + sChageValue; sTempFillFromString = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", ""); oRngStart = SummarySheet.Range[sTempFillFromString]; sTempFillToString = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", ""); if (sTempFillFromString != sTempFillToString) { sMaxRange = sTempFillFromString + ":" + sTempFillToString; oRngTotal = SummarySheet.Range[sMaxRange]; this.AutoFillRange(oRngStart, oRngTotal); } //*** Set Font iDataCol = 9; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize; SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize; SummarySheet.Cells[7, 2].Font.Bold = true; //*** Progress this.RaiseEventSetProgress(45); //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = this.TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_Transform Unit (" + this.TimeStamp + ").xls"; iCtrFillFrom = 8; iCtrFillTo = iCtrFillFrom + msLogArray.GetLength(0) - 1; iDataCol = 8; //*** Set Data LogSheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Value = msLogArray; //*** Set Alignment LogSheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit(); LogSheet.Range[iCtrFillFrom, 4, iCtrFillTo, 4].HorizontalAlignment = HAlign.Left; //xlLeft 'Time column //*** Set Font LogSheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName; LogSheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize; //****************************************************************************************** this.RaiseEventSetProgress(100); // ExcelWorkBook.Sheets(1).Cells[1, 1].Activate(); //*** Bring Focus to top of the page DIExcel.GetWorksheet(0).Cells[0, 0].Activate(); DIExcel.Save(); //*** hide progress bar this.RaiseEventHideProgressBar(); SummarySheet = null; LogSheet = null; this.RaiseEventCalculateStepCompleted("pnl5_4"); } catch (Exception ex) { } finally { this.RaiseEventSetCursor(true); } }
/// <summary> /// Perform SubTotal Calculations /// </summary> /// <param name="userPreference"></param> /// <param name="lastAreaLevel">Last area Level ,It is milastAreaLevel in UI application </param> /// <param name="Step2SelectionDataTable">Data Table having Step 2 selections of area level</param> public void FillSubtotalSummaryLog(UserPreference.UserPreference userPreference, Int32 lastAreaLevel, DataTable Step2SelectionDataTable) { try { //Required Information: UserPreferance //milastAreaLevel from set level method //Step 2 selections of area level this.RaiseEventSetCursor(false); IWorksheet SummarySheet; IWorksheet LogSheet; int i = 0; int j = 0; int k = 0; int iAreaCount = 0; int iCtrFillFrom = 0; int iCtrFillTo = 0; int iDataCol = 0; string sOrgRowFilter = ""; string Str = string.Empty; DataView dvSubgroup; //*** Initialize Progress Bar this.RaiseEventSetProgress(1); // Copy Log to temp location if (File.Exists(this.LogFilePath)) { File.Copy(this.LogFilePath, this.TempLogFilePath, true); System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal); } ApplyLanguageSettings(); // Opening and getting workbook this.DIExcel = new DIExcel(TempLogFilePath); SummarySheet = this.DIExcel.GetWorksheet(0); LogSheet = this.DIExcel.GetWorksheet(1); SummarySheet.Name = LangStrings.SUMMARY; LogSheet.Name = LangStrings.LOG; //*************************** SHOW INDEX ****************************************** this.RaiseEventSetProgress(10); // Get presentation data DataView dvMain = this.PresentationData; //Get subgroup details using user selection Str = this.DBQueries.Calculates.Indicator_Subgroup_Selected(true, userPreference.UserSelection, userPreference.Database.SelectedConnectionDetail.ServerType); dvSubgroup = this.DBConnection.ExecuteDataTable(Str).DefaultView; ////Get area details using user selection Str = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, userPreference.UserSelection.AreaNIds); DataView dvArea = this.DBConnection.ExecuteDataTable(Str).DefaultView; //Getting Log array. //*** Data Array for Log Sheet 'dvArea.Count for blank rows string[,] msLogArray = new string[dvMain.Count + dvArea.Count + 2, 8]; //Use Filter Indicator NId form indicator selected in step 1 dvSubgroup.RowFilter = Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]);// lv4_1_Selected.Items(0).Tag; StringCollection oSubgroup = new StringCollection(); StringCollection oSubgroupVal = new StringCollection(); StringCollection oSubgroupGId = new StringCollection(); for (k = 0; k <= dvSubgroup.Count - 1; k++) { // add subgroup and subgroup gid into subgroup collection if (oSubgroup.Count > 0) { //oSubgroup.Add((string)dvSubgroup[k]["Subgroup_Val_NId"]) give error unable to convert int to string if (oSubgroup.Contains(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString()) == false) { //oSubgroup.Add((string)dvSubgroup[k]["Subgroup_Val_NId"]); oSubgroup.Add(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString()); oSubgroupVal.Add((string)dvSubgroup[k][SubgroupVals.SubgroupVal]); } } else { oSubgroup.Add(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString()); oSubgroupVal.Add((string)dvSubgroup[k][SubgroupVals.SubgroupVal]); } } string PrevAreaId = ""; int iAreaParentNId = 0; ArrayList ArrAreaNId = new ArrayList(); Hashtable AreaCol = new Hashtable(); string sSource = string.Empty; string sTimePeriod = string.Empty; //* Data Filters if (dvMain.RowFilter == "") { sOrgRowFilter = ""; } else { sOrgRowFilter = dvMain.RowFilter + " AND "; } // Add selected area indicator and last area level in filter. So if area level is 3 dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + lastAreaLevel; dvMain.Sort = Timeperiods.TimePeriod + " DESC"; // GetMostRecent Data foreach (DataRowView drv in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID dvArea.RowFilter = Area.AreaNId + "=" + Convert.ToInt32(drv[Area.AreaNId]); iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]); dvArea.RowFilter = Area.AreaNId + "=" + iAreaParentNId; if (AreaCol.ContainsKey(iAreaParentNId) == false) { ArrAreaNId.Add(iAreaParentNId); AreaCol.Add(iAreaParentNId, 0); AreaCol[iAreaParentNId] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaCol[iAreaParentNId]).Add((string)oSubgroup[k], 0); } } ((Hashtable)AreaCol[iAreaParentNId])[(int)drv[SubgroupVals.SubgroupValNId]] = Convert.ToDouble(((Hashtable)AreaCol[iAreaParentNId])[(int)drv["Subgroup_Val_NId"]]) + Convert.ToDouble(drv["Data_Value"]); //((Hashtable)AreaCol[iAreaParentNId])[(string)drv["Subgroup_Val_NId"]] = (int)((Hashtable)AreaCol[iAreaParentNId](string)drv["Subgroup_Val_NId"]] + (int)drv["Data_Value"]; sSource = drv[IndicatorClassifications.ICName].ToString(); sTimePeriod = drv[Timeperiods.TimePeriod].ToString(); } //*** BugFix 10 Aug 2006 Skipping of areas without child nodes at last level dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + Convert.ToString(lastAreaLevel - 1); dvMain.Sort = Timeperiods.TimePeriod + " DESC"; //*** GetMostRecent Data foreach (DataRowView drv in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID if (AreaCol.Count > 0) { if (AreaCol.ContainsKey(drv[Area.AreaNId]) == false) { ArrAreaNId.Add(drv[Area.AreaNId]); AreaCol.Add(drv[Area.AreaNId], 0); AreaCol[drv[Area.AreaNId]] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaCol[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0); } } } else { ArrAreaNId.Add(drv[Area.AreaNId]); AreaCol.Add(drv[Area.AreaNId], 0); AreaCol[drv[Area.AreaNId]] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaCol[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0); } } ((Hashtable)AreaCol[drv[Area.AreaNId]])[(int)drv[SubgroupVals.SubgroupValNId]] = Convert.ToDouble(drv["Data_Value"]); } ArrayList ArrAreaNIdOrg = new ArrayList(); ArrAreaNIdOrg = ArrAreaNId; Hashtable AreaColOrg = new Hashtable(); AreaColOrg = AreaCol; //----------------------- Retrive the index count before declaring array -------------- i = 2; object ColItem; int iDataArrCtr = 0; for (j = lastAreaLevel - 1; j >= 1; j += -1) { //?? 1 may not be the top level Hashtable AreaColTemp = new Hashtable(); ArrayList ArrAreaNIdTemp = new ArrayList(); for (int l = 0; l <= ArrAreaNId.Count - 1; l++) { ColItem = AreaCol[ArrAreaNId[l]]; dvArea = null; string sSql = string.Empty; sSql = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, ((int)ArrAreaNId[l]).ToString()); dvArea = this.DBConnection.ExecuteDataTable(sSql).DefaultView; iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]); if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(iAreaParentNId) == false) { ArrAreaNIdTemp.Add(iAreaParentNId); AreaColTemp.Add(iAreaParentNId, 0); AreaColTemp[iAreaParentNId] = new Hashtable(); } if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true) { for (k = 0; k <= oSubgroup.Count - 1; k++) { iDataArrCtr += 1; } i += 1; } } dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + Convert.ToString(j - 1); dvMain.Sort = Timeperiods.TimePeriod + " DESC"; //*** GetMostRecent Data foreach (DataRowView drv in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(drv[Area.AreaNId]) == false) { ArrAreaNIdTemp.Add(Convert.ToInt32(drv[Area.AreaNId])); AreaColTemp.Add(Convert.ToInt32(drv[Area.AreaNId]), 0); AreaColTemp[drv[Area.AreaNId]] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaColTemp[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0); } } } ArrAreaNId = ArrAreaNIdTemp; AreaCol = AreaColTemp; } ArrAreaNId = ArrAreaNIdOrg; AreaCol = AreaColOrg; //----------------------------------------------------------------------------------- //--------------------- Declare Array ----------------------------------------------- string[,] msSummaryArray = new string[i - 1 + 3, 1 + oSubgroup.Count + 1]; string[,] msTempDataArray = new string[iDataArrCtr, 6]; msSummaryArray[0, 0] = LangStrings.AREAID; msSummaryArray[0, 1] = LangStrings.AREANAME; for (k = 0; k <= oSubgroup.Count - 1; k++) { dvSubgroup.RowFilter = SubgroupVals.SubgroupValNId + " =" + Convert.ToInt32(oSubgroup[k]); msSummaryArray[0, 2 + k] = dvSubgroup[0][SubgroupVals.SubgroupVal].ToString(); } i = 2; iDataArrCtr = 0; for (j = lastAreaLevel - 1; j >= 1; j += -1) { //??? 1 may not be the top level Hashtable AreaColTemp = new Hashtable(); ArrayList ArrAreaNIdTemp = new ArrayList(); for (int l = 0; l <= ArrAreaNId.Count - 1; l++) { ColItem = AreaCol[ArrAreaNId[l]]; dvArea = null; string sSql = string.Empty; sSql = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, ArrAreaNId[l].ToString()); dvArea = this.DBConnection.ExecuteDataTable(sSql).DefaultView; //if (lv4_2_Level.Items(j - 1).Checked) if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true) { msSummaryArray[i, 0] = dvArea[0][Area.AreaID].ToString(); msSummaryArray[i, 1] = dvArea[0][Area.AreaName].ToString(); } // Get Parent area Nid iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]); if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(iAreaParentNId) == false) { ArrAreaNIdTemp.Add(iAreaParentNId); AreaColTemp.Add(iAreaParentNId, 0); AreaColTemp[iAreaParentNId] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaColTemp[iAreaParentNId]).Add(Convert.ToInt32(oSubgroup[k]), 0); } } //if (lv4_2_Level.Items(j - 1).Checked) if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true) { for (k = 0; k <= oSubgroup.Count - 1; k++) { msSummaryArray[i, k + 2] = Convert.ToString(((Hashtable)AreaCol[ArrAreaNId[l]])[(Convert.ToInt32(oSubgroup[k]))]); msTempDataArray[iDataArrCtr, 0] = sTimePeriod; msTempDataArray[iDataArrCtr, 1] = dvArea[0][Area.AreaID].ToString(); msTempDataArray[iDataArrCtr, 2] = dvArea[0][Area.AreaName].ToString(); msTempDataArray[iDataArrCtr, 3] = msSummaryArray[i, k + 2].ToString(); msTempDataArray[iDataArrCtr, 4] = (string)oSubgroupVal[k]; msTempDataArray[iDataArrCtr, 5] = sSource; iDataArrCtr += 1; //*** Bugfix 19 Apr 2007 Error for very high datavalue due to usage of CINT //CType(AreaColTemp.Item(iAreaParentNId), Hashtable).Item(CStr(oSubgroup.Item(k))) = //(CType(AreaColTemp.Item(iAreaParentNId), Hashtable).Item(CStr(oSubgroup.Item(k)))) + //(CType(AreaCol.Item(ArrAreaNId(l)), Hashtable).Item(CStr(oSubgroup.Item(k)))) ((Hashtable)AreaColTemp[iAreaParentNId])[Convert.ToInt32(oSubgroup[k])] = Convert.ToDouble(((Hashtable)AreaColTemp[iAreaParentNId])[Convert.ToInt32(oSubgroup[k])]) + Convert.ToDouble(((Hashtable)AreaCol[ArrAreaNId[l]])[Convert.ToInt32(oSubgroup[k])]); } i += 1; } } //*** BugFix 10 Aug 2006 Skipping of areas without child nodes at last level //dvMain.RowFilter = sOrgRowFilter + "Indicator_NId = " + lv4_1_Selected.Items(0).Tag + " AND Area_Level=" + j - 1; dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND " + Area_Level.AreaLevel + " =" + Convert.ToString(j - 1); dvMain.Sort = Timeperiods.TimePeriod + " DESC"; //*** GetMostRecent Data foreach (DataRowView DataviewRow in dvMain) { //*** Fill Datavalue for Numerator Indicator against each AreaID if (AreaColTemp.ContainsKey(DataviewRow[Area.AreaNId]) == false) { ArrAreaNIdTemp.Add(DataviewRow[Area.AreaNId].ToString()); AreaColTemp.Add(DataviewRow[Area.AreaNId], 0); AreaColTemp[Convert.ToInt32(DataviewRow[Area.AreaNId])] = new Hashtable(); for (k = 0; k <= oSubgroup.Count - 1; k++) { ((Hashtable)AreaColTemp[DataviewRow[Area.AreaNId]]).Add((string)oSubgroup[k], 0); } } //*** Overwrite the subtotal achieved through cumulation, if data for an area exists. ((Hashtable)AreaColTemp[DataviewRow[Area.AreaNId]])[Convert.ToInt32(DataviewRow[SubgroupVals.SubgroupValNId])] = Convert.ToDouble(DataviewRow[Data.DataValue]); } ArrAreaNId = ArrAreaNIdTemp; AreaCol = AreaColTemp; } //Finish button error - SubTotal msDataArray = new string[iDataArrCtr, 6]; for (i = 0; i <= iDataArrCtr - 1; i++) { for (j = 0; j <= 5; j++) { msDataArray[i, j] = msTempDataArray[i, j].ToString(); } } msLogArray[0, 0] = LangStrings.AREAID; msLogArray[0, 1] = LangStrings.AREANAME; msLogArray[0, 2] = LangStrings.INDICATOR; msLogArray[0, 3] = LangStrings.SUBGROUP; msLogArray[0, 4] = LangStrings.TIME; msLogArray[0, 5] = LangStrings.DATAVALUE; msLogArray[0, 6] = LangStrings.UNIT; msLogArray[0, 7] = LangStrings.SOURCE; PrevAreaId = ""; i = 1; dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]);// lv4_1_Selected.Items(0).Tag; dvMain.GetEnumerator().Reset(); dvMain.Sort = " Area_ID"; foreach (DataRowView drv in dvMain) { if (PrevAreaId != drv[Area.AreaID].ToString()) { i += 1; PrevAreaId = drv[Area.AreaID].ToString(); msLogArray[i, 0] = drv[Area.AreaID].ToString(); msLogArray[i, 1] = drv[Area.AreaName].ToString(); } msLogArray[i, 2] = drv[Indicator.IndicatorName].ToString(); msLogArray[i, 3] = drv[SubgroupVals.SubgroupVal].ToString(); msLogArray[i, 4] = drv[Timeperiods.TimePeriod].ToString(); msLogArray[i, 5] = drv[Data.DataValue].ToString(); msLogArray[i, 6] = drv[Unit.UnitName].ToString(); msLogArray[i, 7] = drv[IndicatorClassifications.ICName].ToString(); i += 1; } //*** Special Handling for Subgroup in SubTotal this.DESheetInformation.Subgroup = new string[oSubgroup.Count]; this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count]; for (i = 0; i <= oSubgroup.Count - 1; i++) { string sSql = string.Empty; sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i].ToString()); dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView; this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString(); if (dvMain.Count > 0) { this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString(); } else { this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString(); } } SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE; SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME; SummarySheet.Cells[3, 1].Value = TimeStamp; SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; SummarySheet.Cells[4, 1].Value = "Log_Subtotal (" + TimeStamp + ").xls"; SummarySheet.Cells[8, 2].Value = this.DESheetInformation.Indicator; this.RaiseEventSetProgress(25); //*** Set Data iDataCol = 2 + oSubgroup.Count; iCtrFillFrom = 9; //10; iCtrFillTo = iCtrFillFrom + msSummaryArray.GetLength(0) - 1; SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Value = msSummaryArray; this.RaiseEventSetProgress(35); //Fill Data Values and Formula for each records //*** Setting the Font SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName; SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize; SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit(); //SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Rows.AutoFit(); SummarySheet.Cells[0, 0].Font.Bold = true; SummarySheet.Cells[0, 0].Font.Italic = true; SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize; SummarySheet.Cells[8, 2].Font.Bold = true; //*** Progress this.RaiseEventSetProgress(45); //*********************************** S H O W L O G ************************************** LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME; LogSheet.Cells[2, 0].Value = LangStrings.sMODULE; LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME; LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME; LogSheet.Cells[3, 1].Value = this.TimeStamp; LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME; LogSheet.Cells[4, 1].Value = "Log_Subtotol (" + this.TimeStamp + ").xls"; iCtrFillFrom = 8;// 9; //*** Set Data LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Value = msLogArray; //*** Autofit LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Columns.AutoFit(); LogSheet.Range[iCtrFillFrom, 4, iCtrFillFrom + msLogArray.GetLength(0) - 1, 4].HorizontalAlignment = HAlign.Left;// -4131; //xlLeft 'Time column //*** Set Font LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Font.Name = this.FontName; LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Font.Size = (float)this.FontSize; LogSheet.Cells[0, 0].Font.Bold = true; LogSheet.Cells[0, 0].Font.Italic = true; LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize; //****************************************************************************************** this.RaiseEventSetProgress(100); this.DIExcel.GetWorksheet(0).Cells[0, 0].Activate(); this.DIExcel.Save(); //*** hide progress bar this.RaiseEventHideProgressBar(); SummarySheet = null; LogSheet = null; dvMain.RowFilter = string.Empty; //goExcel.SetParent(pnl4_3.Handle.ToInt32); this.RaiseEventCalculateStepCompleted("pnl4_3"); } catch (Exception ex) { } finally { this.RaiseEventSetCursor(true); } }
public Util_Excel_GridView() { workbook = SpreadsheetGear.Factory.GetWorkbook(); worksheet = workbook.Worksheets[counterPage]; cells = worksheet.Cells; }
/// <summary> /// Builds a line graph (in this case typically the X Axis is always the Elapsed Time Column) /// </summary> /// <param name="dataWorksheet">The data worksheet.</param> /// <param name="graphConfig">The graph configuration.</param> /// <param name="columnNameIndex">Index of the column name.</param> /// <exception cref="ApplicationException">... Error building graph: [{graphConfig.Name}], Expected cols: [{errList}</exception> private static void BuildLineGraph(SpreadsheetGear.IWorksheet dataWorksheet, LineGraphBE lineGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName) { SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook; int columnIdx = -1; int xAxisTargetColumnIdx = -1; string xAxisColumnName = lineGraphConfig.XAxis.FromColumnName; List <string> missingColumnNames = new List <string>(); // step 1: find the column we want to target for the XAxis if (!columnNameIndex.TryGetValue(xAxisColumnName, out xAxisTargetColumnIdx)) { missingColumnNames.Add(xAxisColumnName); } // step 2.1: find the columns we want to target for the YAxis Dictionary <int, string> yAxisTargetColIdxs = new Dictionary <int, string>(); foreach (string yAxisColumnName in lineGraphConfig.YAxis.FromColumnNames) { if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx)) { yAxisTargetColIdxs.Add(columnIdx, yAxisColumnName); } else { missingColumnNames.Add(yAxisColumnName); } } // step 2.2: find the columns we want to target for the YAxis Dictionary <int, string> secondaryYAxisTargetColIdxs = new Dictionary <int, string>(); if (lineGraphConfig.SecondaryYAxis != null) { foreach (string yAxisColumnName in lineGraphConfig.SecondaryYAxis.FromColumnNames) { if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx)) { secondaryYAxisTargetColIdxs.Add(columnIdx, yAxisColumnName); } else { missingColumnNames.Add(yAxisColumnName); } } } // step 3: find the columns we want to reference for the Gains string pidGainsColumnName = lineGraphConfig.Gains?.PIDGains; string followerGainsColumnName = lineGraphConfig.Gains?.FollowerGains; string controlModeColumnName = lineGraphConfig.Gains?.ControlMode; int pidGainsColumnIdx = -1; int followerGainsColumnIdx = -1; int controlModeColumnIdx = -1; int elapsedDeltaColumnIdx = -1; int targetColumnIdx = -1; int actualColumnIdx = -1; int pathNameColumnIdx = -1; if (!string.IsNullOrEmpty(pidGainsColumnName)) { if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx)) { //missingColumnNames.Add(pidGainsColumnName); } } if (!string.IsNullOrEmpty(followerGainsColumnName)) { if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx)) { missingColumnNames.Add(followerGainsColumnName); } } if (!string.IsNullOrEmpty(controlModeColumnName)) { if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx)) { //missingColumnNames.Add(controlModeColumnName); } } if (!string.IsNullOrEmpty(lineGraphConfig.XAxis.FromColumnName)) { if (!columnNameIndex.TryGetValue(lineGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx)) { missingColumnNames.Add(lineGraphConfig.XAxis.FromColumnName); } } if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.TargetColumnName)) { if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.TargetColumnName, out targetColumnIdx)) { missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.TargetColumnName); } } if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.ActualColumnName)) { if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.ActualColumnName, out actualColumnIdx)) { missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.ActualColumnName); } } if (!string.IsNullOrEmpty(pathNameColumnName)) { if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx)) { missingColumnNames.Add(pathNameColumnName); } } // // stop if any were missing if (missingColumnNames.Count > 0) { string errList = String.Join(",", missingColumnNames); throw new ApplicationException($"... Error building graph: [{lineGraphConfig.Name}], Expected cols: [{errList}] cannot be found!"); } // Step 4: add a new worksheet to hold the chart IWorksheet chartSheet = workbook.Worksheets.Add(); chartSheet.Name = lineGraphConfig.Name; // Step 5.1: time to build the chart SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500); SpreadsheetGear.Charts.IChart chart = chartShape.Chart; // working variables int lastRowIdx = dataWorksheet.UsedRange.RowCount; IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0]; IRange yAxisColumn = null; ISeries chartSeries = null; string seriesName = string.Empty; // Step 5.2: add a chart series for each Y axis column in the config foreach (var kvp in yAxisTargetColIdxs) { seriesName = dataWorksheet.Cells[0, kvp.Key].Text; yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key]; chartSeries = chart.SeriesCollection.Add(); chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200"; chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200"; switch (lineGraphConfig.ChartTypeOverride) { case @"XYScatter": chartSeries.ChartType = ChartType.XYScatter; break; default: chartSeries.ChartType = ChartType.Line; break; } chartSeries.Name = seriesName; } foreach (var kvp in secondaryYAxisTargetColIdxs) { seriesName = dataWorksheet.Cells[0, kvp.Key].Text; yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key]; chartSeries = chart.SeriesCollection.Add(); chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200"; chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200"; chartSeries.AxisGroup = AxisGroup.Secondary; switch (lineGraphConfig.ChartTypeOverride) { case @"XYScatter": chartSeries.ChartType = ChartType.XYScatter; break; default: chartSeries.ChartType = ChartType.Line; break; } chartSeries.Name = seriesName; } // Step 5.3: format the chart title chart.HasTitle = true; StringBuilder chartTitle = new StringBuilder(); string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text; chartTitle.AppendLine($"{lineGraphConfig.Name} | Path: [{pathName}]"); // optional add follower gains only if available if (pidGainsColumnIdx >= 0) { chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}"); } // optional add follower gains only if available if (followerGainsColumnIdx >= 0) { chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}"); } if (lineGraphConfig.CalcAreaDelta != null) { (decimal posErr, decimal negErr) = CalcAreaDelta(dataWorksheet, elapsedDeltaColumnIdx, targetColumnIdx, actualColumnIdx, lineGraphConfig.Name); chartTitle.AppendLine($"Error Area (tot): {posErr:N0} | {negErr:N0}"); } chart.ChartTitle.Text = chartTitle.ToString(); chart.ChartTitle.Font.Size = 12; // Step 5.4: format the chart legend chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom; chart.Legend.Font.Bold = true; // Step 5.5: format X & Y Axes IAxis xAxis = chart.Axes[AxisType.Category]; xAxis.HasMinorGridlines = true; xAxis.HasTitle = true; if (chart.ChartType == ChartType.Line) { // this option not valid on xy graphs xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second } IAxisTitle xAxisTitle = xAxis.AxisTitle; xAxisTitle.Text = lineGraphConfig.XAxis.AxisTitle; IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary]; yAxis.HasTitle = true; yAxis.TickLabels.NumberFormat = "General"; yAxis.ReversePlotOrder = lineGraphConfig.YAxis.IsYAxisValuesInReverseOrder; if (lineGraphConfig.YAxis.MajorUnitOverride.HasValue) { yAxis.MajorUnit = (double)lineGraphConfig.YAxis.MajorUnitOverride.Value; } IAxisTitle yAxisTitle = yAxis.AxisTitle; yAxisTitle.Text = lineGraphConfig.YAxis.AxisTitle; }
public static string ProcessLogFile(string logFilePathName, string graphSetName, GraphConfigsBE config) { // Activate SpreadsheetGear SpreadsheetGear.Factory.SetSignedLicense("SpreadsheetGear.License, Type=Trial, Product=BND, Expires=2019-07-27, Company=Tom Bruns, [email protected], Signature=orH+RFO9hRUB8SJXBSWQZJuXP9OfSkV9fLcU9suehfgA#dgunwBK9VssTgnfowKGWaqMNfVgwVetxEWbayzGM1uIA#K"); // Create a new empty workbook in a new workbook set. SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet(); // import the csv file SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(logFilePathName); // get a reference to the active (only) worksheet SpreadsheetGear.IWorksheet dataWorksheet = workbook.ActiveWorksheet; dataWorksheet.Name = System.IO.Path.GetFileNameWithoutExtension(logFilePathName); // freeze 1st row & 1st column(to make scrolling more user friendly) dataWorksheet.WindowInfo.ScrollColumn = 0; dataWorksheet.WindowInfo.SplitColumns = 1; dataWorksheet.WindowInfo.ScrollRow = 0; dataWorksheet.WindowInfo.SplitRows = 1; dataWorksheet.WindowInfo.FreezePanes = true; // build index of column names var columnNameXref = BuildColumnNameXref(dataWorksheet); // find the config for the requested Set of Graphs GraphSetBE graphSet = config.GraphSets.Where(gs => gs.SetName.ToLower() == graphSetName.ToLower()).FirstOrDefault(); if (graphSet == null) { List <string> availableGraphSetNames = config.GraphSets.Select(gs => gs.SetName).ToList(); throw new ApplicationException($"Requested GraphSet: [{graphSetName}], Options: [{String.Join(",", availableGraphSetNames)}]"); } // do any required conversions on the source data (ex Radians to Degrees) if (graphSet.AngleConversions != null) { foreach (AngleConversionBE angleConversion in graphSet.AngleConversions) { PerformAngleConversion(dataWorksheet, angleConversion, columnNameXref); } // rebuild column name index columnNameXref = BuildColumnNameXref(dataWorksheet); } // resize column widths to fit header text dataWorksheet.UsedRange.Columns.AutoFit(); // ==================================== // create any new sheets with a subset of the original columns to make analysis easier // ==================================== foreach (NewSheetBE newSheet in graphSet.NewSheets) { BuildNewSheet(dataWorksheet, newSheet, columnNameXref); } string pathNameColumnName = graphSet.PathNameColumnName; // ==================================== // build a new line graph for each one in the selected graphset // ==================================== foreach (LineGraphBE lineGraph in graphSet.LineGraphs) { BuildLineGraph(dataWorksheet, lineGraph, columnNameXref, pathNameColumnName); } // ==================================== // build a new XY graph for each one in the selected graphset // fyi: these were separated because they require slightly different config data structures // ==================================== foreach (XYGraphBE xyGraph in graphSet.XYGraphs) { BuildXYGraph(dataWorksheet, xyGraph, columnNameXref, pathNameColumnName); } // ==================================== // build a new bar graph for each one in the selected graphset // ==================================== foreach (BarGraphBE barGraph in graphSet.BarGraphs) { BuildBarGraph(dataWorksheet, barGraph, columnNameXref, pathNameColumnName); } // ==================================== // build a new histogram for each one in the selected graphset // ==================================== foreach (HistogramBE histogram in graphSet.Histograms) { BuildHistogram(dataWorksheet, histogram, columnNameXref, pathNameColumnName); } // save the workbook string pathName = GetCellValue <string>(dataWorksheet, graphSet.PathNameColumnName, 1, columnNameXref); string folderPathName = System.IO.Path.GetDirectoryName(logFilePathName); string fileName = System.IO.Path.GetFileNameWithoutExtension(logFilePathName); fileName = (!string.IsNullOrEmpty(pathName)) ? $"{fileName}_{pathName}" : fileName; fileName = System.IO.Path.ChangeExtension(fileName, @".xlsx"); string xlsFilePathName = System.IO.Path.Combine(folderPathName, fileName); workbook.SaveAs(xlsFilePathName, FileFormat.OpenXMLWorkbook); return(xlsFilePathName); }