// Write
internal static void WriteWorksheetFormatToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, WorksheetFormat worksheetFormat)
{
if (worksheetFormat.HasValue())
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.SheetFormatProperties());
if (worksheetFormat.DefaultRowHeight > 0)
writer.WriteAttribute("defaultRowHeight", worksheetFormat.DefaultRowHeight);
writer.WriteEndElement(); // SheetFormatProperties
}
}
// Write
internal static void WriteMergeCellsToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Worksheet worksheet)
{
List<string> mergeCells = MergeCell.GetMergeCellsFromWorksheet(worksheet);
if (mergeCells.Count > 0)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.MergeCells());
foreach (string mergeCell in mergeCells)
{
MergeCell.WriteMergeCellToWriter(writer, mergeCell);
}
writer.WriteEndElement(); // MergeCells
}
}
// Write
internal static void WriteHyperlinksToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Worksheet worksheet)
{
List<Cell> hyperlinkCells = new List<Cell>();
foreach (Row row in worksheet.Rows)
{
hyperlinkCells.AddRange(row.Cells.Where(c => c.HyperlinkToCell != null));
}
if (hyperlinkCells.Count > 0)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Hyperlinks());
foreach (Cell cell in hyperlinkCells)
{
Hyperlink.WriteHyperlinkToWriter(writer, cell);
}
writer.WriteEndElement(); // Hyperlinks
}
}
// Write
internal static void WriteSheetViewsToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, SheetViews sheetViews)
{
if (sheetViews.HasValue())
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.SheetViews());
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.SheetView());
if (sheetViews.Selected)
writer.WriteAttribute("tabSelected", sheetViews.Selected);
// the implementation of worksheetViewId gets quite complicated. The use isn't supported here, but the attribute is necessary to compile.
writer.WriteAttribute("workbookViewId", 0);
if (sheetViews.FrozenRow > 0 || sheetViews.FrozenColumn > 0)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Pane());
writer.WriteAttribute("state", "frozen");
if (sheetViews.FrozenRow > 0) writer.WriteAttribute("ySplit", sheetViews.FrozenRow);
if (sheetViews.FrozenColumn > 0) writer.WriteAttribute("xSplit", sheetViews.FrozenColumn);
writer.WriteAttribute("topLeftCell", sheetViews.Worksheet.Cells[sheetViews.FrozenRow + 1, sheetViews.FrozenColumn + 1].Address);
writer.WriteEndElement(); // Pane
}
writer.WriteEndElement(); // SheetView
writer.WriteEndElement(); // SheetViews
}
}
// Write
internal static void WriteRowToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Row row)
{
if (row.Cells.FirstOrDefault(c => c.IsUsed) == null)
return;
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Row());
writer.WriteAttribute("r", row.Index);
if (row.StyleIndex > CellFormat.DefaultStyleIndex) writer.WriteAttribute("s", row.StyleIndex);
if (row.Height > 0 && row.Height != row.Worksheet.Format.DefaultRowHeight) writer.WriteAttribute("ht", row.Height);
row.Cells.Action(c => Cell.WriteCellToWriter(writer, c));
writer.WriteEndElement(); // Row
}
// Write
internal static void WriteHeaderFooterToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, HeaderFooter headerFooter)
{
if (headerFooter.HasValue())
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.HeaderFooter());
if (!headerFooter.AlignWithMargins) writer.WriteAttribute("alignWithMargins", headerFooter.AlignWithMargins);
if (headerFooter.OddHeader != "")
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.OddHeader());
writer.WriteText(headerFooter.OddHeader);
writer.WriteEndElement(); // OddHeader
}
if (headerFooter.OddFooter != "")
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.OddFooter());
writer.WriteText(headerFooter.OddFooter);
writer.WriteEndElement(); // OddFooter
}
writer.WriteEndElement(); // HeaderFooter
}
}
private static void WriteStylesToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorkbookStylesPart> writer, Styles styles)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Stylesheet());
if (styles._numberFormats != null) NumberFormatCollection.WriteNumberFormatsToWriter(writer, styles.NumberFormats);
if (styles._fonts != null) FontCollection.WriteFontsToWriter(writer, styles.Fonts);
if (styles._fills != null) FillCollection.WriteFillsToWriter(writer, styles.Fills);
if (styles._borders != null) BordersCollection.WriteBordersToWriter(writer, styles.Borders);
if (styles._cellFormats != null) CellFormatCollection.WriteCellFormatsToWriter(writer, styles.CellFormats);
if (styles.IndexedColors != null) Color.WriteIndexedColorsToWriter(writer, styles.IndexedColors);
writer.WriteEndElement(); // Stylesheet
}
// Write
private static void SaveStyles(Styles styles)
{
using (CustomOpenXmlWriter<OpenXmlPackaging.WorkbookStylesPart> writer =
new CustomOpenXmlWriter<OpenXmlPackaging.WorkbookStylesPart>(styles.Workbook.Document.WorkbookPart.WorkbookStylesPart)
)
{
Styles.WriteStylesToWriter(writer, styles);
}
}
private static void WriteHyperlinkToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Cell cell)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Hyperlink());
writer.WriteAttribute("ref", cell.Address);
writer.WriteAttribute("display", cell.Text);
writer.WriteAttribute("location", GetTargetAddress(cell.HyperlinkToCell));
writer.WriteEndElement(); // Hyperlink
}
// Write
private static void SaveWorksheetData(WorksheetData worksheetData)
{
OpenXmlPackaging.WorksheetPart worksheetPart = Worksheet.GetWorksheetPartByWorksheet(worksheetData.Worksheet);
using (CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer = new CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart>(worksheetPart))
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Worksheet());
if (worksheetData._rows != null)
RowCollection.WriteSheetDimensionToWriter(writer, worksheetData.Worksheet);
if (worksheetData._sheetViews != null)
SheetViews.WriteSheetViewsToWriter(writer, worksheetData.SheetViews);
if (worksheetData._format != null)
WorksheetFormat.WriteWorksheetFormatToWriter(writer, worksheetData.Format);
if (worksheetData._columns != null)
ColumnCollection.WriteColumnsToWriter(writer, worksheetData.Columns);
if (worksheetData._rows != null)
RowCollection.WriteRowsToWriter(writer, worksheetData.Rows);
// should probably store merge cells and hyperlinks in their own objects. Reference them from the cells
MergeCell.WriteMergeCellsToWriter(writer, worksheetData.Worksheet);
Hyperlink.WriteHyperlinksToWriter(writer, worksheetData.Worksheet);
if (worksheetData._printOptions != null)
PrintOptions.WritePrintOptionsToWriter(writer, worksheetData.PrintOptions);
if (worksheetData._pageMargins != null)
PageMargins.WritePageMarginsToWriter(writer, worksheetData.PageMargins);
if (worksheetData._pageSetup != null)
PageSetup.WritePageSetupToWriter(writer, worksheetData.PageSetup);
if (worksheetData._headerFooter != null)
HeaderFooter.WriteHeaderFooterToWriter(writer, worksheetData.HeaderFooter);
if (worksheetData._drawing != null)
Drawing.WriteDrawingToWorksheetWriter(writer, worksheetData.Drawing);
writer.WriteEndElement(); // Worksheet
}
if (worksheetData._drawing != null)
worksheetData.Drawing.Save();
}
// Write
internal static void WriteCellToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Cell cell)
{
if (!cell.IsUsed)
return;
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Cell());
string dataType = GetAttributeValueFromCellDataType(cell.CellDataType);
if (dataType != "") writer.WriteAttribute("t", dataType);
if (cell.StyleIndex > CellFormat.DefaultStyleIndex) writer.WriteAttribute("s", cell.StyleIndex);
writer.WriteAttribute("r", cell.Address);
if (cell.RawValue != null && cell.RawValue.ToString() != "")
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.CellValue());
switch (cell.CellDataType)
{
case CellDataType.Boolean:
writer.WriteText((bool)cell.RawValue);
break;
case CellDataType.SharedString:
int sharedStringIndex;
if (cell.SharedString != null)
sharedStringIndex = cell.Worksheet.Workbook.SharedStrings[cell.SharedString];
else
sharedStringIndex = cell.Worksheet.Workbook.SharedStrings[cell.RawValue.ToString()];
writer.WriteText(sharedStringIndex.ToString());
break;
default:
writer.WriteString(cell.RawValue.ToString());
break;
}
writer.WriteEndElement(); // CellValue
}
writer.WriteEndElement(); // Cell
}
// Write
internal static void WritePageMarginsToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, PageMargins pageMargins)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.PageMargins());
writer.WriteAttribute("left", pageMargins.Left);
writer.WriteAttribute("right", pageMargins.Right);
writer.WriteAttribute("top", pageMargins.Top);
writer.WriteAttribute("bottom", pageMargins.Bottom);
writer.WriteAttribute("header", pageMargins.Header);
writer.WriteAttribute("footer", pageMargins.Footer);
writer.WriteEndElement(); // PageMargins
}
// Write
internal static void WriteColumnRangeToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, ColumnRange columnRange)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Column());
writer.WriteAttribute("min", columnRange.MinIndex);
writer.WriteAttribute("max", columnRange.MaxIndex);
if (columnRange.StyleIndex > 0) writer.WriteAttribute("style", columnRange.StyleIndex);
writer.WriteAttribute("width", columnRange.Width);
if (columnRange.IsBestFit) writer.WriteAttribute("bestFit", columnRange.IsBestFit);
if (columnRange.IsCustomWidth) writer.WriteAttribute("customWidth", columnRange.IsCustomWidth);
writer.WriteEndElement(); // Column
}
// Write
internal static void WritePrintOptionsToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, PrintOptions printOptions)
{
if (printOptions.HasValue())
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.PrintOptions());
if (printOptions.HorizontalCentered) writer.WriteAttribute("horizontalCentered", printOptions.HorizontalCentered);
if (printOptions.VerticalCentered) writer.WriteAttribute("verticalCentered", printOptions.VerticalCentered);
if (printOptions.GridLines) writer.WriteAttribute("gridLines", printOptions.GridLines);
if (printOptions.Headings) writer.WriteAttribute("headings", printOptions.Headings);
writer.WriteEndElement(); // PrintOptions
}
}
// Write
internal static void WriteDrawingToWorksheetWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Drawing drawing)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Drawing());
writer.WriteAttribute("id", drawing.RelationshipId, "r");
writer.WriteEndElement(); // Drawing
}
// Write
internal static void WritePageSetupToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, PageSetup pageSetup)
{
if (pageSetup.HasValue())
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.PageSetup());
if (pageSetup.BlackAndWhite) writer.WriteAttribute("blackAndWhite", pageSetup.BlackAndWhite);
if (pageSetup.Copies > 1) writer.WriteAttribute("copies", pageSetup.Copies);
if (pageSetup.Draft) writer.WriteAttribute("draft", pageSetup.Draft);
if (pageSetup.FirstPageNumber > 1) writer.WriteAttribute("firstPageNumber", pageSetup.FirstPageNumber);
if (pageSetup.FitToHeight > -1) writer.WriteAttribute("fitToHeight", pageSetup.FitToHeight);
if (pageSetup.FitToWidth > -1) writer.WriteAttribute("fitToWidth", pageSetup.FitToWidth);
if (pageSetup.HorizontalDpi != 600) writer.WriteAttribute("horizontalDpi", pageSetup.HorizontalDpi);
if (pageSetup.VerticalDpi != 600) writer.WriteAttribute("verticalDpi", pageSetup.VerticalDpi);
if (pageSetup.Orientation != WorksheetOrientation.Portrait) writer.WriteAttribute("orientation", Helpers.ToCamelCase(pageSetup.Orientation.ToString()));
if (pageSetup.PaperSize != 9) writer.WriteAttribute("paperSize", pageSetup.PaperSize);
if (pageSetup.RelationshipId != "") writer.WriteAttribute("id", pageSetup.RelationshipId, "r");
if (pageSetup.Scale != 400) writer.WriteAttribute("scale", pageSetup.Scale);
if (pageSetup.UseFirstPageNumber) writer.WriteAttribute("useFirstPageNumber", pageSetup.UseFirstPageNumber);
if (pageSetup.UsePrinterDefaults) writer.WriteAttribute("usePrinterDefaults", pageSetup.UsePrinterDefaults);
writer.WriteEndElement(); // PageSetup
}
}
private static string CreateDrawingPartByDrawing(Drawing drawing)
{
OpenXmlPackaging.WorkbookPart workbookPart = drawing.Worksheet.Workbook.Document.WorkbookPart;
OpenXmlPackaging.WorksheetPart worksheetPart = Worksheet.GetWorksheetPartByWorksheet(drawing.Worksheet);
OpenXmlPackaging.DrawingsPart drawingsPart = OpenXmlUtilities.CreatePart<OpenXmlPackaging.WorksheetPart, OpenXmlPackaging.DrawingsPart>(worksheetPart);
using (var writer = new CustomOpenXmlWriter<OpenXmlPackaging.DrawingsPart>(drawingsPart))
{
writer.WriteOpenXmlElement(new OpenXmlDrawingSpreadsheet.WorksheetDrawing(), true);
}
string id = worksheetPart.GetIdOfPart(drawingsPart);
worksheetPart.CreateRelationshipToPart(drawingsPart, id);
return id;
}
private static void WriteMergeCellToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, string mergeCell)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.MergeCell());
writer.WriteAttribute("ref", mergeCell);
writer.WriteEndElement(); // MergeCell
}
