/// <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>
/// 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 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 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>
/// Fill Data into Data entry spreadsheet
/// </summary>
/// <param name="parentPanelName">Name Of the parent Panel of client application in which DES will be shown </param>
/// <remarks> This panel name will be used in raising event at end to display this DES generated</remarks>
public void FillDESheetData(string parentPanelName)
{
IWorksheet DataSheet;
int i;
int iCtrFillTo = 11;
///Copy Data entry template file to temp location
if (File.Exists(DESFilePath))
{
File.Copy(DESFilePath, TempDESFilePath, true);
System.IO.File.SetAttributes(TempDESFilePath, FileAttributes.Normal);
}
// Open excel workbook
DIExcel = new DIExcel(TempDESFilePath);
//Get Data Entry sheet
DataSheet = DIExcel.GetWorksheet(0);
DataSheet.Name = LangStrings.DATA;
// if msDataArray has some Value then Reset ictrFillTo value
//int iCtrFillTo = 11 + msDataArray.GetLength(0) - 1;
if (msDataArray.GetLength(0) > 0)
{
iCtrFillTo = 11 + msDataArray.GetLength(0) - 1;
}
//*** Set Data
DataSheet.Cells[0, 0].Value = LangStrings.DEVINFO_DATA_ENTRY_SPREADSHEET;
DataSheet.Cells[2, 0].Value = msClassification[SelectedClassificationIndex].ToString();
DataSheet.Cells[3, 0].Value = LangStrings.IC_CLASS;
DataSheet.Cells[4, 0].Value = LangStrings.INDICATOR;
DataSheet.Cells[6, 0].Value = LangStrings.UNIT;
DataSheet.Cells[6, 6].Value = LangStrings.DECIMALPLACES;
DataSheet.Cells[8, 0].Value = LangStrings.TIME;
DataSheet.Cells[8, 1].Value = LangStrings.AREAID;
DataSheet.Cells[8, 2].Value = LangStrings.AREANAME;
DataSheet.Cells[8, 3].Value = LangStrings.DATAVALUE;
DataSheet.Cells[8, 4].Value = LangStrings.SUBGROUP;
DataSheet.Cells[8, 5].Value = LangStrings.SOURCE;
DataSheet.Cells[8, 6].Value = LangStrings.FOOTNOTES;
DataSheet.Cells[8, 7].Value = LangStrings.DENOMINATOR;
DataSheet.Cells[8, 11].Value = "";
DataSheet.Cells[2, 1].Value = DESheetInformation.SectorName;
DataSheet.Cells[2, 11].Value = DESheetInformation.SectorGUID;
DataSheet.Cells[3, 1].Value = DESheetInformation.ClassName;
DataSheet.Cells[3, 11].Value = DESheetInformation.ClassGUID;
DataSheet.Cells[4, 1].Value = DESheetInformation.Indicator;// DataEntrySheetInfo.Indicator;
DataSheet.Cells[4, 11].Value = DESheetInformation.IndicatorGUID;// DESIndicatorGUId;
DataSheet.Cells[6, 1].Value = DESheetInformation.Unit;
DataSheet.Cells[6, 11].Value = DESheetInformation.IndicatorGUID;// DESUnitGUId;
DataSheet.Cells[6, 7].Value = DESheetInformation.Decimals;
//*** Logic for showing all records irrespective of blank data value
//DataSheet.Range[DataSheet.Cells[11, 1], DataSheet.Cells[iCtrFillTo, 6]].Value = msDataArray;
//If Data Array has Values then show it in Datasheet
if (msDataArray.GetLength(0) > 0)
{
DataSheet.Range[10, 0, iCtrFillTo - 1, 5].Value = msDataArray;
}
//DataSheet.Range[10, 0, iCtrFillTo - 1, 5].Value = msDataArray;
miCtrFillTo = iCtrFillTo;
//*** Special Handling in case of 100 Minus, SubTotal and TransformUnit (multiple subgroup)
if ((ApplicationWizardType == WizardType.HundredMinus | ApplicationWizardType == WizardType.SubTotal | ApplicationWizardType == WizardType.TransformUnit) && DESheetInformation.Subgroup.Length > 1)
{
string sSubgroup;
for (i = 10; i <= miCtrFillTo - 1; i++)
{
//sSubgroup = DataSheet.Cells[i, 5].value;
sSubgroup = DataSheet.Cells[i, 4].Text;
if ((sSubgroup != null))
// DataSheet.Cells[i, 12] = moDESheetInfo.SubgroupGUID(Array.IndexOf(moDESheetInfo.Subgroup, sSubgroup));
DataSheet.Cells[i, 11].Value = DESheetInformation.SubgroupGUID[Array.IndexOf(DESheetInformation.Subgroup, sSubgroup)].ToString();
//DataSheet.Cells[i, 11].Value = DESSubgroupGUId[Array.IndexOf(DESSubgroupText, sSubgroup)];
}
}
else
{
if ( DESheetInformation.Subgroup.Length > 1)
{
DataSheet.Range[10, 11, miCtrFillTo - 1, 11].Value = DESheetInformation.SubgroupGUID[0];// moDESheetInfo.SubgroupGUID(0);
}
}
DataSheet.Range[10, 0, miCtrFillTo - 1, 5].Font.Name = FontName;
DataSheet.Range[10, 0, miCtrFillTo - 1, 5].Font.Size = FontSize;
try
{
DataSheet.Range[2, 0, miCtrFillTo - 1, 5].Columns.AutoFit();
}
catch (Exception ex)
{
throw ex;
}
DIExcel.Save();
this.DESLastDataRowIndex = miCtrFillTo;
RaiseEventStep5Completed(parentPanelName);
}
/// <summary>
/// Generates the .XLS presentation file in specified folder on the basis of current Map object settings.
/// </summary>
/// <param name="presentationOutputFolderPath">Folder Path where .xls presentation is created.</param>
/// <param name="maxHeightInPixelToFitAll">(optional) maximun width (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <param name="maxWidthInPixelToFitAll">(optional) maximun height (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <returns>Returns the full file path of .xls presentation generated.</returns>
public string GeneratePresentation(string presentationOutputFolderPath, int maxWidthInPixelToFitAll, int maxHeightInPixelToFitAll)
{
string RetVal = string.Empty;
if (!(string.IsNullOrEmpty(presentationOutputFolderPath)))
{
float MapOriginalWidth = this.m_Width;
float MapOriginalHeight = this.m_Height;
// Getting the xls sheet names in desired language.
string MapSheetName = DILanguage.GetLanguageString("MAP");
string MapDataSheetName = DILanguage.GetLanguageString("DATA");
string SourceSheetName = DILanguage.GetLanguageString("SOURCECOMMON");
// Set path for presentation and png images
string FileSuffix = DateTime.Now.Ticks.ToString();
// Set Image file extension(.png or .emf) on he basis of UserPreference property.
string ImageExtention = string.Empty;
if (this.UserPreference.General.ShowExcel)
{
//If ShowExcel is True, then User can see presentation in MS Excel object
ImageExtention = "emf";
}
else
{
// Png format is supported in SpreadsheetGear (.Emf NOT supported)
//If ShowExcel is False, then presentation will be opened in Spreadsheet Gear, so Use Png format.
ImageExtention = "png";
}
string PresentationPath = presentationOutputFolderPath + @"\Map" + FileSuffix + ".xls";
string TitleImgPath = Path.Combine(presentationOutputFolderPath, "Title" + FileSuffix + "." + ImageExtention); //"Title"
string MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + "." + ImageExtention);
string DisclaimerImgPath = Path.Combine(presentationOutputFolderPath, "Disclaimer" + FileSuffix + "." + ImageExtention);
string InsetFilePrefix = string.Empty;
DIExcel ExcelApp = new DIExcel();
Image img;
//- Calculate pixelToPoint factor as SpreadsheetGear uses point unit instead of Pixel unit for measuring Length and width
Graphics graphics = Graphics.FromImage(new System.Drawing.Bitmap(1, 1));
double PixcelToPointFactor = 72 / graphics.DpiX; //1 inch = 72 points
Size TitleSize = new Size();
Size LegendSize = new Size();
int MaxLegendHeight = 20;
int MaxLegendWidth = 30;
int RowIndex = 0;
int SheetIndex;
double CellHeight = ExcelApp.GetCellHeight(0, 0, 0); // default cell height from default sheet.
double CellWidth = ExcelApp.GetColumnWidth(0, 0, 0, 0, 1); // Default cell width from default sheet.
int SingleCellWidthInPixel = (int)(ExcelApp.GetWorksheet(0).Cells[0, 0].Width / PixcelToPointFactor);
if (maxWidthInPixelToFitAll > 0 && maxHeightInPixelToFitAll > 0)
{
//- Offset page Margins (default left margin -> 0.7" & right margin -> 1.4") and (default Top & bottom margin -> 0.75")
maxWidthInPixelToFitAll = maxWidthInPixelToFitAll - (int)(2.1 * graphics.DpiX);
maxHeightInPixelToFitAll = maxHeightInPixelToFitAll - (int)(1.5 * graphics.DpiY);
//- Calculating maximum Height & width of presentation's composite contents in Points
//maxWidthInPixelToFitAll = (int)(maxWidthInPixelToFitAll * PixcelToPointFactor);
//maxHeightInPixelToFitAll = (int)(maxHeightInPixelToFitAll * PixcelToPointFactor);
// Offset margin of first column width.
maxWidthInPixelToFitAll -= SingleCellWidthInPixel;
//- Set final Map width so that Map can be accomodated within maximum width specified.
this.m_Width = (float)maxWidthInPixelToFitAll - 2;
//- offset width of Inset images (if present)/
//- generally inset image is 15% of total Map and starts after a gap of 1/2 column width.
if (this.Insets.Count > 0)
{
float InsetImageWidth = MapOriginalWidth; //- Default
if (this.Insets[0].InsetImage != null)
{
InsetImageWidth = (float)(this.Insets[0].InsetImage.Width);
}
this.m_Width -= (InsetImageWidth * 0.1F) + (float)(SingleCellWidthInPixel);
}
//- Adjusting Map height in proportion with CurrentExtent's Width to height ratio.
this.m_Height = this.m_Width * Math.Min(4 / 3, (this.m_CurrentExtent.Height / this.m_CurrentExtent.Width)) + 3;
}
try
{
if (File.Exists(PresentationPath))
{
File.Delete(PresentationPath);
}
ExcelApp.SaveAs(PresentationPath);
ExcelApp.RenameWorkSheet(0, MapSheetName); //language handling Rename first(default) sheet
ExcelApp.Save();
//********************************* WorkSheet 1 - Map Images**************************************************
this.ClearSelection(); //-- Clear any Layer selection (if any)
//Generate the first sheet which is the Map Image for MRD data.
//*** generate title image in temp folder
TitleSize = this.GetTitleImage(Path.GetDirectoryName(TitleImgPath), Path.GetFileNameWithoutExtension(TitleImgPath), ImageExtention);
//*** Generate Themes Legend Image in temp folder
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
//Generate the Legend image, and calculating image maximum height & width.
LegendSize = _ThemeTemp.GetLegendImage(presentationOutputFolderPath, _ThemeTemp.ID + FileSuffix, ImageExtention, this.PointShapeIncluded, this.TemplateStyle.Legends.ShowCaption, this.TemplateStyle.Legends.ShowRange, this.TemplateStyle.Legends.ShowCount, this.TemplateStyle.Legends.ShowMissingLegend);
MaxLegendWidth = Math.Max(MaxLegendWidth, (int)(LegendSize.Width * PixcelToPointFactor));
MaxLegendHeight = Math.Max(MaxLegendHeight, (int)(LegendSize.Height * PixcelToPointFactor));
}
}
//*** generate discalimer (common) image in temp folder
this.GetDisclaimerImage(Path.GetDirectoryName(DisclaimerImgPath), Path.GetFileNameWithoutExtension(DisclaimerImgPath), ImageExtention, maxWidthInPixelToFitAll);
int ImageRowPosition = 0; //Holds the row position for Image to be pasted.
//---Insert Sheet for Map of most recent data.
SheetIndex = ExcelApp.GetSheetIndex(MapSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
if (this._showTimePeriods || this._showAreaLevels)
{
//*** Set map rendering info for most recent time period for all level
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
else
{
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
ExcelApp.Save();
// Set Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
// Converting Image size from Pixel to Points while pasting.
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Set Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
// Set Insets Image
int InsetCtr;
CellWidth = 70; //TODO: Remove hardcoding calculate column width right.
int RowOffset = ImageRowPosition;
int ColOffset = (int)(this.Width / CellWidth) + 2;
InsetFilePrefix = "Inset" + FileSuffix;
for (InsetCtr = 0; InsetCtr <= this.Insets.Count - 1; InsetCtr++)
{
Inset TempInset = this.Insets[InsetCtr];
string InsetImgFilePath = Path.Combine(presentationOutputFolderPath, InsetFilePrefix + InsetCtr + "." + ImageExtention);
if (TempInset.InsetImage != null)
{
TempInset.InsetImage.Save(InsetImgFilePath);
}
else
{
//Extract new "png" image of inset in temp folder.
this.GetInsetImage(presentationOutputFolderPath, ImageExtention, (int)this.Width, (int)this.Height, InsetCtr, InsetFilePrefix);
}
if (TempInset.Visible)
{
//*** Extract InsetName png.
TempInset.GetInsetName(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix, ImageExtention);
img = Image.FromFile(Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention));
ExcelApp.PasteImage(SheetIndex, Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention), ColOffset, RowOffset, img.Width, img.Height);
//*** use previously generated inset images in temp folder
RowOffset += img.Height / (int)CellHeight;
img = Image.FromFile(InsetImgFilePath);
ExcelApp.PasteImage(SheetIndex, InsetImgFilePath, ColOffset, RowOffset, (img.Width * 0.09), (img.Height * 0.09));
RowOffset += (int)(img.Width * 0.09) / (int)CellHeight + 2;
img.Dispose();
}
}
//Set Theme Legend Images
ImageRowPosition += (int)((this.m_Height * PixcelToPointFactor) / (int)CellHeight) + 2; //Increment row position by rows covered by Image height.
int p = 1; //p is the column position of the Legend Images (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); //(double)LegendSize.Width, (double)LegendSize.Height);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
DataView _DVSources = this.GetUniqueSourceList(); //getting unique sources in DataView
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
//- Wrap Cell for source
int EndingColumnToMerge = maxWidthInPixelToFitAll / SingleCellWidthInPixel;
ExcelApp.MergeCells(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge);
ExcelApp.WrapText(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge, true);
}
// Set Disclaimer Image
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
ExcelApp.Save();
//--Now, generate sheets for each timePeriod selected.
if (this._showTimePeriods)
{
DataView DVTimePeriods = this.GetTimePeriodsForThemes();
for (byte i = 0; i <= DVTimePeriods.Count - 1; i++)
{
//Restrict Excel Worksheet name to 31 characters
string sheetName = DVTimePeriods[i][Timeperiods.TimePeriod].ToString();
if (sheetName.Length > 31)
{
sheetName.Substring(0, 31);
}
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(sheetName);
SheetIndex = ExcelApp.GetSheetIndex(sheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo((int)(DVTimePeriods[i][Timeperiods.TimePeriodNId]), -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//*** Add Sources
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
}
else if (this.ShowAreaLevels) //If only AreaLevel is selected.
{
DataView DVAreaLevels = this.GetAreaLevels();
for (byte i = 0; i <= DVAreaLevels.Count - 1; i++)
{
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
SheetIndex = ExcelApp.GetSheetIndex(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, (int)(DVAreaLevels[i][Area_Level.AreaLevel]), iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, SourceSheetName);
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
ExcelApp.Save();
}
//********************************* WorkSheet - Map Data*********************************************
//TODO Use TablePresentation object logic
ExcelApp.InsertWorkSheet(MapDataSheetName); //insert DataSheet.
SheetIndex = ExcelApp.GetSheetIndex(MapDataSheetName);
ExcelApp.ActivateSheet(SheetIndex);
DataTable _DTDataSheet = GetDataSheetView(this.PresentationData); //Getting Map Data or DataSheet.
string[,] arrDataView = new string[_DTDataSheet.Rows.Count, _DTDataSheet.Columns.Count - 1]; //Array Columns will be 1 less than dv, to ignore extra footNote column
// --Adding Column names in desired language , only one time, at row 1.
ExcelApp.SetCellValue(SheetIndex, 1, 0, DILanguage.GetLanguageString("TIMEPERIOD")); //Timeperiods.TimePeriod
ExcelApp.SetCellValue(SheetIndex, 1, 1, DILanguage.GetLanguageString("AREAID")); //Area.AreaID
ExcelApp.SetCellValue(SheetIndex, 1, 2, DILanguage.GetLanguageString("AREANAME")); //Area.AreaName
ExcelApp.SetCellValue(SheetIndex, 1, 3, DILanguage.GetLanguageString("INDICATOR")); //Indicator.IndicatorName
ExcelApp.SetCellValue(SheetIndex, 1, 4, DILanguage.GetLanguageString("DATA")); //Data.DataValue
ExcelApp.SetCellValue(SheetIndex, 1, 5, DILanguage.GetLanguageString("UNIT")); //Unit.UnitName
ExcelApp.SetCellValue(SheetIndex, 1, 6, DILanguage.GetLanguageString("SUBGROUP")); //SubgroupVals.SubgroupVal
ExcelApp.SetCellValue(SheetIndex, 1, 7, DILanguage.GetLanguageString("SOURCECOMMON")); //IndicatorClassifications.ICName
int col = 8; //Columnn number 8th
foreach (string MDColumn in this._MDIndicatorFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataIndicator.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDAreaFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataArea.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDSourceFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataSource.Columns[MDColumn].Caption);
col++;
}
}
// ---Columns added...
RowIndex = 2; //Start displaying Map Data from Row - 2nd
foreach (DataRow DRowDTDataSheet in _DTDataSheet.Rows)
{
for (int Columns = 0; Columns < _DTDataSheet.Columns.Count; Columns++)
{
//Assign cellvalue with data in DataTable.
ExcelApp.SetCellValue(SheetIndex, RowIndex, Columns, DRowDTDataSheet[Columns].ToString());
if (_DTDataSheet.Columns[Columns].ColumnName == Data.DataValue)
{
//If Column id = "DataValue" then add footNotes As-Comment if present.
if (DRowDTDataSheet[FootNotes.FootNote].ToString() != "")
{
ExcelApp.AddComment(SheetIndex, RowIndex, Columns, DRowDTDataSheet[FootNotes.FootNote].ToString(), true);
}
}
}
RowIndex++;
}
ExcelApp.AutoFitColumns(SheetIndex, 1, 0, _DTDataSheet.Rows.Count - 1, _DTDataSheet.Columns.Count - 1); //Auto fitting columns width.
ExcelApp.Save();
//********************************* WorkSheet - Sources**************************************************
ExcelApp.InsertWorkSheet(SourceSheetName);
SheetIndex = ExcelApp.GetSheetIndex(SourceSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.SetCellValue(SheetIndex, 0, 0, SourceSheetName); //Cell value = "Source" in desired language.
ExcelApp.SetCellValue(SheetIndex, 0, 2, DIConnection.ConnectionStringParameters.DbName);
for (byte i = 0; i <= _DVSources.Count - 1; i++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, i + 3, 0, _DVSources[i]["IC_Name"]);
ExcelApp.AutoFitColumn(SheetIndex, i);
}
ExcelApp.Save();
//********************************* WorkSheet - Keyword**************************************************
ExcelApp.InsertWorkSheet(Presentation.KEYWORD_WORKSHEET_NAME);
ExcelApp.ActivateSheet(ExcelApp.GetSheetIndex(MapSheetName)); //Set first sheet as ACTIVE sheet..
ExcelApp.Save();
RetVal = PresentationPath;
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
ExcelApp.Close();
if (graphics != null)
{
graphics.Dispose();
}
this.m_Width = MapOriginalWidth;
this.m_Height = MapOriginalHeight;
}
}
return RetVal;
}
/// <summary>
/// Insert Xml serialized presentation text in a worksheet and append it to presenatation workbook
/// </summary>
/// <param name="Presentation">Preesnataion Class Instance TablePresentation / Map</param>
/// <param name="PresentationPath">Full path of presentation file</param>
/// <param name="presentationType">PresentationType enum value. Table / Graph / Map</param>
public static void InsertSelectionSheet(object Presentation, string PresentationPath, PresentationType presentationType)
{
string SerializedText = string.Empty;
DIExcel DIExcel = new DIExcel(PresentationPath);
DIExcel.InsertWorkSheet(SELECTION_WORKSHEET_NAME);
int SelectionWorksheetIndex = DIExcel.GetSheetIndex(SELECTION_WORKSHEET_NAME);
//-- Upadte the user selection GIds before inserting them in selections sheet
//-- Retrieve serialized text
switch (presentationType)
{
case PresentationType.Table:
SerializedText = ((TablePresentation)Presentation).GetSerializedText(true);
break;
case PresentationType.Graph:
if (((GraphPresentation)Presentation).TablePresentation.UserPreference.General.ShowExcel)
{
SelectionWorksheetIndex -= 1;
}
SerializedText = ((GraphPresentation)Presentation).GetSerializedText();
break;
case PresentationType.Map:
SerializedText = ((Map.Map)Presentation).GetSerializedText(true);
break;
}
//Insert Serialized Text into worksheet
// Single excel cell can hold at the most 32000 characters
// If length of serialized text is greater than 32000 char then break them and place them in multiple cells
//TODO Use this logic for Table and graph also and update GetSerializedPresentationText() function accordingly
if (SerializedText.Length > 32000)
{
int i = 0;
//Calculating and Iterating number of 32000 Characters slots in Text
//-- Set First column format type as "TEXT"
DIExcel.SetColumnFormatType("A:A", SelectionWorksheetIndex, SpreadsheetGear.NumberFormatType.Text);
while (i < Math.Floor((double)(SerializedText.Length / 32000)))
{
//'Adding next 32000 Charcters each time at i row.
if (i == 160)
{
}
DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i, 32000));
i += 1;
}
DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i));
}
else
{
DIExcel.SetCellValue(SelectionWorksheetIndex, 0, 0, SerializedText);
}
//-- Hide the selection sheet.
DIExcel.HideWorksheet(SELECTION_WORKSHEET_NAME);
DIExcel.ActiveSheetIndex = 0;
DIExcel.Save();
DIExcel.Close();
}
