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");