/// <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); } } } } }