Html export - connect table data with external components
In this sample we have connected the exported table to Google charts with javascript. The table data is imported to EPPlus via the LoadFromDataTable function and the Density column is a calculated table column. The total row of the table is calculated via EPPlus built in Total row functions.
The table has addtional classes from Bootstrap: table and table-sm
| Country | Population | Area (km2) | Density |
|---|---|---|---|
| Russia | 145,734,038 | 3,972,400 km2 | 36.7 |
| Germany | 83,124,418 | 357,386 km2 | 232.6 |
| United Kingdom | 67,141,684 | 242,495 km2 | 276.9 |
| France | 64,990,511 | 551,695 km2 | 117.8 |
| Italy | 60,627,291 | 301,338 km2 | 201.2 |
| Spain | 47,394,223 | 498,468 km2 | 95.1 |
| Ukraine | 44,246,156 | 603,628 km2 | 73.3 |
| Poland | 37,921,592 | 312,685 km2 | 121.3 |
| Romania | 19,186,000 | 238,397 km2 | 80.5 |
| Netherlands | 17,553,530 | 41,198 km2 | 426.1 |
| Belgium | 11,521,238 | 30,510 km2 | 377.6 |
| Greece | 10,718,565 | 131,940 km2 | 81.2 |
| Czech Republic | 10,665,677 | 78,866 km2 | 135.2 |
| Sweden | 10,409,248 | 450,295 km2 | 23.1 |
| Portugal | 10,256,193 | 91,568 km2 | 112.0 |
| Hungary | 9,707,499 | 93,030 km2 | 104.3 |
| Belarus | 9,452,617 | 207,600 km2 | 45.5 |
| Austria | 8,891,388 | 83,858 km2 | 106.0 |
| Switzerland | 8,525,611 | 41,290 km2 | 206.5 |
| Bulgaria | 7,051,608 | 110,994 km2 | 63.5 |
| Serbia | 6,871,547 | 77,453 km2 | 88.7 |
| Denmark | 5,850,189 | 44,493 km2 | 131.5 |
| Finland | 5,541,806 | 338,145 km2 | 16.4 |
| Slovakia | 5,459,781 | 49,036 km2 | 111.3 |
| Norway | 5,402,171 | 385,178 km2 | 14.0 |
| Ireland | 4,818,690 | 70,273 km2 | 68.6 |
| Croatia | 4,156,405 | 56,594 km2 | 73.4 |
| Moldova | 4,033,963 | 33,846 km2 | 119.2 |
| Bosnia and Herzegovina | 3,323,925 | 51,129 km2 | 65.0 |
| Albania | 2,882,740 | 28,748 km2 | 100.3 |
| Lithuania | 2,801,264 | 65,300 km2 | 42.9 |
| North Macedonia | 2,083,374 | 25,713 km2 | 81.0 |
| Slovenia | 2,055,496 | 20,273 km2 | 101.4 |
| Latvia | 1,928,459 | 64,589 km2 | 29.9 |
| Kosovo | 1,798,506 | 10,908 km2 | 164.9 |
| Estonia | 1,322,920 | 45,339 km2 | 29.2 |
| Cyrprus | 1,189,265 | 9,251 km2 | 128.6 |
| Montenegro | 627,809 | 13,812 km2 | 45.5 |
| Iceland | 336,713 | 102,775 km2 | 3.3 |
| Total | 747,604,110 | 9,932,496 km2 | Avg: 111.1 |
using System.Data;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
namespace EPPlus.WebSampleMvc.NetCore.Models.HtmlExport
{
public class ExportTable2Model
{
private DataTable _dataTable;
public string Css { get; set; }
public string Html { get; set; }
public void SetupSampleData(TableStyles style = TableStyles.Dark1)
{
InitDataTable();
using(var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Html export sample 2");
var tableRange = sheet.Cells["A1"].LoadFromDataTable(_dataTable, true, style);
// Configure the table
var table = sheet.Tables.GetFromRange(tableRange);
table.Sort(x => x.SortBy.ColumnNamed("Population", eSortOrder.Descending));
table.ShowTotal = true;
table.Columns[0].TotalsRowLabel = "Total";
table.Columns[1].TotalsRowFunction = RowFunctions.Sum;
table.Columns[2].TotalsRowFunction = RowFunctions.Sum;
// Add column for population density
table.Columns.Add(1);
tableRange = table.Range;
table.Columns[3].CalculatedColumnFormula = $"{table.Name}[[#This Row],[Population]]/{table.Name}[[#This Row],[Area (km2)]]";
table.Columns[3].Name = "Density";
table.Columns[3].TotalsRowFunction = RowFunctions.Average;
sheet.Calculate();
// Format the header
sheet.Cells[1, tableRange.Start.Column + 1, 1, tableRange.End.Column].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
// Format the rows
var lastDataRow = tableRange.End.Row - 1;
sheet.Cells[tableRange.Start.Row, 1, lastDataRow, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
sheet.Cells[tableRange.Start.Row, 2, lastDataRow, 2].Style.Numberformat.Format = "#,##0";
sheet.Cells[tableRange.Start.Row, 3, lastDataRow, 3].Style.Numberformat.Format = "#,##0 \"km2\"";
sheet.Cells[tableRange.Start.Row, 4, lastDataRow, 4].Style.Numberformat.Format = "#,##0.0";
// Format the total row
var totalRow = tableRange.End.Row;
sheet.Cells[totalRow, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
sheet.Cells[totalRow, 2].Style.Numberformat.Format = "#,##0";
sheet.Cells[totalRow, 3].Style.Numberformat.Format = "#,##0 \"km2\"";
sheet.Cells[totalRow, 4].Style.Numberformat.Format = "\"Avg: \"#,##0.0 ";
// Configure export settings
var exporter = table.CreateHtmlExporter();
var settings = exporter.Settings;
settings.Minify = false;
settings.AdditionalTableClassNames.Add("table");
settings.AdditionalTableClassNames.Add("table-sm");
settings.TableId = "population-table";
settings.Accessibility.TableSettings.AriaDescribedBy = "table-description";
settings.Accessibility.TableSettings.AriaLabel = "Demo table";
// export css and html
Css = exporter.GetCssString();
Html = exporter.GetHtmlString();
}
}
private void InitDataTable()
{
_dataTable = new DataTable();
_dataTable.Columns.Add("Country", typeof(string));
_dataTable.Columns.Add("Population", typeof(int));
var areaCol = _dataTable.Columns.Add("Area", typeof(int));
areaCol.Caption = "Area (km2)";
_dataTable.Rows.Add("Sweden", 10409248, 450295);
_dataTable.Rows.Add("Norway", 5402171, 385178);
_dataTable.Rows.Add("Netherlands", 17553530, 41198);
_dataTable.Rows.Add("Finland", 5541806, 338145);
_dataTable.Rows.Add("Belgium", 11521238, 30510);
_dataTable.Rows.Add("Denmark", 5850189, 44493);
_dataTable.Rows.Add("Lithuania", 2801264, 65300);
_dataTable.Rows.Add("Greece", 10718565, 131940);
_dataTable.Rows.Add("Russia", 145734038, 3972400);
_dataTable.Rows.Add("Germany", 83124418, 357386);
_dataTable.Rows.Add("France", 64990511, 551695);
_dataTable.Rows.Add("Czech Republic", 10665677, 78866);
_dataTable.Rows.Add("Slovakia", 5459781, 49036);
_dataTable.Rows.Add("Spain", 47394223, 498468);
_dataTable.Rows.Add("Portugal", 10256193, 91568);
_dataTable.Rows.Add("United Kingdom", 67141684, 242495);
_dataTable.Rows.Add("Poland", 37921592, 312685);
_dataTable.Rows.Add("Albania", 2882740, 28748);
_dataTable.Rows.Add("Estonia", 1322920, 45339);
_dataTable.Rows.Add("Hungary", 9707499, 93030);
_dataTable.Rows.Add("Romania", 19186000, 238397);
_dataTable.Rows.Add("Italy", 60627291, 301338);
_dataTable.Rows.Add("Bulgaria", 7051608, 110994);
_dataTable.Rows.Add("Belarus", 9452617, 207600);
_dataTable.Rows.Add("Austria", 8891388, 83858);
_dataTable.Rows.Add("Switzerland", 8525611, 41290);
_dataTable.Rows.Add("Ireland", 4818690, 70273);
_dataTable.Rows.Add("Ukraine", 44246156, 603628);
_dataTable.Rows.Add("Iceland", 336713, 102775);
_dataTable.Rows.Add("Serbia", 6871547, 77453);
_dataTable.Rows.Add("Croatia", 4156405, 56594);
_dataTable.Rows.Add("Latvia", 1928459, 64589);
_dataTable.Rows.Add("Bosnia and Herzegovina", 3323925, 51129);
_dataTable.Rows.Add("Montenegro", 627809, 13812);
_dataTable.Rows.Add("Cyrprus", 1189265, 9251);
_dataTable.Rows.Add("Kosovo", 1798506, 10908);
_dataTable.Rows.Add("Slovenia", 2055496, 20273);
_dataTable.Rows.Add("Moldova", 4033963, 33846);
_dataTable.Rows.Add("North Macedonia", 2083374, 25713);
}
}
}
<table class="epplus-table ts-light1 ts-light1-header ts-light1-total ts-light1-row-stripes table table-sm" id="population-table" role="table" aria-label="Demo table" aria-describedby="table-description">
<thead role="rowgroup">
<tr role="row">
<th data-datatype="string" class="epp-s1" role="columnheader" scope="col">Country</th>
<th data-datatype="number" class="epp-s2" role="columnheader" scope="col" aria-sort="descending">Population</th>
<th data-datatype="number" class="epp-s2" role="columnheader" scope="col">Area (km2)</th>
<th data-datatype="number" class="epp-s2" role="columnheader" scope="col">Density</th>
</tr>
</thead>
<tbody role="rowgroup">
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Russia</td>
<td data-value="145734038" role="cell" class="epp-ar">145,734,038</td>
<td data-value="3972400" role="cell" class="epp-ar">3,972,400 km2</td>
<td data-value="36.68664736683113" role="cell" class="epp-ar">36.7</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Germany</td>
<td data-value="83124418" role="cell" class="epp-ar">83,124,418</td>
<td data-value="357386" role="cell" class="epp-ar">357,386 km2</td>
<td data-value="232.59002311226516" role="cell" class="epp-ar">232.6</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">United Kingdom</td>
<td data-value="67141684" role="cell" class="epp-ar">67,141,684</td>
<td data-value="242495" role="cell" class="epp-ar">242,495 km2</td>
<td data-value="276.87863254912475" role="cell" class="epp-ar">276.9</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">France</td>
<td data-value="64990511" role="cell" class="epp-ar">64,990,511</td>
<td data-value="551695" role="cell" class="epp-ar">551,695 km2</td>
<td data-value="117.80152258041127" role="cell" class="epp-ar">117.8</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Italy</td>
<td data-value="60627291" role="cell" class="epp-ar">60,627,291</td>
<td data-value="301338" role="cell" class="epp-ar">301,338 km2</td>
<td data-value="201.19364633733548" role="cell" class="epp-ar">201.2</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Spain</td>
<td data-value="47394223" role="cell" class="epp-ar">47,394,223</td>
<td data-value="498468" role="cell" class="epp-ar">498,468 km2</td>
<td data-value="95.07977041655633" role="cell" class="epp-ar">95.1</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Ukraine</td>
<td data-value="44246156" role="cell" class="epp-ar">44,246,156</td>
<td data-value="603628" role="cell" class="epp-ar">603,628 km2</td>
<td data-value="73.30037042681917" role="cell" class="epp-ar">73.3</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Poland</td>
<td data-value="37921592" role="cell" class="epp-ar">37,921,592</td>
<td data-value="312685" role="cell" class="epp-ar">312,685 km2</td>
<td data-value="121.27729823944225" role="cell" class="epp-ar">121.3</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Romania</td>
<td data-value="19186000" role="cell" class="epp-ar">19,186,000</td>
<td data-value="238397" role="cell" class="epp-ar">238,397 km2</td>
<td data-value="80.47920066108215" role="cell" class="epp-ar">80.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Netherlands</td>
<td data-value="17553530" role="cell" class="epp-ar">17,553,530</td>
<td data-value="41198" role="cell" class="epp-ar">41,198 km2</td>
<td data-value="426.07723675906595" role="cell" class="epp-ar">426.1</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Belgium</td>
<td data-value="11521238" role="cell" class="epp-ar">11,521,238</td>
<td data-value="30510" role="cell" class="epp-ar">30,510 km2</td>
<td data-value="377.6216978039987" role="cell" class="epp-ar">377.6</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Greece</td>
<td data-value="10718565" role="cell" class="epp-ar">10,718,565</td>
<td data-value="131940" role="cell" class="epp-ar">131,940 km2</td>
<td data-value="81.23817644383811" role="cell" class="epp-ar">81.2</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Czech Republic</td>
<td data-value="10665677" role="cell" class="epp-ar">10,665,677</td>
<td data-value="78866" role="cell" class="epp-ar">78,866 km2</td>
<td data-value="135.23796059138286" role="cell" class="epp-ar">135.2</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Sweden</td>
<td data-value="10409248" role="cell" class="epp-ar">10,409,248</td>
<td data-value="450295" role="cell" class="epp-ar">450,295 km2</td>
<td data-value="23.1165080669339" role="cell" class="epp-ar">23.1</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Portugal</td>
<td data-value="10256193" role="cell" class="epp-ar">10,256,193</td>
<td data-value="91568" role="cell" class="epp-ar">91,568 km2</td>
<td data-value="112.00630132797484" role="cell" class="epp-ar">112.0</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Hungary</td>
<td data-value="9707499" role="cell" class="epp-ar">9,707,499</td>
<td data-value="93030" role="cell" class="epp-ar">93,030 km2</td>
<td data-value="104.34804901644631" role="cell" class="epp-ar">104.3</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Belarus</td>
<td data-value="9452617" role="cell" class="epp-ar">9,452,617</td>
<td data-value="207600" role="cell" class="epp-ar">207,600 km2</td>
<td data-value="45.53283718689788" role="cell" class="epp-ar">45.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Austria</td>
<td data-value="8891388" role="cell" class="epp-ar">8,891,388</td>
<td data-value="83858" role="cell" class="epp-ar">83,858 km2</td>
<td data-value="106.02909680650623" role="cell" class="epp-ar">106.0</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Switzerland</td>
<td data-value="8525611" role="cell" class="epp-ar">8,525,611</td>
<td data-value="41290" role="cell" class="epp-ar">41,290 km2</td>
<td data-value="206.4812545410511" role="cell" class="epp-ar">206.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Bulgaria</td>
<td data-value="7051608" role="cell" class="epp-ar">7,051,608</td>
<td data-value="110994" role="cell" class="epp-ar">110,994 km2</td>
<td data-value="63.53143413157468" role="cell" class="epp-ar">63.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Serbia</td>
<td data-value="6871547" role="cell" class="epp-ar">6,871,547</td>
<td data-value="77453" role="cell" class="epp-ar">77,453 km2</td>
<td data-value="88.71892631660491" role="cell" class="epp-ar">88.7</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Denmark</td>
<td data-value="5850189" role="cell" class="epp-ar">5,850,189</td>
<td data-value="44493" role="cell" class="epp-ar">44,493 km2</td>
<td data-value="131.48560447710875" role="cell" class="epp-ar">131.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Finland</td>
<td data-value="5541806" role="cell" class="epp-ar">5,541,806</td>
<td data-value="338145" role="cell" class="epp-ar">338,145 km2</td>
<td data-value="16.3888450221059" role="cell" class="epp-ar">16.4</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Slovakia</td>
<td data-value="5459781" role="cell" class="epp-ar">5,459,781</td>
<td data-value="49036" role="cell" class="epp-ar">49,036 km2</td>
<td data-value="111.34229953503548" role="cell" class="epp-ar">111.3</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Norway</td>
<td data-value="5402171" role="cell" class="epp-ar">5,402,171</td>
<td data-value="385178" role="cell" class="epp-ar">385,178 km2</td>
<td data-value="14.025128641822741" role="cell" class="epp-ar">14.0</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Ireland</td>
<td data-value="4818690" role="cell" class="epp-ar">4,818,690</td>
<td data-value="70273" role="cell" class="epp-ar">70,273 km2</td>
<td data-value="68.57100166493532" role="cell" class="epp-ar">68.6</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Croatia</td>
<td data-value="4156405" role="cell" class="epp-ar">4,156,405</td>
<td data-value="56594" role="cell" class="epp-ar">56,594 km2</td>
<td data-value="73.44250273880624" role="cell" class="epp-ar">73.4</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Moldova</td>
<td data-value="4033963" role="cell" class="epp-ar">4,033,963</td>
<td data-value="33846" role="cell" class="epp-ar">33,846 km2</td>
<td data-value="119.18581220823731" role="cell" class="epp-ar">119.2</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Bosnia and Herzegovina</td>
<td data-value="3323925" role="cell" class="epp-ar">3,323,925</td>
<td data-value="51129" role="cell" class="epp-ar">51,129 km2</td>
<td data-value="65.010561520859" role="cell" class="epp-ar">65.0</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Albania</td>
<td data-value="2882740" role="cell" class="epp-ar">2,882,740</td>
<td data-value="28748" role="cell" class="epp-ar">28,748 km2</td>
<td data-value="100.27619312647836" role="cell" class="epp-ar">100.3</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Lithuania</td>
<td data-value="2801264" role="cell" class="epp-ar">2,801,264</td>
<td data-value="65300" role="cell" class="epp-ar">65,300 km2</td>
<td data-value="42.898376722817765" role="cell" class="epp-ar">42.9</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">North Macedonia</td>
<td data-value="2083374" role="cell" class="epp-ar">2,083,374</td>
<td data-value="25713" role="cell" class="epp-ar">25,713 km2</td>
<td data-value="81.02415120756038" role="cell" class="epp-ar">81.0</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Slovenia</td>
<td data-value="2055496" role="cell" class="epp-ar">2,055,496</td>
<td data-value="20273" role="cell" class="epp-ar">20,273 km2</td>
<td data-value="101.39081537019682" role="cell" class="epp-ar">101.4</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Latvia</td>
<td data-value="1928459" role="cell" class="epp-ar">1,928,459</td>
<td data-value="64589" role="cell" class="epp-ar">64,589 km2</td>
<td data-value="29.85739057734289" role="cell" class="epp-ar">29.9</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Kosovo</td>
<td data-value="1798506" role="cell" class="epp-ar">1,798,506</td>
<td data-value="10908" role="cell" class="epp-ar">10,908 km2</td>
<td data-value="164.8795379537954" role="cell" class="epp-ar">164.9</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Estonia</td>
<td data-value="1322920" role="cell" class="epp-ar">1,322,920</td>
<td data-value="45339" role="cell" class="epp-ar">45,339 km2</td>
<td data-value="29.17841152208915" role="cell" class="epp-ar">29.2</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Cyrprus</td>
<td data-value="1189265" role="cell" class="epp-ar">1,189,265</td>
<td data-value="9251" role="cell" class="epp-ar">9,251 km2</td>
<td data-value="128.5552913198573" role="cell" class="epp-ar">128.6</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Montenegro</td>
<td data-value="627809" role="cell" class="epp-ar">627,809</td>
<td data-value="13812" role="cell" class="epp-ar">13,812 km2</td>
<td data-value="45.45388068346365" role="cell" class="epp-ar">45.5</td>
</tr>
<tr role="row" scope="row">
<td role="cell" class="epp-s1">Iceland</td>
<td data-value="336713" role="cell" class="epp-ar">336,713</td>
<td data-value="102775" role="cell" class="epp-ar">102,775 km2</td>
<td data-value="3.276215032838725" role="cell" class="epp-ar">3.3</td>
</tr>
<tfoot></tfoot>
</tbody>
<tfoot role="rowgroup"> <tr role="row" scope="row">
<td role="cell" class="epp-s1">Total</td>
<td role="cell" class="epp-ar">747,604,110</td>
<td role="cell" class="epp-ar">9,932,496 km2</td>
<td role="cell" class="epp-ar">Avg: 111.1 </td>
</tr>
</tfoot>
</table>