XLSX file is a Microsoft Excel Open XML Format Spreadsheet file.
Export XLSX file from a LWC with multiples worksheets and style.
No static resources, easy to change, just create a table and export to a file.
This code will export a XML file that is only interpreted by a Microsoft Excel.
You can open the file and Save As another type and Microsoft Excel will automatically convert to a new XLS file.
The message that is shown when you open this type of file are always displayed, unless you Save As this file with another type.
The File format and extension of 'filename.xls' don't match. The file could be corrupted or unsage. Unless you trust its source, don't open it. Do you want to open it anyway?
- About
- Table of Contents
- Microsoft Documentation
- References
- How to use
- Attributes
- Tips
- How to corrupt your file
- Deploy and Test
Here are Microsoft Documentation about all XML tags that we can use to export a XLSX file format:
These are my references about the code base that I'm using:
This JSFiddle project guided me to understand how can I export a XML file that Excel can read and interprete.
I adapted this code to work with Salesforce LWC
.
Let's review the export_example_opportunity
LWC:
<!-- table with lwc:ref -->
<table lwc:ref="opportunityTable">
...
</table>
// import export_xlsx LWC
import { exportXLSX } from 'c/export_xlsx';
// return config about this component to export the file
// @api is only required if you want to a parent to export children components
// each structure of "config" is a new worksheet inside the file
@api
getXLSConfig(){
return {
table: this.refs.opportunityTable,
tabName: 'Opportunity',
displayGrid: true,
zoom: 120,
style: this.getXLSStyle()
};
}
// return <Style> tags for this component
getXLSStyle(){
return `
<Style ss:ID="o-default">
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:Size="12" ss:FontName="Arial"/>
<Borders>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
</Borders>
</Style>
<Style ss:ID="o-col" ss:Parent="o-default">
<Font ss:Size="12" ss:FontName="Arial" ss:Bold="1"/>
<Interior ss:Color="#bfbf67" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="o-value" ss:Parent="o-default">
<Interior ss:Color="#e0e0e0" ss:Pattern="Solid"/>
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
</Style>
<Style ss:ID="o-value-money" ss:Parent="o-default">
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<NumberFormat ss:Format="Currency"></NumberFormat>
<Interior ss:Color="#e0e0e0" ss:Pattern="Solid"/>
</Style>
`;
}
// handle click button
handleExport(){
let config = new Array();
config.push(this.getXLSConfig());
// export the file
exportXLSX(config, this.opportunity.Name);
}
For fixed tables LWC .html file, you can use lwc:ref
:
<table lwc:ref="opportunityTable">
getXLSConfig(){
...
this.refs.opportunityTable
}
For dynamic tables LWC .js file, you can use id
:
<table id="dynamicTable"></table>
getXLSConfig()(){
...
this.template.querySelector("#dynamicTable")
}
All attributes are optional.
Tag | Description | Additional information |
---|---|---|
data-xls-style | ss:ID of the XML Style |
Only one per html tag |
data-xls-type | Type of value | Number/DateTime/Boolean/String |
data-xls-value | Value to export | e.g: use this attribute to show formatted values in LWC but export only numbers to XLSX file If blank will use innerHTML of <td> html tag |
colspan | HTML default tag | Used to merge horizontal cells |
rowspan | HTML default tag | Used to merge vertical cells |
getXLSConfig()
Attribute | Type | Default value | Description |
---|---|---|---|
tabName | $(table) | - | LWC reference table |
displayGrid | boolean | true | Show gridlines |
zoom | Decimal | 100 | Worksheet zoom |
style | String (Array of <Style> ) |
'' |
String with an Array of <Style> |
columns | Array<{width : Decimal, hidden : boolean}> |
[] | Array of column definition If you use this config, you need to specify exact number of columns |
You need to create your HTML table as an Excel spreadsheet, considering each cell (rows and columns).
You can merge columns using HTML colspan
and rowspan
default tag.
<table id="myTable">
<tr>
<td rowspan="3">A</td>
<td rowspan="2">B</td>
<td>C</td>
<td colspan="2">D</td>
<td>E</td>
</tr>
<tr>
<td>3</td>
<td>4</td>
<td>5</td>
<td>6</td>
</tr>
<tr>
<td>8</td>
<td>9</td>
</tr>
</table>
You can use your CSS to style your LWC but for export this LWC you need to convert this CSS to <Style>
specific tags for XLSX, e.g:
LWC .css
.o-default{
text-align: center;
font-size: 12px;
background-color: #e0e0e0;
border: 1px solid;
}
you need to convert to:
LWC .js
<Style ss:ID="o-default">
<Alignment ss:Horizontal="Center"/>
<Font ss:Size="12"/>
<Interior ss:Color="#e0e0e0" ss:Pattern="Solid"/>
<Borders>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
</Borders>
</Style>
XLSX cannot accept more than one <Style>
tag, you need to create a <Style>
tag using ss:Parent="parent-name"
attribute to inherit parent styles.
<Style ss:ID="o-default">
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:Size="12" ss:FontName="Arial"/>
</Style>
<Style ss:ID="o-col" ss:Parent="o-default">
<Font ss:Size="12" ss:FontName="Arial" ss:Bold="1"/>
<Interior ss:Color="#bfbf67" ss:Pattern="Solid"/>
</Style>
You can use export_example_opportunity.js
/ export_example_products.js
getXLSStyle()
function as example to export <Style>
tags.
You can check all style tags from Microsoft Documentation.
You can use the Empty file to create all your styles tags, just open the empty file and define all styles that you want, save it, and open this file with a text editor to check the styles that Microsoft Excel automatically created, copy and paste to your code and it's done.
XML file is sensitivity and will corrupt if something is wrong.
- create your HTML table as an excel file, with the same number of rows and columns
- create
<Style>
tag for all that you're referecing in your htmldata-xls-style
tag - create
<Style>
with a specific name for each LWC that you want to export (e.g apply some name before:o-
for opportunity LWC andp-
products LWC)
- export more than once the same name for
<Style ss:ID="">
- export undefined values
- break values/lines inside
<td>
html tag - use specific html tags such as:
<hr>
<br>
starting clonning the repository
git clone https://github.com/jvaloto/export-lwc-to-xlsx.git
access repository folder
cd export-lwc-to-xlsx
deploy project to Salesforce
sf project deploy start -o your_org_alias
assign permission set to your user
sf org assign permset -n Export_example_LWC -o your_org_alias
Search for Export LWC
tab
You can export individual files or export tables to a single file using parent children LWC