/// <summary> /// Walks down the list of layered maps, and determines information that is to be written into a single cell. /// </summary> /// <param name="coOrdinate">The co ordinate.</param> /// <param name="layeredCellInfo">The layered cell information.</param> private static void ProcessLayeredCellMaps(System.Drawing.Point coOrdinate, LayeredCellInfo layeredCellInfo) { var cellInfo = new ExcelCellInfo(); System.Drawing.Point toCoOrdinate = coOrdinate; // Work up the layers (ie. Cell up to Worksheet) foreach (var map in layeredCellInfo.LayeredMaps) { // For cells, read the value if (map is ExcelMapCoOrdinateCell) { var cell = ((ExcelMapCoOrdinateCell)map); cellInfo.Value = cell.CurrentValue; } // For Placeholder derived maps (cells + placeholders), // determine the 'Span to' information for row/column + layer the style if (map is ExcelMapCoOrdinatePlaceholder) { var placeholder = ((ExcelMapCoOrdinatePlaceholder)map); // If this lower level cell is flagged to span (to end of the higher level containing element), then apply if (placeholder.SpanLastColumn) { cellInfo.LastSpanColumn = placeholder.GetEndColumnIndex(); // higherLayerLastColumn toCoOrdinate.X = (int)cellInfo.LastSpanColumn; } else { cellInfo.LastSpanColumn = (uint)placeholder.ExcelColumnEnd; toCoOrdinate.X = (int)cellInfo.LastSpanColumn; } // If this lower level cell is flagged to span (to end of the higher level containing element), then apply if (placeholder.SpanLastRow) { cellInfo.LastSpanRow = placeholder.GetEndRowIndex(); // higherLayerLastRow; toCoOrdinate.Y = (int)cellInfo.LastSpanRow; } else { cellInfo.LastSpanRow = (uint)placeholder.ExcelRowEnd; toCoOrdinate.Y = (int)cellInfo.LastSpanRow; } // A cell, extract the value and set the base styles cellInfo.StyleInfo.ApplyCellStyles(placeholder.Styles); } // For containers, layer the style, taking into consideration the // position of the current co-ordinate relative to (within) the container if (map is ExcelMapCoOrdinateContainer) { // A container, update the base style var container = ((ExcelMapCoOrdinateContainer)map); cellInfo.StyleInfo.Update(container, coOrdinate.X, coOrdinate.Y, toCoOrdinate.X, toCoOrdinate.Y); } } // Update the layered cell with the result layeredCellInfo.CellInfo = cellInfo; }
/// <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)); }