/// <summary>
/// Gets layer data for WWT layer
/// </summary>
/// <param name="workSheet">Active worksheet</param>
private void GetLayerDataForWWT(_Worksheet workSheet)
{
if (workSheet != null)
{
object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false);
if (layerData != null && layerData.Length > 0)
{
// Gets the range from the excel for data row and columns
Range currentRange = workSheet.GetRange(ThisAddIn.ExcelApplication.ActiveCell, layerData.GetLength(0), layerData.GetLength(1));
if (currentRange != null)
{
if (ValidateAffectedMappedRange(workSheet, currentRange))
{
string address = currentRange.Address;
if (currentRange != null)
{
currentRange.Select();
InsertRows(currentRange);
// Gets the new range for with the active cell address
Range newRange = workSheet.Application.Range[address];
// Creates named range for the new range
CreateRangeForLayer(newRange);
newRange.SetValue(layerData);
SetFormatForDateColumns(workSheet);
}
}
}
}
}
}
/// <summary>
/// Sets the Date format for the columns which are mapped to StartDate and EndDate.
/// </summary>
/// <param name="workSheet">Worksheet object</param>
private void SetFormatForDateColumns(_Worksheet workSheet)
{
Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress];
Range firstCell = null;
if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn != Common.Constants.DefaultColumnIndex)
{
foreach (Range area in currentLayerRange.Areas)
{
firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.StartDateColumn + 1] as Range;
Range startDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1);
startDateColumn.NumberFormat = "m/d/yyyy h:mm";
}
}
if (this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn != Common.Constants.DefaultColumnIndex)
{
foreach (Range area in currentLayerRange.Areas)
{
firstCell = area.Cells[this.currentWorkbookMap.SelectedLayerMap.LayerDetails.EndDateColumn + 1] as Range;
Range endDateColumn = workSheet.GetRange(firstCell, currentLayerRange.GetRowsCount(), 1);
endDateColumn.NumberFormat = "m/d/yyyy h:mm";
}
}
}
private void GetLayerDataForLocalInWWT(_Worksheet workSheet)
{
if (workSheet != null && this.currentWorkbookMap.SelectedLayerMap != null)
{
Range currentLayerRange = workSheet.Application.Range[this.currentWorkbookMap.SelectedLayerMap.RangeAddress];
Range validationRange = currentLayerRange;
if (currentLayerRange != null)
{
object[,] layerData = WWTManager.GetLayerData(this.currentWorkbookMap.SelectedLayerMap.LayerDetails.ID, false);
if (layerData != null && layerData.Length > 0)
{
int difference = 0;
Range rowDifferenceRange = null, columnDifferenceRange = null;
string rowaddress = string.Empty, colAddress = string.Empty;
// 1. Gets the difference from the current layer range
// 2. Gets the range with the first row cell
// 3. Inserts rows/columns to range with difference
if (currentLayerRange.Columns.Count < layerData.GetLength(1))
{
difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1));
// Take the column next to last column of the current range as first column getting inserted.
Range firstColumnCell = (Range)currentLayerRange.Cells[1, currentLayerRange.Columns.Count + 1];
columnDifferenceRange = workSheet.GetRange(firstColumnCell, currentLayerRange.GetRowsCount(), difference);
}
// The range provides a combination of the current range and column difference range,
// which is used for affected range and formula validation.
if (difference > 0)
{
validationRange = validationRange.Resize[Type.Missing, validationRange.Columns.Count + difference];
}
int rowsCount = currentLayerRange.GetRowsCount();
if (rowsCount < layerData.GetLength(0))
{
difference = GetRangeDifference(rowsCount, layerData.GetLength(0));
// Take the row next to last row of the current range as first row getting inserted.
Range firstRowCell = (Range)currentLayerRange.Cells[rowsCount + 1, 1];
rowDifferenceRange = workSheet.GetRange(firstRowCell, difference, currentLayerRange.Columns.Count);
}
// The range provides a combination of the current range and row difference range
// which is used for affected range and formula validation.
if (difference > 0)
{
validationRange = validationRange.Resize[validationRange.GetRowsCount() + difference, Type.Missing];
}
if (ValidateLocalInWWTLayerData(workSheet, validationRange))
{
Range rowRange = null, colRange = null;
if (columnDifferenceRange != null)
{
// Insert the columns which are needed to get the latest data.
colAddress = columnDifferenceRange.Address;
InsertColumns(columnDifferenceRange);
colRange = workSheet.Application.Range[colAddress];
}
if (rowDifferenceRange != null)
{
// Insert the rows which are needed to get the latest data.
rowaddress = rowDifferenceRange.Address;
InsertRows(rowDifferenceRange);
rowRange = workSheet.Application.Range[rowaddress];
}
bool createLayerForRange = false;
if (rowRange != null)
{
// Any new rows added, add them to the layer range.
createLayerForRange = true;
currentLayerRange = workSheet.Application.get_Range(rowRange, currentLayerRange);
}
if (colRange != null)
{
// Any new columns added, add them to the layer range.
createLayerForRange = true;
currentLayerRange = workSheet.Application.get_Range(colRange, currentLayerRange);
}
ThisAddIn.ExcelApplication.SheetChange -= new AppEvents_SheetChangeEventHandler(OnSheetChange);
currentLayerRange.Cells.Clear();
ThisAddIn.ExcelApplication.SheetChange += new AppEvents_SheetChangeEventHandler(OnSheetChange);
currentLayerRange.Select();
// Only in case if the range size increased, update the layer properties.
if (createLayerForRange)
{
CreateRangeForLayer(currentLayerRange);
}
else
{
// Since Range is not getting changed in case of no more rows/columns added to them, call this method to update the layer properties alone.
SetLayerRangeProperties(this.currentWorkbookMap.SelectedLayerMap.RangeName);
}
bool isResizeRangeRequired = false;
// In case if the rows are less, resize the range, instead paste only the rows having data and rest of rows will be left as empty.
// Additional empty rows cannot be deleted, since there could be data in the columns which are not part of range which will be lost.
if (currentLayerRange.GetRowsCount() > layerData.GetLength(0))
{
difference = GetRangeDifference(currentLayerRange.GetRowsCount(), layerData.GetLength(0));
currentLayerRange = currentLayerRange.Resize[currentLayerRange.GetRowsCount() - difference, Type.Missing];
isResizeRangeRequired = true;
}
// In case if the columns are less, resize the range, instead paste only the columns having data and rest of columns will be left as empty.
// Additional empty columns cannot be deleted, since there could be data in the rows which are not part of range which will be lost.
if (currentLayerRange.Columns.Count > layerData.GetLength(1))
{
difference = GetRangeDifference(currentLayerRange.Columns.Count, layerData.GetLength(1));
currentLayerRange = currentLayerRange.Resize[Type.Missing, currentLayerRange.Columns.Count - difference];
isResizeRangeRequired = true;
}
if (isResizeRangeRequired)
{
CreateRangeForLayer(currentLayerRange);
currentLayerRange.Select();
}
currentLayerRange.SetValue(layerData);
SetFormatForDateColumns(workSheet);
}
}
}
}
}
