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