// 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
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Hoja1", "Hoja2" });
#endregion
#region Insert datatable and generic collections
doc.Insert(new InsertText
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 2, Row = 1
},
Data = "Custom text"
}).Insert(new InsertEnumerable <Person>
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 2, Row = 3
},
Data = new Collection <Person>
{
new Person {
Name = "Name-01", Surname = "Surname-01"
},
new Person {
Name = "Name-02", Surname = "Surname-02"
},
new Person {
Name = "Name-03", Surname = "Surname-03"
},
new Person {
Name = "Name-04", Surname = "Surname-04"
},
new Person {
Name = "Name-05", Surname = "Surname-05"
},
new Person {
Name = "Name-06", Surname = "Surname-06"
},
new Person {
Name = "Name-07", Surname = "Surname-07"
},
new Person {
Name = "Name-08", Surname = "Surname-08"
},
new Person {
Name = "Name-09", Surname = "Surname-09"
},
new Person {
Name = "Name-10", Surname = "Surname-10"
}
}
}).Insert(new InsertDataTable
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 10, Row = 3
},
Data = new Collection <Person>
{
new Person {
Name = "Name-01", Surname = "Surname-01"
},
new Person {
Name = "Name-02", Surname = "Surname-02"
},
new Person {
Name = "Name-03", Surname = "Surname-03"
},
new Person {
Name = "Name-04", Surname = "Surname-04"
},
new Person {
Name = "Name-05", Surname = "Surname-05"
},
new Person {
Name = "Name-06", Surname = "Surname-06"
},
new Person {
Name = "Name-07", Surname = "Surname-07"
},
new Person {
Name = "Name-08", Surname = "Surname-08"
},
new Person {
Name = "Name-09", Surname = "Surname-09"
},
new Person {
Name = "Name-10", Surname = "Surname-10"
}
}.ToDataTable <Person>("Person")
});
#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/Sample06/Sample-06"
});
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/Sample06/Sample-06.xlsx");
#endregion
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Hoja1", "Hoja2" });
#endregion
#region Insert picture from image stream
InsertResult insertResult;
using (var image = XlsxImage.FromByteArray(File.ReadAllBytes(iTinIO.Path.PathResolver("~/Resources/Sample-09/image-1.jpg"))))
{
insertResult = doc.Insert(new InsertPicture
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 1, Row = 1
},
Picture =
image.AsPicture(
"image",
size: new XlsxSize
{
Width = 150,
Height = 150
},
border: new XlsxBorder
{
Width = 2,
Color = "Green",
Show = YesNo.Yes,
Style = KnownLineStyle.DashDot
},
shapeEffects: new XlsxShapeEffects {
Shadow = XlsxOuterShadow.DownRight
}
)
});
}
#endregion
#region Insert correctly?
if (!insertResult.Success)
{
logger.Info(" > Error while creating insert");
logger.Info($" > Error: {insertResult.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/Sample09/Sample-09"
});
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/Sample09/Sample-09.xlsx");
#endregion
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Hoja1", "Hoja2" });
#endregion
#region Insert picture
doc.Insert(new InsertPicture
{
SheetName = "Hoja1",
Location = new XlsxPointRange {
Column = 3, Row = 3
},
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,
}
}
}
});
#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/Sample07/Sample-07"
});
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/Sample07/Sample-07.xlsx");
#endregion
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates cell styles
var cellStylesTable = new Dictionary <string, XlsxCellStyle>
{
{
"FieldHeader",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Content = { Color = "#ED7D31" },
Font = { Name = "Calibri", Color = "White", Size = 11.0f, Bold = YesNo.Yes }
}
},
{
"NumericStyle",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Content =
{
Color = "#F8CBAD",
AlternateColor = "#FCE4D6",
DataType = new NumberDataType {
Decimals = 0
},
Alignment = { Horizontal = KnownHorizontalAlignment.Right }
},
Font = { Name = "Calibri", Size = 11.0f }
}
},
{
"TextStyle",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Font = { Name = "Calibri", Size = 11.0f },
Content = { Color = "#F8CBAD", AlternateColor = "#FCE4D6" }
}
},
{
"DecimalStyle",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Content =
{
Color = "#F8CBAD",
AlternateColor = "#FCE4D6",
DataType = new NumberDataType {
Decimals = 2
},
Alignment = { Horizontal = KnownHorizontalAlignment.Right }
},
Font = { Name = "Calibri", Size = 11.0f }
}
},
{
"AggregateNumericStyle",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Content =
{
Color = "#ED7D31",
DataType = new NumberDataType {
Decimals = 0
},
Alignment = { Horizontal = KnownHorizontalAlignment.Right }
},
Font = { Name = "Calibri", Color = "White", Size = 11.0f, Bold = YesNo.Yes }
}
},
{
"AggregateDecimalStyle",
new XlsxCellStyle
{
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Style = KnownBorderStyle.Thin, Color = "White"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Style = KnownBorderStyle.Thin, Color = "White"
}
},
Content =
{
Color = "#ED7D31",
DataType = new NumberDataType {
Decimals = 2
},
Alignment = { Horizontal = KnownHorizontalAlignment.Right }
},
Font = { Name = "Calibri", Color = "White", Size = 11.0f, Bold = YesNo.Yes }
}
}
};
#endregion
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Chart And Secondary Axis", "Hoja2" });
#endregion
#region Sheet 1
#region Sheet 1 > Hides the grid lines
doc.Set(new SetGridLines
{
Show = YesNo.No,
SheetName = "Chart And Secondary Axis"
});
#endregion
#region Sheet 1 > Insert Data
#region ID
doc.Insert(new InsertText
{
Data = "ID",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 1, Row = 1
}
}).Insert(new InsertText
{
Data = "12001",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 1, Row = 2
}
}).Insert(new InsertText
{
Data = "12002",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 1, Row = 3
},
}).Insert(new InsertText
{
Data = "12003",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 1, Row = 4
}
}).Insert(new InsertText
{
Data = "12010",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 1, Row = 5
}
}).Insert(new InsertText
{
Data = "12011",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 1, Row = 6
}
}).Insert(new InsertText
{
Data = "Total",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 1, Row = 7
}
});
#endregion
#region Product
doc.Insert(new InsertText
{
Data = "Product",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 2, Row = 1
}
}).Insert(new InsertText
{
Data = "Nails",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["TextStyle"],
Location = new XlsxPointRange {
Column = 2, Row = 2
}
}).Insert(new InsertText
{
Data = "Hammer",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["TextStyle"],
Location = new XlsxPointRange {
Column = 2, Row = 3
},
}).Insert(new InsertText
{
Data = "Saw",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["TextStyle"],
Location = new XlsxPointRange {
Column = 2, Row = 4
}
}).Insert(new InsertText
{
Data = "Drill",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["TextStyle"],
Location = new XlsxPointRange {
Column = 2, Row = 5
}
}).Insert(new InsertText
{
Data = "Crowbar",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["TextStyle"],
Location = new XlsxPointRange {
Column = 2, Row = 6
}
}).Insert(new InsertText
{
Data = "",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 2, Row = 7
}
});
#endregion
#region Items in Stock
doc.Insert(new InsertText
{
Data = "Items in Stock",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 3, Row = 1
}
}).Insert(new InsertText
{
Data = 37,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 2
}
}).Insert(new InsertText
{
Data = 5,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 3
},
}).Insert(new InsertText
{
Data = 12,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 4
}
}).Insert(new InsertText
{
Data = 20,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 5
}
}).Insert(new InsertText
{
Data = 7,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["NumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 6
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Chart And Secondary Axis",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 3, Row = 2 },
End = { Column = 3, Row = 6 }
}
},
Style = cellStylesTable["AggregateNumericStyle"],
Location = new XlsxPointRange {
Column = 3, Row = 7
}
});
#endregion
#region Purchase Price
doc.Insert(new InsertText
{
Data = "Purchase Price",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 4, Row = 1
}
}).Insert(new InsertText
{
Data = 1.30f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 2
}
}).Insert(new InsertText
{
Data = 5.33f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 3
},
}).Insert(new InsertText
{
Data = 8.99f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 4
}
}).Insert(new InsertText
{
Data = 4.30f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 5
}
}).Insert(new InsertText
{
Data = 13.77f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 6
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Chart And Secondary Axis",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 4, Row = 2 },
End = { Column = 4, Row = 6 }
}
},
Style = cellStylesTable["AggregateDecimalStyle"],
Location = new XlsxPointRange {
Column = 4, Row = 7
}
});
#endregion
#region Price
doc.Insert(new InsertText
{
Data = "Price",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 5, Row = 1
}
}).Insert(new InsertText
{
Data = 3.99f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 2
}
}).Insert(new InsertText
{
Data = 12.10f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 3
},
}).Insert(new InsertText
{
Data = 15.37f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 4
}
}).Insert(new InsertText
{
Data = 8.0f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 5
}
}).Insert(new InsertText
{
Data = 23.48f,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 6
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Chart And Secondary Axis",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 5, Row = 2 },
End = { Column = 5, Row = 6 }
}
},
Style = cellStylesTable["AggregateDecimalStyle"],
Location = new XlsxPointRange {
Column = 5, Row = 7
}
});
#endregion
#region Profit
doc.Insert(new InsertText
{
Data = "Profit",
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 6, Row = 1
}
}).Insert(new InsertText
{
Data = 2.69,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 2
}
}).Insert(new InsertText
{
Data = 6.77,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 3
},
}).Insert(new InsertText
{
Data = 6.38,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 4
}
}).Insert(new InsertText
{
Data = 3.70,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 5
}
}).Insert(new InsertText
{
Data = 9.71,
SheetName = "Chart And Secondary Axis",
Style = cellStylesTable["DecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 6
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Chart And Secondary Axis",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 6, Row = 2 },
End = { Column = 6, Row = 6 }
}
},
Style = cellStylesTable["AggregateDecimalStyle"],
Location = new XlsxPointRange {
Column = 6, Row = 7
}
});
#endregion
#endregion
#region Sheet 1 > Insert Autofilter
doc.Insert(new InsertAutoFilter
{
SheetName = "Chart And Secondary Axis",
Location = new XlsxStringRange {
Address = "A1:F1"
}
});
#endregion
#region Sheet 1 > Insert Chart(s)
var insertChartResult = doc.Insert(new InsertChart
{
SheetName = "Chart And Secondary Axis",
Location = new XlsxPointRange {
Column = 8, Row = 2
},
Chart = new XlsxChart
{
Name = "chart1",
Size = { Width = 800, Height = 600 },
Border =
{
Color = "Green"
},
Axes =
{
Primary =
{
Values = { GridLines = GridLine.Major }
},
Secondary =
{
Values =
{
Labels = { Position = LabelPosition.High },
Values = { Maximum = "50" }
},
Category =
{
Labels = { Position = LabelPosition.High }
}
}
},
Legend =
{
Show = YesNo.Yes,
Font = { Name = "Calibri" }
},
Plots =
{
new XlsxChartPlot
{
Name = "plot1",
Series =
{
new XlsxChartSerie
{
Name = "Purchase Price",
Color = "#336EA9",
ChartType = ChartType.ColumnStacked,
AxisRange = new XlsxRange{
Start ={ Column = 2, Row = 2 }, End = { Column = 2, Row = 6 }
},
FieldRange = new XlsxRange{
Start ={ Column = 4, Row = 2 }, End = { Column = 4, Row = 6 }
}
},
new XlsxChartSerie
{
Name = "Profit",
Color = "#ED7D31",
ChartType = ChartType.ColumnStacked,
AxisRange = new XlsxRange{
Start ={ Column = 2, Row = 2 }, End = { Column = 2, Row = 6 }
},
FieldRange = new XlsxRange{
Start ={ Column = 6, Row = 2 }, End = { Column = 6, Row = 6 }
}
},
}
},
new XlsxChartPlot
{
Name = "plot2",
UseSecondaryAxis = YesNo.Yes,
Series =
{
new XlsxChartSerie
{
Name = "Items in Stock",
Color = "Gray",
ChartType = ChartType.Line,
AxisRange = new XlsxRange{
Start ={ Column = 1, Row = 2 }, End = { Column = 1, Row = 6 }
},
FieldRange = new XlsxRange{
Start ={ Column = 3, Row = 2 }, End = { Column = 3, Row = 6 }
}
}
}
}
},
Effects =
{
Shadow = XlsxPerspectiveShadow.Down,
Illumination = XlsxIlluminationShapeEffect.Emphasis1Points8
}
}
});
#endregion
#endregion
#region Evaluate insert(s) operation(s)
if (!insertChartResult.Success)
{
logger.Info(" > Error while creating chart insert");
logger.Info($" > Error: {insertChartResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true, GlobalSettings = XlsxSettings.Default
});
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/Sample15/Sample-15"
});
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/Sample15/Sample-15.xlsx");
#endregion
}
// 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 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
}
// Generates document
public static void Generate(ILogger logger)
{
#region Creates cell styles
var cellStylesTable = new Dictionary <string, XlsxCellStyle>
{
{
"FieldHeader",
new XlsxCellStyle
{
Font =
{
Name = "Calibri",
Size = 11.0f,
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Bottom, Color = "#9BC2E6"
},
},
Content =
{
Color = "#DDEBF7"
}
}
},
{
"FieldValue",
new XlsxCellStyle
{
Font =
{
Name = "Calibri",
Size = 11.0f,
},
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Color = "#9BC2E6"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Color = "#9BC2E6"
}
},
Content =
{
Color = "#BDD7EE",
AlternateColor = "#DDEBF7",
DataType = new NumberDataType
{
Decimals = 1,
Separator = YesNo.Yes
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
}
},
{
"PeriodValue",
new XlsxCellStyle
{
Font =
{
Name = "Calibri",
Size = 11.0f,
Bold = YesNo.Yes,
},
Content =
{
Color = "#BDD7EE",
AlternateColor = "#DDEBF7",
DataType = new DateTimeDataType
{
Locale = KnownCulture.Current,
Format = KnownDateTimeFormat.ShortDatePattern
}
}
}
},
{
"AggregateHeader",
new XlsxCellStyle
{
Font =
{
Name = "Calibri",
Size = 11.0f,
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Color = "#9BC2E6"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Color = "#9BC2E6"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Color = "#9BC2E6"
}
},
Content =
{
Color = "#DDEBF7",
}
}
},
{
"AggregateFieldValue",
new XlsxCellStyle
{
Font =
{
Name = "Calibri",
Size = 11.0f,
Bold = YesNo.Yes,
},
Borders =
{
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Top, Color = "#9BC2E6"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Left, Color = "#9BC2E6"
},
new XlsxStyleBorder {
Show = YesNo.Yes, Position = KnownBorderPosition.Right, Color = "#9BC2E6"
}
},
Content =
{
Color = "#DDEBF7",
DataType = new NumberDataType
{
Decimals = 1,
Separator = YesNo.Yes
},
Alignment =
{
Horizontal = KnownHorizontalAlignment.Right
}
}
}
},
};
#endregion
#region Creates xlsx file reference
XlsxInput doc = XlsxInput.Create(new[] { "Stacked area chart", "Hoja2" });
#endregion
#region Sheet 1
#region Sheet 1 > Insert Data
#region PERIOD
doc.Insert(new InsertText
{
Data = "Period",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 1, Row = 1
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/01/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 2
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/02/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 3
},
}).Insert(new InsertText
{
Data = DateTime.Parse("01/03/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 4
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/04/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 5
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/05/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 6
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/06/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 7
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/07/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 8
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/08/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 9
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/09/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 10
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/10/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 11
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/11/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 12
}
}).Insert(new InsertText
{
Data = DateTime.Parse("01/12/2010"),
SheetName = "Stacked area chart",
Style = cellStylesTable["PeriodValue"],
Location = new XlsxPointRange {
Column = 1, Row = 13
}
}).Insert(new InsertText
{
Data = "Total",
SheetName = "Stacked area chart",
Style = cellStylesTable["AggregateHeader"],
Location = new XlsxPointRange {
Column = 1, Row = 14
}
});
#endregion
#region Europe
doc.Insert(new InsertText
{
Data = "Europe",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 2, Row = 1
}
}).Insert(new InsertText
{
Data = 12.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 2
}
}).Insert(new InsertText
{
Data = 13.9,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 3
},
}).Insert(new InsertText
{
Data = 11.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 4
}
}).Insert(new InsertText
{
Data = 12.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 5
}
}).Insert(new InsertText
{
Data = 9.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 6
}
}).Insert(new InsertText
{
Data = 14.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 7
}
}).Insert(new InsertText
{
Data = 7.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 8
}
}).Insert(new InsertText
{
Data = 19.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 9
}
}).Insert(new InsertText
{
Data = 21.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 10
}
}).Insert(new InsertText
{
Data = 23.8,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 11
}
}).Insert(new InsertText
{
Data = 25.6,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 12
}
}).Insert(new InsertText
{
Data = 17.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 2, Row = 2 },
End = { Column = 2, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 2, Row = 14
}
});
#endregion
#region Africa
doc.Insert(new InsertText
{
Data = "Africa",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 3, Row = 1
}
}).Insert(new InsertText
{
Data = 8.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 2
}
}).Insert(new InsertText
{
Data = 9.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 3
},
}).Insert(new InsertText
{
Data = 8.7,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 4
}
}).Insert(new InsertText
{
Data = 9.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 5
}
}).Insert(new InsertText
{
Data = 10.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 6
}
}).Insert(new InsertText
{
Data = 9.8,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 7
}
}).Insert(new InsertText
{
Data = 11.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 8
}
}).Insert(new InsertText
{
Data = 10.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 9
}
}).Insert(new InsertText
{
Data = 8.5,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 10
}
}).Insert(new InsertText
{
Data = 9.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 11
}
}).Insert(new InsertText
{
Data = 10.8,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 12
}
}).Insert(new InsertText
{
Data = 12.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 3, Row = 2 },
End = { Column = 3, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 3, Row = 14
}
});
#endregion
#region Asia
doc.Insert(new InsertText
{
Data = "Asia",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 4, Row = 1
}
}).Insert(new InsertText
{
Data = 55.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 2
}
}).Insert(new InsertText
{
Data = 51.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 3
},
}).Insert(new InsertText
{
Data = 54.7,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 4
}
}).Insert(new InsertText
{
Data = 53.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 5
}
}).Insert(new InsertText
{
Data = 45.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 6
}
}).Insert(new InsertText
{
Data = 49.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 7
}
}).Insert(new InsertText
{
Data = 50.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 8
}
}).Insert(new InsertText
{
Data = 49.6,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 9
}
}).Insert(new InsertText
{
Data = 41.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 10
}
}).Insert(new InsertText
{
Data = 45.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 11
}
}).Insert(new InsertText
{
Data = 49.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 12
}
}).Insert(new InsertText
{
Data = 43.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 4, Row = 2 },
End = { Column = 4, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 4, Row = 14
}
});
#endregion
#region North America
doc.Insert(new InsertText
{
Data = "North America",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 5, Row = 1
}
}).Insert(new InsertText
{
Data = 35.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 2
}
}).Insert(new InsertText
{
Data = 35.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 3
},
}).Insert(new InsertText
{
Data = 32.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 4
}
}).Insert(new InsertText
{
Data = 35.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 5
}
}).Insert(new InsertText
{
Data = 25.8,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 6
}
}).Insert(new InsertText
{
Data = 29.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 7
}
}).Insert(new InsertText
{
Data = 31.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 8
}
}).Insert(new InsertText
{
Data = 35.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 9
}
}).Insert(new InsertText
{
Data = 35.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 10
}
}).Insert(new InsertText
{
Data = 32.0,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 11
}
}).Insert(new InsertText
{
Data = 33.6,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 12
}
}).Insert(new InsertText
{
Data = 35.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 5, Row = 2 },
End = { Column = 5, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 5, Row = 14
}
});
#endregion
#region South America
doc.Insert(new InsertText
{
Data = "South America",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 6, Row = 1
}
}).Insert(new InsertText
{
Data = 14.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 2
}
}).Insert(new InsertText
{
Data = 14.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 3
},
}).Insert(new InsertText
{
Data = 13.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 4
}
}).Insert(new InsertText
{
Data = 11.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 5
}
}).Insert(new InsertText
{
Data = 12.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 6
}
}).Insert(new InsertText
{
Data = 14.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 7
}
}).Insert(new InsertText
{
Data = 14.9,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 8
}
}).Insert(new InsertText
{
Data = 13.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 9
}
}).Insert(new InsertText
{
Data = 14.5,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 10
}
}).Insert(new InsertText
{
Data = 16.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 11
}
}).Insert(new InsertText
{
Data = 14.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 12
}
}).Insert(new InsertText
{
Data = 15.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 6, Row = 2 },
End = { Column = 6, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 6, Row = 14
}
});
#endregion
#region Australia
doc.Insert(new InsertText
{
Data = "Australia",
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldHeader"],
Location = new XlsxPointRange {
Column = 7, Row = 1
}
}).Insert(new InsertText
{
Data = 12.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 2
}
}).Insert(new InsertText
{
Data = 11.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 3
},
}).Insert(new InsertText
{
Data = 12.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 4
}
}).Insert(new InsertText
{
Data = 10.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 5
}
}).Insert(new InsertText
{
Data = 13.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 6
}
}).Insert(new InsertText
{
Data = 14.2,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 7
}
}).Insert(new InsertText
{
Data = 12.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 8
}
}).Insert(new InsertText
{
Data = 10.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 9
}
}).Insert(new InsertText
{
Data = 9.3,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 10
}
}).Insert(new InsertText
{
Data = 11.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 11
}
}).Insert(new InsertText
{
Data = 13.4,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 12
}
}).Insert(new InsertText
{
Data = 15.1,
SheetName = "Stacked area chart",
Style = cellStylesTable["FieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 13
}
}).Insert(new InsertAggregateFunction
{
SheetName = "Stacked area chart",
Aggegate =
{
AggregateType = KnownAggregateType.Sum,
HasAutoFilter = YesNo.Yes,
Range = new XlsxRange
{
Start = { Column = 7, Row = 2 },
End = { Column = 7, Row = 13 }
}
},
Style = cellStylesTable["AggregateFieldValue"],
Location = new XlsxPointRange {
Column = 7, Row = 14
}
});
#endregion
#endregion
#region Sheet 1 > Insert Autofilter
doc.Insert(new InsertAutoFilter
{
SheetName = "Stacked area chart",
Location = new XlsxStringRange {
Address = "A1:G1"
}
});
#endregion
#region Sheet 1 > Insert Chart(s)
var insertChartResult = doc.Insert(new InsertChart
{
SheetName = "Stacked area chart",
Location = new XlsxPointRange {
Column = 10, Row = 1
},
Chart = new XlsxChart
{
Name = "chart1",
Size = { Width = 800, Height = 600 },
Axes = { Primary = { Values = { GridLines = GridLine.Major } } },
Legend = { Show = YesNo.Yes, Border = { Show = YesNo.Yes } },
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 }
}
}
}
}
}
}
});
#endregion
#endregion
#region Evaluate insert(s) operation(s)
if (!insertChartResult.Success)
{
logger.Info(" > Error while creating chart insert");
logger.Info($" > Error: {insertChartResult.Errors.AsMessages().ToStringBuilder()}");
return;
}
#endregion
#region Create output result
var result = doc.CreateResult(new OutputResultConfig {
AutoFitColumns = true, GlobalSettings = XlsxSettings.Default
});
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/Sample14/Sample-14"
});
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/Sample14/Sample-14.xlsx");
#endregion
}
