Excel to Json
从 Excel 中解析 JSON，使用 xlsx 包
function readFileForWorkbook(file) {
return new Promise((resolve, reject) => {
const reader = new FileReader();
let wb;
reader.onload = (res) => {
const data = res.target.result;
wb = XLSX.read(data, {
type: "binary",
});
resolve(wb);
};
reader.onerror = (e) => {
reject(e);
};
reader.readAsBinaryString(file);
});
}
const fileInput = document.getElementById("file-input");
fileInput.addEventListener("change", function (event) {
const file = event.target.files[0];
readFileForWorkbook(file).then((wb) => {
console.log(wb.SheetNames);
const sheet = wb.Sheets[wb.SheetNames[0]];
console.log(sheet,'sheet');
// 遍历每一行
// 导出JSON
const data = XLSX.utils.sheet_to_json(sheet);
console.log(JSON.stringify(data));
fetch("http://localhost:3000/excel", {
method: "POST", // or 'PUT'
body: JSON.stringify(data),
headers: {
// "Content-Type": "application/json",
"Content-Type": "application/json;charset=utf-8",
},
})
.then((response) => response.json())
.then((data) => {
fetch("http://localhost:3000/export", {
method: "GET",
// body: JSON.stringify(data.data),
headers: {
// "Content-Type": "application/json",
"Content-Type": "application/json;charset=utf-8",
},
}).then(()=>{
window.alert(' excel生成成功')
}).catch(err=>{
console.log('文本生成')
})
});
});
New Excel
node 使用 fs 模块复制生成多个模版
Edit-Excel
可以通过 exceljs 读取每个模版，并修改对应的数据(每个单元格可以使用 A2、F2 来获取位置，直接修改内容) exceljs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>excel</title>
</head>
<body>
<h1>Excel</h1>
<input type="file" id="file-input" />
<!-- <div id="template">
<h1>模版</h1>
<input type="file" id="file-input2"/>
</div> -->
</body>
<style>
#template {
display: block;
}
</style>
<script
type="text/javascript"
src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js"
></script>
<!-- <script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script> -->
<!-- <script src="https://cdn.jsdelivr.net/npm/xlsx-populate/dist/xlsx-populate.min.js"></script> -->
<!-- xlsx-populate -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"></script>
<script>
// 读取本地文件
function readFileForWorkbook(file) {
return new Promise((resolve, reject) => {
const reader = new FileReader();
let wb;
reader.onload = (res) => {
const data = res.target.result;
wb = XLSX.read(data, {
type: "binary",
});
resolve(wb);
};
reader.onerror = (e) => {
reject(e);
};
reader.readAsBinaryString(file);
});
}
function readExcelImageForWorksheet(file) {
const workbook = new ExcelJS.Workbook();
workbook.xlsx.load(file);
const worksheet = workbook.worksheets[0];
const row = worksheet.getRow(1);
const cell = row.getCell(1);
if (cell.isMerged && cell.master) {
const image = cell.master.image;
const base64Data = image.image.buffer.toString('base64');
const imgElement = document.createElement('img');
imgElement.src = `data:${image.image.contentType};base64,${base64Data}`;
const imageContainer = document.getElementById('imageContainer');
imageContainer.innerHTML = '';
imageContainer.appendChild(imgElement);
}
}
// function readExcelImage(file){
// return new Promise((resolve, reject) => {
// const reader = new FileReader();
// reader.onload = (res) => {
// const data = new Uint8Array(res.target.result);
// // let wb = XLSX.read(data, {
// // type: "array",
// // });
// // resolve(wb);
// const workbook = XLSX.read(data, { type: 'array' });
// // 选择要读取的工作表
// const worksheet = workbook.Sheets[workbook.SheetNames[0]];
// // 获取单元格中的图片
// const cell = worksheet.F2;
// const image = cell && cell.s && cell.s.picture;
// console.log('cell',cell);
// if (image) {
// // 处理图片数据
// const imageData = image._data;
// const base64Data = btoa(String.fromCharCode(...imageData));
// const imgElement = document.createElement('img');
// imgElement.src = `data:image/png;base64,${base64Data}`;
// const imageContainer = document.getElementById('imageContainer');
// imageContainer.innerHTML = '';
// imageContainer.appendChild(imgElement);
// }
// };
// reader.onerror = (e) => {
// reject(e);
// };
// reader.readAsArrayBuffer(file);
// });
// }
const fileInput = document.getElementById("file-input");
fileInput.addEventListener("change", function (event) {
const file = event.target.files[0];
readFileForWorkbook(file).then((wb) => {
console.log(wb.SheetNames);
const sheet = wb.Sheets[wb.SheetNames[0]];
console.log(sheet,'sheet');
// 遍历每一行
// 导出JSON
const data = XLSX.utils.sheet_to_json(sheet);
console.log(JSON.stringify(data));
fetch("http://localhost:3000/excel", {
method: "POST", // or 'PUT'
body: JSON.stringify(data),
headers: {
// "Content-Type": "application/json",
"Content-Type": "application/json;charset=utf-8",
},
})
.then((response) => response.json())
.then((data) => {
// 请上传补货清单的模版
// let template = document.getElementById('template');
// if(data.code == 0){
// template.style.display = 'block';
// }
// 导出多个文档
fetch("http://localhost:3000/export", {
method: "GET",
// body: JSON.stringify(data.data),
headers: {
// "Content-Type": "application/json",
"Content-Type": "application/json;charset=utf-8",
},
}).then(()=>{
window.alert(' excel生成成功')
}).catch(err=>{
console.log('文本生成')
})
});
});
readExcelImageForWorksheet(file);
//读取excel 中的图片
// readExcelImage(file).then((wb) => {
// console.log('readExcelImage');
// const sheet = wb.Sheets[wb.SheetNames[0]];
// console.log(sheet,'sheet123');
// // 遍历每一行
// const cell = sheet.F2;
// console.log(cell,'cell');
// const image = cell && cell.s && cell.s.picture;
// console.log(imgae,'image');
// if (image) {
// const base64 = image.replace(/^[^,]+,/, "");
// const img = document.createElement("img");
// img.src = base64;
// img.style.width = "100%";
// img.style.height = "100%";
// document.body.appendChild(img);
// }
// });
// XlsxPopulate.fromDataAsync(file)
// .then(workbook => {
// // Select the worksheet
// const worksheet = workbook.sheet(0);
// // Get the image from a specific cell
// const image = worksheet.cell('A1').image();
// if (image) {
// const imageBuffer = image._buffer;
// const base64Data = imageBuffer.toString('base64');
// const imgElement = document.createElement('img');
// imgElement.src = `data:image/png;base64,${base64Data}`;
// const imageContainer = document.getElementById('imageContainer');
// imageContainer.innerHTML = '';
// imageContainer.appendChild(imgElement);
// }
// })
// .catch(error => {
// console.log('Error:', error);
// });
});
// const fileInput2 = document.getElementById("file-input2");
// fileInput2.addEventListener("change", function (event) {
// const file = event.target.files[0];
// readFileForWorkbook(file).then((wb) => {
// console.log(wb.SheetNames);
// const sheet = wb.Sheets[wb.SheetNames[0]];
// const data = XLSX.utils.sheet_to_json(sheet);
// console.log(JSON.stringify(data));
// fetch("http://localhost:3000/template", {
// method: "POST", // or 'PUT'
// body: JSON.stringify(data),
// headers: {
// // "Content-Type": "application/json",
// "Content-Type": "application/json;charset=utf-8",
// },
// })
// .then((response) => response.json())
// .then((data) => {
// // 请上传补货清单的模版
// console.log("log");
// });
// });
// });
</script>
</html>
const express = require("express");
const app = express();
const fs = require("fs");
const path = require("path");
const excel = require("exceljs");
// var xlsx = require('node-xlsx');
// create application/json parser
let excelData;
app.use(express.json()); // for parsing application/json
app.use(express.urlencoded({ extended: true })); // for parsing application/x-www-form-urlencod
//*************post
// 设置路由 ( 接口 )
app.get("/", (req, res) => {
res.setHeader("Content-Type", "text/html");
res.sendFile(`${__dirname}/index.html`);
});
app.post("/excel", (req, res) => {
res.setHeader("Content-Type", "application/json");
console.log("********************");
const data = req.body;
// for (let i = 0; i < data.length; i++) {
// //处理excel 数据到补货清单
// }
excelData = data;
res.end(
JSON.stringify({
code: 0,
msg: "success",
data: data,
})
);
});
app.post("/template", (req, res) => {
res.setHeader("Content-Type", "application/json");
const data = req.body;
// for(let i=0;i<data.length;i++){
// //获取表单数据并替换表单数据
// }
// var buffer = xlsx.build(data);
// fs.writeFile('a.xlsx', buffer, function(err) {
// if (err) {
// console.log("Write failed: " + err);
// return;
// }
// console.log("Write completed.");
// });
res.end(
JSON.stringify({
code: 0,
msg: "success",
data: data,
})
);
});
// async function exportToTenFiles(templatePath, outputPath) {
// try {
// // Open the template file
// const workbook = await ExcelJS.Workbook.open(templatePath);
// // Get the first worksheet in the template
// const worksheet = workbook.getWorksheet(1);
// // Create a new workbook for each output file
// for (let i = 1; i <= 10; i++) {
// // Create a new workbook
// const outputWorkbook = new ExcelJS.Workbook();
// // Create a new worksheet in the output workbook
// const outputWorksheet = outputWorkbook.addWorksheet(`Output_${i}`);
// // Get the range of cells in the worksheet
// const range = worksheet.range('A1', 'A10');
// // Copy the data from the template to the output worksheet
// for (let row = 1; row <= 10; row++) {
// range.set(row, 1, `Output_${i}_Row_${row}`);
// }
// // Save the output workbook
// await outputWorkbook.xlsx.writeFile(`${outputPath}/Output_${i}.xlsx`);
// }
// console.log('Files exported successfully.');
// } catch (error) {
// console.error('Error:', error.message);
// }
// }
// 测试
app.get("/operate", async (req, res) => {
// 读取Excel文件
// const workbook = excel.readFile(path.join(__dirname, 'a.xlsx'));
// 读取Excel文件
const workbook = new excel.Workbook();
await workbook.xlsx.readFile(path.join(__dirname, "a.xlsx"));
console.log();
const worksheet = workbook.getWorksheet("门店销售&活动返图及补货需求");
// worksheet.eachSheet((sheet, sheetId) => {
// sheet.eachRow((row, rowNumber) => {
// console.log(`Row #${rowNumber}:`);
// });
// });
// worksheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {
// console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
// });
// const workbook = excel.createAsyncReader(fs.createReadStream(path.join(__dirname, 'a.xlsx')));
// const worksheet = workbook.getWorksheet('Sheet1');
// worksheet.eachRow((row, rowNumber) => {
// console.log(`Row #${rowNumber}:`, row.items());
// });
// // 编辑Excel文件
// worksheet.updateRow(0, {
// name: 'John Doe',
// age: 30,
// email: 'johndoe@example.com'
// });
// // 写入数据到Excel文件
// worksheet.addRow({
// name: 'Jane Smith',
// age: 25,
// email: 'janesmith@example.com'
// });
// 保存Excel文件
for (let i = 0; i < 10; i++) {
await workbook.xlsx
.writeFile(path.join(__dirname, `edited-data${i}.xlsx`))
.then(() => {
console.log(`File saved successfully.`);
})
.catch((error) => {
console.error(`Error saving file:`, error);
});
}
// await workbook.xlsx.writeFile(path.join(__dirname, 'edited-data.xlsx')).then(() => {
// console.log('File saved successfully.');
// }).catch((error) => {
// console.error('Error saving file:', error);
// });
// 关闭Excel文件
// workbook.end();
res.setHeader("Content-Type", "text/html");
res.sendFile(`${__dirname}/index.html`);
});
app.get("/export", async (req, res) => {
async function copyFile(sourcePath, destinationPath) {
fs.copyFile(sourcePath, destinationPath, (error) => {
if (error) {
console.error("An error occurred while copying the file:", error);
} else {
console.log("File copied successfully.");
}
});
}
await copyFile(
path.join(__dirname, "a.xlsx"),
path.join(__dirname, "a1.xlsx")
);
async function operateExcelFiles(data, fileNames) {
const workbook = new excel.Workbook();
await workbook.xlsx.readFile(path.join(__dirname, fileNames[0])); // Load the template file
// for (let i = 0; i < data.length; i++) {
const worksheet = workbook.getWorksheet("门店销售&活动返图及补货需求"); // Get the first worksheet from the template
// const currentData = data[i];
const currentFileName = fileNames[0];
// Update the worksheet with the current data
// Example: worksheet.getCell('A1').value = currentData.someValue;
// Save the worksheet to a new file
let i = 0;
const cell = worksheet.getCell("D3");
cell.value = data[i]["店铺名"];
const cell2 = worksheet.getCell("G3");
cell2.value = data[i]["性质"];
const cell3 = worksheet.getCell("K2");
cell3.value = data[i]["申请编号"];
const cell4 = worksheet.getCell("B7");
cell4.value = data[i]["商品名"];
const cell5 = worksheet.getCell("F7");
cell5.value = data[i]["采购数量"];
const cellImg = worksheet.getCell();
// Clear the existing image from the cell
cell.clear();
// Add the new image to the cell
const imageId = workbook.addImage({
filename: imagePath,
extension: "png",
});
// Set the image in the cell
cell.addImage(imageId, {
tl: { col: cell.col, row: cell.row },
ext: { width: cell.width, height: cell.height },
});
// const mergedCell = worksheet.getCell('K2').merged;
await workbook.xlsx.writeFile(currentFileName);
// }
}
const fileNames = ["a1.xlsx"];
operateExcelFiles(excelData, fileNames)
.then(() => {
console.log("Excel files edited successfully.");
})
.catch((error) => {
console.error("An error occurred while editing the Excel files:", error);
});
res.setHeader("Content-Type", "text/html");
res.sendFile(`${__dirname}/index.html`);
});
// 启动服务
app.listen(3000, () => {
console.log("Server started on port 3000");
});
TODO
读取 excel 中的图片，并保存图片到 excel
