How to Create a Xlsx or Xls Data into MySql Row Data with Node.js, Adonis.js, and Vue.js
Tags: node, vue, adonis, vuex
Published: 2024-06-11 19:04:45

**Introduction**
Uploading and processing Excel files is a common requirement for many applications, especially those dealing with bulk data entry. This blog post will guide you through creating a feature to upload Excel files, process their content, and display the data in a table format using Node.js, Adonis.js, and Vue.js. We will leverage the power of these frameworks to build a robust and efficient solution.

**Prerequisites**
Before we start, ensure you have the following installed:

- Node.js
- Adonis.js CLI
- Vue.js CLI

**Adonis API**

```
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;
```
**Setting Up the Frontend with Vue.js**

```
<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>
```