How to Import and Export Excel XLSX Using Vue

This article demonstrates how software developers can add importing and exporting Excel file capabilities to their Vue applications using a Vue spreadsheet component and API library. This example utilizes SpreadJS, a popular enterprise-level Vue spreadsheet component, which offer a familiar Excel-like spreadsheet UI and import/export capabilities without any dependencies on Microsoft Excel.

Want to test exporting Excel File from You Vue Application? Download a Free Trial of SpreadJS Today!

Steps to Import & Export Excel (.XLSX) from Vue Apps

1. Create a Vue Spreadsheet Application
2. Add Excel Import Code
3. Add Data to the Import Excel File
4. Add Excel Export Code

Download a finished sample application to follow along with the tutorial.

Create a Vue Spreadsheet Application

Using the following command create a simple Vue project. This command will run create-vue, the Vue scaffolding tool, with optional prompts for features like TypeScript and testing support.

npm create vue@latest 

Note, you will be given several optional features such as TypeScript and Testing Support:

After creating the project, use the following commands to navigate to the project directory and install the necessary NPM dependencies for SpreadJS and File-Saver.

cd vue-io-xlsx
npm install
npm install @mescius/spread-sheets @mescius/spread-sheets-vue @mescius/spread-sheets-io @mescius/spread-sheets-charts @mescius/spread-sheets-shapes
npm install file-saver --save

Update the main.js file to integrate the spreadsheet components by replacing its content with the code below. This will enable the use of GcSpreadSheets, GcWorksheet, and GcColumn components throughout your application.

import { createApp } from "vue";
import App from "./App.vue";
import {
  GcSpreadSheets,
  GcWorksheet,
  GcColumn,
} from "@mescius/spread-sheets-vue";

let app = createApp(App);
app.component("gc-spread-sheets", GcSpreadSheets);
app.component("gc-worksheet", GcWorksheet);
app.component("gc-column", GcColumn);
app.mount("#app");

Next, update the App.vue file's template to render the host element for SpreadJS and create options for interacting with the Excel files, including import, modifying, and saving to Excel. 

<template>
  <div class="spreadsheet-container">
    <gc-spread-sheets class="spread-host" @workbookInitialized="initSpread">
    </gc-spread-sheets>
    <div class="options-container">
      <div class="option-row">
        <div class="inputContainer">
          <p>Open Excel File (.xlsx)</p>
          <input type="file" id="fileDemo" class="input" @change="changeFileDemo" />
          <input type="button" id="loadExcel" value="Open Excel" class="button" @click="loadExcel" />
          <p>Add Data</p>
          <input type="button" id="saveExcel" value="Add Revenue" class="button" @click="modifyExcel" />
          <p>Save Excel File (.xlsx)</p>
          <input id="exportFileName" value="export.xlsx" class="input" @change="changeExportFileName" />
          <input type="button" id="saveExcel" value="Save Excel" class="button" @click="saveExcel" />
        </div>
      </div>
    </div>
  </div>
</template>

After the template, within the script tags, declare the imports of the needed SpreadJS and File-Saver components. These imports will enable access to the Vue spreadsheet object of the SpreadJS library and support for charts, shapes, and import/export to/from the spreadsheet instance.

import "@mescius/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css";
import * as GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-io";
import { saveAs } from "file-saver";

In App.vue, define the component options to initialize the Vue Spreadsheet and handle Excel files. The initSpread method initializes the Vue spreadsheet instance, while there are placeholders for loadExcel, modifyExcel, and saveExcel methods. We will cover these file operations in the later sections.

export default {
  name: "App",
  methods: {
    // Initialize the Vue Spreadsheet Instance
    initSpread: function (spread) {
      this.spread = spread;
      let sheet = this.spread.getActiveSheet();
      this.revenueCount = 8;
      this.newRowIndex = 11;
    },
    changeFileDemo(e) {
      this.importExcelFile = e.target.files[0];
    },
    changeExportFileName(e) {
      this.exportFileName = e.target.value;
    },
    loadExcel() {
    // Load an existing Excel file into the Vue spreadsheet app
    },
    modifyExcel() {
    // Modify the import Excel file within the Vue instance
      },
    saveExcel() {
    // Save Vue spreadsheet to local Excel XLSX file
    },
  },
};

Save all of the files, and run the Vue app.

npm run build

You have now created a Vue spreadsheet application.

Add Excel Import Code to a Vue Application

Invoke the SpreadJS Workbook's import method within the loadExcel method to read the user selected Excel file into the Vue spreadsheet object and display it within the UI.

    loadExcel() {
      let spread = this.spread;
      let excelFile = this.importExcelFile;
      let options = {
        fileType: GC.Spread.Sheets.FileType.excel,
      };
      // Import an existing Excel file to Vue spreadsheet
      spread.import(
        excelFile,
        () => {
          console.log("Import successful");
        },
        (e) => {
          console.error("Error during import:", e);
        },
        options
      );
    },

 You can do the same thing with an Excel file on a server by reading it from a URL.

An Excel (.xlsx) file can now be imported, viewed, and modified within the Vue spreadsheet component.

Add Data to the Imported Excel File

In this tutorial we will use the “Profit loss statement” Excel template seen here:

Within the modifyExcel method, developers can programmatically add a new row to the spreadsheet, fill it with revenue data, add the sparkline visualization, and set a formula to calculate the sum for the year.

modifyExcel() {
    let spread = this.spread;
    let sheet = spread.getActiveSheet();
    // Add a new row for the next revenue item
    sheet.addRows(this.newRowIndex, 1);
    // Copy styles from an existing row
    sheet.copyTo(10, 1, this.newRowIndex, 1, 1, 29, GC.Spread.Sheets.CopyToOptions.style);
    // Set the new row's first column with the revenue label
    var cellText = ("Revenue " + this.revenueCount++);
    sheet.setValue(this.newRowIndex, 1, cellText);
    // Fill the row with random revenue data
    for (var c = 3; c < 15; c++) {
        sheet.setValue(this.newRowIndex, c, Math.floor(Math.random() * 200) + 10);
    }
    // Add a sparkline chart for the revenue data
    var data = new GC.Spread.Sheets.Range(this.newRowIndex, 3, 1, 12);
    var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
    setting.options.seriesColor = "Text 2";
    setting.options.lineWeight = 1;
    setting.options.showLow = true;
    setting.options.showHigh = true;
    setting.options.lowMarkerColor = "Text 2";
    setting.options.highMarkerColor = "Text 1";
    sheet.setSparkline(this.newRowIndex, 2, data, GC.Spread.Sheets.Sparklines.DataOrientation.horizontal, GC.Spread.Sheets.Sparklines.SparklineType.line, setting);
    // Add a formula to calculate the sum for the new row's year data
    sheet.setFormula(this.newRowIndex, 15, "=SUM([@[Jan]:[Dec]])");
    // Move to the next row for future modifications
    this.newRowIndex++;
    // Set a fixed value in the next column
    sheet.setValue(this.newRowIndex, 16, 0.15);
    // Copy formula settings to other parts of the row
    sheet.copyTo(10, 17, this.newRowIndex, 17, 1, 13, GC.Spread.Sheets.CopyToOptions.formula);
},

Notice, with this code logic applied the new row of revenue data is added cohesively to the existing Vue spreadsheet data. 

Add Excel Export Code to a Vue Application

Invoke the SpreadJS Workbook's export method within the saveExcel method to save the modified Vue spreadsheet data to a local Excel .xlsx file.

saveExcel() {
      let spread = this.spread;
      var fileName = "Excel_Export.xlsx";
      // Save Vue spreadsheet to local Excel XLSX file
      spread.export(
        function (blob) {
          // save blob to a file
          saveAs(blob, fileName);
        },
        function (e) {
          console.log(e);
        },
        {
          fileType: GC.Spread.Sheets.FileType.excel,
        }
      );
    },

The exported Excel file contains the modified data and keeps the same formatting and styling as seen in the Vue app display, and from the originally imported Excel file.

Learn More About this Vue Spreadsheet Component

This article only scratches the surface of the full capabilities of SpreadJS, the Vue spreadsheet component. Review the documentation to see some of the many available features, and check out our online demos to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about SpreadJS and the new features added in the latest release check out our release pages and this video: