excel upload - pythonbd

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;

MERN