JavaScript操作XLSX文件的示例详解_Javascript

前言

excel 文件（通常以 .xlsx 为后缀）在各种业务场景中都有广泛应用，如数据分析、财务报告和其他数据表单处理。掌握如何在前端或后端使用 javascript 对这些 excel 文件进行读取、创建和修改，能够显著提升我们的开发效率和数据处理能力。

本文将深入探讨如何使用强大的 xlsx 库来操作 excel 文件。我们将涵盖如何在 node.js 和浏览器环境中读取 excel 文件、创建新的 excel 文件、修改现有的 excel 文件以及处理复杂的 excel 数据结构。

常用操作

读取 excel 文件

在 node.js 环境中读取本地文件

在 node.js 环境中，我们可以轻松地读取本地的 excel 文件。以下示例展示了如何读取本地的 example.xlsx 文件并将其内容转换为 json 格式：

const xlsx = require('xlsx');
const workbook = xlsx.readfile('example.xlsx');

// 获取第一个工作表
const sheetname = workbook.sheetnames[0];
const worksheet = workbook.sheets[sheetname];

// 将工作表转换为 json 数据
const data = xlsx.utils.sheet_to_json(worksheet);

console.log(data);

在浏览器环境中读取用户上传的文件

在浏览器环境中，处理用户上传的文件是常见需求。以下示例展示了如何结合 html 的

<input type="file"> 元素读取用户上传的 excel 文件：
<input type="file" id="upload" accept=".xlsx" />

<script>
document.getelementbyid('upload').addeventlistener('change', handlefile, false);

function handlefile(event) {
    const file = event.target.files[0];
    const reader = new filereader();
    reader.onload = function(e) {
        const data = new uint8array(e.target.result);
        const workbook = xlsx.read(data, { type: 'array' });

        const sheetname = workbook.sheetnames[0];
        const worksheet = workbook.sheets[sheetname];
        const jsondata = xlsx.utils.sheet_to_json(worksheet);

        console.log(jsondata);
    };

    reader.readasarraybuffer(file);
}
</script>

创建 excel 文件

创建并下载新的 excel 文件是另一个常见需求。以下示例展示了如何生成包含示例数据的 excel 文件，并允许用户下载：

<button id="download">下载 excel 文件</button>

<script>
document.getelementbyid('download').addeventlistener('click', function() {
    // 创建数据
    const data = [
        { "name": "john", "age": 30, "city": "new york" },
        { "name": "peter", "age": 25, "city": "london" },
        { "name": "sara", "age": 22, "city": "paris" }
    ];

    // 将数据转换为工作表
    const worksheet = xlsx.utils.json_to_sheet(data);

    // 创建工作簿并添加工作表
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, "sheet1");

    // 生成 excel 文件并下载
    xlsx.writefile(workbook, 'example.xlsx');
});
</script>

修改 excel 文件

修改现有的 excel 文件也是常见的操作需求。以下示例展示了如何读取 excel 文件、修改某个单元格的值并保存修改后的文件：

const xlsx = require('xlsx');
const workbook = xlsx.readfile('example.xlsx');

const sheetname = workbook.sheetnames[0];
const worksheet = workbook.sheets[sheetname];

// 修改单元格 a1 的值
worksheet['a1'].v = 'modified value';

// 保存修改后的文件
xlsx.writefile(workbook, 'modified_example.xlsx');

进阶操作

1. 处理多个工作表

如果 excel 文件包含多个工作表，我们可以遍历所有工作表并处理每个工作表的数据：

const xlsx = require('xlsx');
const workbook = xlsx.readfile('example.xlsx');

// 遍历所有工作表
workbook.sheetnames.foreach(sheetname => {
    const worksheet = workbook.sheets[sheetname];
    const data = xlsx.utils.sheet_to_json(worksheet);
    console.log(sheet: ${sheetname}, data);
});

2. 处理复杂的数据结构

处理嵌套的表格或进行数据清洗需要使用 javascript 的数组和对象方法：

const xlsx = require('xlsx');
const workbook = xlsx.readfile('complex_example.xlsx');

const sheetname = workbook.sheetnames[0];
const worksheet = workbook.sheets[sheetname];

// 将工作表转换为 json 数据
const data = xlsx.utils.sheet_to_json(worksheet);

// 清洗数据，去除空值并计算某一列的总和
const cleaneddata = data.filter(row => row['value'] !== null && row['value'] !== undefined);
const totalvalue = cleaneddata.reduce((sum, row) => sum + row['value'], 0);

console.log('cleaned data:', cleaneddata);
console.log('total value:', totalvalue);

3. 指定单元格格式

const xlsx = require('xlsx');
const data = [
    { "date": new date(), "amount": 123.45 }
];

const worksheet = xlsx.utils.json_to_sheet(data);
worksheet['a1'].z = 'mm/dd/yyyy'; // 指定日期格式
worksheet['b1'].z = '#,##0.00';  // 指定数字格式

const workbook = xlsx.utils.book_new();
xlsx.utils.book_append_sheet(workbook, worksheet, "sheet1");
xlsx.writefile(workbook, 'formatted_example.xlsx');

4. 合并单元格

const xlsx = require('xlsx');
const workbook = xlsx.utils.book_new();
const worksheet = xlsx.utils.aoa_to_sheet([
    ['title1', null, 'title2'],
    ['data1', 'data2', 'data3']
]);

// 合并单元格 a1 和 b1
worksheet['!merges'] = [
    { s: { r: 0, c: 0 }, e: { r: 0, c: 1 } } // s 是开始单元格，e 是结束单元格
];

xlsx.utils.book_append_sheet(workbook, worksheet, "sheet1");
xlsx.writefile(workbook, 'merged_cells_example.xlsx');

5. 设置单元格样式

虽然 xlsx 库本身对样式支持有限，但可以借助 exceljs 库来实现更丰富的样式设置：

const exceljs = require('exceljs');
const workbook = new exceljs.workbook();
const worksheet = workbook.addworksheet('sheet1');

worksheet.columns = [
    { header: 'name', key: 'name' },
    { header: 'age', key: 'age' },
    { header: 'city', key: 'city' }
];

worksheet.addrow({ name: 'john', age: 30, city: 'new york' });

// 设置列宽和单元格样式
worksheet.getcolumn('name').width = 20;
worksheet.getrow(1).font = { bold: true };
worksheet.getcell('a2').fill = {
    type: 'pattern',
    pattern: 'solid',
    fgcolor: { argb: 'ffff0000' }
};

workbook.xlsx.writefile('styled_example.xlsx');