/// <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 }