private static void CreateBasicFont(Fonts fonts, Color color)
{
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.AppendChild(fontSize1);
font1.AppendChild(color);
font1.AppendChild(fontName1);
font1.AppendChild(fontFamilyNumbering1);
font1.AppendChild(fontScheme1);
fonts.AppendChild(font1);
}
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);
}
/// <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);
}
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>
/// Create Font styles
/// </summary>
private static void CreateFontStyles(Fonts fts)
{
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
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("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
}
}//экспорт в 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 с учетом типа данных
public CustomStylesheet()
{
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName { Val = StringValue.FromString("Arial") };
var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName { Val = StringValue.FromString("Arial") };
fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.DodgerBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//Top and Bottom Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count =
UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
var numberingFormats = new NumberingFormats();
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
numberingFormats.Count =
UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
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);
}
private void ExporttoExcelXML(DataSet ds)
{
string strPathReports = GetPathUploadReports();
string strNamefile = DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx";
string strFullPath = Server.MapPath(strPathReports) + strNamefile;
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(strFullPath, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
Worksheet ws = new Worksheet();
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
// add styles to sheet
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
var sheetData = new SheetData();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Resumen General" //data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
Bold fbld = new Bold();
FontName ftn = new FontName();
ftn.Val = "Calibri";
DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
ft.Bold = fbld;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
row.Append(fts);
sheetData.AppendChild(row);
row.StyleIndex = (UInt32Value)1U;
var cellIdex = 0;
int intCount = 0;
string strX = string.Empty;
string strY = string.Empty;
foreach (System.Data.DataTable table in ds.Tables)
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
//excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName.ToString().ToUpper();
Cell cel = CreateTextCellHeader(ColumnLetter(cellIdex++), rowIdex, table.Columns[i - 1].ColumnName.ToString().ToUpper() ?? string.Empty);
if (i < 9)
{
cel.StyleIndex = (UInt32Value)1U;
}
if (i > 10 && i < 15)
{
cel.StyleIndex = (UInt32Value)2U;
}
if (i > 15 && i < 20)
{
cel.StyleIndex = (UInt32Value)4;
}
if (i > 20 && i < 25)
{
cel.StyleIndex = (UInt32Value)5U;
}
if (i > 30 && i < 35)
{
cel.StyleIndex = (UInt32Value)6U;
}
row.AppendChild(cel);
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Bold = true;
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Name = "Calibir";
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// REQUIERE TC Y TP
if ((table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE TC Y TP")
||
(table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE REUBICACIÓN")
&&
intCount == 0)
{
intCount = i;
}
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Style.Name = "Normal";
}
for (int j = 0; j < table.Rows.Count; j++)
{
//rowIdex = 1;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
cellIdex = 0;
for (int k = 0; k < table.Columns.Count; k++)
{
if ((k == 2))
{
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "#####";
}
if ((k == 4))
{
// ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "##,###.##";
}
//excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, table.Rows[j].ItemArray[k].ToString() ?? string.Empty));
}
}
}
workbookpart.Workbook.Save();
document.Close();
string strUrl = "Bajarresumengral.aspx?n=" + strNamefile;
Response.Redirect(strUrl, true);
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
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("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
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("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
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);
//Boarder Index 1
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);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
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);
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);
uint iExcelIndex = 164;
NumberingFormats nfs = new 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);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
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 = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.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);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
}
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;
}
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;
}
private static Fonts CreateFonts()
{
var fts = new Fonts();
// font 0
var ft = new Font();
var ftn = new FontName {
Val = StringValue.FromString("Arial")
};
var ftsz = new FontSize {
Val = DoubleValue.FromDouble(11)
};
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.AppendChild(ft);
// font 1
ft = new Font();
ftn = new FontName {
Val = StringValue.FromString("Arial")
};
ftsz = new FontSize {
Val = DoubleValue.FromDouble(12)
};
ft.FontName = ftn;
ft.FontSize = ftsz;
ft.Bold = new Bold();
fts.AppendChild(ft);
// font 2
ft = new Font();
ftn = new FontName {
Val = StringValue.FromString("Arial")
};
ftsz = new FontSize {
Val = DoubleValue.FromDouble(18)
};
ft.FontName = ftn;
ft.FontSize = ftsz;
ft.Bold = new Bold();
fts.AppendChild(ft);
// font 3
ft = new Font();
ftn = new FontName {
Val = StringValue.FromString("Arial")
};
ftsz = new FontSize {
Val = DoubleValue.FromDouble(14)
};
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.AppendChild(ft);
// font 4
ft = new Font();
ftn = new FontName {
Val = StringValue.FromString("Arial")
};
ftsz = new FontSize {
Val = DoubleValue.FromDouble(11)
};
var fontColor = Color.MediumBlue;
ft.Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
{
Rgb = HexBinaryValueFromColor(fontColor)
};
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.AppendChild(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
return(fts);
}
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 static MSOpenXML.Stylesheet CreateStylesheet(int pQuantidadeDecimais = 2)
{
MSOpenXML.Stylesheet ss = new MSOpenXML.Stylesheet();
#region Fontes
MSOpenXML.Fonts fts = new MSOpenXML.Fonts();
MSOpenXML.Font ft = new MSOpenXML.Font();
MSOpenXML.FontName ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
MSOpenXML.FontSize ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new MSOpenXML.Font();
ft.Bold = new MSOpenXML.Bold();
ftn = new MSOpenXML.FontName();
ftn.Val = "Calibri";
ftsz = new MSOpenXML.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
#endregion
#region Preenchimento
MSOpenXML.Fills fills = new MSOpenXML.Fills();
MSOpenXML.Fill fill;
MSOpenXML.PatternFill patternFill;
fill = new MSOpenXML.Fill();
patternFill = new MSOpenXML.PatternFill();
patternFill.PatternType = MSOpenXML.PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
/*fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Gray125;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*
* fill = new dos.Fill();
* patternFill = new dos.PatternFill();
* patternFill.PatternType = dos.PatternValues.Solid;
* patternFill.ForegroundColor = new dos.ForegroundColor();
* patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
* patternFill.BackgroundColor = new dos.BackgroundColor();
* patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
* fill.PatternFill = patternFill;
* fills.Append(fill);
*/
fills.Count = (uint)fills.ChildElements.Count;
#endregion
#region Bordas
MSOpenXML.Borders borders = new MSOpenXML.Borders();
MSOpenXML.Border border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
border = new MSOpenXML.Border();
border.LeftBorder = new MSOpenXML.LeftBorder();
border.RightBorder = new MSOpenXML.RightBorder();
border.TopBorder = new MSOpenXML.TopBorder();
border.TopBorder.Style = MSOpenXML.BorderStyleValues.Thin;
border.BottomBorder = new MSOpenXML.BottomBorder();
border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
#endregion
MSOpenXML.CellStyleFormats csfs = new MSOpenXML.CellStyleFormats();
MSOpenXML.CellFormat cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
MSOpenXML.NumberingFormats nfs = new MSOpenXML.NumberingFormats();
MSOpenXML.CellFormats cfs = new MSOpenXML.CellFormats();
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
MSOpenXML.NumberingFormat nfDateTime = new MSOpenXML.NumberingFormat();
nfDateTime.NumberFormatId = iExcelIndex++;
nfDateTime.FormatCode = "dd/mm/yyyy";
nfs.Append(nfDateTime);
MSOpenXML.NumberingFormat nf4decimal = new MSOpenXML.NumberingFormat();
nf4decimal.NumberFormatId = iExcelIndex++;
nf4decimal.FormatCode = "#,##0";
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
MSOpenXML.NumberingFormat nf2decimal = new MSOpenXML.NumberingFormat();
nf2decimal.NumberFormatId = iExcelIndex++;
nf2decimal.FormatCode = FormatoDecimal(pQuantidadeDecimais); //"#,##0.00"
nfs.Append(nf2decimal);
// @ is also Excel style index 49
MSOpenXML.NumberingFormat nfForcedText = new MSOpenXML.NumberingFormat();
nfForcedText.NumberFormatId = iExcelIndex++;
nfForcedText.FormatCode = "@";
nfs.Append(nfForcedText);
// index 1
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 2
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 3
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 4
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 5
// Header text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cf.Alignment.Horizontal = MSOpenXML.HorizontalAlignmentValues.Center;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// index 6
// group text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
// index 7
// Total text, ColumnHeader Text
cf = new MSOpenXML.CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 1;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cf.Alignment = new MSOpenXML.Alignment()
{
WrapText = false
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
MSOpenXML.CellStyles css = new MSOpenXML.CellStyles();
MSOpenXML.CellStyle cs = new MSOpenXML.CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
MSOpenXML.DifferentialFormats dfs = new MSOpenXML.DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
MSOpenXML.TableStyles tss = new MSOpenXML.TableStyles();
tss.Count = 0;
//tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
//tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
ss.Append(tss);
return(ss);
}
public static Stylesheet GenerateStylesheet2()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
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);
fills.Count = (uint)fills.ChildElements.Count;
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);
borders.Count = (uint)borders.ChildElements.Count;
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 = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
NumberingFormats nfs = new 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);
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.0000";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.00";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// @ is also Excel style index 49
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "@";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (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 = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (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 = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return(ss);
}
public CustomStylesheet()
{
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName {
Val = StringValue.FromString("Arial")
};
var fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(12)
};
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.LightGray);
patternFill.BackgroundColor =
new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.LightGray);
patternFill.BackgroundColor =
new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count =
UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
var numberingFormats = new NumberingFormats();
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
numberingFormats.Count =
UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
private Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
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);
borders.Count = (uint)borders.ChildElements.Count;
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 = (uint)csfs.ChildElements.Count;
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 nfs = new NumberingFormats();
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (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(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (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 = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return(ss);
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName {
Val = "Arial"
};
var ftsz = new FontSize {
Val = 11
};
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font {
FontName = ftn, FontSize = ftsz
};
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
var nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.00"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "@"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle {
Name = "Normal", FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return(ss);
}
/// <summary>
/// Create an stylesheet to use in excel files
/// </summary>
/// <returns></returns>
private static Stylesheet VocStyleSheet()
{
Stylesheet styleSheet = new Stylesheet();
Fonts fonts = new Fonts();
// 0 - normal fonts
DocumentFormat.OpenXml.Spreadsheet.Font myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//1 - font bold
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//2 - font title
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 20 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
FontName = new FontName() { Val = "Verdana" }
};
fonts.Append(myFont);
//3 - font bold
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 16 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//4 - small font white
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
Fills fills = new Fills();
//default fill
Fill fill = new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.None }
};
fills.Append(fill);
//default fill
fill = new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
};
fills.Append(fill);
//title fill
fill = new Fill()
{
PatternFill = new PatternFill()
{
ForegroundColor = new ForegroundColor()
{
Rgb = new HexBinaryValue() { Value = "499EB1" }
},
PatternType = PatternValues.Solid
}
};
fills.Append(fill);
Borders borders = new Borders();
//normal borders
Border border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//borders applied
border = new Border()
{
LeftBorder = new LeftBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
RightBorder = new RightBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
TopBorder = new TopBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
CellFormats cellFormats = new CellFormats();
//0- normal
CellFormat cellFormat = new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 0,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//1 - border
cellFormat = new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 1,
Alignment = new Alignment() { WrapText = true },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//2 - border and bold
cellFormat = new CellFormat()
{
FontId = 1,
FillId = 0,
BorderId = 1,
Alignment = new Alignment() { WrapText = true },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//3 -title
cellFormat = new CellFormat()
{
FontId = 0,
FillId = 2,
BorderId = 0,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//4 - title
cellFormat = new CellFormat()
{
FontId = 2,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//5 - title
cellFormat = new CellFormat()
{
FontId = 2,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.CenterContinuous, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//6 - section title
cellFormat = new CellFormat()
{
FontId = 3,
FillId = 0,
BorderId = 1,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//7 - section title filter values
cellFormat = new CellFormat()
{
FontId = 4,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { WrapText = false, Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
styleSheet.Append(fonts);
styleSheet.Append(fills);
styleSheet.Append(borders);
styleSheet.Append(cellFormats);
return styleSheet;
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName { Val = "Arial" };
var ftsz = new FontSize { Val = 11 };
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return ss;
}
public override byte[] Render(global::Campus.Report.Base.Report report)
{
byte[] buffer;
using (var outputMemoryStream = new MemoryStream())
{
using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
{
// Add a WorkbookPart to the document.
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Add a WorkbookStylesPart to the WorkbookPart
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);
uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;
var styles = report.Select(o => o.Style).ToList();
//нельзя перенести в отдельный метод :(
foreach (var s in styles)
{
if (s is TableStyle)
{
var style = s as TableStyle;
var border = new DocumentFormat.OpenXml.Spreadsheet.Border();
var leftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
var rightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
var topBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();
leftBorder.Style = SetLineType(style.BorderLine);
rightBorder.Style = SetLineType(style.BorderLine);
topBorder.Style = SetLineType(style.BorderLine);
bottomBorder.Style = SetLineType(style.BorderLine);
leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
border.LeftBorder = leftBorder;
border.RightBorder = rightBorder;
border.TopBorder = topBorder;
border.BottomBorder = bottomBorder;
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);
var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
{
BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
Rgb = ToHexBinaryValue(style.Background)
},
ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
Rgb = ToHexBinaryValue(style.Foreground)
},
PatternType = SetPatternType(style.PatternType)
};
var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
PatternFill = patternFill
};
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
else
{
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
DocumentFormat.OpenXml.Spreadsheet.Font font;
if (s != null)
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
foreach (var item in s.TextStyle)
{
switch (item)
{
case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
Val = true
}; break;
case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
Val = true
}; break;
case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
}; break;
case TextStyleType.Normal: break;
}
}
}
else
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
}
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
if (s != null)
{
var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
{
BorderId = UInt32Value.ToUInt32(styleId),
FillId = UInt32Value.ToUInt32(styleId),
FontId = UInt32Value.ToUInt32(styleId)
};
if (s.Aligment != null)
{
var align = new Alignment()
{
TextRotation = new UInt32Value(s.Aligment.Rotation),
Horizontal = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
Vertical = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
};
cellFormat.Append(align);
}
stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
}
styleId = styleId + 1;
}
foreach (var x in report)
{
if (x is ComplexHeaderCell)
{
var element = x as ComplexHeaderCell;
if (Render(element, styles, shData, worksheetPart, workbookpart))
{
continue;
}
}
if (x is TextElement)
{
var element = x as TextElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is TableElement)
{
var element = x as TableElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is ImageElement)
{
var element = x as ImageElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
}
//var dateLine = shData.AppendChild(new Spreadsheet.Row());
//dateLine.AppendChild(new Spreadsheet.Cell()
//{
// CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
// DataType = Spreadsheet.CellValues.String,
// StyleIndex = 0
//});
workbookpart.Workbook.Save();
}
buffer = new byte[outputMemoryStream.Position];
outputMemoryStream.Position = 0;
outputMemoryStream.Read(buffer, 0, buffer.Length);
}
return(buffer);
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
public Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
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);
borders.Count = (uint)borders.ChildElements.Count;
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 = (uint)csfs.ChildElements.Count;
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 nfs = new NumberingFormats();
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (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(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (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 = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return ss;
}
public void ApplySettings(DocumentFormat.OpenXml.Spreadsheet.Font font, DocumentFormat.OpenXml.Spreadsheet.Fill fill, params ExcelBorder[] borders)
{
}
public CustomStylesheet()
{
#region Fonts
var fonts = new Fonts();
var color = new Color();
//Font Index 0
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName {
Val = StringValue.FromString("Arial")
};
var fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(10)
};
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
//Font Index 2
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(15)
};
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
//Font Index 3 Posted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "0000FF"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 4 InProgress
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF8B45"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 5 Submitted/resubmitted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 6 Accepted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "2C6100"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 7 Dashboard
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(15)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 8
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 9 Header
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(18)
};
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FFFFFF"
}
};
font.Color = color;
font.Bold = new Bold();
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 10 Body Bold
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.Bold = new Bold();
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 11 Body Normal
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
#endregion
#region Fills
var fills = new Fills();
//Fill index 0
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 1
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 4
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
patternFill.BackgroundColor = new BackgroundColor()
{
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 5
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(245, 245, 245));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 6 ForeGround Header Blue
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(0, 176, 240));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 7 ForeGround Header green
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(146, 208, 80));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
#endregion
#region Borders
var borders = new Borders();
//All Boarder Index 0
var border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
#endregion
#region CellStyleFormats
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1
};
cellStyleFormats.Append(cellFormat);
cellFormat = new CellFormat {
NumberFormatId = 0, FontId = 2, FillId = 4, BorderId = 1
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
#endregion
#region NumberFormats
var numberingFormats = new NumberingFormats();
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
#endregion
#region CellFormats
var cellFormats = new CellFormats();
// index 0
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 12 for insight header
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 2,
FillId = 4,
//BorderId = 1,
FormatId = 1,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 13
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// index 14 alternate for index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 5,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 15 alternate for index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// index 16 alternate for index 13
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 17
// Integer Cell format
cellFormat = new CellFormat
{
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 18
// Integer Cell format alternate
cellFormat = new CellFormat
{
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
/*Style index for ITAreaList */
// index 19 In-Progress
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 4,
FillId = 0,//6
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 20 Submitted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 5,
FillId = 0,//7
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 21 Posted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 3,
FillId = 0,//8
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 22 Accepted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 6,
FillId = 0,//9
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
/*Style index for ITAreaList */
/*Style index for Dashboard */
// Index 23
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 4,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 24
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 8,
FillId = 4,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.Append(cellFormat);
//cellFormat.ApplyAlignment = new BooleanValue(true);
// Index 25
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 26
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.AppendChild(cellFormat);
// index 27 for wrapText --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
WrapText = true
});
cellFormats.AppendChild(cellFormat);
// index 28 for Alternate Wrap --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
WrapText = true
});
cellFormats.AppendChild(cellFormat);
//cellFormat.ApplyAlignment = new BooleanValue(true);
// index 29 for Alternate Wrap --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.AppendChild(cellFormat);
// Index 30
// Coloured cell text without border
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 31
// Coloured cell text without border right aligned
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.AppendChild(cellFormat);
// index 32
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 33 alternate for index 32
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 34 date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 35
// Cell Standard date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 36 alternate for index 35
// Cell Standard date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// Index 37
// Coloured cell text for Excel Header
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 2,
FillId = 3,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 38
// Bold text Cell - Left Align
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 39
// Bold text Cell with background - Center Align
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyBorder = true,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Center
}
};
cellFormats.Append(cellFormat);
// Index 40
// Bold text Cell - Right Align
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 1,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right
}
};
cellFormats.Append(cellFormat);
/* End Style index for Dashboard */
// Fonts For Tax Savings
// Index 41 For Header
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 9,
FillId = 6,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 42 For Header
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 10,
FillId = 7,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 43 For Body Header Bold
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 10,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Center
}
};
cellFormats.Append(cellFormat);
// Index 44 For Body Header Normal
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 11,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 45 For Body Cell Float
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 11,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right
}
};
cellFormats.Append(cellFormat);
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
#endregion
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
private Stylesheet createStyleSheet()
{
Stylesheet stylesheet = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
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("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
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("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
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;
CellStyleFormats cellStyleFormats = new CellStyleFormats();
CellFormat cellFormat = new CellFormat();
cellFormat.NumberFormatId = 0;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats numberFormats = new NumberingFormats();
CellFormats cellFormats = new CellFormats();
cellFormat = new CellFormat();
cellFormat.NumberFormatId = 0;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormats.Append(cellFormat);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString(this.DateTimeFormat);
numberFormats.Append(nfDateTime);
NumberingFormat nfDate = new NumberingFormat();
nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDate.FormatCode = StringValue.FromString(this.DateFormat);
numberFormats.Append(nfDate);
// index 1
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfDateTime.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfDate.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
numberFormats.Count = UInt32Value.FromUInt32((uint)numberFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
stylesheet.Append(numberFormats);
stylesheet.Append(fts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
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);
stylesheet.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
stylesheet.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
stylesheet.Append(tss);
return(stylesheet);
}
internal void Save()
{
var stylesPart = sd.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
CellFormats cellFormats = new CellFormats();
Fills fills = new Fills();
// There are two fills that seemingly need to be always in there
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill();
patternFill1.PatternType = PatternValues.None;
fill1.Append(patternFill1);
fills.Append(fill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill();
patternFill2.PatternType = PatternValues.Gray125;
fill2.Append(patternFill2);
fills.Append(fill2);
// And two XFs
CellFormat cellFormat1 = new CellFormat();
cellFormat1.FillId = (UInt32)0;
cellFormats.Append(cellFormat1);
CellFormat cellFormat2 = new CellFormat();
cellFormat2.FillId = (UInt32)1;
cellFormats.Append(cellFormat2);
foreach (var pal in palette)
{
CellFormat cellFormat = new CellFormat();
cellFormat.FillId = (UInt32)(pal.Value + 2);
cellFormats.Append(cellFormat);
Fill fill = new Fill();
PatternFill patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
ForegroundColor fgc = new ForegroundColor();
fgc.Rgb = "FF" + ColorTranslator.ToHtml(pal.Key).Replace("#", "");
patternFill.Append(fgc);
fill.Append(patternFill);
fills.Append(fill);
}
// Add dummy borders and fonts, which we need for the cellXfs to work
Borders borders = new Borders();
Border border = new Border();
borders.Append(border);
Fonts fonts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fonts.Append(font);
// Add the other things we made to the stylesheet
stylesPart.Stylesheet.Append(fonts);
stylesPart.Stylesheet.Append(fills);
stylesPart.Stylesheet.Append(borders);
stylesPart.Stylesheet.Append(cellFormats);
sd.Save();
sd.Close();
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
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("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
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("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
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);
//Boarder Index 1
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);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
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);
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);
uint iExcelIndex = 164;
NumberingFormats nfs = new 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);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
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 = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.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);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
return;
}
public NewExcel()
{
//创建默认字体样式1 Arial 24
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName {
Val = StringValue.FromString("Arial")
};
var fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
//创建填充样式1
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray0625
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
//创建线条样式1
var borders = new Borders();
var border = new Border {
LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
//创建单元格样式
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}