/// <summary>
/// Create a new Cell of the given <see cref="CellType"/>, with the given value and format. For some common formats, see <see cref="BuiltInCellFormat"/>.
/// You can also implicitly create a cell from a string or number.
/// </summary>
/// <param name="type"> </param>
/// <param name="value"> </param>
/// <param name="format"> </param>
public Cell(CellType type, object value, string format)
{
_xlsxCellStyle = new XlsxCellStyle();
_xlsxCellStyle.Format = format;
Value = value;
CellType = type;
}
/// <summary>
/// Create a new Cell of the given <see cref="CellType"/>, with the given value and format. For some common formats, see <see cref="BuiltInCellFormat"/>.
/// You can also implicitly create a cell from a string or number.
/// </summary>
/// <param name="type"> </param>
/// <param name="value"> </param>
/// <param name="format"> </param>
public Cell(CellType type, object value, string format)
{
_xlsxCellStyle = new XlsxCellStyle();
_xlsxCellStyle.Format = format;
Value = value;
CellType = type;
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Source sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Destination == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (string.IsNullOrEmpty(Destination.WorkSheet))
{
return(InsertResult.CreateErroResult(
"Destination sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (SourceRange == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (HeaderStyle == null)
{
HeaderStyle = XlsxCellStyle.Default;
}
if (ValueStyle == null)
{
ValueStyle = XlsxCellStyle.Default;
}
return(InsertImpl(context, input, SheetName, SourceRange, Destination, HeaderStyle, ValueStyle));
}
/// <summary>
/// Create a new Cell of the given <see cref="CellType"/>, with the given value and format. For some common formats, see <see cref="BuiltInCellFormat"/>.
/// You can also implicitly create a cell from a string or number.
/// </summary>
/// <param name="type"> </param>
/// <param name="value"> </param>
/// <param name="format"> </param>
public Cell(CellType type, object value, string format)
{
XlsxCellStyle = new XlsxCellStyle
{
Format = format
};
Value = value;
CellType = type;
IgnoredErrors = new IgnoredError();
}
private static InsertResult InsertImpl <T>(IInput context, Stream input, string sheetName, IEnumerable <T> data, XlsxBaseRange location, YesNo showHeaders, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var safeData = data.ToList();
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
ws.Cells[locationAddress.ToString()].LoadFromCollection(safeData, showHeaders == YesNo.Yes);
XlsxCellStyle styleToUse = excel.CreateStyle(style);
var range = ws.Cells[locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.End.Row + safeData.Count - 1, locationAddress.End.Column];
range.StyleName = styleToUse.Name;
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
/// <summary>
/// Returns a new excel style from cell style model.<br/>
/// If style is null returns a default instance.<br/>
/// If style's name is null or empty returns same input style with random style name.
/// </summary>
/// <param name="excel">Excel package</param>
/// <param name="style">Cell style to create</param>
/// <returns>
/// A new <see cref="XlsxCellStyle"/> reference.
/// </returns>
public static XlsxCellStyle CreateStyle(this ExcelPackage excel, XlsxCellStyle style)
{
var safeStyle = style;
if (style == null)
{
safeStyle = XlsxCellStyle.Default;
safeStyle.Name = BaseStyle.GenerateRandomStyleName();
}
else
{
safeStyle.Name = string.IsNullOrEmpty(style.Name) ? BaseStyle.GenerateRandomStyleName() : style.Name;
}
excel.Workbook.Styles.CreateFromModel(safeStyle);
return(safeStyle);
}
/// <summary>
/// Creates list of styles.
/// </summary>
/// <param name="styles">The styles.</param>
/// <param name="model">The model.</param>
public static void CreateFromModel(this ExcelStyles styles, XlsxCellStyle model)
{
SentinelHelper.ArgumentNull(styles, nameof(styles));
SentinelHelper.ArgumentNull(model, nameof(model));
try
{
var xlsxStyle = styles.CreateNamedStyle(model.Name);
xlsxStyle.Style.FormatFromModel(model);
var alternateStyleName = $"{model.Name}_Alternate";
xlsxStyle = styles.CreateNamedStyle(alternateStyleName);
xlsxStyle.Style.FormatFromModel(model, true);
}
catch
{
// Already exist.
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DataStyles"/> class.
/// </summary>
public HeaderStyles()
{
Text = XlsxCellStyle.Default;
DateTime = new XlsxCellStyle
{
Content =
{
DataType = new DateTimeDataType
{
Format = KnownDateTimeFormat.ShortDatePattern
}
}
};
Numeric = new XlsxCellStyle
{
Content =
{
DataType = new NumberDataType {
Decimals = 0, Separator = YesNo.Yes
}
}
};
Decimal = new XlsxCellStyle
{
Content =
{
DataType = new NumberDataType {
Decimals = 2, Separator = YesNo.Yes
}
}
};
Currency = new XlsxCellStyle
{
Content =
{
DataType = new CurrencyDataType {
Decimals = 2
}
}
};
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Location == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Aggegate == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Style == null)
{
Style = XlsxCellStyle.Default;
}
return(InsertImpl(context, input, SheetName, Location, Aggegate, Style));
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, QualifiedAggregateDefinition aggregate, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
string aggregateWorksheetName = aggregate.WorkSheet;
if (string.IsNullOrEmpty(aggregate.WorkSheet))
{
aggregateWorksheetName = sheetName;
}
var aggregateWorksheet = ws;
bool sameSheetName = aggregateWorksheetName.Equals(sheetName, StringComparison.OrdinalIgnoreCase);
if (!sameSheetName)
{
aggregateWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(aggregateWorksheetName, StringComparison.OrdinalIgnoreCase));
}
if (aggregateWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Aggregate sheet '{aggregateWorksheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = aggregateWorksheet.Cells[range];
cell.StyleName = cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
var formula = new XlsxFormulaResolver(aggregate)
{
HasAutoFilter = aggregate.HasAutoFilter, WorkSheet = sheetName
};
cell.Formula = formula.Resolve();
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
/// <summary>
/// Fills a <see cref="ExcelStyle"/> object with model data.
/// </summary>
/// <param name="style">A <see cref="ExcelStyle"/> reference.</param>
/// <param name="model">Style model definition.</param>
/// <param name="useAlternate"><b>true</b> for use alternate color; Otherwise <b>false</b>.</param>
/// <exception cref="System.ArgumentNullException">If <paramref name="style"/> is <b>null</b>.</exception>
/// <exception cref="System.ArgumentNullException">If <paramref name="model"/> is <b>null</b>.</exception>
public static void FormatFromModel(this ExcelStyle style, XlsxCellStyle model, bool useAlternate = false)
{
SentinelHelper.ArgumentNull(style, nameof(style));
SentinelHelper.ArgumentNull(model, nameof(model));
string concreteStyle = model.GetType().Name;
switch (concreteStyle)
{
case "XlsxCellStyle":
{
var cellStyle = (XlsxCellStyle)model;
var hasInheritStyle = !string.IsNullOrEmpty(cellStyle.Inherits);
if (hasInheritStyle)
{
var inheritStyle = cellStyle.TryGetInheritStyle();
cellStyle.Combine((XlsxCellStyle)inheritStyle);
}
style.Font.SetFromFont(cellStyle.Font.ToFont());
style.Font.Color.SetColor(cellStyle.Font.GetColor());
XlsxCellContent cellContent = cellStyle.Content;
style.VerticalAlignment = cellContent.Alignment.Vertical.ToEppVerticalAlignment();
style.HorizontalAlignment = cellContent.Alignment.Horizontal.ToEppHorizontalAlignment();
style.Numberformat.Format = cellContent.DataType.GetDataFormat().ToEppDataFormat(cellContent.DataType);
if (cellContent.Color.Equals(BaseContent.DefaultColor, StringComparison.OrdinalIgnoreCase))
{
style.Fill.PatternType = ExcelFillStyle.None;
}
else
{
style.Fill.PatternType = cellContent.Pattern.PatternType.ToEppPatternFillStyle();
if (style.Fill.PatternType != ExcelFillStyle.None)
{
style.Fill.BackgroundColor.SetColor(useAlternate ? cellContent.GetAlternateColor() : cellContent.GetColor());
style.Fill.PatternColor.SetColor(cellContent.Pattern.GetColor());
}
}
foreach (var border in cellStyle.Borders)
{
style.Border.CreateFromModel(border);
}
}
break;
default:
{
var hasInheritStyle = !string.IsNullOrEmpty(model.Inherits);
if (hasInheritStyle)
{
var inheritStyle = model.TryGetInheritStyle();
model.Combine((XlsxCellStyle)inheritStyle);
}
style.Font.SetFromFont(model.Font.ToFont());
style.Font.Color.SetColor(model.Font.GetColor());
style.HorizontalAlignment = model.Content.Alignment.Horizontal.ToEppHorizontalAlignment();
if (model.Content.Color.Equals(BaseContent.DefaultColor, StringComparison.OrdinalIgnoreCase))
{
style.Fill.PatternType = ExcelFillStyle.None;
}
else
{
style.Fill.BackgroundColor.SetColor(model.Content.GetColor());
}
foreach (var border in model.Borders)
{
style.Border.CreateFromModel(border);
}
}
break;
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxPointRange location, Dictionary <string, object> data, DictionaryStyles styles)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// cell styles > Headers
XlsxCellStyle headerTextTextStyle = excel.CreateStyle(styles.Headers.Text);
// cell styles > Values
XlsxCellStyle valueTextTextStyle = excel.CreateStyle(styles.Values.Text);
XlsxCellStyle valueDatetimeCellStyle = excel.CreateStyle(styles.Values.DateTime);
XlsxCellStyle valueDecimalCellStyle = excel.CreateStyle(styles.Values.Decimal);
var keys = data.Keys;
foreach (var key in keys)
{
var isOdd = location.Row.IsOdd();
var headerCell = ws.Cells[location.Row, location.Column];
headerCell.StyleName = isOdd ? $"{headerTextTextStyle.Name}_Alternate" : headerTextTextStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
headerCell.Value = key;
var value = data[key];
var valueCell = ws.Cells[location.Row, location.Column + 1];
XlsxCellStyle styleToUse;
switch (value)
{
case string _:
styleToUse = valueTextTextStyle;
break;
case float _:
case double _:
styleToUse = valueDecimalCellStyle;
break;
case DateTime _:
styleToUse = valueDatetimeCellStyle;
break;
default:
styleToUse = valueTextTextStyle;
break;
}
valueCell.StyleName = isOdd ? $"{styleToUse.Name}_Alternate" : styleToUse.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
valueCell.Value = styleToUse.Content.DataType.GetFormattedDataValue(value.ToString()).FormattedValue;
location.Offset(0, 1);
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
// Generates document
public static void Generate(ILogger logger)
{
#region global settings
logger.Info("");
logger.Info(" 1) Working with global settings");
var globalSettings = new XlsxSettings
{
DocumentSettings =
{
Author = "iTin",
Company = "iTin",
Manager = "iTin",
Category = "Reports",
Subject = "Subject",
Url = "http://www.iTin.es",
Title = "Report Sample From Code",
Keywords = "Reports, Excel, Summary",
Comments = "Sample Report Sample"
},
SheetsSettings =
{
new XlsxSheetSettings
{
Size = KnownDocumentSize.A3,
View = KnownDocumentView.Design,
Orientation = KnownDocumentOrientation.Landscape,
FreezePanesPoint =
{
Row = 3,
Column = 3,
},
Margins =
{
Bottom = 25,
Left = 25,
Right = 25,
Top = 25,
Units = KnownUnit.Millimeters
},
Header =
{
Sections =
{
new XlsxDocumentHeaderFooterSection
{
Text = "Sample header",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Sample header",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Header text",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Right,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Header text",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Right,
}
}
},
Footer =
{
Sections =
{
new XlsxDocumentHeaderFooterSection
{
Text = "Sample footer",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Sample footer",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "@PageNumber / @NumberOfPages",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Right
},
new XlsxDocumentHeaderFooterSection
{
Text = "@PageNumber / @NumberOfPages",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Right
},
}
}
},
new XlsxSheetSettings
{
Size = KnownDocumentSize.A3,
View = KnownDocumentView.Design,
Orientation = KnownDocumentOrientation.Landscape,
Margins =
{
Bottom = 25,
Left = 25,
Right = 25,
Top = 25,
Units = KnownUnit.Millimeters
},
Header =
{
Sections =
{
new XlsxDocumentHeaderFooterSection
{
Text = "Sample header",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Sample header",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "@SheetName",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Right,
},
new XlsxDocumentHeaderFooterSection
{
Text = "@SheetName",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Right,
}
}
},
Footer =
{
Sections =
{
new XlsxDocumentHeaderFooterSection
{
Text = "Sample footer",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "Sample footer",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Left,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "iTin",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Center,
},
new XlsxDocumentHeaderFooterSection
{
Text = "@PageNumber / @NumberOfPages",
Type = KnownHeaderFooterSectionType.Odd,
Alignment = KnownHeaderFooterAlignment.Right
},
new XlsxDocumentHeaderFooterSection
{
Text = "@PageNumber / @NumberOfPages",
Type = KnownHeaderFooterSectionType.Even,
Alignment = KnownHeaderFooterAlignment.Right
},
}
}
}
}
};
// Save global settings to disk
logger.Info(" a) XML");
var sheetsSettingsAsXmlResult = globalSettings.SaveToFile("~/Output/Sample04/GlobalSettings");
if (!sheetsSettingsAsXmlResult.Success)
{
logger.Info(" > Error while saving global settings as xml to disk");
logger.Info($" > Error: {sheetsSettingsAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved global settings as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/GlobalSettings.xml");
}
// New global settings instances from disk
logger.Info(" > Try to creates a new instance of XML global settings from disk");
var globalSettingsFromXml = XlsxSettings.LoadFromFile("~/Output/Sample04/GlobalSettings.xml");
logger.Info(globalSettingsFromXml == null
? " > Error while loading global settings from xml file"
: " > Global settings loaded correctly from xml '~/Output/Sample04/GlobalSettings.xml' file");
// Save global settings to disk
logger.Info("");
logger.Info(" b) json");
var sheetsSettingsAsJsonResult = globalSettings.SaveToFile("~/Output/Sample04/GlobalSettings", KnownFileFormat.Json);
if (!sheetsSettingsAsJsonResult.Success)
{
logger.Info(" > Error while saving sheet settings as json to disk");
logger.Info($" > Error: {sheetsSettingsAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved global settings as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample4/GlobalSettings.json");
}
// New global settings instances from disk
logger.Info(" > Try to creates a new instance of json global settings from disk");
var globalSettingsFromJson = XlsxSettings.LoadFromFile("~/Output/Sample04/GlobalSettings.json", KnownFileFormat.Json);
logger.Info(globalSettingsFromJson == null
? " > Error while loading global settings from json file"
: " > Global settings loaded correctly from json '~/Output/Sample04/GlobalSettings.json' file");
#endregion
#region style
logger.Info("");
logger.Info(" 2) Working with styles");
var style = new XlsxCellStyle
{
Font =
{
Bold = YesNo.Yes,
Italic = YesNo.Yes,
Color = "Yellow",
Underline = YesNo.No
},
Borders =
{
new XlsxStyleBorder {
Color = "Red", Show = YesNo.Yes, Position = KnownBorderPosition.Right
},
new XlsxStyleBorder {
Color = "Yellow", Show = YesNo.Yes, Position = KnownBorderPosition.Top
}
},
Content =
{
DataType = new DateTimeDataType
{
Locale = KnownCulture.afZA,
Format = KnownDateTimeFormat.FullDatePattern,
Error =
{
Value = "MaxValue",
Comment =
{
Show = YesNo.No,
Text = "dfdfdfdf",
Font =
{
Bold = YesNo.Yes,
Color = "Red",
Name = "Arial",
IsScalable = YesNo.No
}
}
},
Properties = new Properties
{
new Property {
Name = "p001", Value = "v001"
},
new Property {
Name = "p002", Value = "v002"
}
}
},
Alignment = { Horizontal = KnownHorizontalAlignment.Right,Vertical = KnownVerticalAlignment.Top}
}
};
// Save style to disk
logger.Info(" a) XML");
var styleAsXmlResult = style.SaveToFile("~/Output/Sample04/Style");
if (!styleAsXmlResult.Success)
{
logger.Info(" > Error while saving style as xml to disk");
logger.Info($" > Error: {styleAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved style as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Style.xml");
}
// New style instances from disk
logger.Info(" > Try to creates a new instance of XML style from disk");
var styleFromXml = XlsxCellStyle.LoadFromFile("~/Output/Sample04/Style.xml");
logger.Info(styleFromXml == null
? " > Error while loading style from xml file"
: " > Style loaded correctly from xml '~/Output/Sample04/Style.xml' file");
// Save style to disk
logger.Info("");
logger.Info(" b) json");
var styleAsJsonResult = style.SaveToFile("~/Output/Sample04/Style", KnownFileFormat.Json);
if (!styleAsJsonResult.Success)
{
logger.Info(" > Error while saving style as json to disk");
logger.Info($" > Error: {styleAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved style as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Style.json");
}
// New style instances from disk
logger.Info(" > Try to creates a new instance of json style from disk");
var styleFromJson = XlsxCellStyle.LoadFromFile("~/Output/Sample04/Style.json", KnownFileFormat.Json);
logger.Info(styleFromJson == null
? " > Error while loading style from json file"
: " > Style loaded correctly from json '~/Output/Sample04/Style.json' file");
#endregion
#region mini-chart
logger.Info("");
logger.Info(" 3) Working with mini-charts");
var minichart = new XlsxMiniChart
{
Name = "MiniChart1",
EmptyValueAs = MiniChartEmptyValuesAs.Gap,
ChartAxes =
{
Horizontal = { Type = MiniChartHorizontalAxisType.Date }
},
ChartSize =
{
VerticalCells = 3,
HorizontalCells = 3
},
ChartRanges =
{
Axis = new XlsxRange {
Start = { Column= 2, Row = 3 }, End = { Column = 2, Row = 32 }
}, // B3:B32
Data = new XlsxStringRange {
Address = "C3:C32"
}
},
ChartType =
{
Active = MiniChartType.Line,
Column =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
Line =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
WinLoss =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
}
}
};
// Save minichart to disk
logger.Info(" a) XML");
var minichartAsXmlResult = minichart.SaveToFile("~/Output/Sample04/MiniChart");
if (!minichartAsXmlResult.Success)
{
logger.Info(" > Error while saving mini-chart as xml to disk");
logger.Info($" > Error: {minichartAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved mini-chart as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/MiniChart.xml");
}
// New minichart instances from disk
logger.Info(" > Try to creates a new instance of XML mini-chart from disk");
var minichartFromXml = (XlsxMiniChart)XlsxMiniChart.LoadFromFile("~/Output/Sample04/MiniChart.xml");
logger.Info(minichartFromXml == null
? " > Error while loading mini-chart from xml file"
: " > Mini-chart loaded correctly from xml '~/Output/Sample04/MiniChart.xml' file");
// Save minichart to disk
logger.Info("");
logger.Info(" b) json");
var minichartAsJsonResult = minichart.SaveToFile("~/Output/Sample04/MiniChart", KnownFileFormat.Json);
if (!minichartAsJsonResult.Success)
{
logger.Info(" > Error while saving mini-chart as json to disk");
logger.Info($" > Error: {minichartAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved mini-chart as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/MiniChart.json");
}
// New minichart instances from disk
logger.Info(" > Try to creates a new instance of json mini-chart from disk");
var minichartFromJson = (XlsxMiniChart)XlsxMiniChart.LoadFromFile("~/Output/Sample04/MiniChart.json", KnownFileFormat.Json);
logger.Info(minichartFromJson == null
? " > Error while loading mini-chart from json file"
: " > Mini-chart loaded correctly from json '~/Output/Sample04/MiniChart.json' file");
#endregion
#region picture
logger.Info("");
logger.Info(" 4) Working with pictures");
var picture = new XlsxPicture
{
Show = YesNo.Yes,
Name = "image",
Path = "~/Resources/Sample-07/image-1.jpg",
Size = new XlsxSize
{
Width = 300,
Height = 300
},
Effects =
{
new XlsxDarkEffect(),
new XlsxDisabledEffect(),
new XlsxOpacityEffect {
Value = 50.0f
}
},
Border =
{
Width = 4,
Color = "Green",
Show = YesNo.Yes,
Transparency = 50,
Style = KnownLineStyle.DashDot
},
Content =
{
Color = "Blue",
Show = YesNo.Yes,
Transparency = 50,
},
ShapeEffects =
{
Shadow = new XlsxOuterShadow
{
Offset = 2,
Color = "Yellow",
Show = YesNo.Yes,
}
}
};
// Save picture to disk
logger.Info(" a) XML");
var pictureAsXmlResult = picture.SaveToFile("~/Output/Sample04/Picture");
if (!pictureAsXmlResult.Success)
{
logger.Info(" > Error while saving picture as xml to disk");
logger.Info($" > Error: {pictureAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved picture as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Picture.xml");
}
// New picture instances from disk
logger.Info(" > Try to creates a new instance of XML picture from disk");
var pictureFromXml = XlsxPicture.LoadFromFile("~/Output/Sample04/Picture.xml");
logger.Info(pictureFromXml == null
? " > Error while loading picture from xml file"
: " > Picture loaded correctly from xml '~/Output/Sample04/Picture.xml' file");
// Save picture to disk
logger.Info("");
logger.Info(" b) json");
var pictureAsJsonResult = picture.SaveToFile("~/Output/Sample04/Picture", KnownFileFormat.Json);
if (!pictureAsJsonResult.Success)
{
logger.Info(" > Error while saving picture as json to disk");
logger.Info($" > Error: {minichartAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved picture as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Picture.json");
}
// New picture instances from disk
logger.Info(" > Try to creates a new instance of json picture from disk");
var pictureFromJson = XlsxPicture.LoadFromFile("~/Output/Sample04/Picture.json", KnownFileFormat.Json);
logger.Info(pictureFromJson == null
? " > Error while loading picture from json file"
: " > Picture loaded correctly from json '~/Output/Sample04/Picture.json' file");
#endregion
#region shape
logger.Info("");
logger.Info(" 5) Working with shapes");
var shape = new XlsxShape
{
Show = YesNo.Yes,
Name = "shape",
ShapeType = ShapeType.Rect,
Size =
{
Width = 300,
Height = 300
},
Border =
{
Width = 2,
Color = "Yellow",
Show = YesNo.Yes,
Transparency = 50,
Style = KnownLineStyle.DashDot
},
Content =
{
Text = "Text",
Show = YesNo.Yes,
Color = "Yellow",
Font =
{
Color = "Red",
Bold = YesNo.Yes,
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Center
}
},
ShapeEffects =
{
Shadow = new XlsxOuterShadow
{
Offset = 2,
Color = "Yellow",
Show = YesNo.Yes,
}
}
};
// Save shape to disk
logger.Info(" a) XML");
var shapeAsXmlResult = shape.SaveToFile("~/Output/Sample04/Shape");
if (!shapeAsXmlResult.Success)
{
logger.Info(" > Error while saving shape as xml to disk");
logger.Info($" > Error: {shapeAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved shape as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Shape.xml");
}
// New shape instances from disk
logger.Info(" > Try to creates a new instance of XML shape from disk");
var shapeFromXml = XlsxShape.LoadFromFile("~/Output/Sample04/Shape.xml");
logger.Info(shapeFromXml == null
? " > Error while loading shape from xml file"
: " > Shape loaded correctly from xml '~/Output/Sample04/Shape.xml' file");
// Save shape to disk
logger.Info("");
logger.Info(" b) json");
var shapeAsJsonResult = shape.SaveToFile("~/Output/Sample04/Shape", KnownFileFormat.Json);
if (!shapeAsJsonResult.Success)
{
logger.Info(" > Error while saving shape as json to disk");
logger.Info($" > Error: {shapeAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved shape as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Shape.json");
}
// New shape instances from disk
logger.Info(" > Try to creates a new instance of json shape from disk");
var shapeFromJson = XlsxShape.LoadFromFile("~/Output/Sample04/Shape.json", KnownFileFormat.Json);
logger.Info(shapeFromJson == null
? " > Error while loading shape from json file"
: " > Shape loaded correctly from json '~/Output/Sample04/Shape.json' file");
#endregion
#region chart
logger.Info("");
logger.Info(" 6) Working with charts");
var chart = new XlsxChart
{
Name = "chart1",
Size = { Width = 800, Height = 600 },
Axes = { Primary = { Values = { GridLines = GridLine.Major } } },
Legend =
{
Show = YesNo.Yes,
Font = { Bold = YesNo.Yes },
Border = { Show = YesNo.Yes },
Location = LegendLocation.TopRight
},
Plots =
{
new XlsxChartPlot
{
Name = "plot1",
Series =
{
new XlsxChartSerie
{
Name = "Europe",
Color = "#285A8F",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 2, Row = 2 }, End = { Column = 2, Row = 13 }
}
},
new XlsxChartSerie
{
Name = "AFRICA",
Color = "#336EA9",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 3, Row = 2 }, End = { Column = 3, Row = 13 }
}
},
new XlsxChartSerie
{
Name = "ASIA",
Color = "#3572B1",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 4, Row = 2 }, End = { Column = 4, Row = 13 }
}
},
new XlsxChartSerie
{
Name = "NORTHAMERICA",
Color = "#628AC5",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 5, Row = 2 }, End = { Column = 5, Row = 13 }
}
},
new XlsxChartSerie
{
Name = "SOUTHAMERICA",
Color = "#93ADCD",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 6, Row = 2 }, End = { Column = 6, Row = 13 }
}
},
new XlsxChartSerie
{
Name = "AUSTRALIA",
Color = "#BCCCDE",
ChartType = ChartType.AreaStacked,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 13 }
},
FieldRange = new XlsxRange{
Start ={ Column = 7, Row = 2 }, End = { Column = 7, Row = 13 }
}
}
}
}
}
};
// Save chart to disk
logger.Info(" a) XML");
var chartAsXmlResult = chart.SaveToFile("~/Output/Sample04/Chart");
if (!chartAsXmlResult.Success)
{
logger.Info(" > Error while saving chart as xml to disk");
logger.Info($" > Error: {minichartAsXmlResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved chart as xml to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Chart.xml");
}
// New chart instances from disk
logger.Info(" > Try to creates a new instance of XML chart from disk");
var chartFromXml = (XlsxChart)XlsxChart.LoadFromFile("~/Output/Sample04/Chart.xml");
logger.Info(chartFromXml == null
? " > Error while loading chart from xml file"
: " > Chart loaded correctly from xml '~/Output/Sample04/Chart.xml' file");
// Save chart to disk
logger.Info("");
logger.Info(" b) json");
var chartAsJsonResult = chart.SaveToFile("~/Output/Sample04/Chart", KnownFileFormat.Json);
if (!chartAsJsonResult.Success)
{
logger.Info(" > Error while saving chart as json to disk");
logger.Info($" > Error: {chartAsJsonResult.Errors.AsMessages().ToStringBuilder()}");
}
else
{
logger.Info(" > Saved chart as json to disk correctly");
logger.Info(" > Path: ~/Output/Sample04/Chart.json");
}
// New minichart instances from disk
logger.Info(" > Try to creates a new instance of json chart from disk");
var chartFromJson = (XlsxChart)XlsxChart.LoadFromFile("~/Output/Sample04/Chart.json", KnownFileFormat.Json);
logger.Info(chartFromJson == null
? " > Error while loading chart from json file"
: " > Chart loaded correctly from json '~/Output/Sample04/Chart.json' file");
#endregion
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxRange source, QualifiedPointDefinition destination, XlsxCellStyle headerStyle, XlsxCellStyle valueStyle)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var sourceWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (sourceWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"source sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var destinationWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(destination.WorkSheet, StringComparison.OrdinalIgnoreCase));
if (destinationWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Destination sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var startDestination = destination.Point.Clone();
var x = destination.Point.Column - 1;
var sourceRange = sourceWorksheet.Cells[source.Start.Row, source.Start.Column, source.End.Row, source.End.Column];
for (var column = 0; column < sourceRange.Columns; column++)
{
destination.Point = new XlsxPoint {
Column = x, Row = destination.Point.Row
};
for (var row = 0; row < sourceRange.Rows; row++)
{
destination.Point.Offset(1, 0);
destinationWorksheet.Cells[destination.Point.Row, destination.Point.Column].Value = sourceWorksheet.Cells[source.Start.Row + row, source.Start.Column + column].Value;
}
destination.Point.Offset(0, 1);
}
XlsxCellStyle headerStyleToUse = excel.CreateStyle(headerStyle);
var headerRange = destinationWorksheet.Cells[startDestination.Row, startDestination.Column, startDestination.Row, destination.Point.Column];
headerRange.StyleName = headerStyleToUse.Name;
XlsxCellStyle valueStyleToUse = excel.CreateStyle(valueStyle);
var valueRange = destinationWorksheet.Cells[startDestination.Row + 1, startDestination.Column, destination.Point.Row - 1, destination.Point.Column];
valueRange.StyleName = valueStyleToUse.Name;
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Shadows", "Illumination", "Reflection", "Soft Edge" });
#endregion
#region Cell styles
var headerStyle = new XlsxCellStyle
{
Font = { Color = "Blue", Bold = YesNo.Yes },
Content =
{
Color = "LightGray",
Merge = { Cells = 8 },
Pattern = { PatternType = KnownPatternType.Solid },
Alignment = { Horizontal = KnownHorizontalAlignment.Left,Vertical = KnownVerticalAlignment.Center }
},
Borders =
{
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thick
},
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thick
}
}
};
var illuminationHeaderStyle = new XlsxCellStyle
{
Font = { Color = "Blue", Bold = YesNo.Yes },
Content =
{
Color = "LightGray",
Alignment = { Horizontal = KnownHorizontalAlignment.Center,Vertical = KnownVerticalAlignment.Center }
},
Borders =
{
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thick
},
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thick
}
}
};
var reflectionHeaderStyle = new XlsxCellStyle
{
Font = { Color = "Blue", Bold = YesNo.Yes },
Content =
{
Color = "LightGray",
Merge = { Cells = 2 },
Pattern = { PatternType = KnownPatternType.Solid },
Alignment = { Horizontal = KnownHorizontalAlignment.Left,Vertical = KnownVerticalAlignment.Center }
},
Borders =
{
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thick
},
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thick
}
}
};
var softEdgeHeaderStyle = new XlsxCellStyle
{
Font = { Color = "Blue", Bold = YesNo.Yes },
Content =
{
Color = "LightGray",
Merge = { Cells = 2 },
Pattern = { PatternType = KnownPatternType.Solid },
Alignment = { Horizontal = KnownHorizontalAlignment.Left,Vertical = KnownVerticalAlignment.Center }
},
Borders =
{
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thick
},
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thick
}
}
};
#endregion
#region Sheets
#region Sheet > Shadows
var shadowsInsertResult = doc.Insert(new InsertText
{
SheetName = "Shadows",
Data = "Outer shadows",
Location = new XlsxPointRange {
Column = 2, Row = 2
},
Style = headerStyle
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.DownLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 5, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.Down }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 8, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.DownLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 2, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.Left }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 5, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.Center }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 8, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.Right }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 2, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.TopLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 5, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.Top }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 8, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxOuterShadow.TopRight }
}
}).Insert(new InsertText
{
SheetName = "Shadows",
Data = "Inner shadows",
Location = new XlsxPointRange {
Column = 11, Row = 2
},
Style = headerStyle
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 11, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.TopLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 14, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.Top }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 17, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.TopRight }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 11, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.Left }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 14, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.Center }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 17, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.Right }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 11, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.DownLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 14, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.Down }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 17, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxInnerShadow.DownRight }
}
}).Insert(new InsertText
{
SheetName = "Shadows",
Data = "Perspective shadows",
Location = new XlsxPointRange {
Column = 20, Row = 2
},
Style = new XlsxCellStyle
{
Font = { Color = "Blue", Bold = YesNo.Yes },
Content =
{
Color = "LightGray",
Merge = { Cells = 5 },
Alignment = { Horizontal = KnownHorizontalAlignment.Left,Vertical = KnownVerticalAlignment.Center }
},
Borders =
{
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thick
},
new XlsxStyleBorder {
Color = "Green", Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thick
}
}
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 20, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxPerspectiveShadow.TopLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 23, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxPerspectiveShadow.TopRight }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 20, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxPerspectiveShadow.DownLeft }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 23, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxPerspectiveShadow.DownRight }
}
}).Insert(new InsertPicture
{
SheetName = "Shadows",
Location = new XlsxPointRange {
Column = 20, Row = 24
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Shadow = XlsxPerspectiveShadow.Down }
}
});
#endregion
#region Sheet > Illumination
var illuminationInsertResult = doc.Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 1",
Location = new XlsxPointRange {
Column = 2, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis1Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 2, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis1Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 2, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis1Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 2, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis1Points18 }
}
}).Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 2",
Location = new XlsxPointRange {
Column = 4, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 4, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis2Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 4, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis2Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 4, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis2Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 4, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis2Points18 }
}
}).Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 3",
Location = new XlsxPointRange {
Column = 6, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 6, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis3Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 6, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis3Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 6, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis3Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 6, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis3Points18 }
}
}).Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 4",
Location = new XlsxPointRange {
Column = 8, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 8, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis4Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 8, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis4Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 8, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis4Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 8, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis4Points18 }
}
}).Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 5",
Location = new XlsxPointRange {
Column = 10, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 10, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis5Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 10, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis4Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 10, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis5Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 10, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis5Points18 }
}
}).Insert(new InsertText
{
SheetName = "Illumination",
Data = "Accent 6",
Location = new XlsxPointRange {
Column = 12, Row = 2
},
Style = illuminationHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 12, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis6Points5 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 12, Row = 9
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis6Points8 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 12, Row = 14
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis6Points11 }
}
}).Insert(new InsertPicture
{
SheetName = "Illumination",
Location = new XlsxPointRange {
Column = 12, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 71, Height = 71
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Illumination = XlsxIlluminationShapeEffect.Emphasis6Points18 }
}
});
#endregion
#region Sheet > Reflection
var reflectionInsertResult = doc.Insert(new InsertText
{
SheetName = "Reflection",
Data = "Strong",
Location = new XlsxPointRange {
Column = 2, Row = 2
},
Style = reflectionHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.StrongNoOffset }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 2, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.StrongOffset4 }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 2, Row = 36
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.StrongOffset8 }
}
}).Insert(new InsertText
{
SheetName = "Reflection",
Data = "Semi",
Location = new XlsxPointRange {
Column = 5, Row = 2
},
Style = reflectionHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 5, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.SemiNoOffset }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 5, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.SemiOffset4 }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 5, Row = 36
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.SemiOffset8 }
}
}).Insert(new InsertText
{
SheetName = "Reflection",
Data = "Total",
Location = new XlsxPointRange {
Column = 8, Row = 2
},
Style = reflectionHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 8, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.TotalNoOffset }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 8, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.TotalOffset4 }
}
}).Insert(new InsertPicture
{
SheetName = "Reflection",
Location = new XlsxPointRange {
Column = 8, Row = 36
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { Reflection = XlsxReflectionShapeEffect.TotalOffset8 }
}
});
#endregion
#region Sheet > SoftEdge
var softEdgeInsertResult = doc.Insert(new InsertText
{
SheetName = "Soft Edge",
Data = "Soft edge",
Location = new XlsxPointRange {
Column = 2, Row = 2
},
Style = softEdgeHeaderStyle
}).Insert(new InsertPicture
{
SheetName = "Soft Edge",
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { SoftEdge = XlsxSoftEdgeShapeEffect.SoftEdge1 }
}
}).Insert(new InsertPicture
{
SheetName = "Soft Edge",
Location = new XlsxPointRange {
Column = 2, Row = 12
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { SoftEdge = XlsxSoftEdgeShapeEffect.SoftEdge2 }
}
}).Insert(new InsertPicture
{
SheetName = "Soft Edge",
Location = new XlsxPointRange {
Column = 2, Row = 20
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { SoftEdge = XlsxSoftEdgeShapeEffect.SoftEdge5 }
}
}).Insert(new InsertPicture
{
SheetName = "Soft Edge",
Location = new XlsxPointRange {
Column = 2, Row = 29
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { SoftEdge = XlsxSoftEdgeShapeEffect.SoftEdge10 }
}
}).Insert(new InsertPicture
{
SheetName = "Soft Edge",
Location = new XlsxPointRange {
Column = 2, Row = 37
},
Picture = new XlsxPicture
{
Path = "~/Resources/Sample-10/bar-chart.png",
Size = new XlsxSize {
Width = 142, Height = 142
},
Border = { Color = "Green", Show = YesNo.Yes },
ShapeEffects = { SoftEdge = XlsxSoftEdgeShapeEffect.SoftEdge25 }
}
});
#endregion
#endregion
#region Evaluate insert(s) operation(s)
if (!shadowsInsertResult.Success)
{
logger.Info(" > Error while creating insert");
logger.Info($" > Error: {shadowsInsertResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
if (!illuminationInsertResult.Success)
{
logger.Info(" > Error while creating shapes with illuminations");
logger.Info($" > Error: {illuminationInsertResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
if (!reflectionInsertResult.Success)
{
logger.Info(" > Error while creating shapes with reflection");
logger.Info($" > Error: {reflectionInsertResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
if (!softEdgeInsertResult.Success)
{
logger.Info(" > Error while creating shapes with soft-edge");
logger.Info($" > Error: {softEdgeInsertResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true
});
if (!result.Success)
{
logger.Info(" > Error creating output result");
logger.Info($" > Error: {result.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Saves output result
var saveResult = result.Result.Action(new SaveToFile {
OutputPath = "~/Output/Sample10/Sample-10"
});
if (!saveResult.Success)
{
logger.Info(" > Error while saving to disk");
logger.Info($" > Error: {saveResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
logger.Info(" > Saved to disk correctly");
logger.Info(" > Path: ~/Output/Sample10/Sample-10.xlsx");
#endregion
}
// Generates document
public static void Generate(ILogger logger)
{
#region Retrieve report data
//Informe reportData = BuildAverageByDaysReportData("EN");
Informe reportData = BuildAverageByDaysReportData("ES");
//Informe reportData = BuildSumByDaysReportData("EN");
//Informe reportData = BuildSumByDaysReportData("ES");
//Informe reportData = BuildAverageByMonthsReportData("EN");
//Informe reportData = BuildAverageByMonthsReportData("ES");
//Informe reportData = BuildSumByMonthsReportData("EN");
//Informe reportData = BuildSumByMonthsReportData("ES");
#endregion
#region Add dynamic xlsx styles
CellStylesTable.Add(
"ShortDateValue",
new XlsxCellStyle
{
Font =
{
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder
{
Show = YesNo.Yes,
Position = KnownBorderPosition.Right
}
},
Content =
{
DataType = new DateTimeDataType
{
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme)
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
});
// Add dynamic xlsx styles
CellStylesTable.Add(
"MonthYearDateValue",
new XlsxCellStyle
{
Font =
{
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder
{
Show = YesNo.Yes,
Position = KnownBorderPosition.Right
}
},
Content =
{
DataType = new DateTimeDataType
{
Format = KnownDateTimeFormat.YearMonthShortPattern,
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme),
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
});
CellStylesTable.Add(
"SummaryDateValue",
new XlsxCellStyle
{
Content =
{
DataType = new DateTimeDataType
{
Locale = (KnownCulture)EnumHelper.CreateEnumTypeFromDescriptionAttribute <KnownCulture>(reportData.ConfiguracionInforme.IdiomaInforme)
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Left
}
}
});
#endregion
#region Defines custom styles to use
XlsxCellStyle dateAxisStyle =
reportData.ConfiguracionInforme.ValoresPor.Equals("DIA", StringComparison.OrdinalIgnoreCase)
? CellStylesTable["ShortDateValue"]
: CellStylesTable["MonthYearDateValue"];
#endregion
#region Defines localized literals
bool isSpanishLanguage = reportData.ConfiguracionInforme.IdiomaInforme.Equals("ES", StringComparison.OrdinalIgnoreCase);
#region Summary Sheet
string summarySheetName = "Hoja1";
if (!isSpanishLanguage)
{
summarySheetName = "Sheet1";
}
string summaryParameterLiteral = "Parámetro";
if (!isSpanishLanguage)
{
summaryParameterLiteral = "Parameter";
}
string summaryTypeLiteral = "Tipo";
if (!isSpanishLanguage)
{
summaryTypeLiteral = "Type";
}
string summaryValuesByLiteral = "Valores Por";
if (!isSpanishLanguage)
{
summaryValuesByLiteral = "Values By";
}
string summaryInitialDateLiteral = "Fecha Inicio";
if (!isSpanishLanguage)
{
summaryInitialDateLiteral = "Initial Date";
}
string summaryEndDateLiteral = "Fecha Fin";
if (!isSpanishLanguage)
{
summaryEndDateLiteral = "End Date";
}
#endregion
#region Rawdata Sheet
string rawDataSheetName = "Hoja2";
if (!isSpanishLanguage)
{
rawDataSheetName = "Sheet2";
}
string averageLiteral = "Media";
if (!isSpanishLanguage)
{
averageLiteral = "Average";
}
string sumLiteral = "Suma";
if (!isSpanishLanguage)
{
sumLiteral = "Sum";
}
#endregion
#endregion
#region Report type to generate
bool generateSumReport = reportData.ConfiguracionInforme.Tipo.Equals("SUMA", StringComparison.OrdinalIgnoreCase);
#endregion
#region Determines locals to include in report
IEnumerable <Local> localsToPrint = generateSumReport
? reportData.Locales.Where(local => local.Nombre.Equals("Suma", StringComparison.OrdinalIgnoreCase))
: reportData.Locales;
#endregion
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { summarySheetName, rawDataSheetName });
#endregion
#region Sheet 1
#region Sheet 1 > Populates configuration data
doc.Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["ReportTitle"],
Location = new XlsxPointRange {
Column = 2, Row = 1
},
Data = reportData.ConfiguracionInforme.TituloInforme
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 3
},
Data = summaryParameterLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 3
},
Data = reportData.ConfiguracionInforme.Parametro
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 4
},
Data = summaryTypeLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 4
},
Data = reportData.ConfiguracionInforme.Tipo
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 5
},
Data = summaryValuesByLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryValue"],
Location = new XlsxPointRange {
Column = 3, Row = 5
},
Data = reportData.ConfiguracionInforme.ValoresPor
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 6
},
Data = summaryInitialDateLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryDateValue"],
Location = new XlsxPointRange {
Column = 3, Row = 6
},
Data = reportData.ConfiguracionInforme.FechaInicioPeriodo
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryLabel"],
Location = new XlsxPointRange {
Column = 2, Row = 7
},
Data = summaryEndDateLiteral
}).Insert(new InsertText
{
SheetName = summarySheetName,
Style = CellStylesTable["SummaryDateValue"],
Location = new XlsxPointRange {
Column = 3, Row = 7
},
Data = reportData.ConfiguracionInforme.FechaFinPeriodo
});
#endregion
#endregion
#region Sheet 2
#region Sheet 2 > Populates data table
var localId = -1;
var headerLocation = new XlsxPointRange {
Column = 3, Row = 2
};
var dateAxisLocation = new XlsxPointRange {
Column = 2, Row = 3
};
var parameterValueLocation = new XlsxPointRange {
Column = 3, Row = 3
};
var localHeaderStyle = CellStylesTable["LocalHeader"];
foreach (var local in localsToPrint)
{
// Defines local header text
string localName = local.Nombre;
if (localName.Equals("Suma", StringComparison.OrdinalIgnoreCase))
{
localName = sumLiteral;
}
else if (localName.Equals("Media", StringComparison.OrdinalIgnoreCase))
{
localName = averageLiteral;
}
// Defines local style
localHeaderStyle.Name = $"LocalHeader{localId}";
localHeaderStyle.Content.Color = local.Color;
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = localName,
Location = headerLocation,
Style = localHeaderStyle
});
headerLocation.Offset(1, 0);
localId++;
var series = local.Series;
foreach (var serie in series)
{
if (localId == 0)
{
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = serie.Fecha,
Location = dateAxisLocation,
Style = dateAxisStyle
});
}
doc.Insert(new InsertText
{
SheetName = rawDataSheetName,
Data = serie.Valor,
Location = parameterValueLocation,
Style = CellStylesTable["ParameterValue"]
});
dateAxisLocation.Offset(0, 1);
parameterValueLocation.Offset(0, 1);
}
parameterValueLocation = new XlsxPointRange {
Column = parameterValueLocation.Column + 1, Row = 3
};
}
#endregion
#region Sheet 2 > Insert autofilter
doc.Insert(new InsertAutoFilter
{
SheetName = rawDataSheetName,
Location = new XlsxStringRange {
Address = "C2:E2"
}
});
#endregion
#region Sheet 2 > Insert Mini-Charts
doc.Insert(new InsertMiniChart
{
SheetName = rawDataSheetName,
Location = new XlsxPointRange {
Column = 3, Row = 33
}, // B3:B33
MiniChart = new XlsxMiniChart
{
Name = "MiniChart1",
EmptyValueAs = MiniChartEmptyValuesAs.Gap,
ChartAxes =
{
Horizontal = { Type = MiniChartHorizontalAxisType.Date }
},
ChartSize =
{
VerticalCells = 3,
HorizontalCells = 3
},
ChartRanges =
{
Axis = new XlsxRange {
Start = { Column= 2, Row = 3 }, End = { Column = 2, Row = 32 }
}, //B3:B32
Data = new XlsxStringRange {
Address = "C3:C32"
}
},
ChartType =
{
Active = MiniChartType.Line,
Column =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
Line =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
},
WinLoss =
{
Serie = { Color = "#FF5733" },
Points =
{
First = { Color = "Yellow" },
Last = { Color = "Black" },
High = { Color = "Blue" },
Low = { Color = "Green" },
Negative = { Color = "Red" }
}
}
}
}
});
#endregion
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true
});
if (!result.Success)
{
logger.Info(" > Error creating output result");
logger.Info($" > Error: {result.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Saves output result
var saveResult = result.Result.Action(new SaveToFile {
OutputPath = "~/Output/Sample05/Sample-05"
});
if (!saveResult.Success)
{
logger.Info(" > Error while saving to disk");
logger.Info($" > Error: {saveResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
logger.Info(" > Saved to disk correctly");
logger.Info(" > Path: ~/Output/Sample05/Sample-05.xlsx");
#endregion
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, object data, XlsxBaseRange location, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = ws.Cells[range];
cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
if (data != null)
{
cell.Value = safeStyle.Content.DataType.GetFormattedDataValue(data.ToString()).FormattedValue;
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
// Generates document
public static void Generate(ILogger logger)
{
#region Cell styles
var numberStyle = new XlsxCellStyle
{
Content =
{
DataType = new NumberDataType {
Decimals = 1, Separator = YesNo.Yes
},
Alignment = { Horizontal= KnownHorizontalAlignment.Right }
}
};
#endregion
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Hoja1", "Hoja2" });
#endregion
#region Insert data
doc.Insert(new InsertEnumerable <AgePerson>
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 2, Row = 3
},
Data = new Collection <AgePerson>
{
new AgePerson {
Name = "Name-01", Surname = "Surname-01", Age = 23
},
new AgePerson {
Name = "Name-02", Surname = "Surname-02", Age = 7
},
new AgePerson {
Name = "Name-03", Surname = "Surname-03", Age = 11
},
new AgePerson {
Name = "Name-04", Surname = "Surname-04", Age = 22
},
new AgePerson {
Name = "Name-05", Surname = "Surname-05", Age = 52
},
new AgePerson {
Name = "Name-06", Surname = "Surname-06", Age = 33
},
new AgePerson {
Name = "Name-07", Surname = "Surname-07", Age = 8
},
new AgePerson {
Name = "Name-08", Surname = "Surname-08", Age = 2
},
new AgePerson {
Name = "Name-09", Surname = "Surname-09", Age = 12
},
new AgePerson {
Name = "Name-10", Surname = "Surname-10", Age = 21
},
}
});
#endregion
#region Insert aggregate functions
doc.Insert(new InsertText
{
SheetName = "Hoja1",
Data = "Count",
Style = XlsxCellStyle.Default,
Location = new XlsxPointRange {
Column = 1, Row = 14
},
}).Insert(new InsertAggregateFunction
{
SheetName = "Hoja1",
Style = numberStyle,
Aggegate =
{
WorkSheet = "Hoja1",
AggregateType = KnownAggregateType.Count,
Range = new XlsxRange {
Start = { Column = 2, Row= 4 }, End = { Column = 2, Row = 13 }
}
},
Location = new XlsxPointRange {
Column = 2, Row = 14
}
}).Insert(new InsertText
{
SheetName = "Hoja1",
Data = "Sum",
Style = XlsxCellStyle.Default,
Location = new XlsxPointRange {
Column = 1, Row = 15
},
}).Insert(new InsertAggregateFunction
{
SheetName = "Hoja1",
Style = numberStyle,
Aggegate =
{
WorkSheet = "Hoja1",
AggregateType = KnownAggregateType.Sum,
Range = new XlsxRange {
Start = { Column = 2, Row= 4 }, End = { Column = 2, Row = 13 }
}
},
Location = new XlsxPointRange {
Column = 2, Row = 15
}
}).Insert(new InsertText
{
SheetName = "Hoja1",
Data = "Average",
Style = XlsxCellStyle.Default,
Location = new XlsxPointRange {
Column = 1, Row = 16
},
}).Insert(new InsertAggregateFunction
{
SheetName = "Hoja1",
Style = numberStyle,
Aggegate =
{
WorkSheet = "Hoja1",
AggregateType = KnownAggregateType.Average,
Range = new XlsxRange {
Start = { Column = 2, Row= 4 }, End = { Column = 2, Row = 13 }
}
},
Location = new XlsxPointRange {
Column = 2, Row = 16
}
}).Insert(new InsertText
{
SheetName = "Hoja1",
Data = "Max",
Style = XlsxCellStyle.Default,
Location = new XlsxPointRange {
Column = 1, Row = 17
},
}).Insert(new InsertAggregateFunction
{
SheetName = "Hoja1",
Style = numberStyle,
Aggegate =
{
WorkSheet = "Hoja1",
AggregateType = KnownAggregateType.Max,
Range = new XlsxRange {
Start = { Column = 2, Row= 4 }, End = { Column = 2, Row = 13 }
}
},
Location = new XlsxPointRange {
Column = 2, Row = 17
}
}).Insert(new InsertText
{
SheetName = "Hoja1",
Data = "Min",
Style = XlsxCellStyle.Default,
Location = new XlsxPointRange {
Column = 1, Row = 18
},
}).Insert(new InsertAggregateFunction
{
SheetName = "Hoja1",
Style = numberStyle,
Aggegate =
{
WorkSheet = "Hoja1",
AggregateType = KnownAggregateType.Min,
Range = new XlsxRange {
Start = { Column = 2, Row= 4 }, End = { Column = 2, Row = 13 }
}
},
Location = new XlsxPointRange {
Column = 2, Row = 18
}
});
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true
});
if (!result.Success)
{
logger.Info(" > Error creating output result");
logger.Info($" > Error: {result.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Saves output result
var saveResult = result.Result.Action(new SaveToFile {
OutputPath = "~/Output/Sample13/Sample-13"
});
if (!saveResult.Success)
{
logger.Info(" > Error while saving to disk");
logger.Info($" > Error: {saveResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
logger.Info(" > Saved to disk correctly");
logger.Info(" > Path: ~/Output/Sample13/Sample-13.xlsx");
#endregion
}
