/// <summary>
/// Create a number format in the stylesheet and apply to the cellFormat
/// </summary>
/// <param name="cellInfo"></param>
/// <param name="formatCode"></param>
/// <param name="stylesheet"></param>
/// <param name="cellFormat"></param>
private static void UpdateNumberFormat(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, string formatCode, ref Stylesheet stylesheet, ref CellFormat cellFormat)
{
if (cellFormat == null)
{
throw new ArgumentNullException("cellFormat");
}
string numFormat = cellInfo.NumberFormat ?? formatCode;
// Anything to apply?
if (string.IsNullOrEmpty(numFormat))
{
return;
}
// Look up existing...
var item = stylesManager.numberFormats.Find(numFormat);
UInt32Value id = item.Value;
if (id == null)
{
// Create (and append?) a new numbering format in the stylesheet
id = stylesheet.AddNumberingFormat(cellInfo.NumberFormat ?? formatCode);
stylesManager.numberFormats.Add(numFormat, id);
}
cellFormat.NumberFormatId = id;
}
/// <summary>
/// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour.
/// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there.
/// </summary>
/// <param name="stylesheet"></param>
/// <param name="cellInfo"></param>
/// <returns></returns>
private static void UpdateBorder(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
{
if (cellFormat == null)
{
throw new ArgumentNullException("cellFormat");
}
if (cellInfo.BorderInfo != null && cellInfo.BorderInfo.HasBorder)
{
var item = stylesManager.borders.Find(cellInfo.BorderInfo);
UInt32Value id = item.Value;
if (id == null)
{
// Add and return the index of a fill colour
id = CreateNewBorder(stylesheet, cellInfo.BorderInfo);
stylesManager.borders.Add(cellInfo.BorderInfo, id);
}
cellFormat.BorderId = id;
}
}
/// <summary>
/// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour.
/// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there.
/// </summary>
/// <param name="stylesheet"></param>
/// <param name="cellInfo"></param>
/// <returns></returns>
private static void UpdateFillColour(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
{
if (cellFormat == null)
{
throw new ArgumentNullException("cellFormat");
}
if (cellInfo.FillColour != null && cellInfo.FillColour.HasValue && cellInfo.FillColour.Value != System.Windows.Media.Colors.Transparent)
{
var fillItem = stylesManager.fills.Find(cellInfo.FillColour.Value);
UInt32Value fillId = fillItem.Value;
if (fillId == null)
{
// Add and return the index of a fill colour (Badly named as simply returns index of existing colour if it already exists)
fillId = stylesheet.AddIndexedColor(cellInfo.FillColour.Value);
stylesManager.fills.Add(cellInfo.FillColour.Value, fillId);
}
cellFormat.FillId = fillId;
}
}
/// <summary>
/// Creates a new style in the excel <see cref="Stylesheet"/> based on the supplied <see cref="ExportStyle"/>.<br/>
/// Return the index of the newly created style.
/// </summary>
/// <param name="stylesheet"></param>
/// <param name="cellInfo"></param>
/// <param name="formatCode"></param>
/// <returns></returns>
private static uint CreateNewStyle(ExcelStylesManager stylesManager, Stylesheet stylesheet, ExcelCellStyleInfo cellInfo, string formatCode)
{
// Create a cellFormat to which style attributes can be applied.
var cellFormat = new CellFormat();
// Looks up/creates a new fill colour in the stylesheet and applies it to the cellFormat
UpdateFillColour(stylesManager, cellInfo, ref stylesheet, ref cellFormat);
// Create and append a new font to the stylesheet and applies it to the cellFormat
UpdateFont(stylesManager, cellInfo, ref stylesheet, ref cellFormat);
// Create a number format in the stylesheet and apply to the cellFormat
UpdateNumberFormat(stylesManager, cellInfo, formatCode, ref stylesheet, ref cellFormat);
// Creates a border in the stylesheet and applies it to the cellFormat
UpdateBorder(stylesManager, cellInfo, ref stylesheet, ref cellFormat);
// Create an Allignment object which manages indentation, alignment and text rotation and applies it to the cellFormat.
UpdateTextAlignmentAndRotation(cellInfo, ref cellFormat);
uint styleIndex = stylesheet.AddCellFormat(cellFormat);
return(styleIndex);
}
/// <summary>
/// Updates the supplied <see cref="CellFormat"/> with a font index which relates to the fund in the supplied <see cref="ExportStyle"/>
/// </summary>
/// <param name="stylesheet"></param>
/// <param name="cellInfo"></param>
/// <returns></returns>
private static void UpdateFont(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
{
if (cellFormat == null)
{
throw new ArgumentNullException("cellFormat");
}
// Anything to apply?
if (cellInfo.FontInfo == null)
{
return;
}
// Look up existing...
var fontItem = stylesManager.fonts.Find(cellInfo.FontInfo);
UInt32Value id = fontItem.Value;
if (id == null)
{
// Fonts
Font font = new Font();
if (cellInfo.FontInfo.FontFamily != null)
{
font.FontName = new FontName()
{
Val = new StringValue(cellInfo.FontInfo.FontFamily.ToString())
};
}
font.FontSize = new FontSize()
{
Val = new DoubleValue(cellInfo.FontInfo.FontSize)
};
if ((cellInfo.FontInfo.FontWeight != null) &&
(cellInfo.FontInfo.FontWeight == FontWeights.Bold))
{
font.Bold = new Bold();
}
if (cellInfo.FontInfo.FontStyle != null)
{
if (cellInfo.FontInfo.FontStyle == FontStyles.Italic)
{
font.Italic = new Italic();
}
}
font.Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
{
Rgb = new HexBinaryValue(StyleTranslator.Translate(cellInfo.FontInfo.FontColour))
};
if (cellInfo.FontInfo.FontUnderlined)
{
font.Underline = new Underline();
}
id = stylesheet.AddFont(font);
stylesManager.fonts.Add(cellInfo.FontInfo, id);
}
cellFormat.FontId = id;
}
/// <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));
}
