public static uint ajoutFill(Stylesheet ss, string color)
{
Fills fills = ss.Fills;
uint indexFill = fills.Count;
Fill fill;
PatternFill patternFill;
if (color == "")
{
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
}
else
{
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString(color);
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
}
fills.Count = (uint)fills.ChildElements.Count;
return(fills.Count - 1);
}
/// <summary>
/// <see cref="IMCItem"/> の色情報を使用して、<see cref="Fill"/> を生成します。
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private Fill _createFillFromMCItem(IMCItem item)
{
// "#RRGGBB" => "FFRRGGBB"
var backgroundColorCode = HexBinaryValue.FromString(this._mcItemToArgbCode(item));
var pFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
pFill.Append(new ForegroundColor()
{
Rgb = backgroundColorCode
});
pFill.Append(new BackgroundColor()
{
Rgb = backgroundColorCode
});
var fill = new Fill();
fill.Append(pFill);
return(fill);
}
private static Fills GetFills()
{
Fills fills = new Fills();
var defaultFill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
}); // Index 0 - default
fills.Append(defaultFill1);
var defaultFill2 = new Fill(new PatternFill {
PatternType = PatternValues.Gray125
}); // Index 1 - default
fills.Append(defaultFill2);
Fill fillColumnHeader = new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FF005EB8")
}
}
};
fills.Append(fillColumnHeader);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
private static Border CreateBorder(string color)
{
Border border = new Border();
border.LeftBorder = new LeftBorder()
{
Color = new Color()
{
Rgb = HexBinaryValue.FromString(color)
},
Style = BorderStyleValues.Thin
};
border.RightBorder = new RightBorder()
{
Color = new Color()
{
Rgb = HexBinaryValue.FromString(color)
},
Style = BorderStyleValues.Thin
};
border.BottomBorder = new BottomBorder()
{
Color = new Color()
{
Rgb = HexBinaryValue.FromString(color)
},
Style = BorderStyleValues.Thin
};
return(border);
}
/// <summary>
/// Create Fill styles
/// </summary>
private static void CreateFillStyles(Fills fills)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 1
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
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);
}
private void BuildFills(WorkbookStylesPart stylesPart)
{
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill {
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// create green
var green = createColor("FF006836");
// create yellow
var yellow = createColor("FFffff00");
// create orange
var orange = createColor("FFff7d00");
// create blue
var blue = createColor("ff002fff");
// create white
var white = createColor("ffffffff");
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = solidRed
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = green
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = yellow
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = orange
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = blue
});
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = white
});
stylesPart.Stylesheet.Fills.Count = 7;
}
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);
}
/// <summary>
/// Adds the font color to the current
/// style sheet and creates a cell reference style
/// </summary>
/// <param name="color"></param>
private static void AddFontStyle(SystemColor color)
{
HexBinaryValue hex = HexBinaryValue.FromString(color.ToHexArgb());
//Search for style
bool exists = StylePart.Stylesheet.Fonts
.Select(f => f as Font)
.Where(f => f.Color != null)
.Where(f => f.Color.Rgb.Equals(hex))
.Any();
if (exists)
{
return;
}
//Create font element
Font font = new Font();
font.Append(new FontSize()
{
Val = 11D
});
font.Append(new Color()
{
Rgb = hex
});
font.Append(new FontName()
{
Val = "Calibri"
});
font.Append(new FontFamilyNumbering()
{
Val = 2
});
font.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
font.Append(new Bold());
CellFormat cellFontFormat = new CellFormat()
{
FontId = StylePart.Stylesheet.Fonts.Count,
ApplyFont = true
};
StylePart.Stylesheet.Fonts.Append(font);
StylePart.Stylesheet.Fonts.Count++;
//Add the format style reference
StylePart.Stylesheet.CellFormats.Append(cellFontFormat);
StylePart.Stylesheet.CellFormats.Count++;
//Save all changes
StylePart.Stylesheet.Save();
}
public MakeStyleIntent WithFill(string rgbColor, PatternValues patternType = PatternValues.Solid)
{
var _rgb = rgbColor.TrimStart('#');
var patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
patternFill.ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(_rgb)
};
return(WithFill(patternFill));
}
public static Border CreateBorder(bool leftBorder, bool rightBorder, bool topBorder, bool bottomBorder, string borderColor, BorderStyleValues borderSize)
{
Border border = new Border();
Color color = new Color
{
Rgb = HexBinaryValue.FromString(borderColor)
};
if (leftBorder)
{
LeftBorder leftBorder2 = new LeftBorder()
{
Style = borderSize
};
leftBorder2.Color = color;
border.LeftBorder = leftBorder2;
}
if (rightBorder)
{
RightBorder rightBorder2 = new RightBorder()
{
Style = borderSize
};
rightBorder2.Color = (Color)color.CloneNode(true);
border.RightBorder = rightBorder2;
}
if (topBorder)
{
TopBorder topBorder2 = new TopBorder()
{
Style = borderSize
};
topBorder2.Color = (Color)color.CloneNode(true);
border.TopBorder = topBorder2;
}
if (bottomBorder)
{
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = borderSize
};
bottomBorder2.Color = (Color)color.CloneNode(true);
border.BottomBorder = bottomBorder2;
}
return(border);
}
private PatternFill createColor(string colorhex)
{
var color = new PatternFill {
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString(colorhex)
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
return(color);
}
public void SetFont(WorkbookStylesPart stylesPart, UInt32Value fontIndex)
{
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
if (IsBold)
{
font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold();
}
font.Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
{
Rgb = HexBinaryValue.FromString(((uint)Color).ToString("x8"))
};
stylesPart.Stylesheet.Fonts.AppendChild(font);
FontIndex = fontIndex;
}
public static Fill CreateFill(string foreHexColor, string backHexColor)
{
PatternFill solidColor = new PatternFill {
PatternType = PatternValues.Solid
};
solidColor.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString(foreHexColor)
};
solidColor.BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValue.FromString(backHexColor)
};
return(new Fill {
PatternFill = solidColor
});
}
static public PatternFill GetFill(this CellFillAttribute cellFillAttribute)
{
PatternFill retFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
if (!string.IsNullOrEmpty(cellFillAttribute.HexColor))
{
retFill.ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(cellFillAttribute.HexColor)
};
}
return(retFill);
}
/// <summary>
/// Creates fills
/// </summary>
/// <param name="styleSheet">The style sheet.</param>
static void CreateFills(Stylesheet styleSheet)
{
Fills fills = new Fills();
fills.Append(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); //0
fills.Append(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); //1
fills.Append(new Fill //2
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("01DF3A")
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValue.FromString("01DF3A")
},
}
});
fills.Append(new Fill //3
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
},
}
});
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
styleSheet.Append(fills);
}
public static Font CreateFont(string hexForeColor, string fontName, double?fontSize, bool isBold, bool isItalic)
{
// Creamos una fuente nueva
Font font = new Font();
// Si tiene otra fuente diferente a la de por defecto, la cambiamos
if (!string.IsNullOrEmpty(fontName))
{
font.Append(new FontName {
Val = fontName
});
}
// Si tiene un tamaño diferente al de por defecto, lo cambiamos
if (fontSize.HasValue)
{
font.Append(new FontSize {
Val = fontSize.Value
});
}
// Si es negrita lo cambiamos
if (isBold)
{
font.Append(new Bold());
}
// Si es cursiva, lo cambiamos
if (isItalic)
{
font.Append(new Italic());
}
// Establecemos el color de la fuente
Color color = new Color
{
Rgb = HexBinaryValue.FromString(hexForeColor)
};
font.Append(color);
return(font);
}
public Fill ToXmlStyle()
{
var fill = new Fill();
var pattern = new PatternFill()
{
PatternType = PatternType
};
if (!string.IsNullOrEmpty(FillCollor))
{
pattern.ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(FillCollor)
}
}
;
fill.Append(pattern);
return(fill);
}
/// <summary>
/// Adds the background color to the current
/// style sheet and creates a cell reference style
/// </summary>
/// <param name="color"></param>
private static void AddBackgroundStyle(SystemColor color)
{
HexBinaryValue hex = HexBinaryValue.FromString(color.ToHexArgb());
//Search for style
bool exists = StylePart.Stylesheet.Fills
.Select(f => f as Fill)
.Where(f => f.PatternFill.ForegroundColor != null)
.Where(f => f.PatternFill.PatternType.Equals(PatternValues.Solid))
.Where(f => f.PatternFill.ForegroundColor.Rgb.Equals(hex))
.Any();
if (exists)
{
return;
}
//Create fill element
Fill fill = new Fill()
{
PatternFill = new PatternFill()
{
ForegroundColor = new ForegroundColor()
{
Rgb = hex,
},
PatternType = PatternValues.Solid
}
};
//Add the fill (of a background)
CellFormat cellFontFormat = new CellFormat()
{
FillId = StylePart.Stylesheet.Fills.Count,
ApplyFill = true
};
StylePart.Stylesheet.Fills.Append(fill);
StylePart.Stylesheet.Fills.Count++;
//Add the format style reference
StylePart.Stylesheet.CellFormats.Append(cellFontFormat);
StylePart.Stylesheet.CellFormats.Count++;
//Save all changes
StylePart.Stylesheet.Save();
}
/// <summary>
/// 创建一个字体相关的Excel样式
/// </summary>
/// <param name="fontName"></param>
/// <param name="fontSize"></param>
/// <param name="fontColor"></param>
/// <returns></returns>
private static Font CreateFont(string fontName, string fontSize, string fontColor)
{
Font font = new Font();
font.FontName = new FontName()
{
Val = new StringValue(fontName)
};
font.FontSize = new FontSize()
{
Val = new DoubleValue(double.Parse(fontSize))
};
font.Color = new Color()
{
Rgb = HexBinaryValue.FromString(fontColor)
};
font.FontFamilyNumbering = new FontFamilyNumbering {
Val = 2
};
return(font);
}
/// <summary>
/// 获取字体
/// </summary>
/// <param name="size"></param>
/// <param name="name"></param>
/// <param name="dColor"></param>
/// <param name="bold"></param>
/// <param name="underline"></param>
/// <returns></returns>
public static Font GetFont(double size, string name, DColor dColor, Bold bold, Underline underline)
{
var font = new Font()
{
FontSize = new FontSize()
{
Val = size
},
FontName = new FontName()
{
Val = name
},
Bold = bold,
Color = new Color()
{
Rgb = HexBinaryValue.FromString(dColor.GetRgbString())
},
Underline = underline
};
return(font);
}
private uint AddFill(string hexColor)
{
var fills = Style.Fills;
var pattern = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString($"FF{hexColor}")
}
};
fills.AppendChild(new Fill {
PatternFill = pattern
});
var fillIndex = Convert.ToUInt32(fills.ChildElements.Count) - 1;
fills.Count = fillIndex + 1;
return(fillIndex);
}
/// <summary>
/// 获取填充
/// </summary>
/// <param name="pattern"></param>
/// <param name="foreDColor"></param>
/// <param name="backDColor"></param>
public static Fill GetFill(PatternValues pattern, DColor foreDColor, DColor backDColor)
{
var fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = pattern,
//填充色
ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(foreDColor.GetRgbString())
},
//暂时未知
BackgroundColor = new BackgroundColor()
{
Rgb = HexBinaryValue.FromString(backDColor.GetRgbString())
}
}
};
return(fill);
}
private static Fill CreateFill(string color)
{
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
patternFill.Append(new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(color)
});
patternFill.Append(new BackgroundColor()
{
Indexed = (UInt32Value)64U
});
Fill fill = new Fill()
{
PatternFill = patternFill
};
return(fill);
}
public static uint ajoutFont(Stylesheet ss, string name, int size, bool bold, string color)
{
uint indexFont;
Fonts fts = ss.Fonts;
indexFont = fts.Count;
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = name;
FontSize ftsz = new FontSize();
ftsz.Val = size;
ft.FontName = ftn;
ft.FontSize = ftsz;
if (bold)
{
ft.Bold = new Bold();
}
if (color != "")
{
Color couleur = new Color()
{
Rgb = new HexBinaryValue()
{
Value = HexBinaryValue.FromString(color)
}
};
ft.Append(couleur);
}
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
return(fts.Count - 1);
}
public void SetFill(WorkbookStylesPart stylesPart, UInt32Value fillIndex)
{
var fill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid
};
fill.ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor
{
Rgb = HexBinaryValue.FromString(((uint)Color).ToString("x8"))
};
fill.BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor
{
Indexed = 64
};
stylesPart.Stylesheet.Fills.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Fill
{
PatternFill = fill
});
FillIndex = fillIndex;
}
private static Fills CreateFills()
{
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//fill 1
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
//fill 2
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
//fill 3
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
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
public async Task <bool> FillAsync(ITemplate t, IDictionary <string, dynamic> stuffing, Stream output)
{
dynamic rows = stuffing.Values.FirstOrDefault(a => a is IEnumerable <object>);
if (rows == null)
{
return(false);
}
//using (Stream docstream = new MemoryStream())
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(output, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = doc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
#region styles
// Add styles
var stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
// number formats, date is a formatted number
//var numFmt = @"<numFmts count=""1""><numFmt numFmtId=""166"" formatCode=""[$-F800]dddd\,\ mmmm\ dd\,\ yyyy""/></numFmts>";
var numFmtDate = new NumberingFormat();
numFmtDate.NumberFormatId = 166;
numFmtDate.FormatCode = @"[$-F800]dddd\,\ mmmm\ dd\,\ yyyy";
stylesPart.Stylesheet.NumberingFormats = new NumberingFormats();
stylesPart.Stylesheet.NumberingFormats.Count = 1;
stylesPart.Stylesheet.NumberingFormats.AppendChild(numFmtDate);
// blank font list
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
// create fills
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill()
{
PatternType = PatternValues.Solid
};
solidRed.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
}; // red fill
solidRed.BackgroundColor = new BackgroundColor {
Indexed = 64
};
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = solidRed
});
stylesPart.Stylesheet.Fills.Count = 3;
// blank border list
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
// blank cell format list
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
// cell format list
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// cell format index 1 applies the date format
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat {
FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, NumberFormatId = 166, ApplyNumberFormat = true
}).AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Center
});
//stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
stylesPart.Stylesheet.CellFormats.Count = 2;
//stylesPart.Stylesheet.Save();
#endregion
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
// Workbook -> Sheets -> Sheet
Sheet sheet = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart sharedStringPart = ExcelTemplateTool.GetSharedStringTablePart(doc);
// don't let it confuse you
// doc -> WorkbookPart -> WorksheetPart -> WorkSheet -> SheetData
SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (var datarow in rows)
{
IEnumerable <object> cells = datarow as IEnumerable <object>;
if (cells == null)
{
continue;
}
Row contentRow = new Row();
//int index = 10;
//contentRow.RowIndex = (UInt32)index;
foreach (var cellObj in cells)
{
Cell c;
if (cellObj == null)
{
c = CreateEmptyCell();
}
else if (cellObj is string)
{
c = CreateSharedStringCell((string)cellObj, sharedStringPart);
}
else if (cellObj is DateTime)
{
c = CreateDateTimeCell((DateTime)cellObj);
}
else if (cellObj.GetType().GetTypeInfo().IsValueType)
{
c = CreateValueCell(cellObj);
}
else
{
c = CreateInlineStringCell(cellObj.ToString());
}
contentRow.AppendChild(c);
}
sheetdata.AppendChild(contentRow);
}
workbookpart.Workbook.Save();
}
return(true);
}
private Stylesheet CreateStyleSheet()
{
var stylesheet = new Stylesheet();
#region (Default) Font 0 settings (bold, 10ph, "Verdana", "Black").
var font0 = new Font();
font0.Append(new FontSize {
Val = 10D
});
font0.Append(new FontName {
Val = "Verdana"
});
font0.Append(new Color {
Rgb = HexBinaryValue.FromString("FF000000")
});
#endregion
#region (Info) Font 1 settings (bold, 10ph, "Arial", "Green").
var font1 = new Font();
font1.Append(new Bold());
font1.Append(new FontSize {
Val = 10D
});
font1.Append(new FontName {
Val = "Arial"
});
font1.Append(new Color {
Rgb = HexBinaryValue.FromString("FF6AA84F")
});
#endregion
#region (Common headers) Font 2 settings (bold, 10ph, "Arial", "Blue" ).
var font2 = new Font();
font2.Append(new Bold());
font2.Append(new FontSize {
Val = 10D
});
font2.Append(new FontName {
Val = "Arial"
});
font2.Append(new Color {
Rgb = HexBinaryValue.FromString("FF099cce")
});
#endregion
#region (Nested headers) Font 3 settings (bold, 10ph, "Arial", "Black" ).
var font3 = new Font();
font3.Append(new Bold());
font3.Append(new FontSize {
Val = 10D
});
font3.Append(new FontName {
Val = "Arial"
});
font3.Append(new Color {
Rgb = HexBinaryValue.FromString("FF000000")
});
#endregion
// Add Fonts.
var fonts = new Fonts();
fonts.Append(font0);
fonts.Append(font1);
fonts.Append(font2);
fonts.Append(font3);
// Add fills.
var fill0 = new Fill();
var fill1 = new Fill(); // (Info)
var fill2 = new Fill(); // (Common headers)
var fill3 = new Fill(); // (Nested headers)
var fill4 = new Fill(); // (Nested rows)
var fills = new Fills();
fills.Append(fill0);
fills.Append(fill1);
fills.Append(fill2);
fills.Append(fill3);
fills.Append(fill4);
#region (Nested headers) Fill 3 (grey)
//#f1f1f1
var solidlightBlue = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor
{
Rgb = HexBinaryValue.FromString("FFf1f1f1")
}
};
//solidlightBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FF099cce") };
//solidlightBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };
fill3.PatternFill = solidlightBlue;
#endregion
#region (Nested rows) Fill 4 (white)
var solidWhite = new PatternFill
{
//PatternType = PatternValues.Solid
ForegroundColor = new ForegroundColor
{
Rgb = HexBinaryValue.FromString("FFFFFFFF")
}
};
//solidWhite.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFFFFFF") };
//solidWhite.BackgroundColor = new BackgroundColor { Indexed = 64 };
fill4.PatternFill = solidWhite;
#endregion
//#region (Nested headers) Fill 3 (blue)
////#f1f1f1
//var solidlightBlue = new PatternFill
//{
// PatternType = PatternValues.Solid,
// ForegroundColor = new ForegroundColor
// {
// Rgb = HexBinaryValue.FromString("FF099cce")
// }
//};
////solidlightBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FF099cce") };
////solidlightBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };
//fill3.PatternFill = solidlightBlue;
//#endregion
// Add Borders.
var border0 = new Border();
var borders = new Borders();
borders.Append(border0);
// CellFormats.
var verticalAligmentTop = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Top);
var verticalAligmentBottom = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Bottom);
//var cellformat0 = new CellFormat { FontId = 0, FillId = 3, BorderId = 0 };
var cellformat0 = new CellFormat {
FontId = 0, FillId = 0, Alignment = new Alignment {
Vertical = verticalAligmentTop, WrapText = true
}
};
var cellformat1 = new CellFormat {
FontId = 1, Alignment = new Alignment {
Vertical = verticalAligmentTop, WrapText = true
}
}; // Info
var cellformat2 = new CellFormat {
FontId = 2, Alignment = new Alignment {
Vertical = verticalAligmentBottom, WrapText = true
}
}; // (Common headers)
var cellformat3 = new CellFormat {
FontId = 3, FillId = 3, Alignment = new Alignment {
WrapText = true
}
}; // (Nested headers)
var cellformat4 = new CellFormat {
FontId = 0, Alignment = new Alignment {
Vertical = verticalAligmentTop, WrapText = true
}
}; // (Nested rows)
var cellformat5 = new CellFormat {
FontId = 0, Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = verticalAligmentTop, WrapText = true
}
}; // (Nested rows, columns: Estimated, Actual)
var cellformat6 = new CellFormat {
FontId = 0, Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left, Vertical = verticalAligmentTop, WrapText = true
}
}; // (Nested rows, columns: Estimated, Actual)
// Add CellFormats.
var cellformats = new CellFormats();
cellformats.Append(cellformat0);
cellformats.Append(cellformat1);
cellformats.Append(cellformat2);
cellformats.Append(cellformat3);
cellformats.Append(cellformat4);
cellformats.Append(cellformat5);
cellformats.Append(cellformat6);
// Add FONTS, FILLS, BORDERS & CellFormats to stylesheet. (Preserve the ORDER)
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellformats);
return(stylesheet);
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 1
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 8
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
return;
}
public 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));
}