working with xlsx in javascript

Solutions on MaxInterview for working with xlsx in javascript by the best coders in the world

showing results for - "working with xlsx in javascript"
Lorenzo
29 Jun 2018
1/*
2    This code comes from Vincent Lab
3    And it has a video version linked here: https://www.youtube.com/watch?v=TDGsVqVzW4A
4*/
5
6// Import dependencies
7const fs = require("fs");
8const XLSX = require("xlsx");
9const jsontoxml = require("jsontoxml");
10
11// Read the file into memory
12// const workbook = XLSX.read(fs.readFileSync("file-example.xlsx"));
13const workbook = XLSX.readFile("file-example.xlsx");
14
15// Convert the XLSX to JSON
16let worksheets = {};
17for (const sheetName of workbook.SheetNames) {
18    // Some helper functions in XLSX.utils generate different views of the sheets:
19    //     XLSX.utils.sheet_to_csv generates CSV
20    //     XLSX.utils.sheet_to_txt generates UTF16 Formatted Text
21    //     XLSX.utils.sheet_to_html generates HTML
22    //     XLSX.utils.sheet_to_json generates an array of objects
23    //     XLSX.utils.sheet_to_formulae generates a list of formulae
24    worksheets[sheetName] = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
25}
26
27// Show the data as JSON
28console.log("json:\n", JSON.stringify(worksheets.Sheet1), "\n\n");
29
30// Show the data as XML
31console.log("xml:\n", jsontoxml({
32    worksheets: JSON.parse(JSON.stringify(Object.values(worksheets))).map(worksheet => worksheet.map(data => {
33        for (property in data) {
34            const newPropertyName = property.replace(/\s/g, "");
35            if (property !== newPropertyName) {
36                Object.defineProperty(data, newPropertyName,
37                    Object.getOwnPropertyDescriptor(data, property));
38                delete data[property];
39            }
40        }
41        return data;
42    }))
43}, {}), "\n\n");
44
45// Modify the XLSX
46worksheets.Sheet1.push({
47    "First Name": "Bob",
48    "Last Name": "Bob",
49    "Gender": "Male",
50    "Country": "United States",
51    "Age": 35,
52    "Date": "22/09/2020",
53    "Id": 1600,
54    "New Column": "test"
55});
56
57// // Update the XLSX file
58// XLSX.utils.sheet_add_json(workbook.Sheets["Sheet1"], worksheets.Sheet1)
59// XLSX.writeFile(workbook, "file-example.xlsx");
60
61// Create a new XLSX file
62const newBook = XLSX.utils.book_new();
63const newSheet = XLSX.utils.json_to_sheet(worksheets.Sheet1);
64XLSX.utils.book_append_sheet(newBook, newSheet, "Sheet1");
65XLSX.writeFile(newBook,"new-book.xlsx");