There is a form that a user will use to upload an excel file to. From there, in JavaScript, I am able to pull the data (this includes if excel file has multiple sheets) and store it in arrays (columnA, columnB, etc.). However, I am not sure how to write to an existing excel file that is on the server with the data stored in the arrays. Any help would be appreciated.
The following is what I have so far:
let selectedFile, fileName;
document.getElementById('input-excel').addEventListener("change", (event) => {
selectedFile = event.target.files[0];
fileName = event.target.files[0].name;
})
let data=[{
"name":"jayanth",
"data":"scd",
"abc":"sdef"
}]
document.getElementById('button').addEventListener("click", () => {
XLSX.utils.json_to_sheet(data, selectedFile);
if(fileName.includes("Excel FileName")){
let fileReader = new FileReader();
fileReader.readAsBinaryString(selectedFile);
fileReader.onload = (event)=>{
let data = event.target.result;
let workbookInputted = XLSX.read(data,{type:"binary"}); //excel file
let residential_worksheet = workbookInputted.Sheets[workbookInputted.SheetNames[0]]; //test1 sheet
let non_residential_worksheet = workbookInputted.Sheets[workbookInputted.SheetNames[1]]; //test2 sheet
let peh_worksheet = workbookInputted.Sheets[workbookInputted.SheetNames[2]]; //test3 sheet
let educational_worksheet = workbookInputted.Sheets[workbookInputted.SheetNames[3]]; //test4 setting sheet
let correct_worksheet = workbookInputted.Sheets[workbookInputted.SheetNames[4]]; //test5 setting sheet
const sheetList = workbookInputted.SheetNames;
const res = WhichSheet(residential_worksheet,sheetList[0]);
const nonRes = WhichSheet(non_residential_worksheet,sheetList[1]);
const peh = WhichSheet(peh_worksheet,sheetList[2]);
const ed = WhichSheet(educational_worksheet,sheetList[3]);
const correct = WhichSheet(correct_worksheet,sheetList[4]);
//console.log(sheetList[3]);
}
}
});
function WhichSheet(worksheet, fileNme){
if(fileName.localeCompare("test")){
ReadColumntoJSData(worksheet, fileNme);
} else if(fileName.includes("test2")){
ReadColumntoJSData(worksheet, fileNme);
} else if(fileName.includes("test3")){
ReadColumntoJSData(worksheet, fileNme);
} else if(fileName.includes("test4")){
ReadColumntoJSData(worksheet, fileNme);
} else if(fileName.includes("test5")){
ReadColumntoJSData(worksheet, fileNme);
}
}
/*==
====== Following method reads each column per sheet. ======
===*/
function ReadColumntoJSData(worksheet, fileNme){
var colA = [];
var colB = [];
var colC = [];
var colD = [];
var colE = [];
var colF = [];
for(let col in worksheet){
if(col.toString()[0] === 'A'){
if(col === "A1" || col === "A2" || col === "A3"){
//do nothing
} else {
colA.push(worksheet[col].v);
}
}
if(col.toString()[0] === 'B'){
if(col === "B1" || col === "B2" || col === "B3"){
//do nothing
} else {
colB.push(worksheet[col].v);
}
}
if(col.toString()[0] === 'C'){
if(col === "C1" || col === "C2" || col === "C3"){
//do nothing
} else {
colC.push(worksheet[col].v);
}
}
if(col.toString()[0] === 'D'){
if(col === "D1" || col === "D2" || col === "D3"){
//do nothing
} else {
colD.push(worksheet[col].v);
}
}
if(col.toString()[0] === 'E'){
if(col === "E1" || col === "E2" || col === "E3"){
//do nothing
} else {
colE.push(worksheet[col].v);
}
}
if(fileNme == "test2" || fileNme == "test5"){
//do nothing
} else{
if(col.toString()[0] === 'F'){
if(col === "F1" || col === "F2" || col === "F3"){
//do nothing
} else {
colF.push(worksheet[col].v);
}
}
}
}
ExportToExcel(colA,colB,colC,colD,colE,colF);
}
function ExportToExcel(colA,colB,colC,colD,colE,colF,fileName){
console.log(colA)
}
UPDATE: I tried the following to write to the existing excel sheet. However, I am getting the following error: Uncaught TypeError: Cannot read properties of undefined (reading 'A3').
var req = new XMLHttpRequest();
req.open("GET", fileURL2, true);
req.responseType = "arraybuffer";
req.onload = function(e) {
var data = new Uint8Array(req.response);
const writeWorkBook = XLSX.read(data, {type:"array"});
const writeWorkSheet = writeWorkBook.Sheets["TEST123"]
console.log("Worksheet: " + JSON.stringify(writeWorkSheet));
writeWorkSheet['A3'].v = colA[0];
}
req.send();