/// <summary> /// Adds this element to the <see cref="LayeredCellsDictionary">cellsDictionary</see> /// for all worksheet row/column index postions that this entity covers. /// </summary> /// <param name="cellsDictionary">The <see cref="LayeredCellsDictionary"/> to be updated</param> internal virtual void UpdateLayeredCells(ref LayeredCellsDictionary cellsDictionary) { // Update the ExcelRowsColumnsDictionary uint endColumnIndex = this.GetEndColumnIndex(); uint endRowIndex = this.GetEndRowIndex(); for (uint colIdx = (uint)this.ExcelColumnStart; colIdx <= endColumnIndex; colIdx++) { for (uint rowIdx = (uint)this.ExcelRowStart; rowIdx <= endRowIndex; rowIdx++) { cellsDictionary.Upsert(new System.Drawing.Point((int)colIdx, (int)rowIdx), this); } } }
/// <summary> /// Adds all the entites that this element holds, plus this element itself,<br/> /// to the supplied <see cref="LayeredCellsDictionary">cellsDictionary</see><br/> /// for all worksheet row/column index postions that this entity covers. /// </summary> /// <param name="cellsDictionary">The <see cref="LayeredCellsDictionary"/> to be updated</param> internal override void UpdateLayeredCells(ref LayeredCellsDictionary cellsDictionary) { // Consider each element in maps for (uint colIdx = 1; colIdx <= this.mapColumnCount; colIdx++) { for (uint rowIdx = 1; rowIdx <= this.mapRowCount; rowIdx++) { // Use System.Drawing.Point as key as it has a very efficient hashing algorithm var cellListKey = new System.Drawing.Point((int)colIdx, (int)rowIdx); if (this.cells.ContainsKey(cellListKey)) { ExcelMapCoOrdinate element = this.cells[cellListKey]; element.UpdateLayeredCells(ref cellsDictionary); } } } // Add this as a layer base.UpdateLayeredCells(ref cellsDictionary); }
/// <summary> /// Merges all cells that have been marked to be merged in the cellinfos dictionary. /// </summary> /// <param name="worksheet">The worksheet.</param> /// <param name="totalRowCount">The total number of rows in the worksheet.</param> /// <param name="totalColCount">The total number of columns in the worksheet.</param> /// <param name="cellInfos">A dictionary of cell information, keyed by row and column index.</param> private static void MergeCells(OpenXmlSpreadsheet.Worksheet worksheet, uint totalRowCount, uint totalColCount, LayeredCellsDictionary cellInfos) { OpenXmlSpreadsheet.SheetData sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); // Process the collection of merged cells. for (uint worksheetRow = 1; worksheetRow <= totalRowCount; worksheetRow++) { for (uint worksheetCol = 1; worksheetCol <= totalColCount; worksheetCol++) { // Get cellInfo, if it has a MergeTo then merge it. var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); ExcelCellInfo cellInfo = cellInfos[currentCoOrdinate].CellInfo; if (cellInfo.MergeTo != null) { sheetData.MergeCells(cellInfo.Cell, cellInfo.MergeTo.Cell); } } } }
/// <summary> /// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>. /// </summary> /// <param name="sheetName">Name of the sheet.</param> /// <param name="worksheetPart">The worksheet part.</param> /// <param name="mapCoOrdinateContainer">The map co ordinate container.</param> /// <param name="stylesManager">The styles manager.</param> /// <param name="spreadsheetDocument">The spreadsheet document.</param> public static void WriteMapToExcel(string sheetName, WorksheetPart worksheetPart, ExcelMapCoOrdinateContainer mapCoOrdinateContainer, ExcelStylesManager stylesManager, SpreadsheetDocument spreadsheetDocument) { TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName)); var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f); // Manages the writing to Excel. var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet); //** First we need to clear the destination worksheet down (rows, columns and merged areas) excelWriteManager.EmptyWorksheet(); // Build up Columns models on all nested containers. RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel(); int colCount = columnsModel.Count(); // ========================================================== // Traverse each column assigning start and end // column indexes to the maps associated with that column. // ========================================================== RowOrColumnInfo columnInfo = columnsModel.First; while (columnInfo != null) { double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null; excelWriteManager.AddColumn(width, columnInfo.Hidden); // Assign Excel start and end columns to maps for merge operations. foreach (ExcelMapCoOrdinate map in columnInfo.Maps) { // Extend any cells that are merged across maps. if (map is ExcelMapCoOrdinatePlaceholder) { var cell = map as ExcelMapCoOrdinatePlaceholder; if (cell.MergeWith != null) { cell.ExcelColumnStart = cell.MergeWith.ExcelColumnStart; cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol } } if (map.ExcelColumnStart == 0) { map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol; map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol; } else { map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol; } } // Move on to next column in list columnInfo = columnInfo.Next; } TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount)); // Build up Rows models on all nested containers. RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel(); int rowCount = rowsModel.Count(); // ========================================================== // Traverse each row assigning start and end // row indexes to the maps associated with that row. // ========================================================== RowOrColumnInfo rowInfo = rowsModel.First; while (rowInfo != null) { double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null; excelWriteManager.AddRow(height, rowInfo.Hidden); // Assign Excel start and end rows to maps for merge operations. foreach (ExcelMapCoOrdinate map in rowInfo.Maps) { if (map.ExcelRowStart == 0) { map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow; map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow; } else { map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow; } } // Move on to next Row in list rowInfo = rowInfo.Next; } TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount)); // Build a layered cells dictionary for all cells, keyed by Excel row and column index var layeredCellsDictionary = new LayeredCellsDictionary(); mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary); TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count)); // Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information. for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++) { for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++) { // We can now use the layeredCellsDictionary to build the // excel workbook based on layered cell information var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); LayeredCellInfo layeredCellInfo = layeredCellsDictionary[currentCoOrdinate]; // Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo); } } TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount)); //** Write the 2D array of cell information to the Excel Worksheet //** building a list of areas that are to be merged. for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++) { OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow); for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++) { // Pluck out information relating to the current cell var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); ExcelCellInfo cellInfo = layeredCellsDictionary[currentCoOrdinate].CellInfo; // Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol); // All merge cells should have the same style as the source merge cell. if (cellInfo.MergeFrom != null) { // If MergeFrom is non-null, then this cell has been already been marked as a merge cell, // whose style is to be the same as the source 'MergeFrom cell. // Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged. cellInfo.MergeFrom.MergeTo = cellInfo; // Create/lookup styles in the target workbook and return index of created style uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo); // Write in to Excel (style information only) var cell = new OpenXmlSpreadsheet.Cell(); cell.SetCellReference(columnLetter, worksheetRow); cell.StyleIndex = cellStyleIndex; row.Append(cell); cellInfo.Cell = cell; } else { // Create/lookup styles in the target workbook and return index of created style uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo); // NB! If we write a Null value then we lose cell formatting information for some reason object value = cellInfo.Value == null ? string.Empty : cellInfo.Value; // Write in to Excel var cell = new OpenXmlSpreadsheet.Cell(); cell.SetCellReference(columnLetter, worksheetRow); excelWriteManager.SetCellValue(cell, value); cell.StyleIndex = cellStyleIndex; row.Append(cell); cellInfo.Cell = cell; } // Span the cell accross it's parent columns if specified if (cellInfo.LastSpanRow == 0) { cellInfo.LastSpanRow = worksheetRow; } if (cellInfo.LastSpanColumn == 0) { cellInfo.LastSpanColumn = worksheetCol; } // Merge cells if required (spanning) for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++) { for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++) { // Mark processed (so we don't over-write) var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx); if (coOrdinate != currentCoOrdinate) { var mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx); ExcelCellInfo mergeCellInfo = layeredCellsDictionary[mergeCoOrdinate].CellInfo; if (mergeCellInfo.MergeFrom == null) { mergeCellInfo.MergeFrom = cellInfo; } } } } } } //#if DEBUG // if (!skipMerge) // { //#endif // Merge cells that have been marked to merge in the cellInfos dictionary. MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary); //#if DEBUG // } //#endif TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount)); // Create a list of all of the the defined names in the container. var definedNameList = new List <ExcelDefinedNameInfo>(); mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName); // And write into Excel workbook foreach (var definedNameInfo in definedNameList) { uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1; uint rangeRowCount = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1; if (rangeRowCount > 0 && rangeColumnCount > 0) { spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName, definedNameInfo.DefinedName, (uint)definedNameInfo.StartColumnIndex, (uint)definedNameInfo.StartRowIndex, (int)rangeColumnCount, (int)rangeRowCount); } } TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType)); }