/// <summary>
/// Create IC Convention excelFile
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.CONVENTION]);
// -- Generate Convention sheet
ICSheetGenerator ICSheet = new ICSheetGenerator();
ICSheet.GenerateICSheet(ref excelFile, sheetNo, ICType.Convention);
}
/// <summary>
/// Create IC Institution Excelsheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.INSTITUTION]);
// -- Generate Institution sheet contents
ICSheetGenerator ICSheet = new ICSheetGenerator();
ICSheet.GenerateICSheet(ref excelFile, sheetNo, ICType.Institution);
}
/// <summary>
/// Create IUS Linked To Multiple Classifications Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = base.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSLinkedTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
}
/// <summary>
/// Create IC Source Excelsheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.SOURCE]);
// -- Generate Source sheet content
ICSheetGenerator ICSheet = new ICSheetGenerator();
ICSheet.GenerateICSheet(ref excelFile, sheetNo, ICType.Source);
}
/// <summary>
/// Create FootNote Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]);
excelFile.GetCellFont(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get FootNotes Data TAble.
Table = this.GetFootNoteTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Footnotes.FootNotesDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesNameColIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex, true);
excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true);
// -- Set Cell Borders
}
/// <summary>
/// Create TimePeriod without DataValue Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, Constants.SheetsNames.TimeperiodsWithoutData);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.SheetsNames.TimeperiodsWithoutData);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get TimePeriod Data TAble.
Table = this.GetTimeperiodWithoutDataTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, true);
}
/// <summary>
/// Create Unit Sheet of Summary Report
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Unit Sheet
int SheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.UNIT]);
DataTable UnitTable = null;
// -- sheet content
excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.UNIT]);
excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Unit Table Filled
UnitTable = this.GetUnitTable();
// -- Set SNo into TAble
this.SetSNoIntoTableColumn(ref UnitTable);
// -- Set Value To Excel Sheet
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, UnitTable, SheetNo, false);
int LastRow = Constants.Sheet.Unit.DetailsRowIndex + UnitTable.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Unit.LastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex, true);
}
/// <summary>
/// Create IUS NOT Linked TO Classification Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSMissingICTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
// -- Set Cell Borders
excelFile.SetCellBorder(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black);
}
/// <summary>
/// Create IUS Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Timeperiod Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.IUS]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.IUS]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.IUS]);
excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]);
Table = this.GetIUSTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUS.IUSDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUS.IUSLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex);
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSCountColINdex, LastRow, Constants.Sheet.IUS.IUSLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex, true);
// -- Set Cell Borders
excelFile.SetCellBorder(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Medium, Color.Black);
}
/// <summary>
/// Create Goal Excelsheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.GOAL]);
// -- Generate Goal sheet contents
ICSheetGenerator ICSheet = new ICSheetGenerator();
ICSheet.GenerateICSheet(ref excelFile, sheetNo, ICType.Goal);
}
/// <summary>
/// Export IUS into IUS Spreadsheet
/// </summary>
/// <param name="xlsOutputFileName"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
public bool ExportIUS(string xlsOutputFileName, string languageFileNameWPath)
{
bool RetVal = false;
int SheetIndex = 0;
DataTable IUSSheetTable = null;
IWorksheet WorkSheet = null;
DIExcel ExcelObj = new DIExcel();
try
{
// Get IUS Table
IUSSheetTable = this.GetIUSTable();
// Rename First Sheet
ExcelObj.RenameWorkSheet(0, Constants.IUSSheet.SheetName);
WorkSheet = ExcelObj.GetWorkSheet(Constants.IUSSheet.SheetName);
SheetIndex = ExcelObj.GetSheetIndex(Constants.IUSSheet.SheetName);
// Set Sheet Title and Header Values
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetTitle);
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetName);
// Load DataTable Into Worksheet
ExcelObj.LoadDataTableIntoSheet(Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, IUSSheetTable, SheetIndex, false);
// Set Subgroup Dimension Column Header
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.SubgroupDimensionsColumn);
// Merger Subgroup Dimension Column Text
ExcelObj.MergeCells(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.DimensionHeaderRowIndex, ExcelObj.GetUsedRange(SheetIndex).ColumnCount - 1);
// Format Sheet Contents
this.FormatCell(ExcelObj, SheetIndex);
// Freeze Pane
this.FreezePane(ExcelObj, SheetIndex);
// Save Excel File
ExcelObj.SaveAs(xlsOutputFileName);
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (ExcelObj != null)
{
ExcelObj.Close();
}
}
return RetVal;
}
/// <summary>
/// Generate Indicator Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Indicator Sheet
int SheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.INDICATOR]);
DataTable IndicatorTable = null;
// -- sheet content
excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.INDICATOR]);
excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Indicator DataTable with Values
IndicatorTable = this.GetIndicatorDataTable();
this.SetSNoIntoTableColumn(ref IndicatorTable);
// -- Fill Indicator Excel Sheet with Data
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, IndicatorTable, SheetNo, false);
// -- Apply Font Settings
int RowCount = IndicatorTable.Rows.Count;
this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, Constants.Sheet.Indicator.DetailsRowIndex + RowCount, Constants.Sheet.Indicator.DetailsRowIndex, true);
// -- Get Last Row
RowCount = Constants.Sheet.Indicator.DetailsRowIndex + RowCount;
// -- Set Column Width
excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex, true);
//// -- Set Cell Borders
//excelFile.SetCellBorder(Constants.Sheet.Indicator.IndicatorDetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black);
RowCount += 2;
// -- Show Missing Info
excelFile.SetCellValue(SheetNo, RowCount, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]);
excelFile.GetCellFont(SheetNo, RowCount, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
DataTable MissingTable = null;
MissingTable = GetMissingIndicators(IndicatorTable);
this.SetSNoIntoTableColumn(ref MissingTable);
RowCount += 1;
// -- Fill Indicator Excel Sheet with Data
excelFile.LoadDataTableIntoSheet(RowCount, Constants.HeaderColIndex, MissingTable, SheetNo, false);
// -- Apply Font Settings
int LastRow = MissingTable.Rows.Count + RowCount;
this.ApplyFontSettings(ref excelFile, SheetNo, RowCount, Constants.HeaderColIndex, LastRow, Constants.Sheet.Indicator.NameColIndex, true);
}
internal Dictionary<string, string> GetSeries(Fields fields, string xlsFileNameWPath, ChartMode chartMode, int columnCount)
{
Dictionary<string, string> RetVal = new Dictionary<string, string>();
try
{
Dictionary<string, IRange> PyramidSeries = new Dictionary<string, IRange>();
if (chartMode == ChartMode.Insert || chartMode == ChartMode.Edit)
{
this.TableSheetIndex = 1;
}
this.ExcelFile = new DIExcel(xlsFileNameWPath, System.Threading.Thread.CurrentThread.CurrentCulture);
//-- Insert column and its value.
this.InserDataValueColumn(fields, columnCount);
//-- Generate the series for pyramid chart
PyramidSeries = this.GetPyramidSeries(fields, chartMode);
//-- Save the file
this.ExcelFile.Save();
//-- Insert / Edit the chart
switch (chartMode)
{
case ChartMode.Insert:
this.InsertChart(PyramidSeries, xlsFileNameWPath);
break;
case ChartMode.Edit:
this.CreatePyramidChartAndSetDataRange(xlsFileNameWPath, PyramidSeries);
break;
case ChartMode.ExcelInsert:
case ChartMode.ExcelEdit:
RetVal = this.ConvertRangeIntoString(PyramidSeries);
break;
default:
break;
}
this.ExcelFile.Close();
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Generate Sheet for Comparison Report
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="dbConnection">Database Connection </param>
/// <param name="dbQueries">DIQueries</param>
/// <param name="sheetType">Sheet Type for Comparison Reports</param>
internal void GenerateSheet(ref DIExcel excelFile, DIConnection dbConnection, DIQueries dbQueries, SheetType sheetType)
{
int SheetNo = 0;
int CurrentRowIndex = 0;
DataTable MissingRecords = null;
DataTable AdditionalRecords = null;
SheetSource SheetGenerator = null;
// -- Get Sheet Class Instance
SheetGenerator = SheetSourceFactory.CreateInstance(sheetType, dbConnection, dbQueries);
this.NameColIndex = SheetGenerator.NameColumnIndex;
this.LastColIndex = SheetGenerator.LastColumnIndex;
this.LanguageName = SheetGenerator.GetLanguageName();
// -- Get Missing Records
MissingRecords = SheetGenerator.GetMissingRecordsTable();
//-- Get Additional Records
AdditionalRecords = SheetGenerator.GetAdditionalRecordsTable();
// if records is morethan 50000 then create multiple sheets
if (MissingRecords.Rows.Count > Constants.SheetsLayout.MAXEXCELROWS || (MissingRecords.Rows.Count + AdditionalRecords.Rows.Count) > Constants.SheetsLayout.MAXEXCELROWS)
{
// -- Create Multiple Sheet
this.GenerateSheets(ref excelFile, SheetGenerator.SheetName, MissingRecords, AdditionalRecords);
}
else
{
// -- Create Worksheet
SheetNo = this.CreateSheet(ref excelFile, SheetGenerator.SheetName, 0);
// -- Set Initial Sheet Value
this.SetSheetHeading(ref excelFile, SheetNo, SheetGenerator.SheetName);
this.SetSheetLanguageValue(ref excelFile, SheetNo);
this.SetMissingText(ref excelFile, SheetNo);
// -- Load Missing Records Into Sheet
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, MissingRecords, SheetNo, false);
CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + MissingRecords.Rows.Count;
excelFile.SetCellValue(SheetNo, CurrentRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.ADDITIONAL) + " : " + DBNameForAdditionalRecords);
CurrentRowIndex += 1;
// -- Load Additional Records Into Sheet
excelFile.LoadDataTableIntoSheet(CurrentRowIndex, Constants.HeaderColIndex, AdditionalRecords, SheetNo, false);
// -- Apply Font Settings
this.ApplyFontSetting(ref excelFile, SheetNo, MissingRecords.Rows.Count);
}
}
/// <summary>
/// Create TimePeriod Sheet
/// </summary>
/// <param name="excelFile"></param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.AREA]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]);
// -- Get Area Data TAble.
Table = this.GetAreaTable();
excelFile.SetColumnFormatType("H:H",sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.SetColumnFormatType("I:I", sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Area.AreaDetailsRowIndex + Table.Rows.Count;
LastRow += 2;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex, true);
// -- Set Missing Area Header
excelFile.SetCellValue(sheetNo, LastRow, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]);
//excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Bold = true;
excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
LastRow += 1;
DataTable MissingTable = null;
MissingTable = this.GetMissingArea();
excelFile.LoadDataTableIntoSheet(LastRow, Constants.Sheet.SummaryReport.AreaColIndex, MissingTable, sheetNo, false);
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, LastRow, Constants.HeaderColIndex, LastRow + MissingTable.Rows.Count + 1, Constants.Sheet.Area.AreaIDColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.OthersColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaIDColIndex, LastRow, Constants.Sheet.Area.AreaIDColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.AreaNameColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaStartDateColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex, true);
}
private void GenerateExcelFile(string xmlFileName,string excelFileName)
{
Questionnarie XMLFile = new Questionnarie();
DIExcel ExcelFile;
int RowIndex=1;
string HeaderText = string.Empty;
try
{
ExcelFile= new DIExcel();//excelFileName);
if (XMLFile.OpenQuestionnaire(xmlFileName))
{
foreach (Question XmlQuestion in XMLFile.GetAllQuestions().Values)
{
//Write Section Name
if (HeaderText != XmlQuestion.HeaderTxt)
{
HeaderText = XmlQuestion.HeaderTxt;
this.WriteHeader(ExcelFile, RowIndex, HeaderText);
RowIndex += 1;
}
this.WriteQuestionIntoExcel(ExcelFile, XmlQuestion,ref RowIndex);
}
//delete file if already exists
if (File.Exists(excelFileName))
{
File.Delete(excelFileName);
}
ExcelFile.SaveAs(excelFileName);
ExcelFile.Close();
}
}
catch (Exception)
{
}
}
/// <summary>
/// GENERATE Summary Report Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int SheetNo = 0;
// -- Count Available Sheets in Excel WorkBook
int Count = excelFile.AvailableWorksheetsCount;
// -- Rename or Create First existing sheet to "Summary Report"
if (Count > 0)
{
excelFile.RenameWorkSheet(Constants.Sheet.SummaryReport.SummaryReportSheetIndex, base.ColumnHeader[DSRColumnsHeader.SUMMARY]);
}
else
{ excelFile.CreateWorksheet(base.ColumnHeader[DSRColumnsHeader.SUMMARY]); }
//--Get Sheet No of SummaryReport Sheet
SheetNo = excelFile.GetSheetIndex(base.ColumnHeader[DSRColumnsHeader.SUMMARY]);
// -- Generate Summary Report For Both Comparison and Summary Report as per selection
if (ForComparisonReport)
this.GenerateForComparisonReport(ref excelFile, SheetNo);
else
this.GenerateForSummaryReport(ref excelFile, SheetNo);
}
/// <summary>
/// Create DataBase Log Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
// -- Create Timeperiod Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.DATABASE_LOG]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.DATABASE_LOG]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
Table = this.GetDataBaseLogTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex, true);
}
/// <summary>
/// Create Comments Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Comments Data TAble.
Table = this.GetCommentsTables();
int Counter = 0;
foreach (DataRow row in Table.Rows)
{
//-- Exit IF current Record is For Comments.
if (!string.IsNullOrEmpty(row[this.ColumnHeader[DSRColumnsHeader.INDICATOR]].ToString()))
{
Counter += 1;
row[DILanguage.GetLanguageString("SERIAL_NUMBER").ToString()] = Counter;
}
}
Table.AcceptChanges();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Comments.CommentsDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsNameColIndex, LastRow, Constants.Sheet.Comments.CommentsNameColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true);
}
/// <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>
/// Read excel workbook and update Database with Keywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private Int32 GetKeywordAndUpdateDatabase_Old(string presentationFile)
{
Int32 RetVal = 0;
System.Data.DataTable ExcelSheetDataTable;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
PresentationType Pres_Type = PresentationType.M;
String Title = String.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
int Map_Suffix_Lenght = 10; //" - Map.xls"
try
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(presentationFile);
// Getting Pres fileName For Database Field
Pres_FileName = Path.GetFileName(presentationFile);
//Start of getting Data from Excel Sheet
// Check For Presention Type
if (presentationFile.Contains(Presentation.Map_Suffix)) // Presention is Map : Get Title and Data
{
// Get Presentation Type for Database table when presentation file is Map
//Pres_Type = Pres_Type_M;
Pres_Type = PresentationType.M;
// In case of Map Title Column will be name of the file minus " - Map.xls"
Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght);
// Using Excel Control Function to get value from Sheet
ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.MAPDATA);
}
else // Get Title and Data for Graph and Map Presentioan
{
// Get Presentation Type for Database table when presentation file is table or Graph
// Setting Presention Type On the basis of suffix
if (presentationFile.Contains(Presentation.Graph_Suffix) || presentationFile.ToLower().Contains(Presentation.Graph_Suffix.ToLower()))
{
Pres_Type = PresentationType.G;
}
else if (presentationFile.Contains(Presentation.Table_Suffix) || presentationFile.ToLower().Contains(Presentation.Table_Suffix.ToLower()))
{
Pres_Type = PresentationType.T;
}
// Get Presentation Title
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
// DataTable In case of Graph or Table
ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.DATA);
}
//End of getting Data from Excel Sheet
//Getting Keywords
Keywords = this.GetKeyWordsFromPresentationDataTable(ExcelSheetDataTable);
// --- Get UserKeywords ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
// Update Database
Pres_Nid = UpdateDBTables(presentationFile, Pres_Type, Title, UKeyword, Keywords);
RetVal = Pres_Nid;
}
catch (Exception ex)
{
//Successs = false;
}
return RetVal;
}
private void WriteDataValue(DIExcel excelFile, Question xmlQuestion,ref int dataValueRowIndex)
{
string[] SelectedValues =null;
int Index=0;
switch (xmlQuestion.AnswerType)
{
case AnswerType.TB:
case AnswerType.TBN:
case AnswerType.RB:
case AnswerType.CB:
case AnswerType.DateType:
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.DataValue);
break;
case AnswerType.SCB:
case AnswerType.SRB:
break;
case AnswerType.CH:
SelectedValues = DICommon.SplitStringNIncludeEmpyValue(xmlQuestion.DataValue, ",");
foreach (AnswerTypeOption Option in xmlQuestion.Options)
{
excelFile.GetCellFont(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex).Name = "Wingdings";
if (SelectedValues[Index] == "1")
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(252) );
}
else
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(251));
}
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex + 1, Option.Text);
dataValueRowIndex += 1;
Index += 1;
}
break;
case AnswerType.GridType:
int StartRowIndex=dataValueRowIndex;
int EndRowIndex;
int EndColIndex;
excelFile.LoadDataTableIntoSheet(dataValueRowIndex,Constants.DataValueColumnIndex, xmlQuestion.GridTable,Constants.SheetIndex, true);
dataValueRowIndex += xmlQuestion.GridTable.Rows.Count;
EndRowIndex=dataValueRowIndex-1;
EndColIndex =Constants.DataValueColumnIndex+ xmlQuestion.GridTable.Columns.Count-1;
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideHorizontal);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideVertical);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeBottom);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeTop);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeLeft);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeRight);
break;
case AnswerType.Calculate:
break;
case AnswerType.Aggregate:
break;
default:
break;
}
}
/// <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>
/// 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>
/// Read excel workbook and update Database with Keywords
/// This will Get UserKeywords from Excel and Call overloaded GetKeywordAndUpdateDatabase function with
/// two parameter presFile path and UKeywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private Int32 GetKeywordAndUpdateDatabase(string presentationFile)
{
Int32 RetVal = 0;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
try
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(presentationFile);
// --- Get UserKeywords ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
//close excel
DIExl.Close();
// Call Other overload
RetVal = this.GetKeywordAndUpdateDatabase(presentationFile, UKeyword);
}
catch (Exception ex)
{
}
return RetVal;
}
private void WriteHeader(DIExcel excelFile, int rowIndex, string headerText)
{
excelFile.SetCellValue(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex, headerText);
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Size = 12;
}
/// <summary>
/// Extract keyword from DataTable and update Database with Keywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private int GetKeywordAndUpdateDatabase(Presentation.PresentationType presType, String presTitle, String userKeyword, DataTable presentationDataTable, string PresFile)
{
int RetVal = -1;
// If File is an xls File then get presTitle,UKeyword from Excel File
System.Data.DataTable ExcelSheetDataTable;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
PresentationType Pres_Type = PresentationType.M;
String Title = String.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
int Map_Suffix_Lenght = 10; //" - Map.xls"
try
{
//If Excel File.
if (PresFile.EndsWith(DICommon.FileExtension.Excel))
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(PresFile);
// Getting Pres fileName For Database Field
Pres_FileName = Path.GetFileName(PresFile);
// Get Presentation Type pres type abbreviation. Which is entered in Database.M:Map ,T:Table,G:Graph
Pres_Type = GetPresTypeForGalleryDB(presType);
// Get title from excel Presentaion
// In case of Map Title Column will be name of the file minus " - Map.xls"
if (presType==Presentation.PresentationType.Map)
{
// In case of Map Title Column will be name of the file minus " - Map.xls"
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
//Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght);
}
// Presentaion is table or Graph
else
{
// Get Presentation Title
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
}
// --- Get UserKeywords from Excel Presentaion ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
}
//In case of Web application title and Ukeyword will be same as passed by client application
else
{
Title = presTitle;
UKeyword = userKeyword;
Pres_Type = GetPresTypeForGalleryDB(presType);
}
//Getting Keywords from presentation Data table
Keywords = this.GetKeyWordsFromPresentationDataTable(presentationDataTable);
// Update Database
RetVal = UpdateDBTables(PresFile, Pres_Type, Title, UKeyword, Keywords);
Pres_Nid = RetVal;
}
catch (Exception ex)
{
//Successs = false;
}
return RetVal;
}
private void WriteQuestionIntoExcel(DIExcel excelFile, Question xmlQuestion,ref int RowIndex)
{
int DataValueRowIndex = RowIndex + 1;
int BoldStartIndex = -1;
int BoldEndIndex = -1;
int ItalicStartIndex = -1;
int ItalicEndIndex = -1;
string BoldStartTag="<b>";
string ItalicStartTag="<i>";
string BoldEndTag = "</b>";
string ItalicEndTag = "</i>";
string QuestionText=xmlQuestion.Text;
//set question no and question text
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex, xmlQuestion.No);
excelFile.SetColumnWidth(Constants.SheetIndex, 6, RowIndex, Constants.QuestionNoColumnIndex, RowIndex, Constants.QuestionNoColumnIndex);
//replace <br> with new line character and remove enter key
QuestionText= QuestionText.Replace(Char.ConvertFromUtf32(13), string.Empty);
QuestionText = QuestionText.Replace("\n", string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.CrLf, string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.Lf.ToString(), string.Empty);
QuestionText=QuestionText.Replace("<br>", Microsoft.VisualBasic.ControlChars.NewLine);
//apply bold tag
BoldStartIndex = QuestionText.IndexOf(BoldStartTag);
if (BoldStartIndex >= 0)
{
QuestionText = QuestionText.Replace(BoldStartTag, string.Empty);
BoldEndIndex = QuestionText.IndexOf(BoldEndTag);
QuestionText = QuestionText.Replace(BoldEndTag, string.Empty);
}
//apply italic tag
ItalicStartIndex = QuestionText.IndexOf(ItalicStartTag);
if (ItalicStartIndex >= 0)
{
QuestionText = QuestionText.Replace(ItalicStartTag, string.Empty);
ItalicEndIndex = QuestionText.IndexOf(ItalicEndTag);
QuestionText = QuestionText.Replace(ItalicEndTag, string.Empty);
}
//set question text
QuestionText.Replace(ItalicStartTag, string.Empty);
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, QuestionText);
//set bold
if (BoldStartIndex >= 0 & BoldEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, BoldStartIndex, BoldEndIndex-BoldStartIndex).Font.Bold = true;
}
//set italic
if (ItalicStartIndex >= 0 & ItalicEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, ItalicStartIndex, ItalicEndIndex-ItalicStartIndex).Font.Italic= true;
}
//set layout of question text cell and question no cell
//excelFile.MergeCells(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, RowIndex, Constants.QuestionTextColumnIndex + 6);
//excelFile.SetHorizontalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.HAlign.Justify);
//excelFile.SetVerticalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.VAlign.Justify);
//excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex).Bold = true;
excelFile.AutoFitColumn(Constants.SheetIndex, Constants.QuestionNoColumnIndex);
this.WriteDataValue(excelFile, xmlQuestion,ref DataValueRowIndex);
RowIndex =DataValueRowIndex+ 2;
}
/// <summary>
/// Get Title from Presentaion
/// </summary>
/// <param name="presentaionType"></param>
/// <param name="presFileName"></param>
/// <returns></returns>
private String GetPresentationTitle(PresentationType presentaionType,String presFileName, DIExcel dIExl)
{
string RetVal = string.Empty;
int Map_Suffix_Lenght = 10; //" - Map.xls"
int Graph_Suffix_Lenght = 12; //" - Graph.xls"
try
{
switch (presentaionType)
{
//Map . // In case of Map RetVal Column will be name of the file minus " - Map.xls"
case PresentationType.M:
RetVal = presFileName.Substring(0, presFileName.Length - Map_Suffix_Lenght);
break;
case PresentationType.G:
//Get FileName for RetVal Column. In case of table or Graph this will be name of the file minus " - Table.xls" or - Graph.xls so lenght is 1;
RetVal = presFileName.Substring(0, presFileName.Length - Graph_Suffix_Lenght);
break;
case PresentationType.T:
//Get RetVal and Subtitle Column Value when Presentation is not Map
//Get FileName for RetVal Column. In case of table or Graph this will be name of the file minus " - Table.xls" or - Graph.xls so lenght is 1;
RetVal = presFileName.Substring(0, presFileName.Length - Graph_Suffix_Lenght);
// Value in First sheet A1 and A2 Column respectively will added in RetVal
// Getting Value of Main RetVal from first cell and adding this to RetVal
if (dIExl.GetCellValue(0, CELL_A1).Length > 0)
{
//Check RetVal already contain this word or not, If Not then add this to RetVal
if (RetVal.Contains(dIExl.GetCellValue(0, CELL_A1)) == false)
{
//Check column value contain RetVal filename or not
if ((dIExl.GetCellValue(0, CELL_A1)).Contains(RetVal) == false) // Add column value to RetVal
{
RetVal += " " + dIExl.GetCellValue(0, CELL_A1);
}
else // Replace Existing Value of RetVal with this column value
{
RetVal = dIExl.GetCellValue(0, CELL_A1);
}
}
}
// Getting SubTitle (If Available)
if (dIExl.GetCellValue(0, CELL_A2).Length > 0)
{
// Adding SubTitle in RetVal string with a blank space
RetVal += " " + dIExl.GetCellValue(0, CELL_A2);
}
break;
default:
break;
}
}
catch (Exception ex)
{
}
return RetVal;
}
/// <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");
}
