/// <summary>
/// Get Metadata from Excel File
/// </summary>
/// <remarks>
/// This method will Extract Metadata from excel file and make a string Containing Matadata in Xml Format .
/// So this can be stored in database
/// </remarks>
public String GetMetadataFromExcelFile(string excelFilePath, MetaDataType elementType, string fldrMetadataTemplatePath)
{
// Step 1 : Open Excel File for reading
// Step2 : Get Mask and blank Metadata xml file from metadata template Folder
// Step3 : Update Blank Metadata file from metadata found in excel file. Metadata will be inserted in xml using
// Position and path definded in Mask file.
string RetVal = String.Empty;
//Step1. Open excel File
// Open excel and get first worksheet
this.DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
// Matadata starts from 5th Row
//this._MetadataStartRowIndexInExl = 4;
// step2: Get Mask and blank xml file from Metadata Template Folder
/////GetMetadataTemplateFilesForImport(elementType, fldrMetadataTemplatePath);
// Step3: Update metadata blank document using metadata found in metadata excel.
// Import will be done using Mask File. So check for Mask file
//if (this._MetadataMaskFilePath.Length > 0 && File.Exists(this._MetadataMaskFilePath))
//{
// if (this._MetadataFilePath.Length > 0 && File.Exists(this._MetadataFilePath))
// {
// int i = 0;
// XmlNode xn = null;
// XmlNodeList xnList = null;
// try
// {
// int temp = 0;
// // Load Blank xml Structure as Metadata file
// MetadataDOM = new XmlDocument();
// MetadataDOM.Load(this._MetadataFilePath);
// // Load Mask File
// MetadataMaskDOM.Load(this._MetadataMaskFilePath);
// // Iterate all child elements of mask file.
// for (i = 0; i < MetadataMaskDOM.DocumentElement.ChildNodes.Count; i++)
// {
// try
// {
// // Get Position and path information for Metadata
// string[] nodes = MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Path")[0].InnerXml.Split('/');
// string[] position = MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Position")[0].InnerXml.Split('/');
// if (position.Length < 1)
// {
// }
// else if (position.Length == 1)
// {
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// //dom.DocumentElement.InnerXml = this.rtbDataValue[temp].Text;
// MetadataDOM.DocumentElement.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// //dom.DocumentElement.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = this.rtbDataValue[temp].Text;
// MetadataDOM.DocumentElement.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// else if (position.Length >= 2)
// {
// // Get the postion of n is postion array
// int npos = position.Length;
// //--check which position has the n
// for (int l = 1; l < position.Length; l++)
// {
// if (position[l] == "n")
// {
// npos = l;
// break;
// }
// }
// // Select Root Node Like "Indicator_Info"
// // Xnlist contain list of all node under rootNode like indicator_Info
// xnList = MetadataDOM.DocumentElement.SelectNodes(nodes[1]);
// xnList = MetadataDOM.DocumentElement.SelectNodes(nodes[1]);
// // Handling for second Postion
// // If n is not at second Postion then then start from node first child under Indicator_Info(Document Element)
// if (position[1] != "n")
// {
// xn = xnList[Convert.ToInt32(position[1]) - 1];
// }
// else
// {
// xn = MetadataDOM.DocumentElement;
// }
// // Iterate inside this node. till we reach at n postion
// //--get the value of xn till the nth position
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// for (int j = 2; j < npos; j++)
// {
// XmlNodeList xnTempList;
// // Getting List of all child nodes .
// // If our path nodes array contain Indicator_Info,Row1,FLD_VAL,ROWData,temp1
// //In First Iteration we selcted all Fld_Val node under Row1
// // In SEcond Iteration we select AllRowDAta node under FLD_Val
// // Continue until j= postion of n .So we have all nodes inside nodelist for which n is applied
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// // Insert metadata value
// if (npos == position.Length)
// {
// //xn.InnerXml = this.rtbDataValue[temp].Text;
// xn.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// for (int j = 2; j < npos - 1; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// if (npos == position.Length)
// {
// xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// //--get the value of the nodes from the nth position
// if (npos < position.Length)
// {
// // Get all row data for which we have n in position
// xnList = xn.SelectNodes(nodes[npos]);
// //xnlist is value for total no of metadata paragraph required
// for (int o = 0; o < xnList.Count; o++)
// {
// try
// {
// xn = xnList[o];
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// // Handling for after n node
// for (int j = npos + 1; j < nodes.Length; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// // Get Value of each metadata
// // xn.InnerXml = SetCharacterEntities(this.rtbDataValue[temp].Text);
// xn.InnerXml = SetCharacterEntities(this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1).ToString());
// temp++;
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// for (int j = npos + 1; j < nodes.Length - 1; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// //xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = SetCharacterEntities(this.rtbDataValue[temp].Text);
// xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = SetCharacterEntities(this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1).ToString());
// temp++;
// }
// }
// catch (Exception ex)
// {
// }
// }
// }
// }
// }
// catch (Exception ex)
// {
// }
// }
// // Get Metadata Text in RetVal
// RetVal = MetadataDOM.InnerXml; //MetadataDOM.Save(xmlFile);
// DiExcel.Close();
// }
// catch (Exception ex)
// {
// RetVal = String.Empty;
// DiExcel.Close();
// }
// }
//}
return RetVal;
}
/// <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>
/// Export metadata to excel file
/// </summary>
/// <param name="ExcelFilePath">Excel File Path to be written as export data</param>
public void ExportMetaDataToExcel(string ExcelFilePath, MetadataElementType elementType, string elementName, string elementGId,int targetElementNID)
{
try
{
// --Open excel and get first worksheet
this.DiExcel = new DIExcel(ExcelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
// --Set Matadata Type in Cell 0,0
switch (elementType)
{
case MetadataElementType.Indicator:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("INDICATOR") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
case MetadataElementType.Area:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("AREA") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
case MetadataElementType.Source:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("SOURCE") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
default:
break;
}
// --Load data from xml to excel file
this.LoadData(elementType,targetElementNID);
//-- Save excel
this.DiExcel.Save();
}
catch (Exception ex)
{
}
}
/// <summary>
/// Insert Newly added records from DES to database
/// </summary>
/// <param name="sDestDBPath">Destination Database Path</param>
/// <param name="MsAccessPwd">Password</param>
/// <param name="sLng_Suffix">Lang suffix</param>
public void InsertNewRecords(String tempDESheetPath, string sDestDBPath, String MsAccessPwd)
{
DataView oDV = null;
DataRowView oDVRow;
string sDB_Prefix;
string sQry = "";
DIConnection DBConnection;
int i;
int j;
int iIndicatorNId = 0;
int iUnitNId = 0;
int[] iSubgroupNId = new int[this.DESheetInformation.Subgroup.Length];
int[] iIUSNId = new int[this.DESheetInformation.Subgroup.Length];
int iSectorNID = 0;
//int[] iClassNId = new int[Strings.Split(this.DESheetInformation.ClassGUID, "{}").Length];
int[] iClassNId = new int[this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()).Length];
int iParentNId;
//Used for Class and Source
bool bNewIUS = false;
Boolean bNewSector = false;
bool bNewClass = false;
//*** True if any of I / U / S is new
bool SectorGlobal = false;
// -- Create New DiConnection with db as sDestDBPath
DBConnection = new DIConnection(DIServerType.MsAccess, "", "", sDestDBPath, "", MsAccessPwd);
//--- Using DAL for getting Database default Prefix
sDB_Prefix = DBConnection.DIDataSetDefault();
String sLng_Suffix = DBConnection.DILanguageCodeDefault(sDB_Prefix);
DIQueries DBQueries = new DIQueries(sDB_Prefix, DBConnection.DILanguageCodeDefault(sDB_Prefix));
// Get SectorNId
iSectorNID = GetSectorNId(DBConnection, DBQueries, out bNewSector, out bNewSector);
//*** If Selected Class has not been inserted by export routine then insert new Class
string[] sClassGuid;
string[] sClassName;
string[] sClassGlobal;
iParentNId = iSectorNID;
//sClassGuid = Strings.Split(this.DESheetInformation.ClassGUID,"{}");
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sClassName = this.DESheetInformation.ClassName.Split("{}".ToCharArray());
sClassGlobal = this.DESheetInformation.ClassGlobal.Split("{}".ToCharArray());
//*** BugFix 29 Aug 2006 Do not try to insert class into database if indicator is directly associated with sector
if (this.DESheetInformation.ClassGUID != "" & this.DESheetInformation.ClassName != "")
{
for (i = 0; i <= sClassGuid.Length - 1; i++)
{
iClassNId[i] = GetClassNId(DBConnection, DBQueries, out bNewClass, sClassGuid[i].ToString(), SectorGlobal, iParentNId, sClassName[i].ToString());
iParentNId = Convert.ToInt32(iClassNId[i]);
}
}
//*** Indicator_NId
sQry = DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DESheetInformation.IndicatorGUID + "'", FieldSelection.NId);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iIndicatorNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Indicator.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, "", false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Unit_NId
//-- Using DAL for getting and executing query for getting unit NId
sQry = DBQueries.Calculates.GetUnitNIdByGId(this.DESheetInformation.UnitGUID);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iUnitNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Unit.Insert.InsertUnit(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Unit.Replace("'", "''"), this.DESheetInformation.UnitGUID, false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Subgroup_Val_NId
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.Calculates.GetSubgroupValNIdByGId(this.DESheetInformation.SubgroupGUID[i]);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSubgroupNId[i] == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DBQueries.Calculates.InsertSubgroupVal(this.DESheetInformation.Subgroup[0].Replace("'", "''"), this.DESheetInformation.SubgroupGUID[0], false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
//*** IUSNId
if (bNewIUS)
{
//*** Insert New IUS
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
//-- Get query from DAL
sQry = DBQueries.Calculates.InsertNIdsInIUSTable(iIndicatorNId, iUnitNId, iSubgroupNId[i]);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create Relationship with IndicatorClassification
string sICGuid;
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sICGuid = "'" + this.DESheetInformation.SectorGUID + "'";
for (j = 0; j <= sClassGuid.Length - 1; j++)
{
sICGuid += ",'" + sClassGuid[j].ToString() + "'";
}
sQry = DBQueries.Calculates.GetICNIdByGId(sICGuid);
//-- Get Dataview using DAL
oDV = DBConnection.ExecuteDataTable(sQry).DefaultView;
foreach (DataRowView oDVRow1 in oDV)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(Convert.ToInt32(oDVRow1["IC_NId"]), iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
else if (bNewSector || bNewClass)
{
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.IUS.GetIUSByI_U_S(iIndicatorNId.ToString(), iUnitNId.ToString(), iSubgroupNId[i].ToString());
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (bNewSector)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSectorNID, iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
if (bNewClass)
{
for (j = 0; j <= iClassNId.Length - 1; j++)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iClassNId[j], iIUSNId[i]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
int Index = 0;
foreach (int SubgroupNId in iSubgroupNId)
{
//-- Using DAL Query for getting IUSNId
sQry = DBQueries.Calculates.GetIUSNIdByIUS(iIndicatorNId, iUnitNId, SubgroupNId);
iIUSNId[Index] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
Index += 1;
}
}
string sTimePeriod = string.Empty;
int iTimePeriod_NId = 0;
Hashtable oTimePeriod = new Hashtable();
string sAreaId = string.Empty;
int iArea_NId = 0;
Hashtable oAreaId = new Hashtable();
string sDataValue = string.Empty;
System.DateTime dtStartDate = System.DateTime.Now;
System.DateTime dtEndDate = System.DateTime.Now;
string sSubgroup = string.Empty;
string sSource = string.Empty;
int iSource_NId = 0;
Hashtable oSource = new Hashtable();
string sFootNote = "";
int iFootNote_NId = -1;
Hashtable oFootNote = new Hashtable();
string sData_Denominator = "";
IWorksheet DataSheet;
DIDatabase DIdatabase = null;
this.DIExcel = new DIExcel(tempDESheetPath);
// this.DIExcel = new DIExcel(this.TempLogFilePath);
DataSheet = DIExcel.GetWorksheet(0);
{
//For each record in excel sheet '*** Get TimePeriod_NId, Area_NId, Data Value, Subgroup, SourceNId
for (i = 10; i <= miCtrFillTo; i++)
{
try
{
if (DataSheet.Cells[i, 0].Value.ToString() == "" || DataSheet.Cells[i, 1].Value.ToString() == "" || DataSheet.Cells[i, 3].Value.ToString() == "" || DataSheet.Cells[i, 4].Value.ToString() == "" || DataSheet.Cells[i, 5].Value.ToString() == "")
{
}
//*** If TimePeriod, AreaID, DataValue,Subgroup, Source is blank leave this record
else
{
//*** TimePeriod
sTimePeriod = DataSheet.Cells[i, 0].Value.ToString();
iTimePeriod_NId = -1;
if (CheckDate(sTimePeriod))
{
//*** Check for Valid TimeFormat allowed in DE
if (oTimePeriod.ContainsKey(sTimePeriod))
{
iTimePeriod_NId = Convert.ToInt32(oTimePeriod[sTimePeriod]);
}
else
{
sQry = DBQueries.Calculates.GetTimeperiodNIdByTimePeriod(sTimePeriod);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iTimePeriod_NId == 0)
{
// Using DAL for getting and executing Query for inserting timeperiod
sQry = DI_LibDAL.Queries.Timeperiod.Insert.InsertTimeperiod(sDB_Prefix, sTimePeriod);
DBConnection.ExecuteNonQuery(sQry);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oTimePeriod.Add(sTimePeriod, iTimePeriod_NId);
}
SetDate(sTimePeriod, ref dtStartDate, ref dtEndDate);
}
else
{
iTimePeriod_NId = -1;
}
//*** Area
sAreaId = DataSheet.Cells[i, 1].Value.ToString();
iArea_NId = -1;
if (oAreaId.ContainsKey(sAreaId))
{
iArea_NId = Convert.ToInt32(oAreaId[sAreaId]);
}
else
{
//-- Using DAL for getting AreaNId Using AreaID
sQry = DBQueries.Calculates.GetAreaNIdByAreaID(sAreaId);
iArea_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iArea_NId == 0)
{
oAreaId.Add(sAreaId, -1);
}
//*** Don't add New areas to database
else
{
oAreaId.Add(sAreaId, iArea_NId);
}
}
//*** Data value
sDataValue = DataSheet.Cells[i, 3].Value.ToString();
//*** Subgroup
sSubgroup = DataSheet.Cells[i, 4].Value.ToString();
//*** Source
sSource = DI_LibBAL.Utility.DICommon.RemoveQuotes(DataSheet.Cells[i, 5].Value.ToString());
iSource_NId = -1;
if (oSource.ContainsKey(sSource))
{
iSource_NId = Convert.ToInt32(oSource[sSource]);
}
else
{
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSource + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
string[] sSourceArr;
sSourceArr = sSource.Split('_');
//Publisher_ISOCode_Year: Parent= Publisher; Child= Abbr_Year
if (sSourceArr.Length >= 2)
{
//*** Insert Parent
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSourceArr[0] + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSourceArr[0], Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
//'sQry = "INSERT INTO " & sDB_Prefix & "Indicator_Classifications_IUS" & " (IC_NId,IUSNId) VALUES (" & iSource_NId & "," & iIUSNId(j) & ")"
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
// Using DAL for inserting Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
//' oDestDB.ExecuteNonQuery(sQry)
catch (Exception ex)
{
}
}
}
else
{
//iSource_NId = -1
if (sSource.Trim() != "")
{
//*** Insert Parent as "Global"
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, "Global", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global", Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global_" + sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
//*** If New IUS was created then Create Relationship with Source
if (bNewIUS)
{
// using DAL query for getting parentNId
sQry = DBQueries.Calculates.GetICParentNIdByICNId(iSource_NId, "SR");
iParentNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iParentNId != 0)
{
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iParentNId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
//*** database maintains unique composite key for IC_NId and IUSNId - this will prevent duplicate entry if any
}
}
}
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
}
}
oSource.Add(sSource, iSource_NId);
}
try
{
sFootNote = DataSheet.Cells[i, 6].Value.ToString();
}
catch (Exception ex)
{
sFootNote = "";
}
iFootNote_NId = -1;
if (sFootNote != "")
{
if (oFootNote.ContainsKey(sFootNote))
{
iFootNote_NId = Convert.ToInt32(oFootNote[sFootNote]);
}
else
{
sQry = DBQueries.Calculates.GetFootNoteNIdByFootNote(Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(sFootNote)));
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iFootNote_NId == 0)
{
sQry = DI_LibDAL.Queries.Footnote.Insert.InsertFootnote(sDB_Prefix, sLng_Suffix, sFootNote, Guid.NewGuid().ToString());
DBConnection.ExecuteNonQuery(sQry);
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oFootNote.Add(sFootNote, iFootNote_NId);
}
}
try
{
if (Utility.DICommon.IsNumeric(DataSheet.Cells[i, 7].Value.ToString(), System.Threading.Thread.CurrentThread.CurrentCulture))
{
sData_Denominator = DataSheet.Cells[i, 7].Value.ToString() + ",";
}
else
{
sData_Denominator = "";
}
}
catch (Exception ex)
{
sData_Denominator = "";
}
if (iIUSNId[0] == -1 | iTimePeriod_NId == -1 | iArea_NId == -1 | iSource_NId == -1 | sDataValue == "")
{
}
else
{
if (this.ApplicationWizardType == WizardType.HundredMinus)
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "", iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
else
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", sData_Denominator, iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
}
catch (Exception ex)
{
}
}
// Update Indicator Unit subgroupNId
DIdatabase = new DIDatabase(DBConnection, DBQueries);
DIdatabase.UpdateIndicatorUnitSubgroupNIDsInData();
DBConnection.Dispose();
}
//1.Time - 2.AreaId - 3.AreaName - 4.DataValue - 5.Subgroup - 6.Source - 7.Footnote - 8.Denominator - 12.SubgroupNId
//DataSheet = null;
if ((oDV != null))
{
oDV.Dispose();
oDV = null;
}
}
/// <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 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>
/// 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 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 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>
/// 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;
}
private int GetElementNId(string excelFilePath, MetaDataType elementType, int elementNameRowIndex)
{
int RetVal = 0;
string ElementName = string.Empty;
string ElementGId = string.Empty;
SpreadsheetGear.IWorksheet MetadataSheet;
IndicatorBuilder IndBuilder = null;
IndicatorInfo IndInfo = new IndicatorInfo();
DataTable Table = null;
int GIdRowIndex = elementNameRowIndex + 1;
//-- Open excel File and get first worksheet
DIExcel DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
//-- Get Element GId
ElementGId = DiExcel.GetCellValue(ElementSheetIndex, GIdRowIndex, ElementGIdColumnIndex, GIdRowIndex, ElementGIdColumnIndex);
//-- Get Element Name
ElementName = DiExcel.GetCellValue(ElementSheetIndex, elementNameRowIndex, ElementNameColumnIndex, elementNameRowIndex, ElementNameColumnIndex);
//-- Get GId By Name if GID is blank in Excel File
if (!string.IsNullOrEmpty(ElementGId) || !string.IsNullOrEmpty(ElementName))
{
switch (elementType)
{
case MetaDataType.Indicator:
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
RetVal = IndBuilder.GetIndicatorNid(ElementGId, ElementName);
break;
case MetaDataType.Map:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.Area.GetAreaMapMetadataByName(string.IsNullOrEmpty(ElementGId) ? ElementName : ElementGId));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][Area_Map_Metadata.LayerNId]);
}
break;
case MetaDataType.Source:
case MetaDataType.Sector:
case MetaDataType.Goal:
case MetaDataType.CF:
case MetaDataType.Theme:
case MetaDataType.Institution:
case MetaDataType.Convention:
case MetaDataType.IndicatorClassification:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.GId, "'" + ElementGId + "'", FieldSelection.Heavy));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][IndicatorClassifications.ICNId]);
}
break;
default:
break;
}
}
return RetVal;
}
private Dictionary<int, string> GetMetadataXmlFromExcelFile(string excelFilePath, MetaDataType elementType, string fldrMetadataTemplatePath)
{
Dictionary<int, string> RetVal = new Dictionary<int, string>();
string ElementGId = string.Empty;
int ElementNId = 0;
SpreadsheetGear.IWorksheet MetadataSheet;
int CategoryRowStartIndex = 0;
string MetadataText = string.Empty;
//-- Open excel File and get first worksheet
DIExcel DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
//-- Get ElementGId
ElementGId = this.GetElementGId(excelFilePath, elementType, MetaDataBuilder.ElementNameRowIndex);
ElementNId = this.GetElementNId(excelFilePath, elementType, MetaDataBuilder.ElementNameRowIndex);
CategoryRowStartIndex = MetaDataBuilder.ElementGIdRowIndex + 1;
try
{
CategoryRowStartIndex = CategoryRowStartIndex > 3 ? CategoryRowStartIndex : 4;
MetadataText = this.GetElementMetadata(elementType, ElementGId, ElementNId, DiExcel, CategoryRowStartIndex);
RetVal.Add(ElementNId, MetadataText);
}
catch (Exception)
{
throw;
}
return RetVal;
}
private void FreezePane(DIExcel excelObj, int sheetIndex)
{
SpreadsheetGear.IWorksheet Worksheet = excelObj.GetWorksheet(sheetIndex);
// Split after column "B" (ScrollColumn is zero based).
Worksheet.WindowInfo.SplitColumns = 2;
// Split after row 5 (ScrollRow is zero based).
Worksheet.WindowInfo.SplitRows = 5;
// Freeze the panes.
Worksheet.WindowInfo.FreezePanes = true;
}
