public void LoadFromStream(Stream stream)
{
var parser = new StylesheetParser ();
parser.Parse(stream);
Stylesheet = parser.Stylesheet;
}
private static uint AddNewCellFormat(Stylesheet styleSheet, CellFormat cellFormat)
{
var newIndex = (uint)styleSheet.CellFormats.Count();
var copiedCell = CopyCellFormat(cellFormat);
styleSheet.CellFormats.AppendChild(copiedCell);
return newIndex;
}
private static Stylesheet BuildStylesheet(ITree tree)
{
var stylesheet = new Stylesheet();
stylesheet.Build(tree);
return stylesheet;
}
private static UInt32Value CreateCellFormat(
Stylesheet styleSheet,
UInt32Value fontIndex,
UInt32Value fillIndex,
UInt32Value numberFormatId)
{
CellFormat cellFormat = new CellFormat();
if (fontIndex != null)
cellFormat.FontId = fontIndex;
if (fillIndex != null)
cellFormat.FillId = fillIndex;
if (numberFormatId != null)
{
cellFormat.NumberFormatId = numberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
}
styleSheet.CellFormats.Append(cellFormat);
UInt32Value result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return result;
}
private static CellFormat FindCellStyleFormat(Stylesheet styleSheet, string styleName)
{
var list = styleSheet.CellStyleFormats.Descendants<CellFormat>().ToList();
var style = styleSheet.CellStyles.Descendants<CellStyle>().First(x => x.Name == styleName);
var cellFormat = list[checked((int)(uint)style.FormatId)];
return cellFormat;
}
public void LoadFromString(string content)
{
var parser = new StylesheetParser();
parser.Parse (content);
Stylesheet = parser.Stylesheet;
}
public HeaderCell(string header, string text, int index, Stylesheet styles,
System.Drawing.Color fillColour, double? fontSize, bool isBold)
: base(header, text, index)
{
UInt32Value fontId = CreateFont(styles, "", fontSize, isBold, System.Drawing.Color.Black);
UInt32Value fillId = CreateFill(styles, fillColour);
UInt32Value formatId = CreateCellFormat(styles, fontId, fillId, 0);
StyleIndex = formatId;
}
/// <summary> Creates <see cref="Tile"/>. </summary>
/// <param name="quadKey"></param>
/// <param name="stylesheet"></param>
/// <param name="projection"> Projection. </param>
internal Tile(QuadKey quadKey, Stylesheet stylesheet, IProjection projection)
{
QuadKey = quadKey;
Stylesheet = stylesheet;
Projection = projection;
BoundingBox = GeoUtils.QuadKeyToBoundingBox(quadKey);
Rectangle = GeoUtils.QuadKeyToRect(projection, quadKey);
}
private static bool TryGetCellFormatIndex(Stylesheet styleSheet, CellFormat cellFormat, out uint index)
{
var cellFormatInfo = styleSheet.CellFormats.Descendants<CellFormat>()
.Select((c, i) => new { CellFormat = c, Index = (uint)i })
.FirstOrDefault(c => CellFormatEquals(c.CellFormat, cellFormat));
var exists = cellFormatInfo != null;
index = exists ? cellFormatInfo.Index : uint.MaxValue;
return exists;
}
/// <summary>Initialize Stylist</summary>
/// <param name="CSS_File">The path to the CSS file (or null if only internal styles are need to be used)</param>
public Stylist(string CSS_File = null)
{
string DefaultStyle = Utilities.GetEmbeddedResource("Resources.Default.css");
if (DefaultStyle == null) Environment.FailFast("File Commander has been crashed: the default theme's stylesheet is unable to load. Possibly there is a failure of the pluginner.dll body or RAM banks. Try to reinstall FC.", new InvalidProgramException("Default style isn't loading"));
if(CSS_File != null && CSS_File != "")
{ CSS = new Stylesheet(System.IO.File.ReadAllText(CSS_File) + DefaultStyle); }
else
CSS = new Stylesheet(DefaultStyle);
}
private static UInt32Value CreateFont(
Stylesheet styleSheet,
string fontName,
double? fontSize,
bool isBold,
System.Drawing.Color foreColor)
{
Font font = new Font();
if (!string.IsNullOrEmpty(fontName))
{
FontName name = new FontName()
{
Val = fontName
};
font.Append(name);
}
if (fontSize.HasValue)
{
FontSize size = new FontSize()
{
Val = fontSize.Value
};
font.Append(size);
}
if (isBold == true)
{
Bold bold = new Bold();
font.Append(bold);
}
Color color = new Color()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
foreColor.A,
foreColor.R,
foreColor.G,
foreColor.B)).Replace("#", "")
}
};
font.Append(color);
styleSheet.Fonts.Append(font);
UInt32Value result = styleSheet.Fonts.Count;
styleSheet.Fonts.Count++;
return result;
}
public void TestFixtureSetUp()
{
using (var stylesheetFile = TempFile.WithExtension(".xml"))
{
File.WriteAllText(stylesheetFile.Path, Resources.styles_xml);
Exception e;
m_stylesheet = Stylesheet.Load(stylesheetFile.Path, out e);
Assert.IsNull(e);
}
m_stylesheetFromXml = XmlSerializationHelper.DeserializeFromString<Stylesheet>(kXml);
}
private void SaveStyleSheet_Click(object sender, RadRoutedEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Xaml Files|*.xaml";
if (sfd.ShowDialog() == true)
{
using (var stream = sfd.OpenFile())
{
Stylesheet stylesheet = new Stylesheet();
stylesheet.ExtractStylesheetFromDocument(this.editor.Document);
XamlFormatProvider.SaveStylesheet(stylesheet, stream);
}
}
}
/// <summary>
/// Creates Stylesheet from collections of border styles, fontstyles, color styles etc...
/// </summary>
public static Stylesheet GetStyleSheet(ExcelType scheme)
{
var stylesheet = new Stylesheet();
var fontCollection = new FontCollection();
var fillsCollection = new FillsCollection();
var borderCollection = new BorderCollection();
var cellStyleFormats = new CellStyleFormats();
var cellStyle = new CellFormat();
cellStyle.NumberFormatId = 0;
cellStyle.FontId = 0;
cellStyle.FillId = 0;
cellStyle.BorderId = 0;
cellStyleFormats.Append(cellStyle);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
var numberingFormats = new CellContentFormat();
var cellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
cellFormats.Append(GetStyleSheetScheme(scheme));
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
stylesheet.Append(numberingFormats);
stylesheet.Append(fontCollection);
stylesheet.Append(fillsCollection);
stylesheet.Append(borderCollection);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
var css = new DocumentFormat.OpenXml.Spreadsheet.CellStyles();
var 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);
var dfs = new DifferentialFormats();
dfs.Count = 0;
stylesheet.Append(dfs);
var tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
stylesheet.Append(tss);
return stylesheet;
}
/// <summary>
/// Create the spreadsheet.
/// </summary>
/// <param name="documentName">Excel file name.</param>
/// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
/// <param name="rowData">Row data to write.</param>
/// <param name="headerData">Header data.</param>
/// <param name="rowPointers">Row pointers.</param>
/// <param name="styleSheet">Style sheet.</param>
/// <returns>Memory stream.</returns>
private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
{
int rowNum = 0;
int colNum = 0;
int maxWidth = 0;
int minCol = 1;
int maxCol = rowPointers == null ? minCol : rowPointers.Length;
maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;
MemoryStream xmlStream = SpreadsheetReader.Create();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);
SetSheetName(excelWorkSheetName, spreadSheet);
if (styleSheet == null)
{
SetStyleSheet(spreadSheet);
}
else
{
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);
WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);
if (rowPointers == null || rowPointers.Length == 0)
{
WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
else
{
WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
// Save to the memory stream
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return xmlStream;
}
/// <summary>
/// Parses the specified stream.
/// </summary>
/// <param name="stream">The stream.</param>
/// <returns></returns>
public Stylesheet Parse(Stream stream)
{
Errors.Clear();
var builder = new StringBuilder();
var errorStream = new StringWriter(builder);
var scanner = new Scanner(stream);
var parser = new Parser(scanner)
{
errors =
{
errorStream = errorStream
}
};
parser.Parse();
Stylesheet = parser.Stylesheet;
SpitErrors(builder);
return Stylesheet;
}
protected override void InternalSetStyle(Stylesheet stylesheet, string name)
{
ApplyGridStyle(stylesheet.GridStyles.SafelyGetStyle(name));
base.InternalSetStyle(stylesheet, name);
}
public static Stylesheet GenerateStylesheet()
{
Stylesheet styleSheet = null;
Fonts fonts = new Fonts(
new Font( // Index 0 - default
new FontSize()
{
Val = 10
},
new FontName()
{
Val = "Times New Roman"
}
),
new Font( // Index 1 - header
new FontSize()
{
Val = 10
},
new Bold(),
new Color()
{
Rgb = "FFFFFFFF"
},
new FontName()
{
Val = "Times New Roman"
}
));
Fills fills = new Fills(
new Fill(new PatternFill()
{
PatternType = PatternValues.None
}), // Index 0 - default
new Fill(new PatternFill()
{
PatternType = PatternValues.Gray125
}), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue()
{
Value = "FF4472C4"
}
})
{
PatternType = PatternValues.Solid
}), // Index 2 - header
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue()
{
Value = "FFFF0000"
}
})
{
PatternType = PatternValues.Solid
}), //Index 3 - bad
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue()
{
Value = "FF00FF00"
}
})
{
PatternType = PatternValues.Solid
}) //index 4 - good
);
Borders borders = new Borders(
new Border(), // index 0 default
new Border( // index 1 black border
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder())
);
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat(),
new CellFormat()
{
FontId = 1,
FillId = 3,
BorderId = 1,
ApplyFill = true,
FormatId = 0,
}, // 1
new CellFormat()
{
FontId = 1,
FillId = 4,
BorderId = 1,
ApplyFill = true,
FormatId = 1
} // 2
);
CellFormats cellFormats = new CellFormats(
new CellFormat()
{
FormatId = 0
}, //0 default
new CellFormat()
{
FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true
}, //1 body
new CellFormat(
new Alignment()
{
Horizontal = HorizontalAlignmentValues.CenterContinuous,
Vertical = VerticalAlignmentValues.Center
})
{
FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true
}, //2
new CellFormat()
{
FormatId = 1
} // 3
);
CellStyles cellStyles = new CellStyles(
new CellStyle()
{
FormatId = 0
},
new CellStyle()
{
Name = "good", FormatId = 2, BuiltinId = 26
},
new CellStyle()
{
Name = "bad", FormatId = 1, BuiltinId = 27
}
);
styleSheet = new Stylesheet(fonts, fills, borders, cellStyleFormats, cellFormats, cellStyles);
return(styleSheet);
}
public static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = AddFonts();
var fills1 = AddFills();
var borders1 = AddBorders();
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.AppendChild(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 4U
};
// Black text on White background
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
// White text on Orange background
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 2U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// White text on Blue background
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 3U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// Black text on Yellow background
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 4U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
cellFormats1.AppendChild(cellFormat2);
cellFormats1.AppendChild(cellFormat3);
cellFormats1.AppendChild(cellFormat4);
cellFormats1.AppendChild(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.AppendChild(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet1.AppendChild(fonts1);
stylesheet1.AppendChild(fills1);
stylesheet1.AppendChild(borders1);
stylesheet1.AppendChild(cellStyleFormats1);
stylesheet1.AppendChild(cellFormats1);
stylesheet1.AppendChild(cellStyles1);
stylesheet1.AppendChild(differentialFormats1);
stylesheet1.AppendChild(tableStyles1);
stylesheet1.AppendChild(stylesheetExtensionList);
return(stylesheet1);
}
public ExportContext(SpreadsheetDocument spreadSheet)
{
if(spreadSheet == null)
{
throw new ArgumentNullException("spreadSheet");
}
if(spreadSheet.FileOpenAccess != System.IO.FileAccess.ReadWrite)
{
throw new Exception("No access granted for opened excel");
}
SpreadSheet = spreadSheet;
if(SpreadSheet.WorkbookPart == null)
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = SpreadSheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = SpreadSheet.WorkbookPart.Workbook.
AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = SpreadSheet.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
}
WorkbookStylesPart stylesPart = SpreadSheet.WorkbookPart.WorkbookStylesPart;
if(stylesPart == null)
{
stylesPart = SpreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
}
if(stylesPart.Stylesheet == null)
{
stylesPart.Stylesheet = new Stylesheet();
}
StyleSheet = stylesPart.Stylesheet;
if(StyleSheet.Fonts == null)
{
StyleSheet.Fonts = new Fonts();
// required by Excel
StyleSheet.Fonts.AppendChild(new Font());
StyleSheet.Fonts.Count = 1;
}
if(StyleSheet.Fills == null)
{
StyleSheet.Fills = new Fills();
// required, reserved by Excel
StyleSheet.Fills.Append(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } });
// required, reserved by Excel
StyleSheet.Fills.Append(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } });
StyleSheet.Fills.Count = 2;
}
if(StyleSheet.Borders == null)
{
StyleSheet.Borders = new Borders();
// required by Excel
StyleSheet.Borders.Append(new Border());
StyleSheet.Borders.Count = 1;
}
if(StyleSheet.CellStyleFormats == null)
{
StyleSheet.CellStyleFormats = new CellStyleFormats();
// required by Excel
StyleSheet.CellStyleFormats.Append(new CellFormat());
StyleSheet.CellStyleFormats.Count = 1;
}
if(StyleSheet.CellFormats == null)
{
StyleSheet.CellFormats = new CellFormats();
// required by Excel
StyleSheet.CellFormats.AppendChild(new CellFormat());
StyleSheet.CellFormats.Count = 1;
}
Worksheet = SpreadSheet.WorkbookPart.WorksheetParts.First().Worksheet;
SheetData = Worksheet.GetFirstChild<SheetData>();
}
protected override void InternalSetStyle(Stylesheet stylesheet, string name)
{
ApplyTextButtonStyle(new TextButtonStyle(stylesheet.ButtonStyles[name], stylesheet.LabelStyle));
}
/// <summary>
/// Вставить стиль ячейки используя класс CellFormat
/// </summary>
/// <param name="stylesheet">Таблица стилей</param>
/// <param name="format">Объект формата ячейки, содержащии информицию о стиле ячейки.</param>
/// <returns>ID вставленнго формата ячейки в структуре документа.</returns>
public static uint CellFormat(this Stylesheet stylesheet, CellFormat format)
{
return(stylesheet.GetCellFormats().CellFormat(format));
}
// Implementation
private void Compile(XPathNavigator stylesheet, XmlResolver resolver)
{
Debug.Assert(stylesheet != null);
Compiler compiler = (Debugger == null) ? new Compiler() : new DbgCompiler(this.Debugger);
NavigatorInput input = new NavigatorInput(stylesheet);
compiler.Compile(input, resolver ?? XmlNullResolver.Singleton);
Debug.Assert(compiler.CompiledStylesheet != null);
Debug.Assert(compiler.QueryStore != null);
Debug.Assert(compiler.QueryStore != null);
_CompiledStylesheet = compiler.CompiledStylesheet;
_QueryStore = compiler.QueryStore;
_RootAction = compiler.RootAction;
}
public Tree(Stylesheet stylesheet) : this(stylesheet.TreeStyle)
{
}
// Creates an Stylesheet instance and adds its children.
public Stylesheet GenerateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
font1.Append(fontSize1);
font1.Append(fontName1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color1 = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(color1);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color2 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color2);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color3);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color4);
DiagonalBorder diagonalBorder2 = new DiagonalBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
diagonalBorder2.Append(color5);
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
/// <summary>
/// Adds the basic styles to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <returns>True if succesful</returns>
public static bool AddBasicStyles(SpreadsheetDocument spreadsheet)
{
Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// Numbering formats (x:numFmts)
stylesheet.InsertAt(new NumberingFormats(), 0);
// Currency
stylesheet.GetFirstChild <NumberingFormats>().InsertAt <NumberingFormat>(
new NumberingFormat()
{
NumberFormatId = 164,
FormatCode = "#,##0.00"
+ "\\ \"" + CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
}, 0);
// Fonts (x:fonts)
stylesheet.InsertAt(new Fonts(), 1);
stylesheet.GetFirstChild <Fonts>().InsertAt <Font>(
new Font()
{
FontSize = new FontSize()
{
Val = 11
},
FontName = new FontName()
{
Val = "Calibri"
}
}, 0);
// Fills (x:fills)
stylesheet.InsertAt(new Fills(), 2);
stylesheet.GetFirstChild <Fills>().InsertAt <Fill>(
new Fill()
{
PatternFill = new PatternFill()
{
PatternType = new EnumValue <PatternValues>()
{
Value = PatternValues.None
}
}
}, 0);
// Borders (x:borders)
stylesheet.InsertAt(new Borders(), 3);
stylesheet.GetFirstChild <Borders>().InsertAt <Border>(
new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
}, 0);
// Cell style formats (x:CellStyleXfs)
stylesheet.InsertAt(new CellStyleFormats(), 4);
stylesheet.GetFirstChild <CellStyleFormats>().InsertAt <CellFormat>(
new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
}, 0);
// Cell formats (x:CellXfs)
stylesheet.InsertAt(new CellFormats(), 5);
// General text
stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>(
new CellFormat()
{
FormatId = 0,
NumberFormatId = 0
}, 0);
// Date
stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>(
new CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 22,
FontId = 0,
FillId = 0,
BorderId = 0
},
1);
// Currency
stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>(
new CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 164,
FontId = 0,
FillId = 0,
BorderId = 0
},
2);
// Percentage
stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>(
new CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 10,
FontId = 0,
FillId = 0,
BorderId = 0
},
3);
stylesheet.Save();
return(true);
}
private Stylesheet GenerateWorkbookStylesPart()
{
var element =
new Stylesheet(
new Fonts(
new Font(
new FontSize()
{
Val = 11D
},
new Color()
{
Theme = 1U
},
new FontName()
{
Val = "Calibri"
},
new FontFamilyNumbering()
{
Val = 2
},
new FontScheme()
{
Val = FontSchemeValues.Minor
}),
new Font(
new Underline(),
new FontSize()
{
Val = 11D
},
new Color()
{
Indexed = 12U
},
new FontName()
{
Val = "Calibri"
},
new FontFamilyNumbering()
{
Val = 2
})
)
{
Count = 2U
},
new Fills(
new Fill(
new PatternFill()
{
PatternType = PatternValues.None
}),
new Fill(
new PatternFill()
{
PatternType = PatternValues.Gray125
})
)
{
Count = 2U
},
new Borders(
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder())
)
{
Count = 1U
},
new CellStyleFormats(
new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
},
new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 0U
}
)
{
Count = 2U
},
new CellFormats(
new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
},
new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 0U, FormatId = 1U
}
)
{
Count = 2U
},
new CellStyles(
new CellStyle()
{
Name = "Hyperlink", FormatId = 1U, BuiltinId = 8U
},
new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
}
)
{
Count = 2U
},
new DifferentialFormats()
{
Count = 0U
},
new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
});
return(element);
}
protected virtual void InternalSetStyle(Stylesheet stylesheet, string name)
{
}
private Stylesheet GenerateStylesheet()
{
Stylesheet styleSheet = null;
Fills fills = new Fills(
new Fill(new PatternFill()
{
PatternType = PatternValues.None
}),
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue("#DF013A")
})
{
PatternType = PatternValues.Solid
}),
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue("#2EFE9A")
})
{
PatternType = PatternValues.Solid
}),
new Fill(new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue("#81BEF7")
})
{
PatternType = PatternValues.Solid
})
);
Borders borders = new Borders(
new Border(
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder())
);
Fonts font = new Fonts(
new Font()
{
Bold = new Bold()
},
new Font()
);
CellFormats cellFormats = new CellFormats(
new CellFormat
{
FillId = 0,
BorderId = 0,
ApplyFill = true,
FontId = 1
},
new CellFormat
{
Alignment = new Alignment()
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Left),
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Bottom),
WrapText = true
},
FillId = 0,
BorderId = 0,
ApplyFill = true,
FontId = 1
},
new CellFormat
{
Alignment = new Alignment()
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Center),
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Bottom),
WrapText = true
},
FillId = 0,
BorderId = 0,
ApplyFill = true,
FontId = 1
},
new CellFormat
{
Alignment = new Alignment()
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Right),
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center),
WrapText = true
},
FillId = 2,
BorderId = 0,
ApplyFill = true,
FontId = 0
},
new CellFormat
{
Alignment = new Alignment()
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Right),
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center),
WrapText = true
},
FillId = 1,
BorderId = 0,
ApplyFill = true,
FontId = 0
},
new CellFormat
{
Alignment = new Alignment()
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Right),
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center),
WrapText = true
},
FillId = 3,
BorderId = 0,
ApplyFill = true,
FontId = 0
}
);
styleSheet = new Stylesheet(font, fills, borders, cellFormats);
return(styleSheet);
}
/// <summary>
/// Получть стиль ячейки по его ID
/// </summary>
/// <param name="stylesheet">Таблица стилей</param>
/// <param name="formatIndex">ID формата ячейки</param>
/// <returns>Возвращает объект стиля ячейки</returns>
public static CellFormat CellFormat(this Stylesheet stylesheet, int formatIndex)
{
return(stylesheet.GetCellFormats().CellFormat(formatIndex));
}
public Tree(Stylesheet stylesheet, string style) : this(stylesheet.TreeStyles[style])
{
}
internal override string[] GetStyleNames(Stylesheet stylesheet)
{
return(stylesheet.SpinButtonStyles.Keys.ToArray());
}
protected override void SetStyleByName(Stylesheet stylesheet, string name)
{
ApplyTextBlockStyle(stylesheet.TextBlockStyles[name]);
}
private Stylesheet StylesheetFromFile(string path)
{
if (!Path.IsPathRooted(path))
{
path = Path.Combine(Path.GetDirectoryName(FilePath), path);
}
Stylesheet stylesheet;
if (_stylesheetCache.TryGetValue(path, out stylesheet))
{
return(stylesheet);
}
var data = File.ReadAllText(path);
var doc = XDocument.Parse(data);
var root = doc.Root;
var textureAtlases = new Dictionary <string, TextureRegionAtlas>();
var fonts = new Dictionary <string, SpriteFont>();
var designer = root.Element("Designer");
if (designer != null)
{
var folder = Path.GetDirectoryName(path);
foreach (var element in designer.Elements())
{
if (element.Name == "TextureAtlas")
{
var atlasPath = BuildPath(folder, element.Attribute("Atlas").Value);
var imagePath = BuildPath(folder, element.Attribute("Image").Value);
using (var stream = File.OpenRead(imagePath))
{
var texture = Texture2D.FromStream(GraphicsDevice, stream);
var atlasData = File.ReadAllText(atlasPath);
textureAtlases[Path.GetFileName(atlasPath)] = TextureRegionAtlas.FromXml(atlasData, texture);
}
}
else if (element.Name == "Font")
{
var id = element.Attribute("Id").Value;
var fontPath = BuildPath(folder, element.Attribute("File").Value);
var fontData = File.ReadAllText(fontPath);
fonts[id] = BMFontLoader.LoadText(fontData,
s =>
{
if (s.Contains("#"))
{
var parts = s.Split('#');
var region = textureAtlases[parts[0]][parts[1]];
return(new TextureWithOffset(region.Texture, region.Bounds.Location));
}
var imagePath = BuildPath(folder, s);
using (var stream = File.OpenRead(imagePath))
{
var texture = Texture2D.FromStream(GraphicsDevice, stream);
return(new TextureWithOffset(texture));
}
});
}
}
}
stylesheet = Stylesheet.LoadFromSource(data,
s =>
{
TextureRegion result;
foreach (var pair in textureAtlases)
{
if (pair.Value.Regions.TryGetValue(s, out result))
{
return(result);
}
}
throw new Exception(string.Format("Could not find texture region '{0}'", s));
},
s =>
{
SpriteFont result;
if (fonts.TryGetValue(s, out result))
{
return(result);
}
throw new Exception(string.Format("Could not find font '{0}'", s));
}
);
_stylesheetCache[path] = stylesheet;
return(stylesheet);
}
protected override void SetStyleByName(Stylesheet stylesheet, string name)
{
ApplyScrollPaneStyle(stylesheet.ScrollPaneStyles[name]);
}
internal override string[] GetStyleNames(Stylesheet stylesheet)
{
return(stylesheet.TextBlockStyles.Keys.ToArray());
}
/// <summary>
/// 出題按鍵點擊事件
/// </summary>
/// <returns>靜態頁面文件名</returns>
public FileInfo Compile()
{
// HTML模板存放路徑
string sourceFileName = Path.GetFullPath(ConfigurationUtil.HtmlTemplatePath);
// 靜態頁面作成后存放的路徑（文件名：日期時間形式）
string destFileName = Path.GetFullPath(ConfigurationUtil.GetKeyValue("HtmlWork")
+ string.Format(CultureInfo.CurrentCulture, "{0}.html", DateTime.Now.ToString("yyyyMMddHHmmssfff", CultureInfo.CurrentCulture)));
// 文件移動
File.Copy(sourceFileName, destFileName);
StringBuilder htmlTemplate = new StringBuilder();
// 讀取HTML模板內容
htmlTemplate.Append(File.ReadAllText(destFileName, Encoding.UTF8));
// 遍歷已選擇的題型
foreach (KeyValuePair <string, ConcurrentDictionary <SubstituteType, string> > d in _htmlMaps)
{
LogUtil.LogDebug(MessageUtil.GetMessage(() => MsgResources.I0016L, d.Key));
// 替換HTML模板中的預留內容（HTML、JS注入操作）
foreach (KeyValuePair <SubstituteType, string> m in d.Value)
{
LogUtil.LogDebug(MessageUtil.GetMessage(() => MsgResources.I0015L, m.Key));
switch (m.Key)
{
// 樣式庫引用注入
case SubstituteType.Stylesheet:
Stylesheet.AppendLine(m.Value);
break;
// 腳本引用注入
case SubstituteType.Script:
Script.AppendLine(m.Value);
break;
// 打印前設置事件注入
case SubstituteType.PrintSettingEvent:
PrintSettingEvent.AppendLine(m.Value);
break;
// 打印后設置事件注入
case SubstituteType.PrintAfterSettingEvent:
PrintAfterSettingEvent.AppendLine(m.Value);
break;
// 準備事件注入
case SubstituteType.ReadyEvent:
ReadyEvent.AppendLine(m.Value);
break;
// 交卷事件注入
case SubstituteType.TheirPapersEvent:
TictheirPapersEvent.AppendLine(m.Value);
break;
// 答題訂正事件注入
case SubstituteType.MakeCorrectionsEvent:
MakeCorrectionsEvent.AppendLine(m.Value);
break;
// 題型正文注入
case SubstituteType.Content:
Content.AppendLine(m.Value);
break;
default:
break;
}
}
}
// 樣式庫注入
htmlTemplate.Replace("<!--STYLESHEET-->", Stylesheet.ToString());
// 腳本注入
htmlTemplate.Replace("<!--SCRIPT-->", Script.ToString());
// 打印前設置事件注入
htmlTemplate.Replace("// PRINTSETTING", PrintSettingEvent.ToString());
// 打印后設置事件注入
htmlTemplate.Replace("// PRINTAFTERSETTING", PrintAfterSettingEvent.ToString());
// 題型準備事件注入
htmlTemplate.Replace("// READY", ReadyEvent.ToString());
// 題型訂正事件注入
htmlTemplate.Replace("// MAKECORRECTIONS", MakeCorrectionsEvent.ToString());
// 題型交卷事件注入
htmlTemplate.Replace("// TICTHEIRPAPERS", TictheirPapersEvent.ToString());
// 題型正文注入
htmlTemplate.Replace("<!--CONTENT-->", Content.Insert(0, IsEncryptScript).AppendLine().ToString());
LogUtil.LogDebug(MessageUtil.GetMessage(() => MsgResources.I0017L));
// 保存至靜態頁面
File.WriteAllText(destFileName, htmlTemplate.ToString(), Encoding.UTF8);
return(new FileInfo(destFileName));
}
/// <summary>
/// Import cell format
/// </summary>
/// <param name="sourceStyleSheet"></param>
/// <param name="srcFormat"></param>
/// <param name="targetStyleSheet"></param>
/// <returns></returns>
protected static uint ImportCellFormat(Stylesheet sourceStyleSheet, CellFormat srcFormat, Stylesheet targetStyleSheet)
{
var tgtCellFormats = targetStyleSheet.CellFormats ??
targetStyleSheet.AppendChild(new CellFormats());
var tgtCellFormat = tgtCellFormats.AppendChild(srcFormat.Clone() as CellFormat);
tgtCellFormats.Count ++;
// Import Style
if (srcFormat.FormatId != null)
{
var srcStyle = sourceStyleSheet.CellStyleFormats.ElementAt((int) srcFormat.FormatId.Value) as CellFormat;
if (srcStyle == null)
throw new InvalidOperationException(String.Format("Style {0} was not found in source document!",
srcFormat.FormatId));
// Style is complex, so just clone it
var tgtStyle = srcStyle.Clone() as CellFormat;
// Add style to target
if (targetStyleSheet.CellStyleFormats == null)
targetStyleSheet.CellStyleFormats = new CellStyleFormats();
targetStyleSheet.CellStyleFormats.AppendChild(tgtStyle);
tgtCellFormat.FormatId = targetStyleSheet.CellStyleFormats.Count - 1;
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtStyle, targetStyleSheet);
}
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtCellFormat, targetStyleSheet);
return tgtCellFormats.Count - 1;
}
public static Borders CreateBordersCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var borders = new Borders { Count = capacity };
stylesheet.Append(borders);
return borders;
}
public static Fonts CreateFontsCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var fonts = new Fonts { Count = capacity, KnownFonts = true };
stylesheet.Append(fonts);
return fonts;
}
protected override void SetStyleByName(Stylesheet stylesheet, string name)
{
ApplySpinButtonStyle(stylesheet.SpinButtonStyles[name]);
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName { Val = "Arial" };
var ftsz = new FontSize { Val = 11 };
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return ss;
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="workbookPart"></param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static string GetCellValue(Cell cell, WorkbookPart workbookPart, int type = 2)
{
//合并单元格不做处理
if (cell.CellValue == null)
{
return(string.Empty);
}
string cellInnerText = cell.CellValue.InnerXml;
//纯字符串
if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
{
//获取spreadsheetDocument中共享的数据
SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
//如果共享字符串表丢失，则说明出了问题。
if (!stringTable.Any())
{
return(string.Empty);
}
string text = stringTable.ElementAt(int.Parse(cellInnerText)).InnerText;
if (type == 2)
{
return(text.Trim());
}
else if (type == 3)
{
return(text.Replace(" ", ""));
}
else
{
return(text);
}
}
//bool类型
else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
{
return((cellInnerText != "0").ToString().ToUpper());
}
//数字格式代码（numFmtId）小于164是内置的：https://www.it1352.com/736329.html
else
{
//为空为数值
if (cell.StyleIndex == null)
{
return(cellInnerText);
}
Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
uint formatId = cellFormat.NumberFormatId.Value;
double doubleTime; //OLE 自动化日期值
DateTime dateTime; //yyyy/MM/dd HH:mm:ss
switch (formatId)
{
case 0: //常规
return(cellInnerText);
case 9: //百分比【0%】
case 10: //百分比【0.00%】
case 11: //科学计数【1.00E+02】
case 12: //分数【1/2】
return(cellInnerText);
case 14:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
//case 15:
//case 16:
case 17:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM"));
//case 18:
//case 19:
case 20:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("H:mm"));
case 21:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
case 22:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd HH:mm"));
//case 45:
//case 46:
case 47:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 58: //【中国】11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("MM/dd"));
case 176: //【中国】2020年11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 177: //【中国】11:22:00
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
default:
return(cellInnerText);
}
}
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElement4 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(openXmlUnknownElement4);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public NanoStyle()
{
var resCache = IoCManager.Resolve <IResourceCache>();
var notoSans12 = resCache.GetFont("/Nano/NotoSans/NotoSans-Regular.ttf", 12);
var notoSansBold16 = resCache.GetFont("/Nano/NotoSans/NotoSans-Bold.ttf", 16);
var animalSilence40 = resCache.GetFont("/Fonts/Animal Silence.otf", 40);
var textureCloseButton = resCache.GetTexture("/Nano/cross.svg.png");
var windowHeaderTex = resCache.GetTexture("/Nano/window_header.png");
var windowHeader = new StyleBoxTexture
{
Texture = windowHeaderTex,
PatchMarginBottom = 3,
ExpandMarginBottom = 3,
};
var windowBackgroundTex = resCache.GetTexture("/Nano/window_background.png");
var windowBackground = new StyleBoxTexture
{
Texture = windowBackgroundTex,
};
windowBackground.SetPatchMargin(StyleBox.Margin.Horizontal | StyleBox.Margin.Bottom, 2);
windowBackground.SetExpandMargin(StyleBox.Margin.Horizontal | StyleBox.Margin.Bottom, 2);
var buttonNormalTex = resCache.GetTexture("/Nano/button_normal.png");
var buttonNormal = new StyleBoxTexture
{
Texture = buttonNormalTex,
};
buttonNormal.SetPatchMargin(StyleBox.Margin.All, 2);
buttonNormal.SetContentMarginOverride(StyleBox.Margin.Left | StyleBox.Margin.Right, 4);
var buttonHoverTex = resCache.GetTexture("/Nano/button_hover.png");
var buttonHover = new StyleBoxTexture
{
Texture = buttonHoverTex,
};
buttonHover.SetPatchMargin(StyleBox.Margin.All, 2);
buttonHover.SetContentMarginOverride(StyleBox.Margin.Left | StyleBox.Margin.Right, 4);
var buttonPressedTex = resCache.GetTexture("/Nano/button_pressed.png");
var buttonPressed = new StyleBoxTexture
{
Texture = buttonPressedTex,
};
buttonPressed.SetPatchMargin(StyleBox.Margin.All, 2);
buttonPressed.SetContentMarginOverride(StyleBox.Margin.Left | StyleBox.Margin.Right, 4);
var buttonDisabledTex = resCache.GetTexture("/Nano/button_disabled.png");
var buttonDisabled = new StyleBoxTexture
{
Texture = buttonDisabledTex,
};
buttonDisabled.SetPatchMargin(StyleBox.Margin.All, 2);
buttonDisabled.SetContentMarginOverride(StyleBox.Margin.Left | StyleBox.Margin.Right, 4);
var lineEditTex = resCache.GetTexture("/Nano/lineedit.png");
var lineEdit = new StyleBoxTexture
{
Texture = lineEditTex,
};
lineEdit.SetPatchMargin(StyleBox.Margin.All, 3);
lineEdit.SetContentMarginOverride(StyleBox.Margin.Horizontal, 5);
Stylesheet = new Stylesheet(new[]
{
// Default font.
new StyleRule(
new SelectorElement(null, null, null, null),
new[]
{
new StyleProperty("font", notoSans12),
}),
// Window title.
new StyleRule(
new SelectorElement(typeof(Label), new[] { SS14Window.StyleClassWindowTitle }, null, null),
new[]
{
new StyleProperty(Label.StylePropertyFontColor, NanoGold),
new StyleProperty(Label.StylePropertyFont, notoSansBold16),
}),
// Window background.
new StyleRule(
new SelectorElement(null, new[] { SS14Window.StyleClassWindowPanel }, null, null),
new[]
{
new StyleProperty(Panel.StylePropertyPanel, windowBackground),
}),
// Window header.
new StyleRule(
new SelectorElement(typeof(Panel), new[] { SS14Window.StyleClassWindowHeader }, null, null),
new[]
{
new StyleProperty(Panel.StylePropertyPanel, windowHeader),
}),
// Window close button base texture.
new StyleRule(
new SelectorElement(typeof(TextureButton), new[] { SS14Window.StyleClassWindowCloseButton }, null,
null),
new[]
{
new StyleProperty(TextureButton.StylePropertyTexture, textureCloseButton),
new StyleProperty(Control.StylePropertyModulateSelf, Color.FromHex("#4B596A")),
}),
// Window close button hover.
new StyleRule(
new SelectorElement(typeof(TextureButton), new[] { SS14Window.StyleClassWindowCloseButton }, null,
TextureButton.StylePseudoClassHover),
new[]
{
new StyleProperty(Control.StylePropertyModulateSelf, Color.FromHex("#7F3636")),
}),
// Window close button pressed.
new StyleRule(
new SelectorElement(typeof(TextureButton), new[] { SS14Window.StyleClassWindowCloseButton }, null,
TextureButton.StylePseudoClassPressed),
new[]
{
new StyleProperty(Control.StylePropertyModulateSelf, Color.FromHex("#753131")),
}),
// Regular buttons!
new StyleRule(
new SelectorElement(typeof(Button), null, null, Button.StylePseudoClassNormal),
new[]
{
new StyleProperty(Button.StylePropertyStyleBox, buttonNormal),
}),
new StyleRule(
new SelectorElement(typeof(Button), null, null, Button.StylePseudoClassHover),
new[]
{
new StyleProperty(Button.StylePropertyStyleBox, buttonHover),
}),
new StyleRule(
new SelectorElement(typeof(Button), null, null, Button.StylePseudoClassPressed),
new[]
{
new StyleProperty(Button.StylePropertyStyleBox, buttonPressed),
}),
new StyleRule(
new SelectorElement(typeof(Button), null, null, Button.StylePseudoClassDisabled),
new[]
{
new StyleProperty(Button.StylePropertyStyleBox, buttonDisabled),
new StyleProperty("font-color", Color.FromHex("#E5E5E581")),
}),
// Main menu: Make those buttons bigger.
new StyleRule(
new SelectorChild(
new SelectorElement(null, null, "mainMenuVBox", null),
new SelectorElement(typeof(Button), null, null, null)),
new[]
{
new StyleProperty("font", animalSilence40),
}),
// Main menu: also make those buttons slightly more separated.
new StyleRule(new SelectorElement(typeof(BoxContainer), null, "mainMenuVBox", null),
new[]
{
new StyleProperty(BoxContainer.StylePropertySeparation, 2),
}),
// Fancy LineEdit
new StyleRule(new SelectorElement(typeof(LineEdit), null, null, null),
new[]
{
new StyleProperty(LineEdit.StylePropertyStyleBox, lineEdit),
}),
});
}
protected override void InternalSetStyle(Stylesheet stylesheet, string name)
{
ApplyImageTextButtonStyle(new ImageTextButtonStyle(stylesheet.ButtonStyles[name]));
}
public HorizontalMenu(Stylesheet stylesheet, string style) : this(stylesheet.HorizontalMenuStyles[style])
{
}
internal override string[] GetStyleNames(Stylesheet stylesheet)
{
return(stylesheet.ScrollPaneStyles.Keys.ToArray());
}
/// <summary>
/// Import number, font, fill, border etc for cell format
/// </summary>
/// <param name="sourceStyleSheet"></param>
/// <param name="format"></param>
/// <param name="targetStyleSheet"></param>
protected static void ImportCellFormatDetails(Stylesheet sourceStyleSheet, CellFormat format, Stylesheet targetStyleSheet)
{
// Merge number format
#region NumberFormat
if (format.NumberFormatId != null && format.NumberFormatId.Value != 0)
{
var numFmt =
sourceStyleSheet.NumberingFormats.Elements<NumberingFormat>()
.FirstOrDefault(a => a.NumberFormatId == format.NumberFormatId);
if (numFmt != null)
{
// Try to find same number format in target
if (targetStyleSheet.NumberingFormats == null)
targetStyleSheet.NumberingFormats = new NumberingFormats();
var existFmt =
targetStyleSheet.NumberingFormats.Elements<NumberingFormat>()
.FirstOrDefault(a => a.FormatCode == numFmt.FormatCode);
if (existFmt == null)
{
// Not found, add one
var newNumId = targetStyleSheet.NumberingFormats.Any()
? targetStyleSheet.NumberingFormats.Elements<NumberingFormat>()
.Max(a => a.NumberFormatId).Value + 1
: 1;
targetStyleSheet.NumberingFormats.AppendChild(new NumberingFormat
{
NumberFormatId = newNumId,
FormatCode = numFmt.FormatCode
});
format.NumberFormatId = newNumId;
targetStyleSheet.NumberingFormats.Count ++;
}
else
{
// Found, use exist one
format.NumberFormatId = existFmt.NumberFormatId;
}
}
}
#endregion
// Merge font format
#region Font
if (format.FontId != null)
{
var srcFont = sourceStyleSheet.Fonts.ElementAt((int)format.FontId.Value);
if (srcFont == null)
throw new InvalidOperationException(String.Format("Font {0} was not found in source document!",
format.FontId));
// Since font is complex, we just clone it
var tgtFont = srcFont.Clone() as Font;
// Add font to target stylesheet
if (targetStyleSheet.Fonts == null)
targetStyleSheet.Fonts = new Fonts();
targetStyleSheet.Fonts.AppendChild(tgtFont);
targetStyleSheet.Fonts.Count ++;
format.FontId = targetStyleSheet.Fonts.Count - 1;
}
#endregion
// Merge fill
#region Fill
if (format.FillId != null)
{
var srcFill = sourceStyleSheet.Fills.ElementAt((int)format.FillId.Value);
if (srcFill == null)
throw new InvalidOperationException(String.Format("Fill {0} was not found in source document!",
format.FillId));
// Since fill is complex, we just clone it
var tgtFill = srcFill.Clone() as Fill;
// Add fill to target stylesheet
if (targetStyleSheet.Fills == null)
targetStyleSheet.Fills = new Fills();
targetStyleSheet.Fills.AppendChild(tgtFill);
targetStyleSheet.Fills.Count ++;
format.FillId = targetStyleSheet.Fills.Count - 1;
}
#endregion
// Merge border
#region Border
if (format.BorderId != null)
{
var srcBorder = sourceStyleSheet.Borders.ElementAt((int)format.BorderId.Value);
if (srcBorder == null)
throw new InvalidOperationException(String.Format("Border {0} was not found in source document!",
format.BorderId));
// Since border is complex, we just clone it
var tgtBorder = srcBorder.Clone() as Border;
// Add border to target stylesheet
if (targetStyleSheet.Borders == null)
targetStyleSheet.Borders = new Borders();
targetStyleSheet.Borders.AppendChild(tgtBorder);
targetStyleSheet.Borders.Count ++;
format.BorderId = targetStyleSheet.Borders.Count - 1;
}
#endregion
}
public HorizontalMenu(Stylesheet stylesheet) : this(stylesheet.HorizontalMenuStyle)
{
}
public static Fills CreateFillsCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var fills = new Fills { Count = capacity };
stylesheet.Append(fills);
return fills;
}
protected override void SetStyleByName(Stylesheet stylesheet, string name)
{
ApplyMenuStyle(stylesheet.HorizontalMenuStyles[name]);
}
public static Stylesheet CreateStylesheet(WorkbookStylesPart workbookStylesPart)
{
var stylesheet = new Stylesheet { MCAttributes = new MarkupCompatibilityAttributes { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
workbookStylesPart.Stylesheet = stylesheet;
return stylesheet;
}
internal override string[] GetStyleNames(Stylesheet stylesheet)
{
return(stylesheet.HorizontalMenuStyles.Keys.ToArray());
}
/// <summary>
/// Add Datatuples to a Excel Template file
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="dataTuples"> Datatuples to add</param>
/// <param name="filePath">Path of the excel template file</param>
/// <param name="dataStructureId">Id of datastructure</param>
/// <returns>List of Errors or null</returns>
public List<Error> AddDataTuplesToTemplate(DatasetManager datasetManager, List<long> dataTuplesIds, string filePath, long dataStructureId )
{
if (File.Exists(filePath))
{
//Stream file = Open(filePath);
//_dataTuples = dataTuples;
// loading datastructure
dataStructure = GetDataStructure(dataStructureId);
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// get all the defined area
List<DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart);
// select data area
this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault();
// Select variable area
this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();
// Get intergers for reading data
startColumn = getColumnNumber(this.areaOfData.StartColumn);
endColumn = getColumnNumber(this.areaOfData.EndColumn);
numOfColumns = (endColumn - startColumn) + 1;
offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1;
// gerneat Style for cell types
generateStyle(spreadsheetDocument);
// get styleSheet
stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData);
// Get VarioableIndentifiers
this.VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow);
AddRows(worksheetPart, this.areaOfData.StartRow, this.areaOfData.EndRow, dataTuplesIds, datasetManager);
// set data area
foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>())
{
if (name.Name == "Data")
{
string[] tempArr = name.InnerText.Split('$');
string temp = "";
//$A$10:$C$15
tempArr[tempArr.Count() - 1] = numOfDataRows.ToString();
foreach (string t in tempArr)
{
if (t == tempArr.First())
{
temp = temp + t;
}
else
{
temp = temp + "$" + t;
}
}
name.Text = temp;
}
}
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
return ErrorMessages;
}
private Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName {
Val = "Arial"
};
var ftsz = new FontSize {
Val = 11
};
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font {
FontName = ftn, FontSize = ftsz
};
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
var nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.00"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "@"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle {
Name = "Normal", FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return(ss);
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string worksheetName = dt.TableName;
// Create worksheet part, and add it to the sheets collection in workbook
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
sheets.Append(sheet);
// Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders() { Count = (UInt32Value)1U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)3U };
DifferentialFormat differentialFormat1 = new DifferentialFormat();
Font font2 = new Font();
Condense condense1 = new Condense() { Val = false };
Extend extend1 = new Extend() { Val = false };
Color color2 = new Color() { Rgb = "FF9C0006" };
font2.Append(condense1);
font2.Append(extend1);
font2.Append(color2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill();
BackgroundColor backgroundColor1 = new BackgroundColor() { Rgb = "FFFFC7CE" };
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
differentialFormat1.Append(font2);
differentialFormat1.Append(fill3);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
Font font3 = new Font();
Condense condense2 = new Condense() { Val = false };
Extend extend2 = new Extend() { Val = false };
Color color3 = new Color() { Rgb = "FF006100" };
font3.Append(condense2);
font3.Append(extend2);
font3.Append(color3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill();
BackgroundColor backgroundColor2 = new BackgroundColor() { Rgb = "FFC6EFCE" };
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
differentialFormat2.Append(font3);
differentialFormat2.Append(fill4);
DifferentialFormat differentialFormat3 = new DifferentialFormat();
Font font4 = new Font();
Condense condense3 = new Condense() { Val = false };
Extend extend3 = new Extend() { Val = false };
Color color4 = new Color() { Rgb = "FF006100" };
font4.Append(condense3);
font4.Append(extend3);
font4.Append(color4);
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill();
BackgroundColor backgroundColor3 = new BackgroundColor() { Rgb = "FFC6EFCE" };
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
differentialFormat3.Append(font4);
differentialFormat3.Append(fill5);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
differentialFormats1.Append(differentialFormat3);
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)2U };
NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)44U, FormatCode = "_ \"$\"\\ * #,##0.00_ ;_ \"$\"\\ * \\-#,##0.00_ ;_ \"$\"\\ * \"-\"??_ ;_ @_ " };
NumberingFormat numberingFormat2 = new NumberingFormat() { NumberFormatId = (UInt32Value)165U, FormatCode = "mmmm/yyyy" };
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
Fonts fonts1 = new Fonts() { Count = (UInt32Value)10U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 10D };
FontName fontName1 = new FontName() { Val = "Arial" };
font1.Append(fontSize1);
font1.Append(fontName1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() { Val = 10D };
FontName fontName2 = new FontName() { Val = "Arial" };
font2.Append(fontSize2);
font2.Append(fontName2);
Font font3 = new Font();
Bold bold1 = new Bold();
FontSize fontSize3 = new FontSize() { Val = 10D };
FontName fontName3 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
font3.Append(bold1);
font3.Append(fontSize3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering1);
Font font4 = new Font();
Bold bold2 = new Bold();
FontSize fontSize4 = new FontSize() { Val = 10D };
Color color1 = new Color() { Indexed = (UInt32Value)9U };
FontName fontName4 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
font4.Append(bold2);
font4.Append(fontSize4);
font4.Append(color1);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering2);
Font font5 = new Font();
FontSize fontSize5 = new FontSize() { Val = 8D };
FontName fontName5 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 };
font5.Append(fontSize5);
font5.Append(fontName5);
font5.Append(fontFamilyNumbering3);
Font font6 = new Font();
Bold bold3 = new Bold();
FontSize fontSize6 = new FontSize() { Val = 8D };
FontName fontName6 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering() { Val = 2 };
font6.Append(bold3);
font6.Append(fontSize6);
font6.Append(fontName6);
font6.Append(fontFamilyNumbering4);
Font font7 = new Font();
Bold bold4 = new Bold();
FontSize fontSize7 = new FontSize() { Val = 15D };
FontName fontName7 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering5 = new FontFamilyNumbering() { Val = 2 };
font7.Append(bold4);
font7.Append(fontSize7);
font7.Append(fontName7);
font7.Append(fontFamilyNumbering5);
Font font8 = new Font();
FontSize fontSize8 = new FontSize() { Val = 10D };
FontName fontName8 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering6 = new FontFamilyNumbering() { Val = 2 };
font8.Append(fontSize8);
font8.Append(fontName8);
font8.Append(fontFamilyNumbering6);
Font font9 = new Font();
FontSize fontSize9 = new FontSize() { Val = 8D };
FontName fontName9 = new FontName() { Val = "Arial" };
font9.Append(fontSize9);
font9.Append(fontName9);
Font font10 = new Font();
Bold bold5 = new Bold();
FontSize fontSize10 = new FontSize() { Val = 10D };
Color color2 = new Color() { Indexed = (UInt32Value)12U };
FontName fontName10 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering7 = new FontFamilyNumbering() { Val = 2 };
font10.Append(bold5);
font10.Append(fontSize10);
font10.Append(color2);
font10.Append(fontName10);
font10.Append(fontFamilyNumbering7);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fonts1.Append(font6);
fonts1.Append(font7);
fonts1.Append(font8);
fonts1.Append(font9);
fonts1.Append(font10);
Fills fills1 = new Fills() { Count = (UInt32Value)6U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Indexed = (UInt32Value)23U };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Indexed = (UInt32Value)9U };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor3 = new ForegroundColor() { Indexed = (UInt32Value)10U };
BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)26U };
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
Fill fill6 = new Fill();
PatternFill patternFill6 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor4 = new ForegroundColor() { Indexed = (UInt32Value)10U };
BackgroundColor backgroundColor4 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill6.Append(foregroundColor4);
patternFill6.Append(backgroundColor4);
fill6.Append(patternFill6);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
fills1.Append(fill6);
Borders borders1 = new Borders() { Count = (UInt32Value)11U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
Border border3 = new Border();
LeftBorder leftBorder3 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color6 = new Color() { Indexed = (UInt32Value)64U };
leftBorder3.Append(color6);
RightBorder rightBorder3 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color7 = new Color() { Indexed = (UInt32Value)64U };
rightBorder3.Append(color7);
TopBorder topBorder3 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color8 = new Color() { Indexed = (UInt32Value)64U };
topBorder3.Append(color8);
BottomBorder bottomBorder3 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color9 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder3.Append(color9);
DiagonalBorder diagonalBorder3 = new DiagonalBorder();
border3.Append(leftBorder3);
border3.Append(rightBorder3);
border3.Append(topBorder3);
border3.Append(bottomBorder3);
border3.Append(diagonalBorder3);
Border border4 = new Border();
LeftBorder leftBorder4 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color10 = new Color() { Indexed = (UInt32Value)64U };
leftBorder4.Append(color10);
RightBorder rightBorder4 = new RightBorder();
TopBorder topBorder4 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color11 = new Color() { Indexed = (UInt32Value)64U };
topBorder4.Append(color11);
BottomBorder bottomBorder4 = new BottomBorder();
DiagonalBorder diagonalBorder4 = new DiagonalBorder();
border4.Append(leftBorder4);
border4.Append(rightBorder4);
border4.Append(topBorder4);
border4.Append(bottomBorder4);
border4.Append(diagonalBorder4);
Border border5 = new Border();
LeftBorder leftBorder5 = new LeftBorder();
RightBorder rightBorder5 = new RightBorder();
TopBorder topBorder5 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color12 = new Color() { Indexed = (UInt32Value)64U };
topBorder5.Append(color12);
BottomBorder bottomBorder5 = new BottomBorder();
DiagonalBorder diagonalBorder5 = new DiagonalBorder();
border5.Append(leftBorder5);
border5.Append(rightBorder5);
border5.Append(topBorder5);
border5.Append(bottomBorder5);
border5.Append(diagonalBorder5);
Border border6 = new Border();
LeftBorder leftBorder6 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color13 = new Color() { Indexed = (UInt32Value)64U };
leftBorder6.Append(color13);
RightBorder rightBorder6 = new RightBorder();
TopBorder topBorder6 = new TopBorder();
BottomBorder bottomBorder6 = new BottomBorder();
DiagonalBorder diagonalBorder6 = new DiagonalBorder();
border6.Append(leftBorder6);
border6.Append(rightBorder6);
border6.Append(topBorder6);
border6.Append(bottomBorder6);
border6.Append(diagonalBorder6);
Border border7 = new Border();
LeftBorder leftBorder7 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color14 = new Color() { Indexed = (UInt32Value)64U };
leftBorder7.Append(color14);
RightBorder rightBorder7 = new RightBorder();
TopBorder topBorder7 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color15 = new Color() { Indexed = (UInt32Value)64U };
topBorder7.Append(color15);
BottomBorder bottomBorder7 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color16 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder7.Append(color16);
DiagonalBorder diagonalBorder7 = new DiagonalBorder();
border7.Append(leftBorder7);
border7.Append(rightBorder7);
border7.Append(topBorder7);
border7.Append(bottomBorder7);
border7.Append(diagonalBorder7);
Border border8 = new Border();
LeftBorder leftBorder8 = new LeftBorder();
RightBorder rightBorder8 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color17 = new Color() { Indexed = (UInt32Value)64U };
rightBorder8.Append(color17);
TopBorder topBorder8 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color18 = new Color() { Indexed = (UInt32Value)64U };
topBorder8.Append(color18);
BottomBorder bottomBorder8 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color19 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder8.Append(color19);
DiagonalBorder diagonalBorder8 = new DiagonalBorder();
border8.Append(leftBorder8);
border8.Append(rightBorder8);
border8.Append(topBorder8);
border8.Append(bottomBorder8);
border8.Append(diagonalBorder8);
Border border9 = new Border();
LeftBorder leftBorder9 = new LeftBorder();
RightBorder rightBorder9 = new RightBorder();
TopBorder topBorder9 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color20 = new Color() { Indexed = (UInt32Value)64U };
topBorder9.Append(color20);
BottomBorder bottomBorder9 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color21 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder9.Append(color21);
DiagonalBorder diagonalBorder9 = new DiagonalBorder();
border9.Append(leftBorder9);
border9.Append(rightBorder9);
border9.Append(topBorder9);
border9.Append(bottomBorder9);
border9.Append(diagonalBorder9);
Border border10 = new Border();
LeftBorder leftBorder10 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color22 = new Color() { Indexed = (UInt32Value)64U };
leftBorder10.Append(color22);
RightBorder rightBorder10 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color23 = new Color() { Indexed = (UInt32Value)64U };
rightBorder10.Append(color23);
TopBorder topBorder10 = new TopBorder();
BottomBorder bottomBorder10 = new BottomBorder();
DiagonalBorder diagonalBorder10 = new DiagonalBorder();
border10.Append(leftBorder10);
border10.Append(rightBorder10);
border10.Append(topBorder10);
border10.Append(bottomBorder10);
border10.Append(diagonalBorder10);
Border border11 = new Border();
LeftBorder leftBorder11 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color24 = new Color() { Indexed = (UInt32Value)64U };
leftBorder11.Append(color24);
RightBorder rightBorder11 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color25 = new Color() { Indexed = (UInt32Value)64U };
rightBorder11.Append(color25);
TopBorder topBorder11 = new TopBorder();
BottomBorder bottomBorder11 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color26 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder11.Append(color26);
DiagonalBorder diagonalBorder11 = new DiagonalBorder();
border11.Append(leftBorder11);
border11.Append(rightBorder11);
border11.Append(topBorder11);
border11.Append(bottomBorder11);
border11.Append(diagonalBorder11);
borders1.Append(border1);
borders1.Append(border2);
borders1.Append(border3);
borders1.Append(border4);
borders1.Append(border5);
borders1.Append(border6);
borders1.Append(border7);
borders1.Append(border8);
borders1.Append(border9);
borders1.Append(border10);
borders1.Append(border11);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)2U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)44U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyFont = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false };
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)64U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat10 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment1 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat10.Append(alignment1);
CellFormat cellFormat11 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment2 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat11.Append(alignment2);
CellFormat cellFormat12 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment3 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat12.Append(alignment3);
CellFormat cellFormat13 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)3U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat14 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat15 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)5U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat16 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment4 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left };
cellFormat16.Append(alignment4);
CellFormat cellFormat17 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment5 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat17.Append(alignment5);
CellFormat cellFormat18 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)6U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat19 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment6 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat19.Append(alignment6);
CellFormat cellFormat20 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)5U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat21 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat22 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)7U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)6U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment7 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat22.Append(alignment7);
CellFormat cellFormat23 = new CellFormat() { NumberFormatId = (UInt32Value)1U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)7U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment8 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left };
cellFormat23.Append(alignment8);
CellFormat cellFormat24 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)8U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat25 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment9 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat25.Append(alignment9);
CellFormat cellFormat26 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)10U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment10 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat26.Append(alignment10);
CellFormat cellFormat27 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)10U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat28 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment11 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat28.Append(alignment11);
CellFormat cellFormat29 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment12 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat29.Append(alignment12);
CellFormat cellFormat30 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat31 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment13 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat31.Append(alignment13);
CellFormat cellFormat32 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment14 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat32.Append(alignment14);
CellFormat cellFormat33 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat34 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment15 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat34.Append(alignment15);
CellFormat cellFormat35 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment16 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat35.Append(alignment16);
CellFormat cellFormat36 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)9U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment17 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat36.Append(alignment17);
CellFormat cellFormat37 = new CellFormat() { NumberFormatId = (UInt32Value)165U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment18 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat37.Append(alignment18);
CellFormat cellFormat38 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment19 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat38.Append(alignment19);
CellFormat cellFormat39 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)5U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment20 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat39.Append(alignment20);
CellFormat cellFormat40 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment21 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat40.Append(alignment21);
CellFormat cellFormat41 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat42 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat43 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment22 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true };
cellFormat43.Append(alignment22);
CellFormat cellFormat44 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat45 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat46 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat47 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat48 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)7U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat49 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment23 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat49.Append(alignment23);
CellFormat cellFormat50 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment24 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat50.Append(alignment24);
CellFormat cellFormat51 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment25 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat51.Append(alignment25);
CellFormat cellFormat52 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment26 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat52.Append(alignment26);
CellFormat cellFormat53 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat54 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true };
CellFormat cellFormat55 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat56 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat57 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat58 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)8U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat59 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment27 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat59.Append(alignment27);
CellFormat cellFormat60 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment28 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat60.Append(alignment28);
CellFormat cellFormat61 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment29 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat61.Append(alignment29);
CellFormat cellFormat62 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment30 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat62.Append(alignment30);
CellFormat cellFormat63 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment31 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat63.Append(alignment31);
CellFormat cellFormat64 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true };
CellFormat cellFormat65 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat66 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment32 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true };
cellFormat66.Append(alignment32);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
cellFormats1.Append(cellFormat18);
cellFormats1.Append(cellFormat19);
cellFormats1.Append(cellFormat20);
cellFormats1.Append(cellFormat21);
cellFormats1.Append(cellFormat22);
cellFormats1.Append(cellFormat23);
cellFormats1.Append(cellFormat24);
cellFormats1.Append(cellFormat25);
cellFormats1.Append(cellFormat26);
cellFormats1.Append(cellFormat27);
cellFormats1.Append(cellFormat28);
cellFormats1.Append(cellFormat29);
cellFormats1.Append(cellFormat30);
cellFormats1.Append(cellFormat31);
cellFormats1.Append(cellFormat32);
cellFormats1.Append(cellFormat33);
cellFormats1.Append(cellFormat34);
cellFormats1.Append(cellFormat35);
cellFormats1.Append(cellFormat36);
cellFormats1.Append(cellFormat37);
cellFormats1.Append(cellFormat38);
cellFormats1.Append(cellFormat39);
cellFormats1.Append(cellFormat40);
cellFormats1.Append(cellFormat41);
cellFormats1.Append(cellFormat42);
cellFormats1.Append(cellFormat43);
cellFormats1.Append(cellFormat44);
cellFormats1.Append(cellFormat45);
cellFormats1.Append(cellFormat46);
cellFormats1.Append(cellFormat47);
cellFormats1.Append(cellFormat48);
cellFormats1.Append(cellFormat49);
cellFormats1.Append(cellFormat50);
cellFormats1.Append(cellFormat51);
cellFormats1.Append(cellFormat52);
cellFormats1.Append(cellFormat53);
cellFormats1.Append(cellFormat54);
cellFormats1.Append(cellFormat55);
cellFormats1.Append(cellFormat56);
cellFormats1.Append(cellFormat57);
cellFormats1.Append(cellFormat58);
cellFormats1.Append(cellFormat59);
cellFormats1.Append(cellFormat60);
cellFormats1.Append(cellFormat61);
cellFormats1.Append(cellFormat62);
cellFormats1.Append(cellFormat63);
cellFormats1.Append(cellFormat64);
cellFormats1.Append(cellFormat65);
cellFormats1.Append(cellFormat66);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)2U };
CellStyle cellStyle1 = new CellStyle() { Name = "Moneda", FormatId = (UInt32Value)1U, BuiltinId = (UInt32Value)4U };
CellStyle cellStyle2 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
cellStyles1.Append(cellStyle2);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public static UInt32Value CreateFill(Stylesheet styleSheet, System.Drawing.Color fillColor)
{
PatternFill patternFill =
new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
});
patternFill.PatternType = fillColor ==
System.Drawing.Color.White ? PatternValues.None : PatternValues.LightDown;
Fill fill = new Fill(patternFill);
styleSheet.Fills.Append(fill);
UInt32Value result = styleSheet.Fills.Count;
styleSheet.Fills.Count++;
return result;
}
