如何导出保留列样式的Excel数据并同步至Salesforce数据库

一、ExcelJS读取样式的问题排查 #

你之前用ExcelJS未得到预期结果，核心是没正确处理样式的继承与属性读取，正确的读取逻辑需要注意：

• 单元格样式可能继承自行、列或工作簿，必须通过cell.style层级读取具体属性
• Excel的颜色编码为ARGB格式（如FFFF0000），需转换为Salesforce支持的十六进制格式（去掉前两位透明度，如#FF0000）

示例代码：

// 读取单元格核心样式
const fillColor = cell.fill?.fgColor?.argb ? `#${cell.fill.fgColor.argb.slice(2)}` : null;
const fontColor = cell.font?.color?.argb ? `#${cell.font.color.argb.slice(2)}` : null;
const fontWeight = cell.font?.bold ? 'Bold' : 'Normal';

二、Salesforce端样式数据存储设计 #

创建自定义对象Excel_Style__c来存储样式映射关系，核心字段建议：

• Record_Id__c：关联业务对象的记录ID（如Account、Contact的ID）
• Cell_Location__c：单元格位置标识（如"A1"、"B3"）
• Font_Color__c：字体颜色（十六进制字符串）
• Background_Color__c：单元格背景色
• Font_Weight__c：字重（选项值：Normal/Bold）
• Scope__c：样式作用范围（选项值：Cell/Row/Column），用于整行/整列样式的批量存储

三、LWC上传解析实现 #

1. 文件上传与解析组件 #

import { LightningElement } from 'lwc';
import ExcelJS from '@salesforce/resourceUrl/ExcelJS';
import { loadScript } from 'lightning/platformResourceLoader';
import insertExcelData from '@salesforce/apex/ExcelStyleController.insertExcelData';

export default class ExcelStyleUploader extends LightningElement {
    excelJSLoaded = false;

    connectedCallback() {
        loadScript(this, ExcelJS)
            .then(() => this.excelJSLoaded = true)
            .catch(err => console.error('ExcelJS加载失败:', err));
    }

    handleUploadFinished(event) {
        if (!this.excelJSLoaded) return;
        const file = event.detail.files[0];
        const reader = new FileReader();
        reader.onload = (e) => this.parseExcel(e.target.result);
        reader.readAsArrayBuffer(file);
    }

    async parseExcel(buffer) {
        const workbook = new ExcelJS.Workbook();
        await workbook.xlsx.load(buffer);
        const worksheet = workbook.getWorksheet(1);
        
        const records = [];
        worksheet.eachRow({ includeEmpty: false }, (row, rowNum) => {
            row.eachCell({ includeEmpty: false }, (cell, colNum) => {
                const cellLoc = `${String.fromCharCode(64 + colNum)}${rowNum}`;
                // 合并业务数据与样式数据
                records.push({
                    Name: cell.value, // 替换为你的业务字段
                    Cell_Location__c: cellLoc,
                    Font_Color__c: cell.font?.color?.argb ? `#${cell.font.color.argb.slice(2)}` : null,
                    Background_Color__c: cell.fill?.fgColor?.argb ? `#${cell.fill.fgColor.argb.slice(2)}` : null,
                    Font_Weight__c: cell.font?.bold ? 'Bold' : 'Normal'
                });
            });
        });

        // 调用Apex插入数据
        insertExcelData({ records })
            .then(res => console.log('数据插入成功:', res))
            .catch(err => console.error('插入失败:', err));
    }
}

2. Apex控制器 #

public with sharing class ExcelStyleController {
    @AuraEnabled
    public static List<Account> insertExcelData(List<Map<String, Object>> records) {
        List<Account> businessRecords = new List<Account>();
        List<Excel_Style__c> styleRecords = new List<Excel_Style__c>();

        // 先插入业务记录
        for (Map<String, Object> rec : records) {
            businessRecords.add(new Account(Name = (String)rec.get('Name')));
        }
        insert businessRecords;

        // 关联样式记录与业务记录
        Integer idx = 0;
        for (Map<String, Object> rec : records) {
            Excel_Style__c style = new Excel_Style__c();
            style.Record_Id__c = businessRecords[idx].Id;
            style.Cell_Location__c = (String)rec.get('Cell_Location__c');
            style.Font_Color__c = (String)rec.get('Font_Color__c');
            style.Background_Color__c = (String)rec.get('Background_Color__c');
            style.Font_Weight__c = (String)rec.get('Font_Weight__c');
            styleRecords.add(style);
            idx++;
        }
        insert styleRecords;

        return businessRecords;
    }
}

四、Visualforce页面上传实现 #

1. Apex控制器（依赖Apache POI） #

public with sharing class VFExcelStyleController {
    public Blob excelFile { get; set; }
    public String fileName { get; set; }

    public PageReference upload() {
        if (excelFile == null) return null;
        
        XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(excelFile));
        XSSFSheet sheet = workbook.getSheetAt(0);
        
        List<Account> businessRecords = new List<Account>();
        List<Excel_Style__c> styleRecords = new List<Excel_Style__c>();

        for (Row row : sheet) {
            for (Cell cell : row) {
                // 插入业务记录
                businessRecords.add(new Account(Name = cell.getStringCellValue()));
                
                // 解析样式
                XSSFCellStyle cellStyle = cell.getCellStyle();
                XSSFFont font = cellStyle.getFont();
                styleRecords.add(new Excel_Style__c(
                    Cell_Location__c = String.fromCharCode(65 + cell.getColumnIndex()) + (cell.getRowIndex() + 1),
                    Font_Color__c = getHexColor(font.getXSSFColor()),
                    Background_Color__c = getHexColor(cellStyle.getFillForegroundColorColor()),
                    Font_Weight__c = font.getBold() ? 'Bold' : 'Normal'
                ));
            }
        }

        insert businessRecords;
        // 关联样式记录与业务记录，逻辑同LWC Apex部分
        Integer idx = 0;
        for (Excel_Style__c style : styleRecords) {
            style.Record_Id__c = businessRecords[idx].Id;
            idx++;
        }
        insert styleRecords;

        return null;
    }

    // 颜色转换辅助方法
    private String getHexColor(XSSFColor color) {
        if (color == null) return null;
        byte[] rgb = color.getRGB();
        return '#' + String.format('{0}{1}{2}', new List<String>{
            String.valueOf(rgb[0]).leftPad(2, '0'),
            String.valueOf(rgb[1]).leftPad(2, '0'),
            String.valueOf(rgb[2]).leftPad(2, '0')
        });
    }
}

2. Visualforce页面 #

<apex:page controller="VFExcelStyleController">
    <apex:form>
        <apex:inputFile value="{!excelFile}" filename="{!fileName}" accept=".xlsx"/>
        <apex:commandButton value="上传解析" action="{!upload}" style="margin-left:10px;"/>
    </apex:form>
</apex:page>

五、带样式的Excel导出实现 #

从Salesforce导出带样式的Excel时，可复用ExcelJS读取存储的样式数据，示例代码：

async exportStyledExcel() {
    // 从Salesforce获取业务数据与样式数据
    // const data = await fetchCombinedData();
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('带样式数据');

    data.forEach((item, rowIdx) => {
        const cell = worksheet.getRow(rowIdx + 1).getCell(1);
        cell.value = item.Name;
        // 还原样式
        cell.font = {
            color: { argb: item.Font_Color__c ? `FF${item.Font_Color__c.slice(1)}` : 'FF000000' },
            bold: item.Font_Weight__c === 'Bold'
        };
        cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: item.Background_Color__c ? `FF${item.Background_Color__c.slice(1)}` : 'FFFFFFFF' }
        };
    });

    // 生成下载文件
    const buffer = await workbook.xlsx.writeBuffer();
    const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const a = document.createElement('a');
    a.href = URL.createObjectURL(blob);
    a.download = '带样式数据.xlsx';
    a.click();
    URL.revokeObjectURL(a.href);
}

内容的提问来源于stack exchange，提问作者Raju Banothu