I’m using the MERN stack for a e-commerce project I’m working on. I need to manually update the prices of several products using a excel spreadsheet, once the provider sends it to me. Since there are many products, I need to perform a bulk update with the information provided on the spreadsheet. Some prices my vary, other will remain the same. This is something I’ll need to perform every month (as prices vary from month to month) I’ve managed to create a page that shows the values on the spreadsheet. That is currently working.
The Problem: I haven’t been able to save the new information (update the price field) on my MongoDB collection (called “products”)
I’d like to keep it as simple as possible. So far I’m trying to do this from the front end… Perhaps I need to do it from the back end? Add code to my “Product.Controller” file?
After some reading, I’ve decided to go with the xlsx library, but I’m open to suggestions.
Here is a sample of my MongoDB Collection:
Here is the code I’m using to “show” the spreadsheet information:
function AdminBulkUpdate() {
const [data, setData] = useState([]);
const handleFileUpload = (e) => {
const reader = new FileReader();
reader.readAsBinaryString(e.target.files[0]);
reader.onload = (e) => {
const data = e.target.result;
const workbook = XLSX.read(data, { type: "binary" });
const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];
const parsedData = XLSX.utils.sheet_to_json(sheet);
setData(parsedData);
};
}
return (
<Row className="m-5">
<Col md={2}>
<AdminLinksComponent />
</Col>
<Col>
<input
type="file"
accept=".xlsx, .xls"
onChange={handleFileUpload}
/>
{data.length > 0 && (
<table>
<thead>
<tr>
{Object.keys(data[0]).map((key) => (
<th key={key}>{key}</th>
))}
</tr>
</thead>
<tbody>
{data.map((row, index) => (
<tr key={index}>
{Object.values(row).map((value, index) => (
<td key={index}>{value}</td>
))}
</tr>
))}
</tbody>
</table>
)}
</Col>
</Row>
);
}
export default AdminBulkUpdate;
This is my Product Model (Schema):
const mongoose = require("mongoose")
const Review = require ("./ReviewModel")
const imageSchema = mongoose.Schema({
path: {type: String, required: true}
})
const productSchema = mongoose.Schema({
name: {
type: String,
required: true,
unique: true,
},
description: {
type: String,
required: true,
},
category: {
type: String,
required: true,
},
count: {
type: Number,
required: true,
},
price: {
type: Number,
required: true
},
rating: {
type: Number
},
reviewsNumber: {
type: Number,
},
sales: {
type: Number,
default: 0
},
attrs: [
{key: {type: String}, value: {type: String}}
],
images: [imageSchema],
reviews: [
{
type: mongoose.Schema.Types.ObjectId,
ref: Review,
}
],
}, {
timestamps: true,
})
const Product = mongoose.model("Product", productSchema)
module.exports = Product
The expected outcome is: When I receive a spreadsheet with the new product’s prices, I’ll upload such file from the front end and update the price field on MongoDB. This needs to happen on demand.
Hope you can help me with this. Thanks.