iam unable to export table in excel format with following code in LWC (Salesforce) #3040

New Issue

Closed

opened 2023-12-03 16:30:51 +00:00 by murali · 6 comments

murali commented 2023-12-03 16:30:51 +00:00

Copy Link

HTML:

			</tr>
			<tr>
				<td class="slds-text-align_center slds-text-heading_medium" colspan="3">Actual Change in</td>
				<td class="slds-text-align_center slds-text-heading_medium">% Change in the </td>
				<td> </td>
				<td class="slds-text-align_center slds-text-heading_medium">% Change in the</td>
				<td class="slds-text-align_center slds-text-heading_medium" colspan="3">Actual Change in </td>
			</tr>
			<tr>
				<td class="slds-text-align_center slds-text-heading_medium">HDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">CDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">TDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">TDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">Month</td>
				<td class="slds-text-align_center slds-text-heading_medium">TDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">HDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">CDD</td>
				<td class="slds-text-align_center slds-text-heading_medium">TDD</td>
			</tr>	
            <template for:each={details} for:item="wsRec">
                <tr key={wsRec}>
                    <td class="slds-text-align_center">{wsRec.actualHDD} {wsRec.percentage}</td>
                    <td class="slds-text-align_center">{wsRec.actualCDD} {wsRec.percentage}</td>
                    <td class="slds-text-align_center">{wsRec.actualTDD} {wsRec.percentage}</td>
                    <td class="slds-text-align_center">{wsRec.actualPerTDD} %</td>
                    <td class="slds-text-align_center slds-text-heading_medium">{wsRec.month}</td>
					<td class="slds-text-align_center">{wsRec.normalPerTDD}%</td>
                    <td class="slds-text-align_center">{wsRec.normalHDD} {wsRec.percentage}</td>
                    <td class="slds-text-align_center">{wsRec.normalCDD} {wsRec.percentage}</td>
                    <td class="slds-text-align_center">{wsRec.normalTDD} {wsRec.percentage}</td>
                    
                </tr>
            </template>
		</tbody>
	</table>
  </div>
</template>

{weatherStationName} Weather Station
Actual & Percent Variation in Heating, Cooling & Total Degree Days
From Year 2022 to Year 2023					From NOAA "Normal" to Year 2023

Java ScriptCode:

import { LightningElement, api, wire,track } from 'lwc';
import getWSAPVariation from "@salesforce/apex/WeatherStationReport.getWSAPReport";
import XLSX from '@salesforce/resourceUrl/SheetJS';
import { loadScript, loadStyle } from 'lightning/platformResourceLoader';
import TabularCSS from '@salesforce/resourceUrl/tabulatorCSS';
import TabularJS from '@salesforce/resourceUrl/tabulatorJS';
export default class WsActualPercentVariationReport extends LightningElement {
@api weatherStationName;
@track isFound = false;
details = [];

@wire(getWSAPVariation, {weatherStationName: '$weatherStationName'})
retrievedData({error, data}){
    if(data){
        this.details = data;
        this.isFound = true;
    } else if(error){
        console.log('error: ', error);
    }
}

async connectedCallback() {
    await loadStyle(this,TabularCSS);
     await loadScript(this, TabularJS);
    await loadScript(this, XLSX); // load the library
    // At this point, the library is accessible with the `XLSX` variable
    this.version = XLSX.version;
    console.log('version: '+this.version);      
}
  exportData() {
   const table = this.template.querySelector('[data-id="myTable"]');
    if (table){
         console.log('entered');
        const ws = XLSX.utils.table_to_sheet(table);
        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
        XLSX.writeFile(wb, "output.xlsx");
    }else{
         console.log('didnt get referrence'); 
    }
}

}

**HTML:** <template> <template if:true={error}> <lightning-formatted-rich-text value={error}></lightning-formatted-rich-text> </template> <template if:true={isFound}> <div class="slds-theme_default" style="overflow: overlay"> <lightning-button label="Export" title="Export" onclick={exportData}></lightning-button> <table class="table table-bordered table-hover table-condensed" border="1" data-id="myTable"> <thead> <tr> <td class="slds-card__body slds-card__body_inner slds-text-align_center slds-text-heading_medium" colspan="9" title="Field #1">{weatherStationName} Weather Station</td> </tr> <tr> <td class="slds-card__body slds-card__body_inner slds-text-align_center slds-text-heading_medium color-text-brand" colspan="9">Actual &amp; Percent Variation in Heating, Cooling &amp; Total Degree Days</td> </tr> </thead> <tbody> <tr> <td class="slds-text-align_center slds-text-heading_medium" colspan="4">From Year 2022 to Year 2023</td> <td></td> <td class="slds-text-align_center slds-text-heading_medium" colspan="4">From NOAA &quot;Normal&quot; to Year 2023</td> </tr> <tr> <td class="slds-text-align_center slds-text-heading_medium" colspan="3">Actual Change in</td> <td class="slds-text-align_center slds-text-heading_medium">% Change in the </td> <td> </td> <td class="slds-text-align_center slds-text-heading_medium">% Change in the</td> <td class="slds-text-align_center slds-text-heading_medium" colspan="3">Actual Change in </td> </tr> <tr> <td class="slds-text-align_center slds-text-heading_medium">HDD</td> <td class="slds-text-align_center slds-text-heading_medium">CDD</td> <td class="slds-text-align_center slds-text-heading_medium">TDD</td> <td class="slds-text-align_center slds-text-heading_medium">TDD</td> <td class="slds-text-align_center slds-text-heading_medium">Month</td> <td class="slds-text-align_center slds-text-heading_medium">TDD</td> <td class="slds-text-align_center slds-text-heading_medium">HDD</td> <td class="slds-text-align_center slds-text-heading_medium">CDD</td> <td class="slds-text-align_center slds-text-heading_medium">TDD</td> </tr> <template for:each={details} for:item="wsRec"> <tr key={wsRec}> <td class="slds-text-align_center">{wsRec.actualHDD} {wsRec.percentage}</td> <td class="slds-text-align_center">{wsRec.actualCDD} {wsRec.percentage}</td> <td class="slds-text-align_center">{wsRec.actualTDD} {wsRec.percentage}</td> <td class="slds-text-align_center">{wsRec.actualPerTDD} %</td> <td class="slds-text-align_center slds-text-heading_medium">{wsRec.month}</td> <td class="slds-text-align_center">{wsRec.normalPerTDD}%</td> <td class="slds-text-align_center">{wsRec.normalHDD} {wsRec.percentage}</td> <td class="slds-text-align_center">{wsRec.normalCDD} {wsRec.percentage}</td> <td class="slds-text-align_center">{wsRec.normalTDD} {wsRec.percentage}</td> </tr> </template> </tbody> </table> </div> </template> </template> **Java ScriptCode:** import { LightningElement, api, wire,track } from 'lwc'; import getWSAPVariation from "@salesforce/apex/WeatherStationReport.getWSAPReport"; import XLSX from '@salesforce/resourceUrl/SheetJS'; import { loadScript, loadStyle } from 'lightning/platformResourceLoader'; import TabularCSS from '@salesforce/resourceUrl/tabulatorCSS'; import TabularJS from '@salesforce/resourceUrl/tabulatorJS'; export default class WsActualPercentVariationReport extends LightningElement { @api weatherStationName; @track isFound = false; details = []; @wire(getWSAPVariation, {weatherStationName: '$weatherStationName'}) retrievedData({error, data}){ if(data){ this.details = data; this.isFound = true; } else if(error){ console.log('error: ', error); } } async connectedCallback() { await loadStyle(this,TabularCSS); await loadScript(this, TabularJS); await loadScript(this, XLSX); // load the library // At this point, the library is accessible with the `XLSX` variable this.version = XLSX.version; console.log('version: '+this.version); } exportData() { const table = this.template.querySelector('[data-id="myTable"]'); if (table){ console.log('entered'); const ws = XLSX.utils.table_to_sheet(table); const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "output.xlsx"); }else{ console.log('didnt get referrence'); } } }

sheetjs commented 2023-12-05 03:59:17 +00:00

Owner

Copy Link

Which part is failing? Does the Salesforce LWC demo (https://docs.sheetjs.com/docs/demos/cloud/salesforce) work for you?

Which part is failing? Does the Salesforce LWC demo (https://docs.sheetjs.com/docs/demos/cloud/salesforce) work for you?

murali commented 2023-12-08 06:04:06 +00:00

Author

Copy Link

The compiler returning at this line
const ws = XLSX.utils.table_to_sheet(table);

Is it possible to export the HTML table to excel file in LWC?

The compiler returning at this line const ws = XLSX.utils.table_to_sheet(table); Is it possible to export the HTML table to excel file in LWC?

sheetjs commented 2023-12-08 06:49:04 +00:00

Owner

Copy Link

It may be a LWC limitation. We'll take another look when we revisit the salesforce demo

https://docs.sheetjs.com/docs/demos/cloud/salesforce#exporting-data-from-sf-list It might be easier to directly inspect and export the captured data (details in your example). Read the linked section of the demo to see how to transform the data

It may be a LWC limitation. We'll take another look when we revisit the salesforce demo https://docs.sheetjs.com/docs/demos/cloud/salesforce#exporting-data-from-sf-list It might be easier to directly inspect and export the captured data (`details` in your example). Read the linked section of the demo to see how to transform the data

murali commented 2023-12-08 08:45:13 +00:00

Author

Copy Link

Thank you for your prompt response.

Is it possible to export data in given excel file format (with styles) by using community edition of sheetJS?. Can you please refer the attachment and confirm

Thank you for your prompt response. Is it possible to export data in given excel file format (with styles) by using community edition of sheetJS?. Can you please refer the attachment and confirm

Actual & Percent Variation.xlsx

11 KiB

dan.cojocari commented 2024-01-16 11:27:12 +00:00

Copy Link

Hello @murali,

Are you able to download file but in a format other than excel or you are not able to download file at all?

I'm having this issue currently.

I have some LWCs used in a community which are rendering some tables with data. I'm able to download table contents, but it does not work all the time.

It works well in Development and Testing environments, but not in Production.
I noticed it does not work for in Chrome and MS Edge browsers in normal window, but it is working in Incognito window.
I tried in Firefox and it works well.

For other developers in my team it works well in all browsers.

I tried to clear cache of the browsers, relaunch them and even set back to default settings but it still does not download the file in right format.

It is downloading a weird file with a weird system name (e.g. bc563f4c-de88-4ad8-8e31-e16a5808f361).

Most likely it's not related to the library, because it's working for other team members, but any idea to what it could be related? @sheetjs

Hello @murali, Are you able to download file but in a format other than excel or you are not able to download file at all? I'm having this issue currently. I have some LWCs used in a community which are rendering some tables with data. I'm able to download table contents, but it does not work all the time. It works well in Development and Testing environments, but not in Production. I noticed it does not work for in Chrome and MS Edge browsers in normal window, but it is working in Incognito window. I tried in Firefox and it works well. For other developers in my team it works well in all browsers. I tried to clear cache of the browsers, relaunch them and even set back to default settings but it still does not download the file in right format. It is downloading a weird file with a weird system name (e.g. bc563f4c-de88-4ad8-8e31-e16a5808f361). Most likely it's not related to the library, because it's working for other team members, but any idea to what it could be related? @sheetjs

image.png

3.5 KiB

Capture.PNG

2.6 KiB

sheetjs commented 2024-05-05 04:56:35 +00:00

Owner

Copy Link

Retested the LWC demo against version 59.0 and it appears to work as expected. That example exports a SF list to SheetForceExport.xlsx

---

To test the HTML table case:

1. Follow https://docs.sheetjs.com/docs/demos/cloud/salesforce and produce the final component and page.

2. Replace force-app/main/default/lwc/sheetComponent/sheetComponent.html with the following:

<template>
	<table class="sheetjs-export"><tr><td>SheetJS LWC Export HTML Table</td></tr></table>
  <button onclick={download}><b>Click to Export!</b></button>
</template>

(it has a simple HTML table with a known class)

3. Replace force-app/main/default/lwc/sheetComponent/sheetComponent.js with the following:

import { LightningElement, api } from 'lwc';
import { loadScript } from 'lightning/platformResourceLoader';

import sheetjs from '@salesforce/resourceUrl/sheetjs';

export default class SheetComponent extends LightningElement {
  @api async download() {
    await loadScript(this, sheetjs); // load the library
    const elt = this.template.querySelector(".sheetjs-export");
    console.log(elt);
    var wb = XLSX.utils.table_to_book(elt);
    XLSX.writeFile(wb, "SheetForceExport.xlsx");
  };
}

4. Deploy the project again. Replace SF@USER.NAME with the unique Username:

npx @salesforce/cli project deploy start -d force-app -o SF@USER.NAME

5. Refresh the page and click the button to create the new file.

Note how the table is identified. The assigned ID in the component HTML may be changed by LWC, so the recommended approach involves other DOM properties (e.g. class names or data- attributes on the TABLE element)

---

@dan.cojocari there are known issues where Chrome may mangle the name of the export. As the library uses standard browser APIs to initiate a download, it is unclear how to address the issue without using nonstandard browser behaviors.

Retested the LWC demo against version 59.0 and it appears to work as expected. That example exports a SF list to `SheetForceExport.xlsx` --- To test the HTML table case: 1) Follow https://docs.sheetjs.com/docs/demos/cloud/salesforce and produce the final component and page. 2) Replace `force-app/main/default/lwc/sheetComponent/sheetComponent.html` with the following: ```html <template> <table class="sheetjs-export"><tr><td>SheetJS LWC Export HTML Table</td></tr></table> <button onclick={download}><b>Click to Export!</b></button> </template> ``` (it has a simple HTML table with a known class) 3) Replace `force-app/main/default/lwc/sheetComponent/sheetComponent.js` with the following: ```js import { LightningElement, api } from 'lwc'; import { loadScript } from 'lightning/platformResourceLoader'; import sheetjs from '@salesforce/resourceUrl/sheetjs'; export default class SheetComponent extends LightningElement { @api async download() { await loadScript(this, sheetjs); // load the library const elt = this.template.querySelector(".sheetjs-export"); console.log(elt); var wb = XLSX.utils.table_to_book(elt); XLSX.writeFile(wb, "SheetForceExport.xlsx"); }; } ``` 4) Deploy the project again. Replace `SF@USER.NAME` with the unique Username: ```bash npx @salesforce/cli project deploy start -d force-app -o SF@USER.NAME ``` 5) Refresh the page and click the button to create the new file. Note how the table is identified. The assigned ID in the component HTML may be changed by LWC, so the recommended approach involves other DOM properties (e.g. class names or `data-` attributes on the TABLE element) --- @dan.cojocari there are known issues where Chrome may mangle the name of the export. As the library uses standard browser APIs to initiate a download, it is unclear how to address the issue without using nonstandard browser behaviors.

sheetjs closed this issue 2024-05-05 04:56:35 +00:00

sheetjs referenced this issue from a commit 2024-05-05 16:06:35 +00:00

LWC Refresh (see SheetJS/sheetjs#3040)

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.20.3

v0.20.2

v0.20.1

v0.20.0

v0.19.3

v0.19.2

v0.19.1

v0.19.0

v0.18.12

v0.18.11

v0.18.10

v0.18.9

v0.18.8

v0.18.7

v0.18.5

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0

v0.17.5

v0.17.4

v0.17.3

v0.17.2

v0.17.1

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.1

v0.12.0

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.11

v0.7.10

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.1

v0.5.10

v0.5.9

v0.5.8

v0.5.7

v0.5.0

v0.4.3

Labels

Clear labels   

DBF

  

Dates

  

Defined Names

  

Features

  

Formula

  

HTML

  

Images

  

Infrastructure

  

Integration

  

International

  

ODS

  

Operations

  

Performance

  

PivotTables

  

Pro

  

Protection

  

Read Bug

  

SSF

  

SYLK

  

Style

  

Write Bug

  

good first issue

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

3 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#3040

No description provided.