-
Notifications
You must be signed in to change notification settings - Fork 275
HTML Export
EPPlus 6 introduces the ability to export a table or a range to HTML/CSS. The best way to get started with this functionality is to have a look at the examples in our web samples project
This feature supports integration of spreadsheet data and styling into web applications - and it is not an implementation of Excel's "Save the workbook as html" feature. Our intention is to make EPPlus a useful tool for bringing the spreadsheet model to web applications rather than providing a pixel-perfect html-representation of an existing workbook.
The spreadsheet data is always exported as a html table. The html has support for accessibility (aria-*) attributes that can be configured when exporting the data. You can also get the raw cell data and javascript compatible datatypes included via data-* attributes in order to use the exported table not only as a UI element but also as a data carrier.
EPPlus separates styling from html and creates a separate stylesheet (css) for the exported data. The classes in this stylesheet are created based on the internal Excel styles. This is per default based on the built in Office theme, but you can easily switch to another theme as demonstrated our html sample 1. EPPlus will export css for table styles when exporting a table. NOTE: Exporting a range that contains a table will not include the table styles in the css.
Images in the cells will be included by default as base64 encoded strings in the stylesheet. Images are per default not included in the export, for more details see this chapter.
We will add new features to this export over time, but please note that some Excel features such as charts, shapes, pivot tables, sparklines and threaded comments are included in the export. Full support for conditional formatting was added in version 7.2.
You can export html/css either from a range or from a a table in your workbook. This is done via the CreateHtmlExporter()
function, see below:
Please note that table styles are not included in the css, if you export a range that contains a table via the ExcelRangeBase.CreateHtmlExporter()
.
var exporter = sheet.Cells["B5:M19"].CreateHtmlExporter();
var html = exporter.GetHtmlString();
var css = exporter.GetCssString();
This export will contain the css for the table style as well as any cell style css.
var exporter = sheet.Tables[0].CreateHtmlExporter();
var html = exporter.GetHtmlString();
var css = exporter.GetCssString();
You can also create an exporter for several ranges at once to get a shared css for multiple ranges over multiple worksheets. Our web sample 6 shows how to do this in detail.
var exporter = package.Workbook.CreateHtmlExporter(
package.Workbook.Worksheet[0].Cells["B5:M19"],
package.Workbook.Worksheet[1].Cells["A1:D35"]
);
//This will get the html for the first range, B5:M19 in the first sheet
var html1 = exporter.GetHtmlString(0);
//This will get the html for the first range, A1:D35 in the second sheet
var html2 = exporter.GetHtmlString(1);
//This gets the combined css for both ranges.
var css = exporter.GetCssString();
There are async versions of GetHtmlString()
and GetCssString()
, suffixed with "Async". Other methods renders html and css on a System.IO.Stream
, see RenderHtml(stream)
, RenderHtmlAsync(stream)
, RenderCss(stream)
and RenderCssAsync(stream)
.
Use the properties below to set the id attribute and add additional css classes on the exported html table.
var settings = exporter.Settings;
settings.TableId = "currency-table";
settings.AdditionalTableClassNames.Add("table");
settings.AdditionalTableClassNames.Add("table-sm");
settings.AdditionalTableClassNames.Add("table-borderless");
Via the Settings property on the exporter you can control how the html is exported. This class has the following properties:
Property | Data type | Default value | Description |
---|---|---|---|
TableId |
string |
null |
id of the table html element |
Minify |
bool |
true |
If true the html will not contain indents and linebreaks. |
HiddenRows |
eHiddenState |
Exclude |
Controls how hidden cells are exported. One of the following: Exclude , IncludeButHide , Include
|
HorizontalAlignmentWhenGeneral |
eHtmlGeneral-AlignmentHandling | CellDataType | Controls alignment when a cells style is set to General. One of the following: DontSet , ColumnDataType (if the cell value data type is numeric or date, alignment will be right otherwise left), CellDataType (if the cell value data type is numeric or date, alignment will be right otherwise left) |
Accessibility |
AccessibilitySettings |
N/A | See Accessibility attributes below |
AdditionalTableClassNames |
List<string> |
The list is empty | Here you can add additional class names that will be added to the exported tables class attribute |
Culture |
CultureInfo |
CurrentCulture | The culture used when formatting the cell output. |
Encoding |
Encoding |
Encoding.UTF8 |
Encoding for the output |
SetColumnWidth |
bool |
false |
Set the column width for columns in the table via the columngroup/col element. Columns with the default width will have the default column width class set. Columns with custom column width will have the width set directly via the style attribute. |
SetRowHeight |
bool |
false |
Set the row height for rows in the table. Rows with the default height will have the default row height class set. Rows with custom row height will have the height set directly via the style attribute. |
StyleClassPrefix |
string |
epp- | Prefix for style classes added by EPPlus. |
CellStyleClassName |
string |
s | The name used for cell style css classes. Cell style css classes will be named {StyleClassPrefix}{CellStyleClassName}{index}
|
IncludePictures |
bool |
false |
If pictures in cells should be included |
IncludeCssClassNames |
bool |
true |
If true the exported html will include css class names that refers to exported css. |
RenderDataAttributes |
bool |
true |
If true raw cell data values will be included in the data-value attribute |
RenderDataTypes |
bool |
true |
If true data types will be included in the data-datatype attribute |
Settings controling the css output.
Property | Data type | Default value | Description |
---|---|---|---|
CssExclude |
CssExclude |
Properties to exclude specific styles from the css. | |
AdditionalCssElements |
Dictionary<string,string> |
empty | Add additional css elements to the default table style. For example exporter.Settings.Css.AdditionalCssElements.Add("font-family", "verdana");
|
IncludeSharedClasses |
bool |
true |
If the css should include shared styles used for the exported table. |
IncludeNormalFont |
bool |
true |
If the normal font name and size should be included in the css for the table. |
IndentValue |
float |
2 | The value for indented cells. |
IndentUnit |
string |
em | The unit for indented cells. |
Pictures/images are not exported per default, but this can be enabled like this:
exporter.Settings.Pictures.Include = ePictureInclude.Include;
The ePictureInclude
enum has three members: Exclude
(pictures will not be included), IncludeInCssOnly
(pictures will be included in css only, so they can be added manually) and Include
.
Here are the parameters you can set via the Exporter.Settings.Picture property.
Property | Data type | Default value | Description |
---|---|---|---|
Include |
ePictureInclude |
Exclude |
Sets if and how pictures should be included. |
Position |
ePicturePosition |
Relative |
Possible values: DontSet , Absolute or Relative
|
AddMarginTop |
bool |
false |
If the margin in pixels from the left corner should be used. |
AddMarginLeft |
bool |
false |
If the margin in pixels from the top corner should be used |
KeepOriginalSize |
bool |
false |
If set to true the original size of the image is used, otherwise the size in the workbook is used |
CssExclude |
PictureCssExclude |
N/A | With this property you can exclude certain parts from the exported css, such as border and alignment |
In html you can add custom attributes that is prefixed with "data-". In EPPlus we have added two attributes to the exported table elements: data-value and data-datatype.
Our web sample 4 demonstrates how these attributes can be used.
This attribute is added either on the table.thead.tr.th element or on the table.tbody.tr.td element. See the table below for how the .NET datatypes from EPPlus are mapped to the exported html.
.NET data type | Exported datatype name | Comment |
---|---|---|
bool |
boolean | Exported as 1/0 |
Byte , Sbyte , UInt16 , UInt32 , UInt64 , Int16 , Int32 , Int64 , Decimal , Double , Single (float) |
number | All types mapped to the single number datatype |
DateTime |
datetime | Exported to milliseconds before/after January 1, 1970 (Linux/Unix epoch). |
TimeSpan |
timespan | Exported as a numeric value, total milliseconds after midnight |
string (and all other datatypes) |
string | Exported as-is |
See below how the data-datatype attribute is included in the html of an exported table.
<thead role="rowgroup">
<tr role="row">
<th data-datatype="string" class="epp-al" role="columnheader" scope="col">Country</th>
<th data-datatype="string" class="epp-al" role="columnheader" scope="col">FirstName</th>
<th data-datatype="string" class="epp-al" role="columnheader" scope="col">LastName</th>
<th data-datatype="datetime" class="epp-al" role="columnheader" scope="col">BirthDate</th>
<th data-datatype="string" class="epp-al" role="columnheader" scope="col">City</th>
</tr>
</thead>
This attribute will contain the raw value of the cell, see the datatypes table above. Here is an example:
<tr role="row" scope="row">
<td role="cell">Scotland</td>
<td role="cell">Autumn</td>
<td role="cell">Toy</td>
<td data-value="-1485302400000" role="cell" class="epp-ar">1922-12-08</td>
<td role="cell">New Andrewhaven</td>
</tr>
Note that this attribute is only present if the raw value differs from the formatted value (the content of the td element).
Accessibility attributes are included by default. Here is an example on how to set the aria-label attribute on the exported table:
var exporter = sheet.Cells["B5:M19"].CreateHtmlExporter();
exporter.Settings.Accessibility.TableSettings.AriaLabel = "This html-table is exported from EPPlus";
results in:
<table class="epplus-table" role="table" aria-label="This html-table is exported from EPPlus">
If you don't want to include accessibility attributes in the html set the below property to false:
exporter.Settings.Accessibility.TableSettings.AddAccessibilityAttributes = false;
Via the exporter's Settings.Accessibility.TableSettings
property you can set the following attributes:
EPPlus attribute | Html attribute | Html element | Default value |
---|---|---|---|
AriaLabel |
aria-label | table | null |
AriaLabelledBy |
aria-labelledby | table | null |
AriaDescribedBy |
aria-describedby | table | null |
TableRole |
role | table | table |
TheadRole |
role | table.thead | rowgroup |
TbodyRole |
role | table.tbody | rowgroup |
TfootRole |
role | table.tfoot | rowgroup |
TableHeaderCellRole |
role | table.thead.tr.th | columnheader |
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles