private static Fonts CreateFonts()
{
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(10);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts = new Fonts();
ft = new Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
//fonts count
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
return(fts);
}
/// <summary>
/// Creates fonts
/// </summary>
/// <param name="styleSheet">The style sheet.</param>
static void CreateFonts(Stylesheet styleSheet)
{
Fonts fonts = new Fonts();
fonts.Append(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
fonts.Append(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
},
Bold = new Bold {
Val = BooleanValue.FromBoolean(true)
}
});
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
styleSheet.Append(fonts);
}
public void SetValue(uint rowId, uint columnId, double value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
}
private static Fonts GetFonts()
{
Fonts fonts = new Fonts();
// 0
Font fontNormal = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
};
fonts.Append(fontNormal);
// 1
Font fontTitle = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(25)
},
Color = new Color {
Rgb = HexBinaryValue.FromString("ff584642")
}
};
fonts.Append(fontTitle);
// 2
Font fontColumnHeaders = new Font()
{
FontName = new FontName {
Val = StringValue.FromString("Calibri")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(13.5)
},
Color = new Color()
{
Rgb = HexBinaryValue.FromString("ffffffff")
}, //white
Bold = new Bold()
{
Val = true
}
};
fonts.Append(fontColumnHeaders);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
return(fonts);
}
public ExcelNumberFormatAttribute(bool applyNumberFormat, UInt32 numberFormat = 0U, double width = 10D,
int horizontalAlignment = -1)
{
NumberFormat = numberFormat;
ApplyNumberFormat = applyNumberFormat;
Width = DoubleValue.FromDouble(width);
if (Enum.IsDefined(typeof(HorizontalAlignmentValues), horizontalAlignment))
{
HorizontalAlignment = (HorizontalAlignmentValues)horizontalAlignment;
}
}
private void SetFont(double size, string fontName, int fontFamilyId, bool bold, bool italic, UnderlineValues underlineValue, string fontColor = null)
{
Font font = new Font();
font.FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(size)
};
font.FontName = new FontName()
{
Val = StringValue.FromString(fontName)
};
font.FontFamilyNumbering = new FontFamilyNumbering()
{
Val = Int32Value.FromInt32(fontFamilyId)
};
font.Bold = new Bold()
{
Val = BooleanValue.FromBoolean(bold)
};
font.Italic = new Italic()
{
Val = BooleanValue.FromBoolean(italic)
};
font.Underline = new Underline()
{
Val = new EnumValue <UnderlineValues>(underlineValue)
};
if (!string.IsNullOrEmpty(fontColor))
{
font.Color = new Color()
{
Rgb = fontColor
};
}
//font.FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(fontScheme) };
//font.Color = new Color() { Theme = UInt32Value.FromUInt32(colorSchemeId) };
ActualFonts.Append(font);
SetFontsCount();
}
public static Cell GerarCelula(int linha, int coluna, object conteudo)
{
var celula = new Cell
{
CellReference = $"{DeParaColuna(coluna)}{linha}",
};
if (conteudo != null)
{
if (conteudo is short || conteudo is int)
{
celula.CellValue = new CellValue(Int32Value.FromInt32(Convert.ToInt32(conteudo)));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if (conteudo is DateTime)
{
celula.CellValue = new CellValue(Convert.ToDateTime(conteudo).ToOADate().ToString(cultura));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if (conteudo is double || conteudo is decimal)
{
celula.CellValue = new CellValue(DoubleValue.FromDouble(Convert.ToDouble(conteudo, cultura)));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
celula.CellValue = new CellValue(conteudo.ToString());
celula.DataType = new EnumValue <CellValues>(CellValues.String);
}
}
else
{
celula.CellValue = new CellValue();
}
return(celula);
}
/// <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);
}
public void OpenXmlSimpleTypeConverterTest()
{
// 1. Base64BinaryValue
Base64BinaryValue base64 = new Base64BinaryValue();
base64 = "AA3322";
Assert.True(base64 == "AA3322");
Assert.Equal("AA3322", base64.Value);
base64 = Base64BinaryValue.FromString("1234");
Assert.Equal("1234", base64.ToString());
Assert.Equal("1234", Base64BinaryValue.ToString(base64));
// 2. BooleanValue
BooleanValue booleanValue = new BooleanValue();
booleanValue = true;
Assert.True(booleanValue);
Assert.True(booleanValue.Value);
booleanValue = BooleanValue.FromBoolean(false);
Assert.False(booleanValue);
Assert.False(BooleanValue.ToBoolean(booleanValue));
// 3. ByteValue
ByteValue byteValue = new ByteValue();
byte bt = 1;
byteValue = bt;
Assert.True(bt == byteValue);
Assert.Equal(bt, byteValue.Value);
bt = 2;
byteValue = ByteValue.FromByte(bt);
Assert.Equal(bt, ByteValue.ToByte(byteValue));
// 4. DateTimeValue
DateTimeValue dtValue = new DateTimeValue();
DateTime dt = DateTime.Now;
dtValue = dt;
Assert.True(dt == dtValue);
dt = DateTime.Now.AddDays(1);
dtValue = DateTimeValue.FromDateTime(dt);
Assert.Equal(dt, dtValue.Value);
Assert.Equal(dt, DateTimeValue.ToDateTime(dt));
// 5. DecimalValue
DecimalValue decimalValue = new DecimalValue();
decimal dcm = 10;
decimalValue = dcm;
Assert.True(dcm == decimalValue);
decimalValue = DecimalValue.FromDecimal(20);
Assert.Equal(20, decimalValue.Value);
Assert.Equal(20, DecimalValue.ToDecimal(decimalValue));
// 6. DoubleValue
DoubleValue doubleValue = new DoubleValue();
double dbl = 1.1;
doubleValue = dbl;
Assert.True(dbl == doubleValue);
doubleValue = DoubleValue.FromDouble(2.2);
Assert.Equal(2.2, doubleValue.Value);
Assert.Equal(2.2, DoubleValue.ToDouble(doubleValue));
// 7. HexBinaryValue
HexBinaryValue hexBinaryValue = new HexBinaryValue();
string hex = "0X99CCFF";
hexBinaryValue = hex;
Assert.True(hex == hexBinaryValue);
hex = "111111";
hexBinaryValue = HexBinaryValue.FromString(hex);
Assert.Equal(hex, hexBinaryValue.Value);
Assert.Equal(hex, HexBinaryValue.ToString(hexBinaryValue));
// 8. Int16
Int16Value int16Value = new Int16Value();
short int16 = 16;
int16Value = int16;
Assert.True(int16 == int16Value);
int16 = 17;
int16Value = Int16Value.FromInt16(int16);
Assert.Equal(int16, int16Value.Value);
Assert.Equal(int16, Int16Value.ToInt16(int16Value));
// 9. Int32
Int32Value int32Value = new Int32Value();
int int32 = 32;
int32Value = int32;
Assert.True(int32 == int32Value);
int32 = 33;
int32Value = Int32Value.FromInt32(int32);
Assert.Equal(int32, int32Value.Value);
Assert.Equal(int32, Int32Value.ToInt32(int32Value));
// 10. Int64
Int64Value int64Value = new Int64Value();
long int64 = 64;
int64Value = int64;
Assert.True(int64 == int64Value);
int64 = 17;
int64Value = Int64Value.FromInt64(int64);
Assert.Equal(int64, int64Value.Value);
Assert.Equal(int64, Int64Value.ToInt64(int64Value));
// 11. IntegerValue
IntegerValue integerValue = new IntegerValue();
int integer = 64;
integerValue = integer;
Assert.True(integer == integerValue);
integer = 17;
integerValue = IntegerValue.FromInt64(integer);
Assert.Equal(integer, integerValue.Value);
Assert.Equal(integer, IntegerValue.ToInt64(integerValue));
// 12. OnOffValue
OnOffValue onOffValue = new OnOffValue();
onOffValue = true;
Assert.True(onOffValue);
onOffValue = OnOffValue.FromBoolean(false);
Assert.False(onOffValue.Value);
Assert.False(OnOffValue.ToBoolean(onOffValue));
// 13. SByteValue
SByteValue sbyteValue = new SByteValue();
sbyte sbt = sbyte.MaxValue;
sbyteValue = sbt;
Assert.True(sbt == sbyteValue);
sbt = sbyte.MinValue;
sbyteValue = SByteValue.FromSByte(sbt);
Assert.Equal(sbt, sbyteValue.Value);
Assert.Equal(sbt, SByteValue.ToSByte(sbt));
// 14. SingleValue
SingleValue singleValue = new SingleValue();
float single = float.MaxValue;
singleValue = single;
Assert.True(single == singleValue);
single = float.NaN;
singleValue = SingleValue.FromSingle(single);
Assert.Equal(single, singleValue.Value);
Assert.Equal(single, SingleValue.ToSingle(singleValue));
// 15. StringValue
StringValue stringValue = new StringValue();
string str = "Ethan";
stringValue = str;
Assert.True(str == stringValue);
str = "Yin";
stringValue = StringValue.FromString(str);
Assert.Equal(str, stringValue.Value);
Assert.Equal(str, stringValue.ToString());
Assert.Equal(str, StringValue.ToString(stringValue));
// 16. TrueFalseBlankValue
TrueFalseBlankValue tfbValue = new TrueFalseBlankValue();
tfbValue = true;
Assert.True(tfbValue);
tfbValue = TrueFalseBlankValue.FromBoolean(false);
Assert.False(tfbValue.Value);
Assert.False(TrueFalseBlankValue.ToBoolean(tfbValue));
// 17. TrueFalseValue
TrueFalseValue tfValue = new TrueFalseValue();
tfValue = true;
Assert.True(tfValue);
tfValue = TrueFalseValue.FromBoolean(false);
Assert.False(tfValue.Value);
Assert.False(TrueFalseValue.ToBoolean(tfValue));
// 18. UInt16Value
UInt16Value uint16Value = new UInt16Value();
ushort uint16 = ushort.MaxValue;
uint16Value = uint16;
Assert.True(uint16 == uint16Value);
uint16 = ushort.MinValue;
uint16Value = UInt16Value.FromUInt16(uint16);
Assert.Equal(uint16, uint16Value.Value);
Assert.Equal(uint16, UInt16Value.ToUInt16(uint16Value));
// 19. UInt32Value
UInt32Value uint32Value = new UInt32Value();
uint uint32 = uint.MaxValue;
uint32Value = uint32;
Assert.True(uint32 == uint32Value);
uint32 = uint.MinValue;
uint32Value = UInt32Value.FromUInt32(uint32);
Assert.Equal(uint32, uint32Value.Value);
Assert.Equal(uint32, UInt32Value.ToUInt32(uint32Value));
// 20. UInt64Value
UInt64Value uint64Value = new UInt64Value();
ulong uint64 = ulong.MaxValue;
uint64Value = uint64;
Assert.True(uint64 == uint64Value);
uint64 = ulong.MinValue;
uint64Value = UInt64Value.FromUInt64(uint64);
Assert.Equal(uint64, uint64Value.Value);
Assert.Equal(uint64, UInt64Value.ToUInt64(uint64Value));
}
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);
}
// ******************************************************************************
// ******************************************************************************
// CreateWorksheetColumns configures the sizing of columns that
// comprise the worksheet
// ******************************************************************************
// ******************************************************************************
private Columns CreateWorksheetColumns()
{
// define a new columns object
Columns workSheetColumns = new Columns();
// invoice number column
Column col = new Column();
col.Width = DoubleValue.FromDouble(16.0);
col.Min = UInt32Value.FromUInt32((UInt32)1);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// date column
col = new Column();
col.Width = DoubleValue.FromDouble(25.0);
col.Min = UInt32Value.FromUInt32((UInt32)2);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// first name column
col = new Column();
col.Width = DoubleValue.FromDouble(20);
col.Min = UInt32Value.FromUInt32((UInt32)3);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// last name column
col = new Column();
col.Width = DoubleValue.FromDouble(20.0);
col.Min = UInt32Value.FromUInt32((UInt32)4);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// will pickup column
col = new Column();
col.Width = DoubleValue.FromDouble(15.0);
col.Min = UInt32Value.FromUInt32((UInt32)5);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// qty column
col = new Column();
col.Width = DoubleValue.FromDouble(15.0);
col.Min = UInt32Value.FromUInt32((UInt32)6);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// unit price column
col = new Column();
col.Width = DoubleValue.FromDouble(15.0);
col.Min = UInt32Value.FromUInt32((UInt32)7);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
// subtotal column
col = new Column();
col.Width = DoubleValue.FromDouble(15.0);
col.Min = UInt32Value.FromUInt32((UInt32)8);
col.Max = col.Min;
col.CustomWidth = BooleanValue.FromBoolean(true);
workSheetColumns.Append(col);
return(workSheetColumns);
}
/// <summary>
/// Creates the content of the shet (columns, rows, cells)
/// </summary>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="worksheetPart">The worksheetpart for this item</param>
private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Any())
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
#region Excel headers
string header1 = "DesignID";
int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
headerCell1.DataType = new EnumValue <CellValues>(CellValues.SharedString);
string header2 = "GroupOrder";
int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
headerCell2.DataType = new EnumValue <CellValues>(CellValues.SharedString);
string header3 = "GroupTypeID";
int index3 = _sharedResources.InsertSharedStringItem(header3, shareStringPart);
Cell headerCell3 = _sharedResources.InsertCellInWorksheet("C", 1, worksheetPart);
headerCell3.CellValue = new CellValue(index3.ToString(CultureInfo.InvariantCulture));
headerCell3.DataType = new EnumValue <CellValues>(CellValues.SharedString);
string header4 = "PageTypeID";
int index4 = _sharedResources.InsertSharedStringItem(header4, shareStringPart);
Cell headerCell4 = _sharedResources.InsertCellInWorksheet("D", 1, worksheetPart);
headerCell4.CellValue = new CellValue(index4.ToString(CultureInfo.InvariantCulture));
headerCell4.DataType = new EnumValue <CellValues>(CellValues.SharedString);
string header5 = "IncludedTypeID";
int index5 = _sharedResources.InsertSharedStringItem(header5, shareStringPart);
Cell headerCell5 = _sharedResources.InsertCellInWorksheet("E", 1, worksheetPart);
headerCell5.CellValue = new CellValue(index5.ToString(CultureInfo.InvariantCulture));
headerCell5.DataType = new EnumValue <CellValues>(CellValues.SharedString);
#endregion
#region Create temporary list containing the groups needed to create the ktUIOrder excel sheet
foreach (PageType page in _workspaceVm.PageList)
{
foreach (GroupTypeOrder group in page.GroupTypeOrders)
{
if (group.Group != null)
{
for (int i = 0; i < group.Group.ItemOrder.Count; i++)
{
if (!_tempList.Any(x => x.GroupTypeID == group.GroupTypeID))
{
_tempList.Add(group);
}
}
}
}
}
#endregion
#region insert the items from the temporary list into the ktUIOrder excel sheet
int columnCount = 1;
uint rowCount = 2;
foreach (GroupTypeOrder group in _tempList)
{
for (int i = 0; i < group.Group.ItemOrder.Count; i++)
{
if (columnCount >= 4)
{
columnCount = 1;
}
string text1 = group.Group.ItemOrder[i].DesignID;
Cell cell1 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell1.CellValue = new CellValue(text1);
cell1.DataType = CellValues.Number;
columnCount++;
double text2 = group.Group.ItemOrder[i].ItemOrder;
Cell cell2 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell2.DataType = CellValues.Number;
cell2.CellValue = new CellValue(DoubleValue.FromDouble(text2));
columnCount++;
string text3 = group.Group.ItemOrder[i].GroupTypeID;
Cell cell3 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell3.CellValue = new CellValue(text3);
cell3.DataType = CellValues.Number;
columnCount++;
string text4 = "255"; //Dummy value - Not used for anything, but cannot be null
Cell cell4 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell4.CellValue = new CellValue(text4);
cell4.DataType = CellValues.Number;
columnCount++;
string text5 = group.Group.ItemOrder[i].IncludedTypeID;
Cell cell5 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell5.CellValue = new CellValue(text5);
cell5.DataType = CellValues.Number;
rowCount++;
}
}
#endregion
worksheetPart.Worksheet.Save();
}
private void LCreateStyle(string numberFormatCode,
double fontSize, string fontName, System.Drawing.Color fontColor
, bool fontBold, bool fontItalic, PatternValues fillPattern, System.Drawing.Color fillForeGroundColor, System.Drawing.Color borderLeftColor
, BorderStyleValues borderLeftStyleValue, System.Drawing.Color borderBottomColor, BorderStyleValues borderBottomStyleValue, System.Drawing.Color borderRightColor
, BorderStyleValues borderRightStyleValue, System.Drawing.Color borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical)
{
if (numberFormatCode != null)
{
NumberingFormat numberingFormat = new NumberingFormat();
numberingFormat.FormatCode = numberFormatCode;
_oNumberingFormat = numberingFormat;
}
//font
Font font = new Font();
FontSize _fontSize = new FontSize()
{
Val = DoubleValue.FromDouble(fontSize)
};
font.Append(_fontSize);
if (fontName != null)
{
FontName _fontName = new FontName()
{
Val = fontName
};
font.Append(_fontName);
}
if (fontColor != null)
{
Color _fontColor = new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fontColor.R, fontColor.G, fontColor.B)).Replace("#", "")
}
};
font.Append(_fontColor);
}
if (fontBold)
{
Bold _fontBold = new Bold();
font.Append(_fontBold);
}
if (fontItalic)
{
Italic _fontItalic = new Italic();
font.Append(_fontItalic);
}
_oFont = font;
//fill
Fill _fontFill = null;
if (fillForeGroundColor == null)
{
_fontFill = new Fill(new PatternFill()
{
PatternType = fillPattern
});
}
else
{
_fontFill = new Fill(new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fillForeGroundColor.R, fillForeGroundColor.G, fillForeGroundColor.B)).Replace("#", "")
}
}
)
{
PatternType = PatternValues.Solid
});
}
_oFill = _fontFill;
//border
Border _border = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = borderLeftStyleValue
};
Color colorLeftBorder = null;
if (borderLeftColor != null)
{
colorLeftBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderLeftColor.R, borderLeftColor.G, borderLeftColor.B)).Replace("#", "")
}
};
}
else
{
colorLeftBorder = new Color();
}
leftBorder.Append(colorLeftBorder);
_border.Append(leftBorder);
RightBorder rightBorder = new RightBorder()
{
Style = borderRightStyleValue
};
Color colorRightBorder = null;
if (borderRightColor != null)
{
colorRightBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderRightColor.R, borderRightColor.G, borderRightColor.B)).Replace("#", "")
}
};
}
else
{
colorRightBorder = new Color();
}
rightBorder.Append(colorRightBorder);
_border.Append(rightBorder);
TopBorder topBorder = new TopBorder()
{
Style = borderTopStyleValue
};
Color colorTopBorder = null;
if (borderTopColor != null)
{
colorTopBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderTopColor.R, borderTopColor.G, borderTopColor.B)).Replace("#", "")
}
};
}
else
{
colorTopBorder = new Color();
}
topBorder.Append(colorTopBorder);
_border.Append(topBorder);
BottomBorder bottomBorder = new BottomBorder()
{
Style = borderBottomStyleValue
};
Color colorBottomBorder = null;
if (borderBottomColor != null)
{
colorBottomBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderBottomColor.R, borderBottomColor.G, borderBottomColor.B)).Replace("#", "")
}
};
}
else
{
colorBottomBorder = new Color();
}
bottomBorder.Append(colorBottomBorder);
_border.Append(bottomBorder);
_oBorder = _border;
Alignment _alignment = new Alignment();
_alignment.Horizontal = alignmentHorizontal;
_alignment.Vertical = alignmentVertical;
_oAlignment = _alignment;
_oAlignment.WrapText = wrapText;
_oAlignment.TextRotation.Value = textRotate;
}
private static Fonts CreateFonts()
{
var fts = new Fonts();
// font 0
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
// font 1
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(12)
},
Bold = new Bold()
});
// font 2
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(18)
},
Bold = new Bold()
});
// font 3
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(14)
}
});
// font 4
fts.AppendChild(new Font
{
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = HexBinaryValueFromColor(Color.MediumBlue)
},
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
return(fts);
}
/// <summary>
///
/// </summary>
/// <param name="document">マップアート データ</param>
/// <param name="stream">書き込み先ファイルのストリーム</param>
/// <param name="baseDirectory">このオプションは使用されません</param>
/// <returns></returns>
public override async Task <ExportResult> ExportAsync(PixelArtDocument document, Stream stream, string baseDirectory)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream));
}
var spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(
// SheetData よりも先に Columns が必要
new Columns(new Column()
{
// 列の幅など
Min = 1u,
Max = (uint)document.Size.GetWidth(),
Width = 3,
CustomWidth = true,
}),
new SheetData());
var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "MCPixelArtNavi"
};
sheets.Append(sheet);
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
var stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
// スタイル生成 :: アイテム塗りつぶしの生成
var enabledItems = MCItemUtils.EnabledItems.ToList();
var fillList = new List <Fill>();
// Fill[0]: デフォルト fill の追加 (頭に追加してデフォルト化)
fillList.Add(new Fill()
{
// 固定値
PatternFill = new PatternFill()
{
PatternType = PatternValues.None
}
});
// Fill[1]: 予約枠を潰す
fillList.Add(new Fill()
{
// 固定値
PatternFill = new PatternFill()
{
PatternType = PatternValues.Gray125
}
});
// Fill[2 ～]: アイテムの色情報の Fill
fillList.AddRange(enabledItems.Select(this._createFillFromMCItem));
// デフォルト fill を含む、すべての fill に対応する cellFormat の生成
var cellFormatList = fillList.Select((e, idx) => this._createCellFormatFromFill(idx)).ToList();
// デフォルト フォント (特に中身にこだわりはない)
var font = new Font(new Color()
{
Theme = UInt32Value.FromUInt32(1U)
})
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(11d)
},
FontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
},
FontCharSet = new FontCharSet()
{
Val = 128
},
FontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
},
};
// デフォルト セル スタイル
var cellStyle = new CellStyle()
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0,
};
stylesPart.Stylesheet = new Stylesheet(
new Fonts(font),
new Fills(fillList),
new Borders(new Border()),
new CellFormats(cellFormatList),
new CellStyles(cellStyle),
new DifferentialFormats(),
new TableStyles()
{
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16"),
});
// 行作成
var rows = new Row[document.Size.GetHeight()];
var p = 0;
for (var i = 0; i < rows.Length; i++)
{
// 列作成
rows[i] = new Row()
{
RowIndex = new UInt32Value((uint)(i + 1))
};
for (var j = 0; j < document.Size.GetWidth(); j++, p++)
{
var cell = new Cell()
{
CellReference = this._toCellReference(j, i),
CellValue = new CellValue(""),
DataType = CellValues.String,
StyleIndex = (uint)(enabledItems.IndexOf(document.Pixels[p]) + 2),
};
rows[i].InsertAt(cell, j);
}
sheetData.Append(rows[i]);
}
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
return(new ExportResult());
}
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);
}
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);
}
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);
}
private static void ApplyContent(ReportData pClient, OpenXmlPackage pPackage, BlockItem pBlock, TableDefinition pContent, Dictionary <string, string> pOptions)
{
try
{
string chartId = null;
var phElem = GetElements(pBlock.OxpBlock, pClient.ReportType)?.ToList();
if (phElem == null || phElem.Count == 0)
{
LogHelper.Instance.LogError("No placeholder content found.");
return;
}
#region Get the block Id in document
var allElementInPlaceHolder = GetElementsInPlaceHolder(pClient, phElem);
XElement graphicElement = phElem.Descendants(A.graphic).FirstOrDefault() ?? phElem.FirstOrDefault(_ => A.graphic.Equals(_.Name));
if (null != graphicElement)
{
XElement chartElem = graphicElement.Descendants(C.chart).FirstOrDefault();
if (null != chartElem)
{
chartId = chartElem.Attribute(R.id)?.Value;
}
else
{
LogHelper.Instance.LogFatal("Graphic object present but not a graph.");
}
}
else
{
LogHelper.Instance.LogFatal("No graphic / chart object inside the block.");
}
if (null == chartId)
{
pBlock.XBlock.ReplaceWith(allElementInPlaceHolder); return;
}
#endregion Get the block Id in document
var chartPart = GetChartPart(pPackage, pBlock, chartId);
if (null != chartPart)
{
string spreadsheetId = GetSpreadsheetId(chartPart);
if (!string.IsNullOrWhiteSpace(spreadsheetId))
{
#region Associated content management
var embedPackage = (EmbeddedPackagePart)chartPart.GetPartById(spreadsheetId);
if (null != embedPackage)
{
using (var ms = new MemoryStream())
{
#region Set content in memory to work with
using (Stream str = embedPackage.GetStream())
{
StreamHelper.CopyStream(str, ms);
str.Close();
}
#endregion Set content in memory to work with
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(ms, true))
{
#region Associated Data File content Management
var ws = (OXS.Sheet)spreadsheetDoc.WorkbookPart.Workbook.Sheets.FirstOrDefault();
if (ws != null)
{
string sheetId = ws.Id;
var wsp = (WorksheetPart)spreadsheetDoc.WorkbookPart.GetPartById(sheetId);
XDocument shPart = wsp.GetXDocument();
XElement shData = shPart.Descendants(S.sheetData).FirstOrDefault();
#region Use of the data content (Only data, no titles)
if (null != shData)
{
IEnumerable <XElement> allRows = shData.Descendants(S.row); // => if ToList() cause some graph to fail to generate
int ctRow = 0;
int nbRows = allRows.Count();
// Cleaning Cells ======================================================================
// We clean row not having Cell information
int idxRow = 1;
var nbCorrectSeries = allRows.Where(x => x.Descendants(S.c).Any(y => y.Attribute(NoNamespace.r) != null &&
y.Attribute(NoNamespace.r)?.Value != ""))
.Max(x => x.Descendants(S.c).Count());
for (int ctn = 0; ctn < nbRows; ctn += 1)
{
var oneRow = allRows.ElementAt(ctn);//[ctn];
// DCO - 9/21/2012 - I added the condition that the count of CELL must be indeed equal of numCorrectSeries OR to the number of column
// It happens for graphs with 3 cells defined per row, but only two used (so no Value for third cell), when NbColumns was == 2
var isRowValid = ((oneRow.Descendants(S.c).Count() == nbCorrectSeries || oneRow.Descendants(S.c).Count() >= pContent.NbColumns) &&
(oneRow.Descendants(S.c).Descendants(S.v).Count() == oneRow.Descendants(S.c).Count() ||
oneRow.Descendants(S.c).Descendants(S.v).Count() >= pContent.NbColumns));
// We remove rows that are not defined in content
if (isRowValid == false || ctRow >= pContent.NbRows)
{
oneRow.Remove();
ctn -= 1;
nbRows -= 1;
continue;
}
var _xAttribute = oneRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = idxRow.ToString();
}
idxRow += 1;
}
// ====================================================================================
// Copying new row ========================================================================
// We copied new row if needed and extrapolate formula
// Take cell with no t SharedString
nbRows = allRows.Count();
if (pContent.NbRows > nbRows)
{
// We need to detect the best ROW to copy
// Usually the first row is the header so it contains two cells with SharedString
// Case 1: For others rows, it will be two cells with value.
// Case 2: For other rows, it can be one cell with SharedString and one cell with value (in case of App Nane + value)
var oneSerieRow =
// Case 1
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Any() == false)
?? // Case 2: One or several SharedString, but at least one Value (by using < content.NbColumns)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Count() < pContent.NbColumns)
?? // Case 3: Any row but the first (avoiding Header)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" &&
x.Attribute(NoNamespace.r)?.Value != "1" && x.Descendants(S.c).Any());
if (oneSerieRow != null)
{
var previousRowValue = Convert.ToInt32(oneSerieRow.Attribute(NoNamespace.r)?.Value);
while (nbRows < pContent.NbRows)
{
var newRow = new XElement(oneSerieRow);
var _xAttribute = newRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = (nbRows + 1).ToString(); // DCO Correction, ROW ID starts at 1
}
var serieCells = newRow.Descendants(S.c);
foreach (var oneCell in serieCells)
{
if (oneCell.Attribute(NoNamespace.r) == null)
{
continue;
}
var previousFormula = oneCell.Attribute(NoNamespace.r)?.Value;
// We extrapolate
int indexRow;
int indexCol;
WorksheetAccessorExt.GetRowColumnValue(previousFormula, out indexRow, out indexCol);
int newRowValue = nbRows + 1 + (indexRow - previousRowValue);
var _attribute = oneCell.Attribute(NoNamespace.r);
if (_attribute != null)
{
_attribute.Value = string.Concat(WorksheetAccessor.GetColumnId(indexCol), newRowValue.ToString());
}
if (oneCell.Attributes(NoNamespace.t).Any() != true)
{
continue;
}
var vElement = oneCell.Descendants(S.v).FirstOrDefault();
if (vElement != null)
{
vElement.Value = WorksheetAccessorExt.AddSharedStringValue(spreadsheetDoc, "").ToString();
}
// ---
}
shData.Add(newRow);
nbRows += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Adding Rows: Could not find a row without a SharedString element.");
}
}
//-----
// Define Sheet Dimension ================================================================
int minStartRow = -1;
int minStartCol = -1;
int maxEndRow = -1;
int maxEndCol = -1;
var entireScope = allRows.SelectMany(x => x.Descendants(S.c).Attributes(NoNamespace.r).Select(y => y.Value));
foreach (var oneFormula in entireScope)
{
var startRow = -1;
var endRow = -1;
var startCol = -1;
var endCol = -1;
WorksheetAccessorExt.GetFormulaCoord(oneFormula, out startRow, out startCol,
out endRow, out endCol);
if (minStartRow == -1 || startRow < minStartRow)
{
minStartRow = startRow;
}
if (minStartCol == -1 || startCol < minStartCol)
{
minStartCol = startCol;
}
if (maxEndRow == -1 || endRow > maxEndRow)
{
maxEndRow = endRow;
}
if (maxEndCol == -1 || endCol > maxEndCol)
{
maxEndCol = endCol;
}
}
XElement sheetDimension = shPart.Descendants(S.s + "dimension").FirstOrDefault();
if (sheetDimension?.Attribute(NoNamespace._ref) != null)
{
sheetDimension.Attribute(NoNamespace._ref)?.SetValue(WorksheetAccessorExt.SetFormula("", minStartRow, minStartCol, maxEndRow, maxEndCol, false));
}
// ====================================================================================
int contentEltCount = pContent.Data?.Count() ?? 0;
// Apply values =======================================================================
for (int ctn = 0; ctn < nbRows; ctn++)
{
var oneRow = allRows.ElementAt(ctn);
// TODO: We may have to correct the "spans" in: <row r="1" spans="1:3"
List <XElement> allCells = oneRow.Descendants(S.c).ToList();
var ctCell = 0;
int nbCells = allCells.Count;
for (int ctc = 0; ctc < nbCells; ctc++)
{
var oneCell = allCells[ctc];
// We remove cell if they are not defined as content columns
if (ctCell >= pContent.NbColumns)
{
LogHelper.Instance.LogWarn("More cells that defined content ");
if (null != oneCell.Parent)
{
oneCell.Remove();
}
ctc -= 1;
nbCells -= 1;
continue;
}
// We inject text
var targetText = ((ctRow * pContent.NbColumns + ctCell) < contentEltCount ?
pContent.Data?.ElementAt(ctRow * pContent.NbColumns + ctCell) :
string.Empty);
if (null != targetText && !"<KEEP>".Equals(targetText)) // Keep for managing UniversalGraph
{
var isSharedString = oneCell.Attribute(NoNamespace.t);
if (null != isSharedString && "s".Equals(isSharedString.Value))
{
if ("".Equals(targetText))
{
LogHelper.Instance.LogWarn("Target Text empty for Shared String, this can create abnormal behavior.");
}
var idx = Convert.ToInt32(oneCell.Value);
WorksheetAccessorExt.SetSharedStringValue(spreadsheetDoc, idx, targetText);
}
else
{
XElement cell = oneCell.Descendants(S.v).FirstOrDefault();
if (null != cell)
{
cell.Value = targetText;
}
else
{
LogHelper.Instance.LogWarn("No correct cell value found");
}
}
}
ctCell += 1;
}
ctRow += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Embedded spreadsheet is not formatted correctly");
}
// ====================================================================================
#endregion Get and use of the data content (Only data, no titles)
// We modify Table Definition (defining scope of Graph)
foreach (TableDefinitionPart t in wsp.TableDefinitionParts)
{
t.Table.Reference = String.Concat(WorksheetAccessor.GetColumnId(1), 1, ":",
WorksheetAccessor.GetColumnId(pContent.NbColumns), pContent.NbRows);
// We reduce the scope TableColumn if needed
var columnCount = t.Table.TableColumns.Count;
for (int ctCol = 0; ctCol < columnCount; ctCol += 1)
{
var tabColumn = t.Table.TableColumns.ElementAt(ctCol);
if (ctCol >= pContent.NbColumns)
{
tabColumn.Remove();
ctCol -= 1;
columnCount -= 1; // DCO - 10/23/2012 - Correction when reducing scope to a column (count is corrected afterwards).
continue;
}
// We align column name with the correct Shared String
if (!string.IsNullOrEmpty(pContent.Data?.ElementAt(ctCol)) && ctCol < pContent.NbColumns && "<KEEP>" != pContent.Data.ElementAt(ctCol))
{
tabColumn.SetAttribute(new OpenXmlAttribute("", "name", "", pContent.Data.ElementAt(ctCol)));
}
}
// The Count attribute is not updated correctly, so we do the work for them :)
if (pContent.NbColumns < t.Table.TableColumns.Count)
{
t.Table.TableColumns.SetAttribute(new OpenXmlAttribute("", "count", "", pContent.NbColumns.ToString()));
}
}
// We save the XML content
wsp.PutXDocument(shPart);
}
// We update cached data in Word document
UpdateCachedValues(pClient, chartPart, spreadsheetDoc, pContent);
#endregion Associated Data File content Management
}
// Write the modified memory stream back
// into the embedded package part.
using (Stream s = embedPackage.GetStream())
{
ms.WriteTo(s);
s.SetLength(ms.Length);
}
}
}
else
{
LogHelper.Instance.LogWarn("No embedded excel file found.");
}
#endregion Associated content management
}
else
{
LogHelper.Instance.LogWarn("No spreadsheet identifier found.");
}
#region Additionnal parameters
if (null == pContent.GraphOptions || !pContent.GraphOptions.HasConfiguration)
{
return;
}
Chart chart = chartPart.ChartSpace.Descendants <Chart>().FirstOrDefault();
PlotArea p_c = chart?.PlotArea;
var primaryVerticalAxis = p_c?.Descendants <ValueAxis>().FirstOrDefault(_ => "valAx".Equals(_.LocalName));
if (pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.HasValue)
{
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MinAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.Value);
}
}
if (!pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.HasValue)
{
return;
}
// ReSharper disable once PossibleInvalidOperationException
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MaxAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.Value);
}
#endregion Additionnal parameters
}
}
catch (Exception exception)
{
LogHelper.Instance.LogError("Unexpected exception thrown.", exception);
throw;
}
}
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 static SheetData CreateSheetData <T>(List <T> dataList, bool createHeaderRow, List <ClassToExcelColumn> columns) where T : class
{
var sheetData = new SheetData();
// create row
uint rowIndex = 0;
if (createHeaderRow)
{
rowIndex++;
Row headeRow = sheetData.AppendChild(new Row {
RowIndex = rowIndex
});
foreach (var dataColumn in columns)
{
// Assign COLUMN NAME to the cell
Cell newCell = new Cell {
CellReference = String.Format("{0}1", dataColumn.ExcelColumnLetter),
DataType = CellValues.String,
CellValue = new CellValue(dataColumn.ColumnName)
};
headeRow.Append(newCell);
}
}
foreach (var row in dataList)
{
rowIndex++;
Row newRow = sheetData.AppendChild(new Row {
RowIndex = rowIndex
});
foreach (ClassToExcelColumn dataColumn in columns)
{
object value = dataColumn.Property.GetValue(row, null);
if (value != null)
{
Cell newCell;
if (dataColumn.IsDate)
{
DateTime valueAsDate = (DateTime)value;
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.Number,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(valueAsDate.ToOADate().ToString(CultureInfo.InvariantCulture))
};
}
else if (dataColumn.IsDouble)
{
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.Number,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(DoubleValue.FromDouble((double)value)) // nulls can't make it here
};
}
else if (dataColumn.IsDecimal)
{
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.Number,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(DecimalValue.FromDecimal((decimal)value)) // nulls can't make it here
};
}
else if (dataColumn.IsInteger)
{
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.Number,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(IntegerValue.FromInt64((int)value)) // nulls can't make it here
};
}
else if (dataColumn.IsBoolean)
{
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.Boolean,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(BooleanValue.FromBoolean((bool)value))
};
}
else
{
newCell = new Cell
{
CellReference = String.Format("{0}{1}", dataColumn.ExcelColumnLetter, rowIndex),
DataType = CellValues.String,
StyleIndex = dataColumn.StyleIndex,
CellValue = new CellValue(value.ToString())
};
}
newRow.Append(newCell);
}
}
}
return(sheetData);
}
public Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
Font ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(8)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(18)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(9)
},
Bold = new Bold()
};//new Bold() { Val = new BooleanValue(true) }
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(14)
}
};
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.None
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Gray125
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
},
BackgroundColor = new BackgroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
}
}
};
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
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);
border = new Border()
{
LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thick
},
RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thick
},
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thick
},
BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thick
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Double
},
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, 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()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("@")
};
nfs.Append(nfForcedText);
// index 1
cf = new CellFormat()
{
NumberFormatId = nfDateTime.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 2
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 3
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 4
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 5 Header text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 6 column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center)
};
cfs.Append(cf);
// index 7 coloured 2 decimal text
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 8 coloured column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 9
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 10
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center
};
cfs.Append(cf);
// index 11
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 3, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 12
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 13
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 3, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(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);
var css = new CellStyles();
DocumentFormat.OpenXml.Spreadsheet.CellStyle cs = new DocumentFormat.OpenXml.Spreadsheet.CellStyle()
{
Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
TableStyles tss = new TableStyles()
{
Count = 0, DefaultTableStyle = StringValue.FromString("TableStyleMedium9"), DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
ss.Append(tss);
return(ss);
}
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;
}
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);
}
