/// <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");
}
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>
/// Generates the .XLS presentation file in specified folder on the basis of current Map object settings.
/// </summary>
/// <param name="presentationOutputFolderPath">Folder Path where .xls presentation is created.</param>
/// <param name="maxHeightInPixelToFitAll">(optional) maximun width (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <param name="maxWidthInPixelToFitAll">(optional) maximun height (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <returns>Returns the full file path of .xls presentation generated.</returns>
public string GeneratePresentation(string presentationOutputFolderPath, int maxWidthInPixelToFitAll, int maxHeightInPixelToFitAll)
{
string RetVal = string.Empty;
if (!(string.IsNullOrEmpty(presentationOutputFolderPath)))
{
float MapOriginalWidth = this.m_Width;
float MapOriginalHeight = this.m_Height;
// Getting the xls sheet names in desired language.
string MapSheetName = DILanguage.GetLanguageString("MAP");
string MapDataSheetName = DILanguage.GetLanguageString("DATA");
string SourceSheetName = DILanguage.GetLanguageString("SOURCECOMMON");
// Set path for presentation and png images
string FileSuffix = DateTime.Now.Ticks.ToString();
// Set Image file extension(.png or .emf) on he basis of UserPreference property.
string ImageExtention = string.Empty;
if (this.UserPreference.General.ShowExcel)
{
//If ShowExcel is True, then User can see presentation in MS Excel object
ImageExtention = "emf";
}
else
{
// Png format is supported in SpreadsheetGear (.Emf NOT supported)
//If ShowExcel is False, then presentation will be opened in Spreadsheet Gear, so Use Png format.
ImageExtention = "png";
}
string PresentationPath = presentationOutputFolderPath + @"\Map" + FileSuffix + ".xls";
string TitleImgPath = Path.Combine(presentationOutputFolderPath, "Title" + FileSuffix + "." + ImageExtention); //"Title"
string MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + "." + ImageExtention);
string DisclaimerImgPath = Path.Combine(presentationOutputFolderPath, "Disclaimer" + FileSuffix + "." + ImageExtention);
string InsetFilePrefix = string.Empty;
DIExcel ExcelApp = new DIExcel();
Image img;
//- Calculate pixelToPoint factor as SpreadsheetGear uses point unit instead of Pixel unit for measuring Length and width
Graphics graphics = Graphics.FromImage(new System.Drawing.Bitmap(1, 1));
double PixcelToPointFactor = 72 / graphics.DpiX; //1 inch = 72 points
Size TitleSize = new Size();
Size LegendSize = new Size();
int MaxLegendHeight = 20;
int MaxLegendWidth = 30;
int RowIndex = 0;
int SheetIndex;
double CellHeight = ExcelApp.GetCellHeight(0, 0, 0); // default cell height from default sheet.
double CellWidth = ExcelApp.GetColumnWidth(0, 0, 0, 0, 1); // Default cell width from default sheet.
int SingleCellWidthInPixel = (int)(ExcelApp.GetWorksheet(0).Cells[0, 0].Width / PixcelToPointFactor);
if (maxWidthInPixelToFitAll > 0 && maxHeightInPixelToFitAll > 0)
{
//- Offset page Margins (default left margin -> 0.7" & right margin -> 1.4") and (default Top & bottom margin -> 0.75")
maxWidthInPixelToFitAll = maxWidthInPixelToFitAll - (int)(2.1 * graphics.DpiX);
maxHeightInPixelToFitAll = maxHeightInPixelToFitAll - (int)(1.5 * graphics.DpiY);
//- Calculating maximum Height & width of presentation's composite contents in Points
//maxWidthInPixelToFitAll = (int)(maxWidthInPixelToFitAll * PixcelToPointFactor);
//maxHeightInPixelToFitAll = (int)(maxHeightInPixelToFitAll * PixcelToPointFactor);
// Offset margin of first column width.
maxWidthInPixelToFitAll -= SingleCellWidthInPixel;
//- Set final Map width so that Map can be accomodated within maximum width specified.
this.m_Width = (float)maxWidthInPixelToFitAll - 2;
//- offset width of Inset images (if present)/
//- generally inset image is 15% of total Map and starts after a gap of 1/2 column width.
if (this.Insets.Count > 0)
{
float InsetImageWidth = MapOriginalWidth; //- Default
if (this.Insets[0].InsetImage != null)
{
InsetImageWidth = (float)(this.Insets[0].InsetImage.Width);
}
this.m_Width -= (InsetImageWidth * 0.1F) + (float)(SingleCellWidthInPixel);
}
//- Adjusting Map height in proportion with CurrentExtent's Width to height ratio.
this.m_Height = this.m_Width * Math.Min(4 / 3, (this.m_CurrentExtent.Height / this.m_CurrentExtent.Width)) + 3;
}
try
{
if (File.Exists(PresentationPath))
{
File.Delete(PresentationPath);
}
ExcelApp.SaveAs(PresentationPath);
ExcelApp.RenameWorkSheet(0, MapSheetName); //language handling Rename first(default) sheet
ExcelApp.Save();
//********************************* WorkSheet 1 - Map Images**************************************************
this.ClearSelection(); //-- Clear any Layer selection (if any)
//Generate the first sheet which is the Map Image for MRD data.
//*** generate title image in temp folder
TitleSize = this.GetTitleImage(Path.GetDirectoryName(TitleImgPath), Path.GetFileNameWithoutExtension(TitleImgPath), ImageExtention);
//*** Generate Themes Legend Image in temp folder
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
//Generate the Legend image, and calculating image maximum height & width.
LegendSize = _ThemeTemp.GetLegendImage(presentationOutputFolderPath, _ThemeTemp.ID + FileSuffix, ImageExtention, this.PointShapeIncluded, this.TemplateStyle.Legends.ShowCaption, this.TemplateStyle.Legends.ShowRange, this.TemplateStyle.Legends.ShowCount, this.TemplateStyle.Legends.ShowMissingLegend);
MaxLegendWidth = Math.Max(MaxLegendWidth, (int)(LegendSize.Width * PixcelToPointFactor));
MaxLegendHeight = Math.Max(MaxLegendHeight, (int)(LegendSize.Height * PixcelToPointFactor));
}
}
//*** generate discalimer (common) image in temp folder
this.GetDisclaimerImage(Path.GetDirectoryName(DisclaimerImgPath), Path.GetFileNameWithoutExtension(DisclaimerImgPath), ImageExtention, maxWidthInPixelToFitAll);
int ImageRowPosition = 0; //Holds the row position for Image to be pasted.
//---Insert Sheet for Map of most recent data.
SheetIndex = ExcelApp.GetSheetIndex(MapSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
if (this._showTimePeriods || this._showAreaLevels)
{
//*** Set map rendering info for most recent time period for all level
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
else
{
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
ExcelApp.Save();
// Set Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
// Converting Image size from Pixel to Points while pasting.
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Set Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
// Set Insets Image
int InsetCtr;
CellWidth = 70; //TODO: Remove hardcoding calculate column width right.
int RowOffset = ImageRowPosition;
int ColOffset = (int)(this.Width / CellWidth) + 2;
InsetFilePrefix = "Inset" + FileSuffix;
for (InsetCtr = 0; InsetCtr <= this.Insets.Count - 1; InsetCtr++)
{
Inset TempInset = this.Insets[InsetCtr];
string InsetImgFilePath = Path.Combine(presentationOutputFolderPath, InsetFilePrefix + InsetCtr + "." + ImageExtention);
if (TempInset.InsetImage != null)
{
TempInset.InsetImage.Save(InsetImgFilePath);
}
else
{
//Extract new "png" image of inset in temp folder.
this.GetInsetImage(presentationOutputFolderPath, ImageExtention, (int)this.Width, (int)this.Height, InsetCtr, InsetFilePrefix);
}
if (TempInset.Visible)
{
//*** Extract InsetName png.
TempInset.GetInsetName(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix, ImageExtention);
img = Image.FromFile(Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention));
ExcelApp.PasteImage(SheetIndex, Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention), ColOffset, RowOffset, img.Width, img.Height);
//*** use previously generated inset images in temp folder
RowOffset += img.Height / (int)CellHeight;
img = Image.FromFile(InsetImgFilePath);
ExcelApp.PasteImage(SheetIndex, InsetImgFilePath, ColOffset, RowOffset, (img.Width * 0.09), (img.Height * 0.09));
RowOffset += (int)(img.Width * 0.09) / (int)CellHeight + 2;
img.Dispose();
}
}
//Set Theme Legend Images
ImageRowPosition += (int)((this.m_Height * PixcelToPointFactor) / (int)CellHeight) + 2; //Increment row position by rows covered by Image height.
int p = 1; //p is the column position of the Legend Images (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); //(double)LegendSize.Width, (double)LegendSize.Height);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
DataView _DVSources = this.GetUniqueSourceList(); //getting unique sources in DataView
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
//- Wrap Cell for source
int EndingColumnToMerge = maxWidthInPixelToFitAll / SingleCellWidthInPixel;
ExcelApp.MergeCells(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge);
ExcelApp.WrapText(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge, true);
}
// Set Disclaimer Image
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
ExcelApp.Save();
//--Now, generate sheets for each timePeriod selected.
if (this._showTimePeriods)
{
DataView DVTimePeriods = this.GetTimePeriodsForThemes();
for (byte i = 0; i <= DVTimePeriods.Count - 1; i++)
{
//Restrict Excel Worksheet name to 31 characters
string sheetName = DVTimePeriods[i][Timeperiods.TimePeriod].ToString();
if (sheetName.Length > 31)
{
sheetName.Substring(0, 31);
}
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(sheetName);
SheetIndex = ExcelApp.GetSheetIndex(sheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo((int)(DVTimePeriods[i][Timeperiods.TimePeriodNId]), -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//*** Add Sources
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
}
else if (this.ShowAreaLevels) //If only AreaLevel is selected.
{
DataView DVAreaLevels = this.GetAreaLevels();
for (byte i = 0; i <= DVAreaLevels.Count - 1; i++)
{
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
SheetIndex = ExcelApp.GetSheetIndex(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, (int)(DVAreaLevels[i][Area_Level.AreaLevel]), iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, SourceSheetName);
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
ExcelApp.Save();
}
//********************************* WorkSheet - Map Data*********************************************
//TODO Use TablePresentation object logic
ExcelApp.InsertWorkSheet(MapDataSheetName); //insert DataSheet.
SheetIndex = ExcelApp.GetSheetIndex(MapDataSheetName);
ExcelApp.ActivateSheet(SheetIndex);
DataTable _DTDataSheet = GetDataSheetView(this.PresentationData); //Getting Map Data or DataSheet.
string[,] arrDataView = new string[_DTDataSheet.Rows.Count, _DTDataSheet.Columns.Count - 1]; //Array Columns will be 1 less than dv, to ignore extra footNote column
// --Adding Column names in desired language , only one time, at row 1.
ExcelApp.SetCellValue(SheetIndex, 1, 0, DILanguage.GetLanguageString("TIMEPERIOD")); //Timeperiods.TimePeriod
ExcelApp.SetCellValue(SheetIndex, 1, 1, DILanguage.GetLanguageString("AREAID")); //Area.AreaID
ExcelApp.SetCellValue(SheetIndex, 1, 2, DILanguage.GetLanguageString("AREANAME")); //Area.AreaName
ExcelApp.SetCellValue(SheetIndex, 1, 3, DILanguage.GetLanguageString("INDICATOR")); //Indicator.IndicatorName
ExcelApp.SetCellValue(SheetIndex, 1, 4, DILanguage.GetLanguageString("DATA")); //Data.DataValue
ExcelApp.SetCellValue(SheetIndex, 1, 5, DILanguage.GetLanguageString("UNIT")); //Unit.UnitName
ExcelApp.SetCellValue(SheetIndex, 1, 6, DILanguage.GetLanguageString("SUBGROUP")); //SubgroupVals.SubgroupVal
ExcelApp.SetCellValue(SheetIndex, 1, 7, DILanguage.GetLanguageString("SOURCECOMMON")); //IndicatorClassifications.ICName
int col = 8; //Columnn number 8th
foreach (string MDColumn in this._MDIndicatorFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataIndicator.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDAreaFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataArea.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDSourceFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataSource.Columns[MDColumn].Caption);
col++;
}
}
// ---Columns added...
RowIndex = 2; //Start displaying Map Data from Row - 2nd
foreach (DataRow DRowDTDataSheet in _DTDataSheet.Rows)
{
for (int Columns = 0; Columns < _DTDataSheet.Columns.Count; Columns++)
{
//Assign cellvalue with data in DataTable.
ExcelApp.SetCellValue(SheetIndex, RowIndex, Columns, DRowDTDataSheet[Columns].ToString());
if (_DTDataSheet.Columns[Columns].ColumnName == Data.DataValue)
{
//If Column id = "DataValue" then add footNotes As-Comment if present.
if (DRowDTDataSheet[FootNotes.FootNote].ToString() != "")
{
ExcelApp.AddComment(SheetIndex, RowIndex, Columns, DRowDTDataSheet[FootNotes.FootNote].ToString(), true);
}
}
}
RowIndex++;
}
ExcelApp.AutoFitColumns(SheetIndex, 1, 0, _DTDataSheet.Rows.Count - 1, _DTDataSheet.Columns.Count - 1); //Auto fitting columns width.
ExcelApp.Save();
//********************************* WorkSheet - Sources**************************************************
ExcelApp.InsertWorkSheet(SourceSheetName);
SheetIndex = ExcelApp.GetSheetIndex(SourceSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.SetCellValue(SheetIndex, 0, 0, SourceSheetName); //Cell value = "Source" in desired language.
ExcelApp.SetCellValue(SheetIndex, 0, 2, DIConnection.ConnectionStringParameters.DbName);
for (byte i = 0; i <= _DVSources.Count - 1; i++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, i + 3, 0, _DVSources[i]["IC_Name"]);
ExcelApp.AutoFitColumn(SheetIndex, i);
}
ExcelApp.Save();
//********************************* WorkSheet - Keyword**************************************************
ExcelApp.InsertWorkSheet(Presentation.KEYWORD_WORKSHEET_NAME);
ExcelApp.ActivateSheet(ExcelApp.GetSheetIndex(MapSheetName)); //Set first sheet as ACTIVE sheet..
ExcelApp.Save();
RetVal = PresentationPath;
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
ExcelApp.Close();
if (graphics != null)
{
graphics.Dispose();
}
this.m_Width = MapOriginalWidth;
this.m_Height = MapOriginalHeight;
}
}
return RetVal;
}
/// <summary>
/// Save Summary Report Excel WorkBook.
/// </summary>
/// <param name="excelFile">Excel File</param>
private void SaveSummaryReport(ref DIExcel excelFile)
{
try
{
// -- If File exist delete file.
if (System.IO.File.Exists(this.DestinationFilePath))
{
System.IO.File.Delete(this.DestinationFilePath);
}
}
catch { }
excelFile.ActiveSheetIndex = 0;
excelFile.SaveAs(this.DestinationFilePath);
}
/// <summary>
/// Generate Range Check Report
/// </summary>
/// <returns></returns>
public bool GenerateExcelReport()
{
bool RetVal = false;
DataTable ReportTable;
int SheetNo = 0;
int ProgressBarValue=0;
//-- Initialize Collection
this.InitColumnHeading();
//-- Check Selected File Selected
if (this.ReportDestinationFilePath.Length > 0)
{
DIExcel RangeCheckSheet = new DIExcel();
string NumDecSeparator = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
//-- Problem with Different Locale of Office and Regional(Setting)
System.Threading.Thread ThisThread = System.Threading.Thread.CurrentThread ;
System.Globalization.CultureInfo OriginalCulture = ThisThread.CurrentCulture ;
//-- Create Worksheet named "Range Check"
RangeCheckSheet.CreateWorksheet(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
SheetNo = RangeCheckSheet.GetSheetIndex(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
this.SetWorkbookInitialValue( ref RangeCheckSheet, SheetNo);
ThisThread.CurrentCulture = OriginalCulture;
//-- Fill DataTable
ReportTable = this.DBConnection.ExecuteDataTable(DBQueries.Data.GetValuesRangeCheck());
// TempTable.Merge(ReportTable,false);
int maxValue = ReportTable.Rows.Count;
try
{
ThisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//-- Proceed If datatable has Records
if (ReportTable.Rows.Count > 0)
{
// Initialize progress bar
this.RaiseProgressBarInitialize(maxValue + 1);
int RowNum = RangeCheckFileRowsInfo.DataStartingRowIndex;
int LastIUSNID = 0;
//-- Fill Record Indicator wise
foreach (DataRow rowval in ReportTable.Rows)
{
// -- Avoid Process if IUSNID is Last IUSNID
if (Convert.ToInt32(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]) != LastIUSNID)
{
LastIUSNID = Convert.ToInt32(rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]);
//-- Set Data Column Header Bold
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum + RangeCheckFileRowsInfo.HeaderRowCount, RangeCheckFileRowsInfo.SheetHeaderColIndex).Bold = true;
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Indicator]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator.IndicatorName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Unit]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Subgroup]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal]));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Min]);
// -- Get Minimum Value
double MinValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MinValue])? Convert.ToDouble("0.0") :Convert.ToDouble( rowval[Indicator_Unit_Subgroup.MinValue])) ;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MinValue);
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Max]);
double MaxValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MaxValue]) ? Convert.ToDouble("0.0") : Convert.ToDouble(rowval[Indicator_Unit_Subgroup.MaxValue]));
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MaxValue);
RowNum += 1;
DataTable Table=null;
DataTable OtherTable=null;
// -- insert timeperiod, areaid,area name,datavalue,source
#region "-- Change No: c1 --"
if (MaxValue > MinValue)
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID,RangeType.DataBelow_MinVal ).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MaxVal).Select());
}
else if (MaxValue == 0.0 && MinValue == 0.0) { }
else
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MinVal).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataBelow_MaxVal).Select());
}
#endregion
////if (MaxValue > MinValue)
////{
//// Table = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID )); //+ " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MinValue ));
//// OtherTable = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID ));//+ " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MaxValue));
////}
////else if (MaxValue == 0.0 && MinValue == 0.0) { }
////else
////{
//// Table = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MinValue));
//// OtherTable = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MaxValue));
////}
if (Table != null)
{
// -- Merge Both Table
Table.Merge(OtherTable, true);
//-- Check if Records Exist in DataTable Then Export to Excel Workbook
if (Table.Rows.Count > 0)
{
//-- Set Data Column Header Font
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex).Italic = true;
//-- Set Column Backgroud Color TO Grey
RangeCheckSheet.SetRangeColor(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex, Color.Black, System.Drawing.Color.FromArgb(Color.Gray.R, Color.Gray.G, Color.Gray.B));
//-- Raname DataTable Column Name As Per Excel Sheet
this.RenameColumn(ref Table);
//-- Load DataTAble Into Excel
RangeCheckSheet.LoadDataTableIntoSheet(RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, Table, SheetNo, false);
}
RowNum += Table.Rows.Count + 1;
}
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
} //End Of Next
//-- Increase ProgressBar Value
ProgressBarValue++;
//-- IF numbers of record in dataview is morethan the max rows i.e 50,000 available in excel
if (RowNum > RangeCheckCustomizationInfo.MAX_EXCEL_ROWS )
{
break;
}
}
//-- Set WorkSheet Border Line width
this.SetColumnWidth(ref RangeCheckSheet, SheetNo);
this.SetSheetBorder(ref RangeCheckSheet, SheetNo);
} //End OF IF
//-- Make Progressbar Value to Maximum
this.RaiseProgressBarIncrement(maxValue );
try
{
// -- Save the Workbook If file exist then delete and then Save
if (System.IO.File.Exists(this.ReportDestinationFilePath))
{
System.IO.File.SetAttributes(this.ReportDestinationFilePath, FileAttributes.Normal);
System.IO.File.Delete(this.ReportDestinationFilePath);
}
RangeCheckSheet.SaveAs(this.ReportDestinationFilePath);
this.RaiseProgressBarClose();
RetVal = true;
}
catch
{
RetVal = false;
}
}
catch (Exception ex)
{
this.RaiseProgressBarClose();
throw new ApplicationException(ex.Message);
}
}//End OF IF
return RetVal;
}
/// <summary>
/// Save Summary Report Excel WorkBook.
/// </summary>
/// <param name="excelFile">Excel File</param>
private string SaveComparisonReport(ref DIExcel excelFile)
{
string RetVal = string.Empty;
string FilePath = string.Empty;
try
{
excelFile.ActiveSheetIndex = 0;
// -- Get File name from Temp folder
FilePath = Path.Combine(DICommon.DefaultFolder.DefaultSpreadSheetsFolder, Path.GetFileNameWithoutExtension(this.ReferenceDBConnection.ConnectionStringParameters.DbName)) + DICommon.FileExtension.Excel;
excelFile.SaveAs(FilePath);
RetVal = FilePath;
//OpenComparisonReport(FilePath);
}
catch { }
return RetVal;
}
/// <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;
}
