js xlsx code example

Example: Working with XLSX in JavaScript

/*
    This code comes from Vincent Lab
    And it has a video version linked here: https://www.youtube.com/watch?v=TDGsVqVzW4A
*/

// Import dependencies
const fs = require("fs");
const XLSX = require("xlsx");
const jsontoxml = require("jsontoxml");

// Read the file into memory
// const workbook = XLSX.read(fs.readFileSync("file-example.xlsx"));
const workbook = XLSX.readFile("file-example.xlsx");

// Convert the XLSX to JSON
let worksheets = {};
for (const sheetName of workbook.SheetNames) {
    // Some helper functions in XLSX.utils generate different views of the sheets:
    //     XLSX.utils.sheet_to_csv generates CSV
    //     XLSX.utils.sheet_to_txt generates UTF16 Formatted Text
    //     XLSX.utils.sheet_to_html generates HTML
    //     XLSX.utils.sheet_to_json generates an array of objects
    //     XLSX.utils.sheet_to_formulae generates a list of formulae
    worksheets[sheetName] = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
}

// Show the data as JSON
console.log("json:\n", JSON.stringify(worksheets.Sheet1), "\n\n");

// Show the data as XML
console.log("xml:\n", jsontoxml({
    worksheets: JSON.parse(JSON.stringify(Object.values(worksheets))).map(worksheet => worksheet.map(data => {
        for (property in data) {
            const newPropertyName = property.replace(/\s/g, "");
            if (property !== newPropertyName) {
                Object.defineProperty(data, newPropertyName,
                    Object.getOwnPropertyDescriptor(data, property));
                delete data[property];
            }
        }
        return data;
    }))
}, {}), "\n\n");

// Modify the XLSX
worksheets.Sheet1.push({
    "First Name": "Bob",
    "Last Name": "Bob",
    "Gender": "Male",
    "Country": "United States",
    "Age": 35,
    "Date": "22/09/2020",
    "Id": 1600,
    "New Column": "test"
});

// // Update the XLSX file
// XLSX.utils.sheet_add_json(workbook.Sheets["Sheet1"], worksheets.Sheet1)
// XLSX.writeFile(workbook, "file-example.xlsx");

// Create a new XLSX file
const newBook = XLSX.utils.book_new();
const newSheet = XLSX.utils.json_to_sheet(worksheets.Sheet1);
XLSX.utils.book_append_sheet(newBook, newSheet, "Sheet1");
XLSX.writeFile(newBook,"new-book.xlsx");