About

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.

Attention

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?

Table of Contents

• About
  • Attention
• Table of Contents
• Microsoft Documentation
• References
• How to use
  • Get HTML table
    • Using fixed HTML table
    • Using backend dynamic table
• Attributes
  • HTML XLS
  • JSON
• Tips
  • Code
  • Style
    • Converting
    • One <Style> tag per HTML <td>
    • Using a XLSX file to create style
• How to corrupt your file
  • Should
  • Shouldn't
• Deploy and Test

Microsoft Documentation

Here are Microsoft Documentation about all XML tags that we can use to export a XLSX file format:

• XML in Excel and the Spreadsheet Component
• XML Spreadsheet Reference

References

These are my references about the code base that I'm using:

• Question on Stack Overflow
• JSFiddle project

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.

How to use

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);
}

Get HTML table

Using fixed HTML table

For fixed tables LWC .html file, you can use lwc:ref:

<table lwc:ref="opportunityTable">

getXLSConfig(){
    ...
    this.refs.opportunityTable
}

Using backend dynamic table

For dynamic tables LWC .js file, you can use id:

<table id="dynamicTable"></table>

getXLSConfig()(){
    ...
    this.template.querySelector("#dynamicTable")
}

Attributes

HTML XLS

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

JSON

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

Tips

Code

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>

Style

Converting

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>

One <Style> tag per HTML <td>

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.

Using a XLSX file to create style

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.

How to corrupt your file

XML file is sensitivity and will corrupt if something is wrong.

Should

• 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 html data-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 and p- products LWC)

Shouldn't

• 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>

Deploy and Test

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