Exporting data to Excel is a very common requirement in Salesforce. While many solutions rely on third-party libraries but using Apex and LWC we can generate a fully functional .xlsx file.
In this blog, you will learn:
- How XLSX file structure works internally
- How to generate Excel using Apex LWC
- How to package it using native ZIP
- How to download it using LWC
Key Concept
An .xlsx file is not a single file — it is actually a ZIP archive containing multiple XML files.
XML Files → ZIP Packaging → Base64 → Browser Download → Excel Opens
XLSX File Structure (Important)
.xlsx (ZIP)
│
├── [Content_Types].xml → Defines file types
├── _rels/
│ └── .rels → Entry point
│
└── xl/
├── workbook.xml → Sheet definitions
├── _rels/
│ └── workbook.xml.rels → Sheet mapping
│
└── worksheets/
└── sheet1.xml → Actual data
How Excel Reads This
Excel → .rels → workbook.xml → relationships → sheet1.xml → render UI
Apex Code
public with sharing class ExportToXLSX {
/**
* Fetch Account records for UI preview and export
* cacheable=true improves performance in LWC (uses client-side cache)
*/
@AuraEnabled(cacheable=true)
public static List<Account> getAccounts() {
// Simple SOQL query with filter to avoid null-heavy data
return [
SELECT Name, BillingState, BillingCity, BillingCountry, Phone, Type
FROM Account
WHERE BillingCity != NULL AND BillingState != NULL
LIMIT 100
];
}
/**
* Main method to generate XLSX file
* Returns Base64 string because LWC cannot directly handle Blob from Apex
*/
@AuraEnabled
public static String generateXlsx(List<Account> accounts) {
// =====================================================
// STEP 1: Build Sheet Data (Rows + Cells)
// =====================================================
// Each entry in this list represents one Excel row (<row>)
List<String> rows = new List<String>();
// -------------------------------
// Header Row (Row 1)
// -------------------------------
rows.add(
'<row r="1">' + // r="1" = Excel row number
// Each cell uses reference like A1, B1, etc.
// t="inlineStr" means value stored directly inside XML
cell('A1','Name') +
cell('B1','Billing State') +
cell('C1','Billing City') +
cell('D1','Billing Country') +
cell('E1','Phone') +
cell('F1','Type') +
'</row>'
);
Integer rowNum = 2; // Start from row 2 for actual data
// -------------------------------
// Data Rows
// -------------------------------
for (Account acc : accounts) {
rows.add(
'<row r="' + rowNum + '">' +
// Order of cells defines Excel column order
cell('A' + rowNum, acc.Name) +
cell('B' + rowNum, acc.BillingState) +
cell('C' + rowNum, acc.BillingCity) +
cell('D' + rowNum, acc.BillingCountry) +
cell('E' + rowNum, acc.Phone) +
cell('F' + rowNum, acc.Type) +
'</row>'
);
rowNum++;
}
// Combine all row XML into one string
String sheetData = String.join(rows, '');
// =====================================================
// STEP 2: Worksheet XML (Actual Data Container)
// =====================================================
String worksheet =
'<?xml version="1.0" encoding="UTF-8"?>' +
// Root element for worksheet
'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
// sheetData holds all rows
'<sheetData>' + sheetData + '</sheetData>' +
'</worksheet>';
// =====================================================
// STEP 3: Workbook XML (Defines Sheets)
// =====================================================
String workbook =
'<?xml version="1.0" encoding="UTF-8"?>' +
'<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ' +
'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">' +
'<sheets>' +
// r:id connects this sheet to workbook relationships
'<sheet name="Accounts" sheetId="1" r:id="rId1"/>' +
'</sheets>' +
'</workbook>';
// =====================================================
// STEP 4: Workbook Relationships
// Maps sheet reference → actual file location
// =====================================================
String workbookRels =
'<?xml version="1.0" encoding="UTF-8"?>' +
'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
'<Relationship Id="rId1" ' +
'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" ' +
'Target="worksheets/sheet1.xml"/>' +
'</Relationships>';
// =====================================================
// STEP 5: Root Relationships (Entry Point)
// =====================================================
String rootRels =
'<?xml version="1.0" encoding="UTF-8"?>' +
'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
// Points Excel to workbook.xml
'<Relationship Id="rId1" ' +
'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" ' +
'Target="xl/workbook.xml"/>' +
'</Relationships>';
// =====================================================
// STEP 6: Content Types (Mandatory)
// =====================================================
String contentTypes =
'<?xml version="1.0" encoding="UTF-8"?>' +
'<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' +
'<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' +
'<Default Extension="xml" ContentType="application/xml"/>' +
'<Override PartName="/xl/workbook.xml" ' +
'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' +
'<Override PartName="/xl/worksheets/sheet1.xml" ' +
'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>' +
'</Types>';
// =====================================================
// STEP 7: ZIP Packaging (Creates XLSX)
// =====================================================
Compression.ZipWriter writer = new Compression.ZipWriter();
// IMPORTANT: File structure must match Excel specification exactly
writer.addEntry('[Content_Types].xml', Blob.valueOf(contentTypes));
writer.addEntry('_rels/.rels', Blob.valueOf(rootRels));
writer.addEntry('xl/workbook.xml', Blob.valueOf(workbook));
writer.addEntry('xl/_rels/workbook.xml.rels', Blob.valueOf(workbookRels));
writer.addEntry('xl/worksheets/sheet1.xml', Blob.valueOf(worksheet));
// Final XLSX file (ZIP archive)
Blob zipBlob = writer.getArchive();
// Return Base64 string to LWC
return EncodingUtil.base64Encode(zipBlob);
}
/**
* Helper method to create a cell
*/
private static String cell(String ref, String value) {
return '<c r="' + ref + '" t="inlineStr">' +
'<is>' +
'<t>' + escape(value) + '</t>' +
'</is>' +
'</c>';
}
/**
* Escape XML special characters
* Prevents file corruption
*/
private static String escape(String val) {
if (val == null) return '';
return val.replace('&','&')
.replace('<','<')
.replace('>','>');
}
}
LWC JavaScript
import { LightningElement, wire, track } from 'lwc';
// Import Apex methods
import getAccounts from '@salesforce/apex/ExportToXLSX.getAccounts';
import generateXlsx from '@salesforce/apex/ExportToXLSX.generateXlsx';
export default class ExportToXLSX extends LightningElement {
// Reactive properties
@track accounts = []; // Holds Account data
@track isLoading = false; // Controls spinner UI
@track error; // Stores error if any
// Define datatable columns
columns = [
{ label: 'Name', fieldName: 'Name' },
{ label: 'State', fieldName: 'BillingState' },
{ label: 'City', fieldName: 'BillingCity' },
{ label: 'Country', fieldName: 'BillingCountry' },
{ label: 'Phone', fieldName: 'Phone' },
{ label: 'Type', fieldName: 'Type' }
];
/**
* Fetch Accounts using @wire (reactive + cacheable)
*/
@wire(getAccounts)
wiredAccounts({ error, data }) {
if (data) {
this.accounts = data; // Assign data to UI
this.error = undefined;
} else if (error) {
this.error = error;
this.accounts = [];
console.error(error);
}
}
/**
* Handle Download Button Click
*/
async handleDownload() {
// Prevent export if no data
if (!this.accounts || this.accounts.length === 0) {
alert('No data to export');
return;
}
this.isLoading = true;
try {
// Call Apex to generate XLSX
const base64 = await generateXlsx({
accounts: this.accounts
});
// Convert Base64 → downloadable file
this.downloadFile(base64, 'Accounts.xlsx');
} catch (err) {
console.error('Download error:', err);
} finally {
this.isLoading = false;
}
}
/**
* Convert Base64 → Blob → Trigger Download
*/
downloadFile(base64, fileName) {
// Decode Base64 string into binary string
const byteCharacters = atob(base64);
// Convert binary string → byte array
const byteNumbers = new Array(byteCharacters.length);
for (let i = 0; i < byteCharacters.length; i++) {
byteNumbers[i] = byteCharacters.charCodeAt(i);
}
// Create typed array
const byteArray = new Uint8Array(byteNumbers);
// Create Blob (file object)
const blob = new Blob([byteArray], {
type: 'application/octet-stream'
});
// Create temporary download link
const url = URL.createObjectURL(blob);
const link = document.createElement('a');
link.href = url;
link.download = fileName;
// Trigger download
document.body.appendChild(link);
link.click();
// Cleanup
document.body.removeChild(link);
URL.revokeObjectURL(url);
}
}
LWC HTML
<template>
<!-- Card container -->
<lightning-card title="Account Excel Download" icon-name="standard:account">
<div class="slds-m-around_medium">
<!-- Download Button -->
<!-- Disabled when file generation is in progress -->
<lightning-button
label="Download Excel (.xlsx)"
variant="brand"
onclick={handleDownload}
disabled={isLoading}>
</lightning-button>
<!-- Spinner shown during processing -->
<template if:true={isLoading}>
<lightning-spinner
alternative-text="Generating Excel..."
size="medium">
</lightning-spinner>
</template>
<!-- Error display -->
<template if:true={error}>
<div class="slds-text-color_error slds-m-top_small">
Error loading data
</div>
</template>
<!-- Data preview -->
<template if:true={accounts}>
<!-- Record count -->
<div class="slds-m-top_medium">
<p><b>Total Records:</b> {accounts.length}</p>
</div>
<!-- Datatable -->
<!-- Used only for preview, not for export -->
<lightning-datatable
key-field="Id"
data={accounts}
columns={columns}
hide-checkbox-column>
</lightning-datatable>
</template>
</div>
</lightning-card>
</template>
Best Practices
- Always escape XML characters (&, <, >)
- Validate structure carefully
- Keep dataset small (< 5k records ideally)
- Use batch/async for large exports
Debug Tip
Rename .xlsx → .zip → Extract → Inspect XML files
Conclusion
Using native Apex and LWC, you can generate Excel files without any third-party libraries. This approach is lightweight, secure, and ideal for simple export use cases.
No external dependency required 🎉
Code has been kept @ https://github.com/AvijitGoraiGitHub/Export-Data-to-XLSX-in-Salesforce
No comments:
Post a Comment