Create A NodeJs Project
npm init --y
Install Library
npm install express multer xlsx
Create Server And Run Server
Create a File index.js
const express = require("express");
const app = express();
app.listen(3000,()=>{
console.log("Started");
})
multer এর middleware টি index.js এ যুক্ত করি
// index.js
const multer = require('multer');
// Multer middleware to handle file uploads
const storage = multer.memoryStorage();
const upload = multer({ storage: storage });
routes/uploadexcel.js নামে একটি কন্ট্রোলার বানাই
// routes/uploadexcel.js
const xlsx = require('xlsx');
const getCellValue = (worksheet, cellReference) => {
try {
return worksheet[cellReference].v || '';
} catch (error) {
console.error(`Error getting value for cell ${cellReference}: ${error.message}`);
return '';
}
};
const handleFileUpload = (req, res) => {
try {
// Get the uploaded file buffer
const fileBuffer = req.file.buffer;
// Parse Excel file
const workbook = xlsx.read(fileBuffer, { type: 'buffer' });
// Get the first worksheet
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
// Extract specific columns from the first three rows
const companyNames = getCellValue(worksheet, 'B2');
const to = getCellValue(worksheet, 'H2');
const date = getCellValue(worksheet, 'J2');
const dest = getCellValue(worksheet, 'N2');
const type = getCellValue(worksheet, 'B3');
const fm = getCellValue(worksheet, 'H3');
const flight = getCellValue(worksheet, 'J3');
const mawb = getCellValue(worksheet, 'N3'); // Corrected cell reference for mawb
// Create a JSON object with named properties for data before the table
const jsonData = {
companyName: companyNames,
to: to,
date: date,
dest: dest,
type: type,
fm: fm,
flight: flight,
mawb: mawb
};
// Extract table data starting from line 5 as a JSON array
const excelTable = xlsx.utils.sheet_to_json(worksheet, { header: 1, range: 6 });
// Filter out empty rows from the table data
const nonEmptyRows = excelTable.filter(row => row.some(cell => cell !== ''));
// Convert the non-empty table data to a JSON array with specific keys
const excelData = nonEmptyRows.map(row => ({
awb: row[1] || '',
ref: row[2] || '',
shipper: row[3] || '',
shipper_address: row[4] || '',
consignee: row[5] || '',
bin: row[6] || '',
dest: row[7] || '',
cnee_address: row[8] || '',
ctc: row[9] || '',
tel_no: row[10] || '',
nop: row[11] || '',
wt: row[12] || '',
vol: row[13] || '',
dsct: row[14] || '',
cod: row[15] || '',
val: row[16] || '',
re: row[17] || '',
bag_no: row[18] || '',
}));
// Modify values based on conditions
excelData.forEach(row => {
// Example: Modify data based on a condition
if (row.awb === "fh") {
row.awb = 'df';
}
// Add more conditions and modifications as needed
});
// Save jsonData and excelData to a file or database, for example
// You can replace this with your own logic to save the data
res.json({ success: true, data: { jsonData, excelData } });
} catch (error) {
console.error(error);
res.status(500).json({ success: false, error: 'Error processing the Excel file.' });
}
};
module.exports = { handleFileUpload };
index.js এ route হিসাবে যোগ করি
const { handleFileUpload } = require('./routes/uploadexcel');
// Define route for uploading Excel file
app.post('/uploadexcel', upload.single('excelFile'), handleFileUpload);
আমি যদি express সার্ভার থেকে index.html নামে একটি ফাইল কে আপলোড পেজ হিসাবে দেখতে চাই তাহলে index.js এ নিচের কোড যোগ করতে হবে এবং index.html নামে একটি ফাইল তৈরী করতে হবে।
index.js
// Serve HTML form for file upload
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Excel Upload</title>
</head>
<body>
<h1>Excel Upload</h1>
<form action="/upload" method="post" enctype="multipart/form-data">
<label for="excelFile">Choose Excel File:</label>
<input type="file" id="excelFile" name="excelFile" accept=".xlsx, .xls">
<br>
<button type="submit">Upload</button>
</form>
</body>
</html>
আমি যদি reactjs থেকে করতে চাই তাহলে নিচের মতো কম্পোনেন্ট বানাতে হবে।
import React, { useState } from "react";
import axios from "axios";
import "bootstrap/dist/css/bootstrap.min.css";
import { toast } from "react-toastify";
function ExcelUpload() {
const [file, setFile] = useState(null);
const handleFileChange = (event) => {
setFile(event.target.files[0]);
};
const handleUpload = async () => {
try {
const formData = new FormData();
formData.append("excelFile", file);
const response = await axios.post(
"http://localhost:4000/uploadexcel",
formData,
{
headers: {
"Content-Type": "multipart/form-data",
},
}
);
toast.success("File uploaded successfully!", {
position: toast.POSITION.TOP_RIGHT,
});
console.log("File uploaded successfully!", response.data);
} catch (error) {
toast.error("File upload Failed!", {
position: toast.POSITION.TOP_RIGHT,
});
console.error("Error uploading file:", error.message);
}
};
return (
<div>
<h1>Excel Upload from React</h1>
<div className="mb-3">
<label htmlFor="SmallFile" className="form-label"></label>
<input
className="form-control
form-control"
id="SmallFile"
type="file"
accept=".xlsx, .xls"
onChange={handleFileChange}
/>
</div>
<button
className="btn btn-primary"
onClick={handleUpload}
disabled={!file}
>
{" "}
Upload{" "}
</button>
</div>
);
}
export default ExcelUpload;