private static void AggregateRows(Interop.Range row2, ref Dictionary <string, object> resultRow,
int firstColumnInTableExcelIndex, int lastColumnInTableExcelIndex,
int firstRowInTableExcelIndex, int lastRowInTableExcelIndex, Workbook wb, Worksheet activeWS)
{
try
{
int tableColumnNum = 1;
string columnName;
foreach (Interop.Range row2Cell in row2.Columns)
{
columnName = "Column " + tableColumnNum;
if (Transform.usrInputForAggregationProcess.Keys.Contains(columnName))
{
// do the operations on the same column for both rows based on the new column type
// we know that each cell of the column can be converted to this type because Validator.AllTblColsCanBeParsedToTheNewType()
// passed
// convert cell from result and cell from row2 to the new column data type
switch (Transform.usrInputForAggregationProcess[columnName].ColumnDataType)
{
case "string":
// escape to CSV " to ""
StringCellsHandler.AggregateCells(columnName, tableColumnNum, row2Cell, ref resultRow);
break;
case "double":
NumberCellsHandler.AggregateCells(columnName, tableColumnNum, row2Cell, ref resultRow);
break;
case "boolean":
BooleanCellsHandler.AggregateCells(columnName, tableColumnNum, row2Cell, ref resultRow);
break;
case "datetime":
DateTimeCellsHandler.AggregateCells(columnName, tableColumnNum, row2Cell, ref resultRow);
break;
// 16 March 2018 - cells can't be converted to a "null" column data type
//case "null":
// EmptyCellsHandler.AggregateCells(columnName, tableColumnNum, row2Cell, ref resultRow);
// break;
default:
throw new Exception($"Can't handle aggregation operation for column data type {Transform.usrInputForAggregationProcess[columnName].ColumnDataType}.");
}
}
tableColumnNum++;
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void CellValueToStr(Interop.Range cell, int tblColIdx, out string cellValToObj)
{
string typeName;
if (cell.Value == null)
{
typeName = "null";
}
else
{
typeName = cell.Value.GetType().ToString().Replace("System.", "").ToLower();
}
switch (typeName)
{
case "string":
cellValToObj = Convert.ToString(cell.Value2);
break;
case "double":
cellValToObj = Convert.ToString(
NumberCellsHandler.ConvertToDecimalDataType(cell.Value2)
);
break;
case "boolean":
try
{
cellValToObj = Convert.ToString(Convert.ToBoolean(cell.Value2)).ToUpper();
}
catch (FormatException ex)
{
throw new FormatException(
$"The {cell.Value2.GetType().Name} value {Convert.ToString(cell.Value2)} is not recognized as a valid boolean value."
);
}
catch (InvalidCastException ex)
{
throw new InvalidCastException(
$"Conversion of the {cell.Value.GetType().Name} value {Convert.ToString(cell.Value)} to a boolean value is not supported."
);
}
break;
case "datetime":
cellValToObj = Convert.ToString(DateTimeCellsHandler.CovertCellValuesToDateTime(cell.Value2));
break;
case "null":
cellValToObj = String.Empty;
break;
default:
throw new Exception($"Application can't convert {typeName} table cells to their string representation.");
}
}
public static void CellValueToObject(Interop.Range cell, out object cellValToObj)
{
string typeName;
if (cell.Value == null)
{
typeName = "null";
}
else
{
typeName = cell.Value.GetType().ToString().Replace("System.", "").ToLower();
}
switch (typeName)
{
case "string":
cellValToObj = cell.Value2;
break;
case "double":
try
{
cellValToObj = (Convert.ToDecimal(cell.Value2));
}
catch (OverflowException ex)
{
throw new OverflowException($"The {cell.Value2.GetType().Name} value {Convert.ToString(cell.Value2)} is out of range of the Decimal type.");
}
catch (FormatException ex)
{
throw new FormatException($"The {cell.Value2.GetType().Name} value {Convert.ToString(cell.Value2)} is not recognized as a valid Decimal value.");
}
catch (InvalidCastException ex)
{
throw new InvalidCastException($"Conversion of the {cell.Value2.GetType().Name} value {Convert.ToString(cell.Value2)} to a Decimal is not supported.");
}
break;
case "boolean":
try
{
cellValToObj = Convert.ToBoolean(cell.Value2);
}
catch (FormatException ex)
{
throw new FormatException(
$"The {cell.Value2.GetType().Name} value {Convert.ToString(cell.Value2)} is not recognized as a valid boolean value."
);
}
catch (InvalidCastException ex)
{
throw new InvalidCastException(
$"Conversion of the {cell.Value.GetType().Name} value {Convert.ToString(cell.Value)} to a boolean value is not supported."
);
}
break;
case "datetime":
cellValToObj = DateTimeCellsHandler.CovertCellValuesToDateTime(cell.Value2);
break;
case "null":
cellValToObj = String.Empty;
break;
default:
throw new Exception($"Application can't convert {typeName} table cells to {typeName} data structure.");
}
}
