Export to excel, when the number of cells with styles set exceeds 65500, an error will be reported when opening excel

[yangtianyin]

Export Large Data to Excel Example

Export Data

<script> import XlsxPopulate from 'xlsx-populate' import { saveAs } from 'file-saver' export default { name: 'ExportExcel', methods: { generateLargeData() { const data = [] for (let i = 0; i < 65500; i++) { // 100万条数据示例 data.push({ name: `User ${i}`, age: Math.floor(Math.random() * 100), email: `user${i}@example.com` }) } return data }, async exportToExcel() { const data = this.generateLargeData() try { const workbook = await XlsxPopulate.fromBlankAsync() const sheet = workbook.sheet(0) // 写入表头 sheet.cell('A1').value('Name') sheet.cell('B1').value('Age') sheet.cell('C1').value('Email') // 分批写入数据，避免一次性加载过多数据 const BATCH_SIZE = 10000 for (let i = 0; i < data.length; i += BATCH_SIZE) { const batchData = data.slice(i, i + BATCH_SIZE) for (let j = 0; j < batchData.length; j++) { const row = i + j + 2 // +2因为Excel行从1开始，并且第1行是表头 sheet.cell(`A${row}`).value(batchData[j].name) sheet.cell(`B${row}`).value(batchData[j].age) sheet.cell(`C${row}`).value(batchData[j].email) sheet.range(`A${row}:A${row}`).style({ border: true, horizontalAlignment: 'right', numberFormat: '#,##0.00', fontSize: 9, fontColor: '#000000', fontFamily: 'Tahoma' }) } } // 将工作簿导出为Blob const blob = await workbook.outputAsync() saveAs(blob, 'large_data.xlsx') } catch (error) { console.error('Error exporting data to Excel:', error) } } } } </script> <style> button { padding: 10px 20px; margin: 10px; background-color: #4CAF50; color: white; border: none; cursor: pointer; font-size: 16px; } button:hover { background-color: #45a049; } </style>