ExcelJS将数组写入excel

jv4diomz  于 2022-12-27  发布在  其他
关注(0)|答案(1)|浏览(223)

我正在使用一个API获取信息,并将其存储在一个名为res的数组中。我希望将此数组写入Excel文件。数组中的第一个信息应该在A2中,第二个信息应该在B2中,等等。
这是我的代码:

const api2 = require("fordonsuppgifter-api-wrapper");


(async () => {
    console.log("fetching vehicle info");
    var res = await api2.GetVehicleInformation("XMP433");
    console.log(res);
})();

const Excel = require('exceljs');

const fileName = 'simple.xlsx';

const wb = new Excel.Workbook();
const ws = wb.addWorksheet('My Sheet');

const r3 = ws.getRow(3);
r3.values = [1, 2, 3, 4, 5, 6];

wb.xlsx
    .writeFile(fileName)
    .then(() => {
        console.log('file created');
    })
    .catch(err => {
        console.log(err.message);
    });

API工作正常,但我无法让它写入Excel文件。

6ljaweal

6ljaweal1#

您可以创建excel文件与这两个组合调用。exceljs库需要移动单元格写入函数

ws.getCell(<cell_address>).value = <assigned_value>

cell_address需要增加数字以向下移动(增加行号)
示例:A1 -〉A2表示下一行。
columnName()可以将整数转换为Excel列地址。
示例

columnName(1) -> A
columnName(2) -> B

您需要将Object()从JSON转换为Object类型,然后可以获得JSON key的列表

Object.keys(carData)

最终代码

const Excel = require('exceljs');
const api2 = require("fordonsuppgifter-api-wrapper");

const columnName = (index) => {
    var cname = String.fromCharCode(65 + ((index - 1) % 26));
    if (index > 26)
        cname = String.fromCharCode(64 + (index - 1) / 26) + cname;
    return cname;
}

const getCarInformation = async (keyword) => {
    try {
        const res = await api2.GetVehicleInformation(keyword);
        return Promise.resolve(res);
    } catch (error) {
        return Promise.reject(error);
    }
}

// Search car by keyword
getCarInformation("XMP433")
    .then((carData) => {
        const fileName = 'simple.xlsx';
        const workbook = new Excel.Workbook();
        // make workbook with 'car' name
        const ws = workbook.addWorksheet("car")

        // Start Cell A1 for title column
        let headerColumn = 1
        let section_number = 1
        for (let key in carData) {
            // title column, example : A1 = Sammanfattning
            ws.getCell(columnName(headerColumn) + String(section_number)).value = key
            subItems = carData[key]
            row_number = section_number
            for (let subKey in subItems) {
                // Sub title Cell Bx, example : B1 = Registreringsnummer
                ws.getCell(columnName(headerColumn + 1) + String(row_number)).value = subKey
                // value Cell Cx, example : C1 = '(2*) XMP433'
                ws.getCell(columnName(headerColumn + 2) + String(row_number)).value = subItems[subKey]
                row_number++;
            }
            // Jump to next title
            section_number = section_number + Object.keys(carData[key]).length;
        }
        workbook.xlsx
            .writeFile(fileName)
            .then(() => {
                console.log('file created');
            })
            .catch(err => {
                console.log(err.message);
            });
    })
    .catch(err => {
        console.log(err.message);
    });

结果以simple.xlsx表示

保存文件后,您需要单击标题以扩展列宽,以适应其内容。

相关问题