static Fonts GenerateFonts() { var fonts = new Fonts { Count = 1U, KnownFonts = true }; var font1 = new Font(); var fontSize1 = new FontSize { Val = 10D }; var color1 = new Color { Theme = 1U }; var fontName1 = new FontName { Val = "Times New Roman" }; var fontFamilyNumbering1 = new FontFamilyNumbering { Val = 1 }; var fontCharSet1 = new FontCharSet { Val = 204 }; font1.Append(fontSize1); font1.Append(color1); font1.Append(fontName1); font1.Append(fontFamilyNumbering1); font1.Append(fontCharSet1); fonts.Append(font1); return(fonts); }
/// <summary> /// Creates a new font and appends it to the workbook's stylesheet /// </summary> /// <param name="styleSheet">The stylesheet for the current WorkBook</param> /// <param name="fontName">The font name.</param> /// <param name="fontSize">The font size.</param> /// <param name="isBold">Set to true for bold font.</param> /// <param name="foreColor">The font color.</param> /// <returns>The index of the font.</returns> public static UInt32Value CreateFont( Stylesheet styleSheet, string fontName, double?fontSize, bool isBold, Color foreColor) { // Fonts fonts = styleSheet.GetFirstChild<Fonts>(); var font = new Font( ); if (!string.IsNullOrEmpty(fontName)) { var name = new FontName { Val = fontName }; font.Append(name); } if (fontSize.HasValue) { var size = new FontSize { Val = fontSize.Value }; font.Append(size); } if (isBold) { var bold = new Bold( ); font.Append(bold); } var color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = new HexBinaryValue { Value = ColorTranslator.ToHtml( Color.FromArgb( foreColor.A, foreColor.R, foreColor.G, foreColor.B)).Replace("#", "") } }; font.Append(color); styleSheet.Fonts.Append(font); UInt32Value result = styleSheet.Fonts.Count; styleSheet.Fonts.Count++; return(result); }
private static UInt32Value CreateFont( Stylesheet styleSheet, string fontName, double?fontSize, bool isBold, System.Drawing.Color foreColor) { DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font(); if (!string.IsNullOrEmpty(fontName)) { FontName name = new FontName() { Val = fontName }; font.Append(name); } if (fontSize.HasValue) { DocumentFormat.OpenXml.Spreadsheet.FontSize size = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = fontSize.Value }; font.Append(size); } if (isBold == true) { Bold bold = new Bold(); font.Append(bold); } DocumentFormat.OpenXml.Spreadsheet.Color color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = System.Drawing.ColorTranslator.ToHtml( System.Drawing.Color.FromArgb( foreColor.A, foreColor.R, foreColor.G, foreColor.B)).Replace("#", "") } }; font.Append(color); styleSheet.Fonts.Append(font); UInt32Value result = styleSheet.Fonts.Count; styleSheet.Fonts.Count++; return(result); }
public void Write(Font font) { if (font == null) { throw new ArgumentNullException(nameof(font)); } var f = new OpenXml.Font(); if (font.Size.HasValue) { f.Append(new OpenXml.FontSize { Val = font.Size.Value }); } if (font.Color != null) { f.Append(font.Color.MapToColor()); } if (!string.IsNullOrEmpty(font.Name)) { f.Append(new OpenXml.FontName { Val = font.Name }); } if (font.Bold ?? false) { f.Append(new OpenXml.Bold()); } if (font.Italic ?? false) { f.Append(new OpenXml.Italic()); } stylesheet.Fonts.Append(f); }
/// <summary> Creates the stylesheet. </summary> /// /// <returns> The new stylesheet. </returns> private static Stylesheet CreateStylesheet() { Stylesheet stylesheet = new Stylesheet(); Font font0 = new Font(); // Default font Font font1 = new Font(); // Bold font Bold bold = new Bold(); font1.Append(bold); Fonts fonts = new Fonts(); // <APENDING Fonts> fonts.Append(font0); fonts.Append(font1); // <Fills> Fill fill0 = new Fill(); // Default fill Fills fills = new Fills(); // <APENDING Fills> fills.Append(fill0); // <Borders> Border border0 = new Border(); // Defualt border Borders borders = new Borders(); // <APENDING Borders> borders.Append(border0); CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0 CellFormat cellformat1 = new CellFormat() { FontId = 1 }; CellFormats cellformats = new CellFormats(); cellformats.Append(cellformat0); cellformats.Append(cellformat1); stylesheet.Append(fonts); stylesheet.Append(fills); stylesheet.Append(borders); stylesheet.Append(cellformats); return(stylesheet); }
/// <summary> /// Método de agrega una hoja de estilos /// </summary> /// <param name="spreadsheet">Documento original</param> /// <returns></returns> private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet) { WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet workbookstylesheet = new Stylesheet(); DocumentFormat.OpenXml.Spreadsheet.Font font0 = new DocumentFormat.OpenXml.Spreadsheet.Font(); // Default font DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font(); // Bold font Bold bold = new Bold(); font1.Append(bold); Fonts fonts = new Fonts(); // <APENDING Fonts> fonts.Append(font0); fonts.Append(font1); // <Fills> Fill fill0 = new Fill(); // Default fill Fills fills = new Fills(); // <APENDING Fills> fills.Append(fill0); // <Borders> Border border0 = new Border(); // Defualt border Borders borders = new Borders(); // <APENDING Borders> borders.Append(border0); // <CellFormats> CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0 CellFormat cellformat1 = new CellFormat() { FontId = 1 }; // Style with Bold text ; Style ID = 1 // <APENDING CellFormats> CellFormats cellformats = new CellFormats(); cellformats.Append(cellformat0); cellformats.Append(cellformat1); // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER> workbookstylesheet.Append(fonts); workbookstylesheet.Append(fills); workbookstylesheet.Append(borders); workbookstylesheet.Append(cellformats); // Finalize stylesheet.Stylesheet = workbookstylesheet; stylesheet.Stylesheet.Save(); return(stylesheet); }
static private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1) { Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true }; DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font(); FontSize fontSize1 = new FontSize() { Val = 11D }; DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U }; FontName fontName1 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 }; FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor }; font1.Append(fontSize1); font1.Append(color1); font1.Append(fontName1); font1.Append(fontFamilyNumbering1); font1.Append(fontScheme1); DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font(); Bold bold1 = new Bold(); FontSize fontSize2 = new FontSize() { Val = 11D }; DocumentFormat.OpenXml.Spreadsheet.Color color2 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U }; FontName fontName2 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 }; FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor }; font2.Append(bold1); font2.Append(fontSize2); font2.Append(color2); font2.Append(fontName2); font2.Append(fontFamilyNumbering2); font2.Append(fontScheme2); fonts1.Append(font1); fonts1.Append(font2); Fills fills1 = new Fills() { Count = (UInt32Value)2U }; Fill fill1 = new Fill(); PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None }; fill1.Append(patternFill1); Fill fill2 = new Fill(); PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 }; fill2.Append(patternFill2); fills1.Append(fill1); fills1.Append(fill2); Borders borders1 = new Borders() { Count = (UInt32Value)2U }; Border border1 = new Border(); LeftBorder leftBorder1 = new LeftBorder(); RightBorder rightBorder1 = new RightBorder(); TopBorder topBorder1 = new TopBorder(); BottomBorder bottomBorder1 = new BottomBorder(); DiagonalBorder diagonalBorder1 = new DiagonalBorder(); border1.Append(leftBorder1); border1.Append(rightBorder1); border1.Append(topBorder1); border1.Append(bottomBorder1); border1.Append(diagonalBorder1); Border border2 = new Border(); LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin }; DocumentFormat.OpenXml.Spreadsheet.Color color3 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = (UInt32Value)64U }; leftBorder2.Append(color3); RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin }; DocumentFormat.OpenXml.Spreadsheet.Color color4 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = (UInt32Value)64U }; rightBorder2.Append(color4); TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin }; DocumentFormat.OpenXml.Spreadsheet.Color color5 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = (UInt32Value)64U }; topBorder2.Append(color5); BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin }; DocumentFormat.OpenXml.Spreadsheet.Color color6 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = (UInt32Value)64U }; bottomBorder2.Append(color6); DiagonalBorder diagonalBorder2 = new DiagonalBorder(); border2.Append(leftBorder2); border2.Append(rightBorder2); border2.Append(topBorder2); border2.Append(bottomBorder2); border2.Append(diagonalBorder2); borders1.Append(border1); borders1.Append(border2); CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U }; CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }; cellStyleFormats1.Append(cellFormat1); CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)3U }; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }; CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true }; CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyBorder = true }; cellFormats1.Append(cellFormat2); cellFormats1.Append(cellFormat3); cellFormats1.Append(cellFormat4); CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U }; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U }; cellStyles1.Append(cellStyle1); DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U }; TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" }; StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList(); StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" }; stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" }; stylesheetExtension1.Append(slicerStyles1); StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" }; stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" }; stylesheetExtension2.Append(timelineStyles1); stylesheetExtensionList1.Append(stylesheetExtension1); stylesheetExtensionList1.Append(stylesheetExtension2); stylesheet1.Append(fonts1); stylesheet1.Append(fills1); stylesheet1.Append(borders1); stylesheet1.Append(cellStyleFormats1); stylesheet1.Append(cellFormats1); stylesheet1.Append(cellStyles1); stylesheet1.Append(differentialFormats1); stylesheet1.Append(tableStyles1); stylesheet1.Append(stylesheetExtensionList1); workbookStylesPart1.Stylesheet = stylesheet1; }
}//экспорт в XLSX private void ExportDataSet(DataTable table, string destination) { using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { //режем тэйбл от мусора table.Columns.Remove("Код"); table.Columns.Remove("ID"); //создание workbook+sheets var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); //собственный формат для даты //styles WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3"); Stylesheet stylesheet = new Stylesheet(); // Date Time Display Format when s="1" is applied to cell NumberingFormats numberingFormats = new NumberingFormats() { Count = (UInt32Value)1U }; NumberingFormat numberingFormat = new NumberingFormat() { NumberFormatId = (UInt32Value)164U, FormatCode = "dd.MM.yyyy" }; numberingFormats.Append(numberingFormat); // Cell font Fonts fonts = new Fonts() { Count = (UInt32Value)1U }; DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font(); FontSize fontSize = new FontSize() { Val = 11D }; FontName fontName = new FontName() { Val = "Calibri" }; font.Append(fontSize); font.Append(fontName); fonts.Append(font); // empty, but mandatory Fills fills = new Fills() { Count = (UInt32Value)1U }; Fill fill = new Fill(); fills.Append(fill); Borders borders = new Borders() { Count = (UInt32Value)1U }; Border border = new Border(); borders.Append(border); // cellFormat1 for text cell cellFormat2 for Datetime cell CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)2U }; CellFormat cellFormat1 = new CellFormat() { FontId = (UInt32Value)0U }; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)164U, FontId = (UInt32Value)0U, ApplyNumberFormat = true }; cellFormats.Append(cellFormat1); cellFormats.Append(cellFormat2); // Save as styles stylesheet.Append(numberingFormats); stylesheet.Append(fonts); stylesheet.Append(fills); stylesheet.Append(borders); stylesheet.Append(cellFormats); workbookStylesPart.Stylesheet = stylesheet; { var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = "Отчет" }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); //создаем header в xlsx foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); //Перенос из datatable foreach (System.Data.DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); //проверка типа входных данных Type dtype = dsrow[col].GetType(); switch (dtype.Name.ToString()) { case "DateTime": DateTime dt = Convert.ToDateTime(dsrow[col].ToString()); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dt.ToOADate().ToString()); cell.StyleIndex = 1; break; case "String": cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); break; case "Int32": cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); break; default: break; } newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } } }//вывод dataTable в xlsx с учетом типа данных
private static Stylesheet CreateStylesheet() { Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true }; //Normal Font DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font(); DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize1 = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D }; DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U }; FontName fontName1 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 }; FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor }; font1.Append(fontSize1); font1.Append(color1); font1.Append(fontName1); font1.Append(fontFamilyNumbering1); font1.Append(fontScheme1); fonts1.Append(font1); //Bold Font DocumentFormat.OpenXml.Spreadsheet.Font bFont = new DocumentFormat.OpenXml.Spreadsheet.Font(); DocumentFormat.OpenXml.Spreadsheet.FontSize bfontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D }; DocumentFormat.OpenXml.Spreadsheet.Color bcolor = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U }; FontName bfontName = new FontName() { Val = "Calibri" }; FontFamilyNumbering bfontFamilyNumbering = new FontFamilyNumbering() { Val = 2 }; FontScheme bfontScheme = new FontScheme() { Val = FontSchemeValues.Minor }; Bold bFontBold = new Bold(); bFont.Append(bfontSize); bFont.Append(bcolor); bFont.Append(bfontName); bFont.Append(bfontFamilyNumbering); bFont.Append(bfontScheme); bFont.Append(bFontBold); fonts1.Append(bFont); Fills fills1 = new Fills() { Count = (UInt32Value)6U }; // FillId = 0 Fill fill1 = new Fill(); PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None }; fill1.Append(patternFill1); // FillId = 1 Fill fill2 = new Fill(); PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 }; fill2.Append(patternFill2); // FillId = 2,RED Fill fill3 = new Fill(); PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid }; ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "5c881a" }; BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U }; // patternFill3.Append(foregroundColor1); patternFill3.Append(backgroundColor1); fill3.Append(patternFill3); // FillId = 3,BLUE Fill fill4 = new Fill(); PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid }; ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "0070c0" }; BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U }; patternFill4.Append(foregroundColor2); patternFill4.Append(backgroundColor2); fill4.Append(patternFill4); // FillId = 4,YELLO Fill fill5 = new Fill(); PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid }; ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" }; BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U }; patternFill5.Append(foregroundColor3); patternFill5.Append(backgroundColor3); fill5.Append(patternFill5); // FillId = 5,RED and BOLD Text Fill fill6 = new Fill(); PatternFill patternFill6 = new PatternFill() { PatternType = PatternValues.Solid }; ForegroundColor foregroundColor4 = new ForegroundColor() { Rgb = "5c881a" }; BackgroundColor backgroundColor4 = new BackgroundColor() { Indexed = (UInt32Value)64U }; Bold bold1 = new Bold(); patternFill6.Append(bold1); patternFill6.Append(foregroundColor4); patternFill6.Append(backgroundColor4); fill6.Append(patternFill6); fills1.Append(fill1); fills1.Append(fill2); fills1.Append(fill3); fills1.Append(fill4); fills1.Append(fill5); fills1.Append(fill6); Borders borders1 = new Borders() { Count = (UInt32Value)1U }; Border border1 = new Border(); LeftBorder leftBorder1 = new LeftBorder(); RightBorder rightBorder1 = new RightBorder(); TopBorder topBorder1 = new TopBorder(); BottomBorder bottomBorder1 = new BottomBorder(); DiagonalBorder diagonalBorder1 = new DiagonalBorder(); border1.Append(leftBorder1); border1.Append(rightBorder1); border1.Append(topBorder1); border1.Append(bottomBorder1); border1.Append(diagonalBorder1); borders1.Append(border1); CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U }; CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }; cellStyleFormats1.Append(cellFormat1); CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U }; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }; CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true }; CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true }; CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true }; cellFormats1.Append(cellFormat2); cellFormats1.Append(cellFormat3); cellFormats1.Append(cellFormat4); cellFormats1.Append(cellFormat5); CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U }; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U }; cellStyles1.Append(cellStyle1); DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U }; TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" }; StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList(); StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" }; stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" }; stylesheetExtension1.Append(slicerStyles1); stylesheetExtensionList1.Append(stylesheetExtension1); stylesheet1.Append(fonts1); stylesheet1.Append(fills1); stylesheet1.Append(borders1); stylesheet1.Append(cellStyleFormats1); stylesheet1.Append(cellFormats1); stylesheet1.Append(cellStyles1); stylesheet1.Append(differentialFormats1); stylesheet1.Append(tableStyles1); stylesheet1.Append(stylesheetExtensionList1); return(stylesheet1); }
static Stylesheet CreateStylesheet() { Stylesheet ss = new Stylesheet(); #region fts (fonts) Fonts fts = new Fonts(); var ft = new DocumentFormat.OpenXml.Spreadsheet.Font(); FontName ftn = new FontName(); ftn.Val = StringValue.FromString("Arial"); FontSize ftsz = new FontSize(); ftsz.Val = DoubleValue.FromDouble(11); ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); ft = new DocumentFormat.OpenXml.Spreadsheet.Font(); ftn = new FontName(); ftn.Val = StringValue.FromString("Verdana"); ftsz = new FontSize(); ftsz.Val = DoubleValue.FromDouble(18); ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); ft = new DocumentFormat.OpenXml.Spreadsheet.Font(); ft.Append(new Bold()); ftn = new FontName(); ftn.Val = StringValue.FromString("Arial"); ftsz = new FontSize(); ftsz.Val = DoubleValue.FromDouble(11); ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count); #endregion #region fills Fills fills = new Fills(); Fill fill; PatternFill patternFill; fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.None; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.Gray125; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.Solid; patternFill.ForegroundColor = new ForegroundColor(); patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00efffd9"); patternFill.BackgroundColor = new BackgroundColor(); patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.Solid; patternFill.ForegroundColor = new ForegroundColor(); patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00f8e8d6"); patternFill.BackgroundColor = new BackgroundColor(); patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb; fill.PatternFill = patternFill; fills.Append(fill); fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count); #endregion #region borders Borders borders = new Borders(); Border border = new Border(); border.LeftBorder = new LeftBorder(); border.RightBorder = new RightBorder(); border.TopBorder = new TopBorder(); border.BottomBorder = new BottomBorder(); border.DiagonalBorder = new DiagonalBorder(); borders.Append(border); border = new Border(); border.LeftBorder = new LeftBorder(); border.LeftBorder.Style = BorderStyleValues.Thin; border.RightBorder = new RightBorder(); border.RightBorder.Style = BorderStyleValues.Thin; border.TopBorder = new TopBorder(); border.TopBorder.Style = BorderStyleValues.Thin; border.BottomBorder = new BottomBorder(); border.BottomBorder.Style = BorderStyleValues.Thin; border.DiagonalBorder = new DiagonalBorder(); borders.Append(border); borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count); #endregion #region csfs (CellStyleFormats) CellStyleFormats csfs = new CellStyleFormats(); CellFormat cf = new CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; csfs.Append(cf); csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count); #endregion uint iExcelIndex = 164; var nfs = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(); CellFormats cfs = new CellFormats(); cf = new CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cfs.Append(cf); var nfDateTime = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss"); nfs.Append(nfDateTime); var nfDate = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nfDate.FormatCode = StringValue.FromString("dd/mm/yyyy"); nfs.Append(nfDate); var nf4decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nf4decimal.FormatCode = StringValue.FromString("#,##0.0000"); nfs.Append(nf4decimal); // #,##0.00 is also Excel style index 4 var nf2decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nf2decimal.FormatCode = StringValue.FromString("#,##0.00"); nfs.Append(nf2decimal); // @ is also Excel style index 49 var nfForcedText = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nfForcedText.FormatCode = StringValue.FromString("@"); nfs.Append(nfForcedText); // #,##0.00 is also Excel style index 4 var nf0decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat(); nf0decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nf0decimal.FormatCode = StringValue.FromString("#,##0"); nfs.Append(nf0decimal); // index 1 cf = new CellFormat(); cf.NumberFormatId = nfDate.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 2 cf = new CellFormat(); cf.NumberFormatId = nf4decimal.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 3 cf = new CellFormat(); cf.NumberFormatId = nf0decimal.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 4 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 0; //NO Fill cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 5 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 1; //Verdana 18 cf.FillId = 0; //NO Fill cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 6 // column text cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 2; //Arial 11, Bold cf.FillId = 0; //NO Fill cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 7 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 3; //Light Orange cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 8 // column text cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 2; //Arial 11, Bold cf.FillId = 2; //Light Green cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 9 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 0; //NO Fill cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cf.Append(new Alignment() { WrapText = true }); cfs.Append(cf); // index 10 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; //Arial 11 cf.FillId = 3; //Light Orange cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cf.Append(new Alignment() { WrapText = true }); cfs.Append(cf); nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count); cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count); ss.Append(nfs); ss.Append(fts); ss.Append(fills); ss.Append(borders); ss.Append(csfs); ss.Append(cfs); CellStyles css = new CellStyles(); CellStyle cs = new CellStyle(); cs.Name = StringValue.FromString("Normal"); cs.FormatId = 0; cs.BuiltinId = 0; css.Append(cs); css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count); ss.Append(css); DifferentialFormats dfs = new DifferentialFormats(); dfs.Count = 0; ss.Append(dfs); TableStyles tss = new TableStyles(); tss.Count = 0; tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9"); tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16"); ss.Append(tss); return(ss); }
private static Stylesheet CreateStylesheet() { Stylesheet stylesheet1 = new Stylesheet(); DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts() { Count = (UInt32Value)1U, KnownFonts = true }; DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font(); FontSize fontSize1 = new FontSize() { Val = 11 }; Color color1 = new Color() { Theme = (UInt32Value)1U }; FontName fontName1 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 }; DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme1 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme() { Val = FontSchemeValues.Minor }; font1.Append(fontSize1); font1.Append(color1); font1.Append(fontName1); font1.Append(fontFamilyNumbering1); font1.Append(fontScheme1); DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font(); FontSize fontSize2 = new FontSize() { Val = 14 }; Color color2 = new Color() { Rgb = "FF0070C0" }; FontName fontName2 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 }; DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme2 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme() { Val = FontSchemeValues.Minor }; font2.Append(fontSize1); font1.Append(color2); font1.Append(fontName2); font1.Append(fontFamilyNumbering2); font1.Append(fontScheme2); fonts1.Append(font1); fonts1.Append(font2); CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U }; CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U }; cellStyleFormats1.Append(cellFormat1); CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U }; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U }; CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U }; cellFormats1.Append(cellFormat2); cellFormats1.Append(cellFormat3); CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U }; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U }; cellStyles1.Append(cellStyle1); stylesheet1.Append(fonts1); stylesheet1.Append(cellStyleFormats1); stylesheet1.Append(cellFormats1); stylesheet1.Append(cellStyles1); return(stylesheet1); }
private int? GetStyleIndex(Style style,Row row, ref string value) { StyleInfo pt = GetStyle(style, row); // DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts() // { Count = (UInt32Value)1U, KnownFonts = true }; int? fontId = null; int? borderId = null; int? cellFormatId = null; DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font(); if (pt.IsFontBold()) font.Append(new DocumentFormat.OpenXml.Spreadsheet.Bold()); if (pt.FontStyle == FontStyleEnum.Italic) font.Append(new DocumentFormat.OpenXml.Spreadsheet.Italic()); font.Append(new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = (Double)pt.FontSize }); font.Append(new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = pt.FontFamily }); //font.Append(new DocumentFormat.OpenXml.Spreadsheet.Color() // { Rgb=GetColor(si.Color) }); int id = 0; foreach (var fo in _styleSheet.Fonts) { if (fo.OuterXml.Equals(font.OuterXml)) { fontId = id; break; } id++; } if (fontId == null) { _styleSheet.Fonts.Append(font); _styleSheet.Fonts.Count = (uint)_styleSheet.Fonts.ChildElements.Count; fontId = _styleSheet.Fonts.ChildElements.Count - 1; } Border border = new Border(); if (pt.BStyleLeft != BorderStyleEnum.None) { border.LeftBorder = new LeftBorder() { Style = GetBorderStyle(pt.BStyleLeft) }; } if (pt.BStyleRight != BorderStyleEnum.None) { border.RightBorder = new RightBorder() { Style = GetBorderStyle(pt.BStyleRight) }; } if (pt.BStyleTop != BorderStyleEnum.None) { border.TopBorder = new TopBorder() { Style = GetBorderStyle(pt.BStyleTop) }; } if (pt.BStyleBottom != BorderStyleEnum.None) { border.BottomBorder = new BottomBorder() { Style = GetBorderStyle(pt.BStyleBottom) }; } id = 0; foreach (var bo in _styleSheet.Borders) { if (bo.OuterXml.Equals(border.OuterXml)) { borderId = id; break; } id++; } if (borderId == null) { _styleSheet.Borders.Append(border); _styleSheet.Borders.Count = (uint)_styleSheet.Borders.ChildElements.Count; borderId = _styleSheet.Borders.ChildElements.Count - 1; } value = NumericValue(value) ?? value; CellFormat cf = new CellFormat(); cf.NumberFormatId = (uint)StyleInfo.GetFormatCode(pt._Format); cf.FontId = (uint)fontId; cf.FillId = 0; cf.BorderId = (uint)borderId; // cf.FormatId = 0; id = 0; foreach (var cef in _styleSheet.CellFormats) { if (cef.OuterXml.Equals(cf.OuterXml)) { cellFormatId = id; break; } id++; } if (cellFormatId == null) { _styleSheet.CellFormats.Append(cf); _styleSheet.CellFormats.Count = (uint)_styleSheet.CellFormats.ChildElements.Count; cellFormatId = _styleSheet.CellFormats.ChildElements.Count - 1; } return cellFormatId; }