/// <summary>
/// Constructor with parameters.
/// </summary>
/// <param name="dir">Directory in which to save the file.</param>
/// <param name="filename">Filename without extension.</param>
/// <param name="title">Title to display in the spreadsheet.</param>
/// <param name="worksheetName">Name of the worksheet.</param>
public ExcelWriter(string dir, string filename, string title, string worksheetName)
{
// check for null or empty filename, title or worksheet name
if (filename == null || filename.Equals(""))
{
throw new ArgumentNullException("Cannot have a spreadsheet without a filename.");
}
if (worksheetName == null || worksheetName.Equals(""))
{
throw new ArgumentNullException("Cannot have a worksheet without a name.");
}
if (title == null || title.Equals(""))
{
throw new ArgumentNullException("Cannot have an empty title.");
}
// create a new package
this._dir = dir;
this._filename = filename;
this._title = title;
this._pck = new ExcelPackage(new System.IO.FileInfo(_filename + ".xlsx"));
// create a worksheet
_ws = _pck.Workbook.Worksheets.Add(worksheetName);
// by default, the header row is 7
_headerRow = 7;
// add named styles
// metadata
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml namedStyleMeta = _pck.Workbook.Styles.CreateNamedStyle(META_STYLE);
namedStyleMeta.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
System.Drawing.Color metaColour = System.Drawing.Color.MediumSeaGreen;
namedStyleMeta.Style.Fill.BackgroundColor.SetColor(metaColour);
// header
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml namedStyleHeader = _pck.Workbook.Styles.CreateNamedStyle(HEADER_STYLE);
namedStyleHeader.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
System.Drawing.Color headerColour = System.Drawing.Color.MediumSlateBlue;
namedStyleHeader.Style.Fill.BackgroundColor.SetColor(headerColour);
// even data row
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml namedStyleEvenDataRow = _pck.Workbook.Styles.CreateNamedStyle(EVEN_DATA_ROW_STYLE);
namedStyleEvenDataRow.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
System.Drawing.Color evenDataRowColour = System.Drawing.Color.LightSkyBlue;
namedStyleEvenDataRow.Style.Fill.BackgroundColor.SetColor(evenDataRowColour);
// odd data row
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml namedStyleOddDataRow = _pck.Workbook.Styles.CreateNamedStyle(ODD_DATA_ROW_STYLE);
namedStyleOddDataRow.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
System.Drawing.Color oddDataRowColour = System.Drawing.Color.PaleTurquoise;
namedStyleOddDataRow.Style.Fill.BackgroundColor.SetColor(oddDataRowColour);
}
//public string fontName { get; set; } = "Cambria"; //"Times New Roman";
//public System.Drawing.Color columnCaption = System.Drawing.Color.SteelBlue;
//public System.Drawing.Color extraEven = System.Drawing.Color.LightSlateGray;
//public System.Drawing.Color extraEvenOther = System.Drawing.Color.LightSteelBlue;
//public System.Drawing.Color extraOdd = System.Drawing.Color.SlateGray;
//public System.Drawing.Color dataOdd = System.Drawing.Color.WhiteSmoke;
//public System.Drawing.Color dataEven = System.Drawing.Color.Snow;
public void DefineStyles(ExcelWorksheet ws)
{
if (ws.Workbook.Styles.NamedStyles.Count() > 0)
{
return;
}
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.data);
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.mergedHeaderTitle); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.mergedHeaderTitle));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.mergedHeaderInfo); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.mergedHeaderInfo));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.columnCaption); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.columnCaption));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.columnDescription); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.columnDescription));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.columnInformation); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.columnInformation));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.mergedCategoryHeader); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.mergedCategoryHeader));
style = ws.Workbook.Styles.MakeStyle(styleSet, DataRowInReportTypeEnum.mergedFooterInfo); //.CreateNamedStyle(nameof(DataRowInReportTypeEnum.mergedFooterInfo));
}
/// <summary>
/// Converts a Armp into a Excel binary.
/// </summary>
/// <param name="source">Input format.</param>
/// <returns>The xlsx format.</returns>
/// <exception cref="ArgumentNullException">Thrown if source is null.</exception>
public BinaryFormat Convert(ArmpTable source)
{
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage();
OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml namedStyle = package.Workbook.Styles.CreateNamedStyle("HyperLink");
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
TableToSheet(source, "Main", package);
byte[] data = package.GetAsByteArray();
DataStream stream = DataStreamFactory.FromArray(data, 0, data.Length);
return(new BinaryFormat(stream));
}
