private static void PopulateNewDocument(ExcelOpenXmlDocument excel)
{
// create the workbook
excel.Document.AddWorkbookPart();
AddStylesParts(excel);
excel.Document.WorkbookPart.Workbook = new Workbook(); // create the worksheet
excel.Document.WorkbookPart.AddNewPart <WorksheetPart>();
excel.Document.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
// create sheet data
excel.Document.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
// save worksheet
excel.Document.WorkbookPart.WorksheetParts.First().Worksheet.Save();
// create the worksheet to workbook relation
excel.Document.WorkbookPart.Workbook.AppendChild(new Sheets());
#region Not Needed Anymore (Only use if want to create default sheet)
//excel.Document.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
//{
// Id = excel.Document.WorkbookPart.GetIdOfPart(excel.Document.WorkbookPart.WorksheetParts.First()),
// SheetId = 1,
// Name = firstSheetName
//});
#endregion Not Needed Anymore (Only use if want to create default sheet)
excel.Document.WorkbookPart.Workbook.Save();
}
/// <summary>
/// Creates a new instance based on the specified System.IO.Stream.
/// </summary>
/// <param name="stream">The System.IO.Stream to open the spreadsheet from.</param>
/// <returns>New instance of ExcelOpenXmlDocument class.</returns>
public static ExcelOpenXmlDocument Load(Stream stream)
{
var excel = new ExcelOpenXmlDocument
{
Document = SpreadsheetDocument.Open(stream, true)
};
excel.Intialize();
return(excel);
}
/// <summary>
/// Creates a new instance based on the specified existing spreadsheet file.
/// </summary>
/// <param name="fileName">The location of the existing spreadsheet document.</param>
/// <returns>New instance of ExcelOpenXmlDocument class.</returns>
public static ExcelOpenXmlDocument Load(string filePath)
{
var excel = new ExcelOpenXmlDocument
{
Document = SpreadsheetDocument.Open(filePath, true)
};
excel.Intialize();
return(excel);
}
/// <summary>
/// <para>Creates a new spreadsheet in the specified System.IO.Stream and returns a new instance</para>
/// <para>of this class.</para>
/// </summary>
/// <param name="stream">The System.IO.Stream to create the spreadsheet in.</param>
/// <returns>New instance of ExcelOpenXmlDocument class.</returns>
public static ExcelOpenXmlDocument Create(Stream stream)
{
var excel = new ExcelOpenXmlDocument();
excel.Document = SpreadsheetDocument.Create(
stream,
SpreadsheetDocumentType.Workbook);
PopulateNewDocument(excel);
excel.Intialize();
return(excel);
}
/// <summary>
/// Creates a new spreadsheet based on the specified spreadsheet template.
/// </summary>
/// <param name="filePath">The location of the new spreadsheet document.</param>
/// <param name="templateFilePath">The location to the template spreadsheet.</param>
/// /// <returns>New instance of ExcelOpenXmlDocument class.</returns>
public static ExcelOpenXmlDocument Create(string filePath, string templateFilePath)
{
if (!File.Exists(templateFilePath))
{
throw new FileNotFoundException("Could not find specified Excel template.");
}
File.Copy(templateFilePath, filePath);
var excel = new ExcelOpenXmlDocument
{
Document = SpreadsheetDocument.Open(filePath, true)
};
excel.Intialize();
return(excel);
}
private static void AddStylesParts(ExcelOpenXmlDocument excel)
{
var part = excel.Document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
new Stylesheet(
new Fonts(
new Font(
new FontSize {
Val = 11D
},
new Color {
Theme = (UInt32Value)1U
},
new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
},
new FontScheme {
Val = FontSchemeValues.Minor
})
)
{
Count = (UInt32Value)1U
},
new Fills(
new Fill(
new PatternFill {
PatternType = PatternValues.None
}),
new Fill(
new PatternFill {
PatternType = PatternValues.Gray125
})
)
{
Count = (UInt32Value)2U
},
new Borders(
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder())
)
{
Count = (UInt32Value)1U
},
new CellStyleFormats(
new CellFormat {
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
}
)
{
Count = (UInt32Value)1U
},
new CellFormats(
new CellFormat {
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
}
)
{
Count = (UInt32Value)1U
},
new CellStyles(
new CellStyle {
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
}
)
{
Count = (UInt32Value)1U
},
new DifferentialFormats {
Count = (UInt32Value)0U
},
new TableStyles {
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
}
).Save(part);
}
