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>
/// It makes sort string to be used for sorting DataView.
/// </summary>
/// <param name="sortedFields">Field class object having sorted elements.</param>
/// <returns>sort string</returns>
private string GetSortString(Fields sortedFields)
{
string RetVal = string.Empty;
try
{
if (!(sortedFields == null))
{
foreach (Field field in sortedFields.Sort)
{
if (RetVal.Length == 0)
{
RetVal = field.FieldID + " " + field.SortType.ToString().ToUpper();
}
else
{
RetVal += ", " + field.FieldID + " " + field.SortType.ToString().ToUpper();
}
}
}
}
catch (Exception)
{
RetVal = string.Empty;
//throw;
}
return RetVal;
}
/// <summary>
/// Generates DES for missing data ( blank DES + DES where data exists )
/// </summary>
/// <param name="singleWorkbook"></param>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="sortedFields"></param>
/// <param name="xlsFileNameWPath"></param>
/// <param name="includeGUID"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
public bool ExportMissingDataDES(bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataView BlankDESDataView = null;
DataTable BlankRecordsTable = null;
DataTable WhereDataExistsTable = null;
DataColumn[] PrimayKeyColumns;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
string IndicatorNId = string.Empty;
string UnitNId = string.Empty;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(true, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
IndicatorNId = dr[Indicator.IndicatorNId].ToString();
UnitNId = dr[Unit.UnitNId].ToString();
// 2.1 get records where data exists
WhereDataExistsTable = this.GetDESDataViewWhereDataExits(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, IndicatorNId, UnitNId).Table;
// 2.2 get blank records for all given combination
// get blank DES data view
BlankRecordsTable = GetBlankDESDataView(BlankDESDataView, IndicatorNId, UnitNId, singleWorkbook, dBConnection, dBQueries, userSelection, includeGUID).Table;
// 2.3. merge WhereDataExistsTable and BlankRecordsTable
// delete denominator column from BlankRecordsTable
if (BlankRecordsTable.Columns.Contains(Data.DataDenominator))
{
BlankRecordsTable.Columns.Remove(Data.DataDenominator);
}
// merge missing records & records where data exists
if (WhereDataExistsTable != null)
{
// set primary key
if (WhereDataExistsTable.Rows.Count > 0)
{
PrimayKeyColumns = this.GetPrimaryKeyColumnForMissingDataDES(WhereDataExistsTable, userSelection);
WhereDataExistsTable.PrimaryKey = PrimayKeyColumns;
}
}
if (BlankRecordsTable != null)
{
// set primary key
if (BlankRecordsTable.Rows.Count > 0)
{
PrimayKeyColumns = this.GetPrimaryKeyColumnForMissingDataDES(BlankRecordsTable, userSelection);
BlankRecordsTable.PrimaryKey = PrimayKeyColumns;
}
}
try
{
if (userSelection.TimePeriodNIds.Length == 0 || userSelection.TimePeriodNIds.Length == 0)
{
foreach (DataRow BlankDESRow in BlankRecordsTable.Rows)
{
string SelectionClause = string.Empty;
SelectionClause = Area.AreaID + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaID])) + "' AND "
+ Indicator.IndicatorName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorName])) + "' AND "
+ Unit.UnitName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitName])) + "' AND "
+ SubgroupVals.SubgroupVal + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupVal])) + "'";
if (userSelection.TimePeriodNIds.Length > 0)
{
SelectionClause += " AND " + Timeperiods.TimePeriod + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Timeperiods.TimePeriod])) + "'";
}
if (userSelection.SourceNIds.Length > 0)
{
SelectionClause += " AND " + IndicatorClassifications.ICName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[IndicatorClassifications.ICName])) + "'";
}
if (WhereDataExistsTable.Select(SelectionClause).Length == 0)
{
DataRow NewRow = WhereDataExistsTable.NewRow();
// add timeperiod, Area_ID,Area_name,IC_name,Indicator_name,indicator_gid, Unit_Name,unit_gid, Sugbroup_Val, Subgroup_val_gid
// Timeperiod
NewRow[Timeperiods.TimePeriod] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Timeperiods.TimePeriod]));
// source
NewRow[IndicatorClassifications.ICName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[IndicatorClassifications.ICName]));
// Area
NewRow[Area.AreaID] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaID]));
NewRow[Area.AreaName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaName]));
//Indicator
NewRow[Indicator.IndicatorName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorName]));
if (BlankRecordsTable.Columns.Contains(Indicator.IndicatorGId))
{
NewRow[Indicator.IndicatorGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorGId]));
}
//Unit
NewRow[Unit.UnitName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitName]));
if (BlankRecordsTable.Columns.Contains(Unit.UnitGId))
{
NewRow[Unit.UnitGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitGId]));
}
// subgroup_val
NewRow[SubgroupVals.SubgroupVal] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupVal]));
if (BlankRecordsTable.Columns.Contains(SubgroupVals.SubgroupValGId))
{
NewRow[SubgroupVals.SubgroupValGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupValGId]));
}
WhereDataExistsTable.Rows.Add(NewRow);
WhereDataExistsTable.AcceptChanges();
}
}
}
else
{
WhereDataExistsTable.Merge(BlankRecordsTable, true, MissingSchemaAction.AddWithKey);
}
}
catch (Exception ex)
{
}
// add missing columns
if (WhereDataExistsTable.Columns.Contains(Data.DataDenominator) == false)
{
WhereDataExistsTable.Columns.Add(Data.DataDenominator);
}
FilteredDataView = WhereDataExistsTable.DefaultView;
//////// 2.4 Limit DataRows to 65,536 as Excels sheet has 65,536 rows limit.
//////if (FilteredDataView.Table.Rows.Count > 65500)
//////{
////// for (int i = FilteredDataView.Table.Rows.Count - 1; i > 65500; i--)
////// {
////// FilteredDataView.Table.Rows[i].Delete();
////// }
//////}
//////FilteredDataView.Table.AcceptChanges();
// 2.5 update filteredDataView
try
{
// Rename GID columns if not required.
if (!(includeGUID))
{
FilteredDataView.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
FilteredDataView.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
if (FilteredDataView.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
{
FilteredDataView.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
}
}
//// Update Sector , Class in DataView
//DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, IndicatorNId, UnitNId);
FilteredDataView.Table.AcceptChanges();
}
catch (Exception)
{
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
//// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath, dBConnection, dBQueries, IndicatorNId, UnitNId);
}
catch (Exception)
{
}
}
}
//if (singleWorkbook)
//{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
//}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
/// <summary>
/// Generates DES where data exists
/// </summary>
/// <param name="singleWorkbook"></param>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="sortedFields"></param>
/// <param name="xlsFileNameWPath"></param>
/// <param name="includeGUID"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
public bool ExportDESWhereDataExists(bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataTable MissingRecordTable;
DataTable WhereDataExistsTable;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
string IndicatorNId = string.Empty;
string UnitNId = string.Empty;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(false, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
IndicatorNId = dr[Indicator.IndicatorNId].ToString();
UnitNId = dr[Unit.UnitNId].ToString();
// get records where data exists
FilteredDataView = this.GetDESDataViewWhereDataExits(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, IndicatorNId, UnitNId);
// update filteredDataView
try
{
// Rename GID columns if not required.
if (!(includeGUID))
{
FilteredDataView.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
FilteredDataView.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
if (FilteredDataView.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
{
FilteredDataView.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
}
}
//// Update Sector , Class in DataView
//DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, IndicatorNId, UnitNId);
FilteredDataView.Table.AcceptChanges();
}
catch (Exception)
{
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
//// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath, dBConnection, dBQueries, IndicatorNId, UnitNId);
}
catch (Exception)
{
}
}
}
if (singleWorkbook)
{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
/// <summary>
/// Generates DevInfo Data Entry Spreadsheet using Database as data source.
/// </summary>
/// <param name="singleWorkbook">true, if single workbook is required.</param>
/// <param name="emptyDES">true, to get missing records</param>
/// <param name="withWhereDataExists">true to get records where data exists</param>
/// <param name="dBConnection">DbConnection object of source database.</param>
/// <param name="dBQueries">DBQueries object of source database.</param>
/// <param name="userSelection">UserSelection object containg NIDs of Indicator, Unit, Subgroup, Area, Source, TimePeriod</param>
/// <param name="xlsFileNameWPath">workbook file name with path.</param>
/// <param name="includeGUID">true, if GUID of I, U, S to be included in DES.</param>
/// <param name="languageFileNameWPath">Xml language file name with path, for language handling.</param>
/// <returns></returns>
public bool ExportDataEntrySpreadsheet(bool singleWorkbook, bool emptyDES, bool withWhereDataExists, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataTable MissingRecordTable;
DataTable WhereDataExistsTable;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(emptyDES, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count );
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
//// Prepare DES DataView for given IU
//if (emptyDES && FilteredDataView != null)
//{
// // If FilteredDataView is already made, Dataview will be same for every IU,
// // then use existing Dataview
// // Update Indicator & Unit in same DataView
// DIExport.UpdateIndicatorUnitInDataView(ref FilteredDataView, dBConnection, dBQueries, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
// // Update Sector , Class in DataView
// DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
//}
//else
// get missing records with where data exists
if (emptyDES & withWhereDataExists)
{
//-- CASE: Missing Records + DATA Exists
//get records where data exists
WhereDataExistsTable = DIExport.GetDESDataViewForIU(includeGUID, false, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString()).Table;
//get missing records
MissingRecordTable = DIExport.GetMissingRecords(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString()).Table;
List<String> RequiredColumns = new List<string>();
foreach (DataColumn Column in MissingRecordTable.Columns)
{
if (Column.ColumnName != Data.DataDenominator)
RequiredColumns.Add(Column.ColumnName);
}
MissingRecordTable = MissingRecordTable.DefaultView.ToTable(true, RequiredColumns.ToArray());
// merge missing records & records where data exists
WhereDataExistsTable.Merge(MissingRecordTable);
// add missing columns
if (WhereDataExistsTable.Columns.Contains(Data.DataDenominator) == false)
{
WhereDataExistsTable.Columns.Add(Data.DataDenominator);
}
FilteredDataView = WhereDataExistsTable.DefaultView;
}
else // get missing records or where data exists
{
//-- CASE 1: blank Records
//-- CASE 2: Where Data Exists
FilteredDataView = DIExport.GetDESDataViewForIU(includeGUID, emptyDES, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
}
//raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath);
}
catch (Exception)
{
}
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue);
}
//if (singleWorkbook)
//{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
//}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
/// <summary>
/// Generates DevInfo Data Entry Spreadsheet using DataView as data source.
/// <para>DataView must have manadatory columns</para>
/// <para>Mandatory columns (Indicator_Name, Unit_Name, Subgroup_Val, TimePeriod, AreaID, Area_Name, DataValue, ICSource, Footnote, DataDenominator)</para>
/// <para>Optional columns: Indicator_GID, Unit_GID, Subgroup_Val_Gid, Sector (IC_Name), Class (IC_Name)</para>
/// <para>To insert Sector & Class in a DataView, Developer can use DIExport.AddSectorClassInDataTable()</para>
/// </summary>
/// <param name="singleWorkbook">true, if single workbook is required.</param>
/// <param name="desDataView">DataView containing Data required for DES generation. Columns required for DES are mentioned above.
/// </param>
/// <param name="xlsFileNameWPath">workbook file name with path.</param>
/// <param name="languageFileNameWPath">Xml language file name with path, for language handling.</param>
/// <returns></returns>
public bool ExportDataEntrySpreadsheet(bool singleWorkbook, DataView desDataView, bool includeGUID, Fields sortedFields, string xlsFileNameWPath, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
string[] IndicatorUnitColumns = new string[2];
string RowFilter = string.Empty;
string SortFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
try
{
// 1. Check if DataView is valid to be used to generate DES.
if ((DIExport.IsValidDESDataView(desDataView)))
{
// 1) Remove GUID columns in desDataView , if GUID is not required.
if (!includeGUID)
{
if (desDataView.Table.Columns.Contains(Indicator.IndicatorGId))
{
desDataView.Table.Columns.Remove(Indicator.IndicatorGId);
}
if (desDataView.Table.Columns.Contains(Unit.UnitGId))
{
desDataView.Table.Columns.Remove(Unit.UnitGId);
}
if (desDataView.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
{
desDataView.Table.Columns.Remove(SubgroupVals.SubgroupValGId);
}
}
desDataView.Table.AcceptChanges();
// 2. Get distinct Indicator, Unit from sourceDataView.
IndicatorUnitColumns[0] = Indicator.IndicatorName;
IndicatorUnitColumns[1] = Unit.UnitName;
DistinctIU = desDataView.ToTable(true, IndicatorUnitColumns);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 4. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
IUCounter++;
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue);
this.RaiseEndIndicatorUnit(); //Indicates that previous indicator unit is processed.
// 4.1) Set rowfilter on sourceDataview
desDataView.RowFilter = Indicator.IndicatorName + " = '" + DICommon.RemoveQuotes(dr[Indicator.IndicatorName].ToString()) + "' AND " + Unit.UnitName + " = '" + DICommon.RemoveQuotes(dr[Unit.UnitName].ToString()) + "'";
// 4.2) Set sort order on desDataView.
desDataView.Sort = this.GetSortString(sortedFields);
// 4.3) Get DataView for selected Indicator & Unit , by applying rowfilter on sourceDataview
DataView FilteredDataView = desDataView.ToTable().DefaultView;
desDataView.RowFilter = string.Empty;
desDataView.Sort = string.Empty;
//// 4.4) Set indicator, unit Name, and DataCount in progress bar event
//this.RaiseProcessIndicatorUnitInfo(dr[Indicator.IndicatorName].ToString(), dr[Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// 4.5) Process workbook. Fills it with Data fromm DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath);
}
//if (singleWorkbook)
//{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
//}
RetVal = true;
}
else
{
// TODO: validate exception message.
throw new ApplicationException("Invalid dataview. Column missing.");
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
/// <summary>
/// Inser the data value column and its data value
/// </summary>
/// <param name="fields"></param>
private void InserDataValueColumn(Fields fields, int columnCount)
{
int RowCount = -1;
int ColumnCount = -1;
string Range = string.Empty;
int FormulaRange = -1;
int NewColumnIndex = -1;
//-- Get the index of the column
InsertColumnIndex = columnCount + fields.Rows.Count;
NewColumnIndex = this.InsertColumnIndex;
if (this.ExcelFile.GetUsedRange(TableSheetIndex).Columns.Count > 2)
{
FormulaRange = fields.Rows.Count + 1;
}
else
{
FormulaRange = this.InsertColumnIndex - 1;
}
//-- Used row count
RowCount = this.ExcelFile.GetUsedRange(TableSheetIndex).Rows.Count;
ColumnCount = this.ExcelFile.GetUsedRange(TableSheetIndex).Columns.Count;
for (int ColumnIndex = FormulaRange; ColumnIndex < ColumnCount; ColumnIndex += 2)
{
//-- Copy the datavalues through formula
for (int Index = 0; Index < RowCount; Index++)
{
string ColumnRange = this.ExcelFile.GetRange(TableSheetIndex, Index, ColumnIndex, Index, ColumnIndex);
if (Index == 0)
{
this.ExcelFile.ExecuteFormula(TableSheetIndex, Index, NewColumnIndex, Index, NewColumnIndex, "=" + ColumnRange);
}
else
{
this.ExcelFile.ExecuteFormula(TableSheetIndex, Index, NewColumnIndex, Index, NewColumnIndex, "=-" + ColumnRange);
}
this.ExcelFile.SetForegroundColor(TableSheetIndex, NewColumnIndex, System.Drawing.Color.White);
}
NewColumnIndex += 1;
}
}
/// <summary>
/// Generate the pyramid series
/// </summary>
/// <returns></returns>
private Dictionary<string, IRange> GetPyramidSeries(Fields fields, ChartMode chartMode)
{
Dictionary<string, IRange> RetVal = new Dictionary<string, IRange>();
try
{
int Index=0;
int StartRowIndex = 1;
int FormulaRange = -1;
IRange Range = null;
string ColumnHeader = string.Empty;
string TempAreaName = string.Empty;
string AreaName = string.Empty;
int RowCount = this.ExcelFile.GetUsedRange(TableSheetIndex).Rows.Count;
int ColumnCount = this.ExcelFile.GetUsedRange(TableSheetIndex).Columns.Count;
int NewColumnIndex = this.InsertColumnIndex;
if (fields.Rows.Count > 1)
{
if (this.ExcelFile.GetUsedRange(TableSheetIndex).Columns.Count > 2)
{
FormulaRange = fields.Rows.Count;
}
else
{
FormulaRange = this.InsertColumnIndex - 2;
}
AreaName = this.ExcelFile.GetCellValue(TableSheetIndex, 1, AreaColumnIndex, 1, AreaColumnIndex);
for (int ColumnIndex = FormulaRange; ColumnIndex < this.InsertColumnIndex; ColumnIndex += 2)
{
for (int RowIndex = 2; RowIndex < RowCount; RowIndex++)
{
TempAreaName = this.ExcelFile.GetCellValue(TableSheetIndex, RowIndex, AreaColumnIndex, RowIndex, AreaColumnIndex);
if (AreaName.ToLower() != TempAreaName.ToLower())
{
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, ColumnIndex, 0, ColumnIndex).Replace("\n", "-");
if (Index == 0)
{
//this._DataSourceDimensions = StartRowIndex.ToString() + "," + DimensionIndex.ToString() + "," + (RowIndex - 1) + "," + (this.InsertColumnIndex - 2);
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, DimensionIndex, RowIndex - 1, FormulaRange);
RetVal.Add(AreaName + " - " + ColumnHeader, Range);
}
else
{
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, RowIndex, ColumnIndex, RowIndex, ColumnIndex);
RetVal.Add(AreaName + " - " + ColumnHeader, Range);
}
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, NewColumnIndex, 0, NewColumnIndex).Replace("\n", "-");
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, RowIndex, NewColumnIndex, RowIndex, NewColumnIndex);
RetVal.Add(AreaName + " - " + ColumnHeader, Range);
AreaName = TempAreaName;
StartRowIndex = RowIndex;
}
}
Index += 1;
StartRowIndex = 2;
NewColumnIndex += 1;
}
}
else
{
for (int ColumnIndex = 1; ColumnIndex < this.InsertColumnIndex; ColumnIndex += 2)
{
if (Index == 0)
{
if (chartMode == ChartMode.Insert)
{
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, 1, RowCount - 1, 1);
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, 1, 0, 1).Replace("\n", "-");
RetVal.Add(ColumnHeader, Range);
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, 0, RowCount - 1, 0);
ColumnHeader = "1";
RetVal.Add(ColumnHeader, Range);
}
else if (chartMode == ChartMode.ExcelInsert)
{
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, 0, RowCount - 1, 1);
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, 1, 0, 1).Replace("\n", "-");
RetVal.Add(ColumnHeader, Range);
}
else if (chartMode == ChartMode.Edit || chartMode == ChartMode.ExcelEdit)
{
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, 0, RowCount - 1, fields.Columns.Count - 1);
ColumnHeader = "";
RetVal.Add(ColumnHeader, Range);
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, 1, RowCount - 1, 1);
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, 1, 0, 1).Replace("\n", "-");
RetVal.Add(ColumnHeader, Range);
}
}
else
{
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, ColumnIndex, RowCount - 1, ColumnIndex);
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, ColumnIndex, 0, ColumnIndex).Replace("\n", "-");
if (!string.IsNullOrEmpty(ColumnHeader.Trim()))
{
RetVal.Add(ColumnHeader, Range);
}
}
Range = this.ExcelFile.GetSelectedRange(TableSheetIndex, StartRowIndex, NewColumnIndex, RowCount - 1, NewColumnIndex);
ColumnHeader = this.ExcelFile.GetCellValue(TableSheetIndex, 0, NewColumnIndex, 0, NewColumnIndex).Replace("\n", "-");
if (!string.IsNullOrEmpty(ColumnHeader.Trim()))
{
RetVal.Add(ColumnHeader, Range);
}
Index += 1;
NewColumnIndex += 1;
}
}
}
catch (Exception)
{
}
return RetVal;
}
public DataviewPreference(string languageFileName)
{
this._Fields = new Fields();
this.FillAllFields(languageFileName);
this.FillAvailableList();
this.FillSelectedList();
}
