Export a workbook with a chart and a table
C# code below.
public string Html { get; private set; }
public string Css { get; private set; }
public async Task LoadHtmlAndChartExport(ePresetChartStyleMultiSeries chartStyle, TableStyles tableStyle)
{
var package = CreateWorkbook(chartStyle, tableStyle);
var sheet = package.Workbook.Worksheets[0];
var exporter = sheet.Cells.CreateHtmlExporter();
var settings = exporter.Settings;
settings.Drawings.Include = eDrawingInclude.Include;
settings.Culture = CultureInfo.InvariantCulture;
//Set absolute positioning and sizing.
settings.Drawings.Position = eDrawingPosition.Absolute;
settings.SetRowHeight = true;
settings.SetColumnWidth = true;
settings.TableId = "currency-table";
Html = await exporter.GetHtmlStringAsync();
Css = await exporter.GetCssStringAsync();
}
public static ExcelPackage CreateWorkbook(ePresetChartStyleMultiSeries chartStyle, TableStyles tableStyle)
{
var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Html export with svg chart");
var csvFileInfo = new FileInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"data\\currencies2011weekly.csv"));
if (csvFileInfo.Exists == false) return null;
var format = new ExcelTextFormat
{
Delimiter = ';',
Culture = CultureInfo.InvariantCulture,
DataTypes = new eDataTypes[] { eDataTypes.DateTime, eDataTypes.Number, eDataTypes.Number, eDataTypes.Number, eDataTypes.Number }
};
var tableRange = sheet.Cells["A15"].LoadFromText(csvFileInfo, format, tableStyle, true);
sheet.Cells["B1:E1"].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)ExcelHorizontalAlignment.Right;
sheet.Cells[tableRange.Start.Row, 1, tableRange.End.Row, 1].Style.Numberformat.Format = "yyyy-MM-dd";
sheet.Cells[tableRange.Start.Row, 2, tableRange.End.Row, 5].Style.Numberformat.Format = "#,##0.0000";
tableRange.AutoFitColumns();
var table = sheet.Tables.GetFromRange(tableRange);
table.ShowFirstColumn = true;
var chart = sheet.Drawings.AddLineChart("LineChart1", eLineChartType.Line);
var serie1 = chart.Series.Add(tableRange.TakeColumnsBetween(1, 1).SkipRows(1), tableRange.TakeColumns(1).SkipRows(1));
serie1.HeaderAddress = sheet.Cells["B15"];
var serie2 = chart.Series.Add(tableRange.TakeColumnsBetween(2, 1).SkipRows(1), tableRange.TakeColumns(1).SkipRows(1));
serie2.HeaderAddress = sheet.Cells["C15"];
var serie3 = chart.Series.Add(tableRange.TakeColumnsBetween(3, 1).SkipRows(1), tableRange.TakeColumns(1).SkipRows(1));
serie3.HeaderAddress = sheet.Cells["D15"];
var serie4 = chart.Series.Add(tableRange.TakeColumnsBetween(4, 1).SkipRows(1), tableRange.TakeColumns(1).SkipRows(1));
serie4.HeaderAddress = sheet.Cells["E15"];
chart.SetPosition(0, 0);
chart.To.Row = 14;
chart.To.Column = 10;
chart.StyleManager.SetChartStyle(chartStyle);
return package;