How to Create a Xlsx or Xls Data into MySql Row Data with Node.js, Adonis.js, and Vue.js

const MarketingData = use(“App/Models/Admins/MarketingData”);
const Database = use(“Database”);
const xlsx = require(“xlsx”);
const Helpers = use(“Helpers”);
class MarketingDataController {
async uploadExcel({ request, response }) {
try {
const excelFile = request.file(“excelfile”, {
types: [“application”],
extnames: [“xls”, “xlsx”],
size: “2mb”,
});
if (!excelFile) {
return response.status(400).json({
success: false,
message: “Please upload an Excel file”,
});
}
const filePath = Helpers.tmpPath(uploads/${new Date().getTime()}_${excelFile.clientName});
await excelFile.move(Helpers.tmpPath(“uploads”), {
name: ${new Date().getTime()}_${excelFile.clientName},
});
if (!excelFile.moved()) {
return response.status(500).json({
success: false,
message: “Error moving the file”,
error: excelFile.error(),
});
}
const workbook = xlsx.readFile(filePath);
const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];
const data = xlsx.utils.sheet_to_json(sheet);
await Database.transaction(async (trx) => {
for (const record of data) {
// Check if email or phone already exists in the database
const existingRecord = await MarketingData.query()
.where(’email’, record.email)
.orWhere(‘phone’, record.phone)
.first();
// If email or phone already exists, skip inserting this record
if (existingRecord) {
console.log(Skipping record with email ‘${record.email}’ or phone ‘${record.phone}’ as it already exists in the database.);
continue;
}
// Insert the record into the database
await MarketingData.create({
name: record.name,
email: record.email,
phone: record.phone,
remarks: record.remarks,
created_at: new Date(),
updated_at: new Date(),
}, trx);
}
});
return response.status(200).json({
success: true,
message: “Data uploaded and inserted successfully”,
});
} catch (error) {
console.log(“File Read/Parse Error:”, error);
return response.status(500).json({
success: false,
message: “Server error”,
});
}
}
}
module.exports = MarketingDataController;

<template>
<div class=”profileView pa-4 responsive-height”>
<v-container class=”custom-container”>
<v-row>
<v-col lg=”12″ md=”6″ sm=”12″ cols=”12″>
<v-card
class=”verification-card”
flat
tile
:color=”$vuetify.theme.dark ? ‘#172233’ : ‘#F1F7FB'”
>
<v-card-actions class=”px-0″>
<v-card-title class=”pl-1″>
<v-icon class=”pr-2″ color=”#708AA7″
>mdi mdi-account-outline</v-icon
>Import Excel Data
</v-card-title>
</v-card-actions>

<v-card-subtitle>
Click the box below to select file or you can drag and drop your
<span>xls, .xlsx</span>
files.
</v-card-subtitle>
<div class=”zone-wrapper”>
<v-card-text class=”text-center”>
<vue-dropzone
ref=”myVueDropzone”
id=”dropzone”
:options=”dropzoneOptions”
@vdropzone-success=”handleUpload”
></vue-dropzone>
<div class=”dropzone-content”>
<v-icon large class=”mb-3″ color=”#708AA7″
>mdi mdi-tray-arrow-up</v-icon
>
<p class=”mb-0″>Drop File Here or <strong>Browse</strong></p>
<span>File size max 2MB</span>
</div>
</v-card-text>
<div class=”details”>
<h3>Need Sample Excel File?</h3>
<p>
Here We Have Linked 1 Excel File for an example. To get the
example file, Please
<a href=”/sample.xlsx” download target=”_blank”>Click Here</a>
</p>
</div>
</div>
</v-card>
</v-col>
</v-row>
<v-row>
<v-col cols=”12″>
<v-card
elevation=”0″
:color=”$vuetify.theme.dark ? ‘#131c29’ : ‘#E0EAF2′”
>
<v-row class=”mb-1″>
<v-col cols=”12″>
<v-text-field
v-model=”search”
prepend-icon=”mdi-magnify”
label=”Search here…”
single-line
solo
hide-details
class=”custom-v-input”
></v-text-field>
</v-col>
</v-row>
<v-row dense>
<v-col cols=”12″ sm=”12″>
<v-card
class=”pa-3 rounded-0 elevation-0″
:color=”$vuetify.theme.dark ? ‘#172233’ : ‘#E8F1F8′”
>
<v-sheet
class=”transactionTable depositTabTable”
:color=”$vuetify.theme.dark ? ‘#1A2A3E ‘ : ‘#F1F7FB'”
>
<v-data-table
:headers=”headers”
:items=”marketingusers”
:search=”search”
:items-per-page=”10″
>
<template v-slot:item.sl=”{ item, index }”>
{{ index + 1 }}
</template>
</v-data-table>
</v-sheet>
</v-card>
</v-col>
</v-row>
</v-card>
</v-col>
</v-row>
</v-container>
</div>
</template>

<script>
import vue2Dropzone from “vue2-dropzone”;
import “vue2-dropzone/dist/vue2Dropzone.min.css”;
import { mapState, mapActions } from “vuex”;
import { MARKETINGDATA__ACTIONS } from “@/store/action-types”;

export default {
name: “dropzoneView”,
data() {
return {
search: “”,
dropzoneOptions: {
url: “#”, // Not used as we handle the upload manually
thumbnailWidth: 100,
maxFilesize: 2, // 2MB limit
acceptedFiles: “.xlsx,.xls”, // Only accept these file types
headers: { “My-Awesome-Header”: “header value” },
},
headers: [
{ text: “SL”, value: “sl”, align: “start” },
{ text: “Name”, value: “name” },
{ text: “Email”, value: “email” },
{ text: “Mobile No”, value: “phone” },
{ text: “Remarks”, value: “remarks” },
],
};
},
components: {
vueDropzone: vue2Dropzone,
},
computed: {
…mapState(“marketingdata”, [“marketingusers”]),
},
methods: {
…mapActions(“marketingdata”, [MARKETINGDATA__ACTIONS.GETMARKETINGUSERS]),
async handleUpload(file) {
const formData = new FormData();
formData.append(“file”, file);

// Dispatch Vuex action to upload file
await this.$store.dispatch(“marketingdata/uploadFile”, formData);
},
},
async mounted() {
await this[MARKETINGDATA__ACTIONS.GETMARKETINGUSERS]();
},
};
</script>