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