Vuex and front-end forms perform a "combination punch" to achieve interactive enhancement of large-screen display applications

Vuex is a state management pattern developed for Vue.js applications. It uses a centralized store to manage the state of all components of the application, and uses corresponding rules to ensure that the state changes in a predictable manner.

The following figure is an example of a large-screen display interface that is very common in product development.
Through the Vuex provided by Vue, the upper three dashboards and the lower table components share the same data source, and synchronous response updates after data changes have been realized.

"Excellent large-screen display function, can it support the import and export of Excel data, and can table data be edited and updated in real time?"

If you've been developing software for a long time, you've probably heard this soul-crushing question from end customers or product managers more than once. For non-technical people, it is a very normal and easy to implement requirement to require Excel import/export/display.

But in reality, this question often scares front-end developers. Working with Excel files is a lot of work.
This problem can be simplified with a front-end form, which embeds the spreadsheet into a Web application. Also interact with other components. This blog post will look at how to use this existing big screen Vue application as a base and enhance it with a front-end spreadsheet.

This article assumes that you already know HTML, CSS, and JavaScript. And the basic application of Vue. If you have used Vuex, of course it will be easier to understand, if not, don't worry. The application of VueX in this project is simple.

About VueX, you can learn more about Vue official website

This article will be divided into the following sections

• The original application of Vuex
• Add real-time editing capabilities to your app
• Add Excel data import function
• Add export to Excel function

The original application with Vuex As you can see in the image above, the Vue application that will be used is a simple large screen display interface with several dashboards of summary information and a data table.

You can get the Vue application project code through the attachment below, and then run "npm install" and "npm run serve" to start the application.
Attachment download address:
https://gcdn.grapecity.com.cn/forum.php?mod=attachment&aid=MjI1NzA1fDNkMDNjNjQ2fDE2NjAxMTUxMjF8NjI2NzZ8OTk3MTg%3D

The original Vue application code structure is as follows:

• Both Vuex and Vue applications are defined in main.js.
• There are several single-file Vue components, located in the components folder.

The Vuex store code is as follows. The initial state has only one value set to recentSales, indicating recent sales records:

 const store = new Vuex.Store({  
state: {  
    recentSales  
  }  
});

With the recentSales data, how to generate three statistical tables and one table? Open the Dashboard.vue component. In it, you can see that several computed properties are generated based on the data in the Vuex store:

 <template>
  <div style="background-color: #ddd">
    <NavBar title="销售仪表板"/>
    <div class="container">
      <div class="row">
        <TotalSales :total="totalSales"/>
        <SalesByCountry :salesData="countrySales"/>
        <SalesByPerson :salesData="personSales"/>
        <SalesTableBySpreadjs :tableData="salesTableData"/>
        <SalesTable :tableData="salesTableData"/>
        
      </div>
    </div>
  </div>
</template>

<script>
import NavBar from "./NavBar";
import TotalSales from "./TotalSales";
import SalesByCountry from "./SalesByCountry";
import SalesByPerson from "./SalesByPerson";
import SalesTable from "./SalesTable";
import SalesTableBySpreadjs from "./SalesTableBySpreadjs";
import { groupBySum } from "../util/util";

export default {
  components: { NavBar, SalesByCountry, SalesByPerson, SalesTable, TotalSales ,SalesTableBySpreadjs},
  computed: {
    totalSales() {
      const total = this.$store.state.recentSales.reduce(
        (acc, sale) => (acc += sale.value),
        0
      );
      return parseInt(total);
    },
    countrySales() {
      const items = this.$store.state.recentSales;
      const groups = groupBySum(items, "country", "value");
      return groups;
    },
    personSales() {
      const items = this.$store.state.recentSales;
      const groups = groupBySum(items, "soldBy", "value");
      return groups;
    },
    salesTableData() {
      return this.$store.state.recentSales;
    }
  }
};
</script>

Therefore, the single dataset recentSales can currently provide consistent data for several dashboards and tables displayed on this large screen. Since the data is in the Vuex store, all the dashboard panels are automatically updated if the data is updated.
This feature will come in handy when we replace existing spreadsheets with editable spreadsheets for editing.

Add front-end spreadsheets to your Vue app

We will replace this html table with the front-end spreadsheet, create a new vue file in the component folder, name it SalesTableBySpreadjs.vue, and add a template to it:

 <template>
  <TablePanel title="近期销售额">
    <gc-spread-sheets
      :hostClass="hostClass"
      @workbookInitialized="workbookInit"
      style="height: 300px"
    >
      <gc-worksheet
        :dataSource="tableData"
        :autoGenerateColumns="autoGenerateColumns"
      >
        <gc-column
          :width="50"
          :dataField="'id'"
          :headerText="'ID'"
          :visible="visible"
          :resizable="resizable"
        >
        </gc-column>
        <gc-column
          :width="300"
          :dataField="'client'"
          :headerText="'Client'"
          :visible="visible"
          :resizable="resizable"
        >
        </gc-column>
        <gc-column
          :width="350"
          :headerText="'Description'"
          :dataField="'description'"
          :visible="visible"
          :resizable="resizable"
        >
        </gc-column>
        <gc-column
          :width="100"
          :dataField="'value'"
          :headerText="'Value'"
          :visible="visible"
          :formatter="priceFormatter"
          :resizable="resizable"
        >
        </gc-column>
        <gc-column
          :width="100"
          :dataField="'itemCount'"
          :headerText="'Quantity'"
          :visible="visible"
          :resizable="resizable"
        >
        </gc-column>
        <gc-column
          :width="100"
          :dataField="'soldBy'"
          :headerText="'Sold By'"
          :visible="visible"
          :resizable="resizable"
        ></gc-column>
        <gc-column
          :width="100"
          :dataField="'country'"
          :headerText="'Country'"
          :visible="visible"
          :resizable="resizable"
        ></gc-column>
      </gc-worksheet>
    </gc-spread-sheets>
  </TablePanel>
</template>

Among them, the gc-spread-sheets element creates a spreadsheet and defines how to display the columns of data. The dataField property in gc-column tells the column which property of the underlying dataset should be displayed.

Next is the js part:

 import "@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css";

_// SpreadJS imports_  
import "@grapecity/spread-sheets-vue";  
import Excel from "@grapecity/spread-excelio";

import TablePanel from "./TablePanel";  
export default {  
  components: { TablePanel },  
  props: ["tableData"],  
  data(){  
      return {  
        hostClass:'spreadsheet',  
        autoGenerateColumns:true,  
        width:200,  
        visible:true,  
        resizable:true,  
        priceFormatter:"$ #.00"  
      }  
    },  
  methods: {  
      workbookInit: function(_spread_) {  
        this._spread = spread;  
      }  
    }  
};

It can be done with very little code. Several of the data properties and methods are configuration options bound to the pure front-end spreadsheet component, and the workbookInit method is the callback that SpreadJS calls when the worksheet is initialized.

Go back to the Dashboard.vue file and add the SalesTableBySpreadjs component you just created.
Then run it again to display the spreadsheet data:

 <template>
  <div style="background-color: #ddd">
    <NavBar title="销售仪表板"/>
    <div class="container">
      <div class="row">
        <TotalSales :total="totalSales"/>
        <SalesByCountry :salesData="countrySales"/>
        <SalesByPerson :salesData="personSales"/>
        <SalesTableBySpreadjs :tableData="salesTableData"/>
<SalesTable :tableData="salesTableData"/>

        
      </div>
    </div>
  </div>
</template>

<script>
import NavBar from "./NavBar";
import TotalSales from "./TotalSales";
import SalesByCountry from "./SalesByCountry";
import SalesByPerson from "./SalesByPerson";
import SalesTable from "./SalesTable";
import SalesTableBySpreadjs from "./SalesTableBySpreadjs";
import { groupBySum } from "../util/util";

export default {
  components: { NavBar, SalesByCountry, SalesByPerson, SalesTable, TotalSales ,SalesTableBySpreadjs},
  computed: {
    totalSales() {
      const total = this.$store.state.recentSales.reduce(
        (acc, sale) => (acc += sale.value),
        0
      );
      return parseInt(total);
    },
    countrySales() {
      const items = this.$store.state.recentSales;
      const groups = groupBySum(items, "country", "value");
      return groups;
    },
    personSales() {
      const items = this.$store.state.recentSales;
      const groups = groupBySum(items, "soldBy", "value");
      return groups;
    },
    salesTableData() {
      return this.$store.state.recentSales;
    }
  }
};
</script>

Now that we have replaced the original html table with a complete spreadsheet, the amount displayed in the Amount column in the spreadsheet can be edited. For example, if you change the amount on line 6 from $35,000 to $3500, you can see that the three dashboards above are also updated at the same time.

The reason is that SpreadJS updates its data source synchronously after being edited => recentSales in VUEX store.

At this point we have an enhanced dashboard that updates in real time as the data changes. Next we can further enhance it by exporting and importing Excel data.

Export as Excel file

Adding Excel export functionality to a worksheet is easy. First, add an export button to the dashboard. Put this at the bottom of the table pane, after the gc-spread-sheets closing tag:

 </gc-spread-sheets>  
    <div class="row my-3">
      <div class="col-sm-4">
        <button class="btn btn-primary mr-3" @click="exportSheet">
          导出文件
        </button>
      </div>
    </div>
  </TablePanel>  
</template>

Next, add the exportSheet method that is triggered when clicked, and import a function from the NPM package named file-saver:

 import { saveAs } from 'file-saver';

Then add exportSheet to the component's method object:

 exportSheet: function () {
      const spread = this._spread;
      const fileName = "SalesData.xlsx";
      //const sheet = spread.getSheet(0);
      const excelIO = new IO();
      const json = JSON.stringify(
        spread.toJSON({
          includeBindingSource: true,
          columnHeadersAsFrozenRows: true,
        })
      );
      excelIO.save(
        json,
        function (blob) {
          saveAs(blob, fileName);
        },
        function (e) {
          console.log(e);
        }
      );
    },

Run the test and click the button to get the exported excel file directly.
Note that we set two serialization options: includeBindingSource and columnHeadersAsFrozenRows. to ensure that the data bound to the worksheet is exported correctly and that the worksheet contains column headers.

Excel data import

In the template, add the following code to add a file type input for importing the file:

 <div class="col-sm-8">
        <button class="btn btn-primary float-end mx-2">导入文件</button>
        <input
          type="file"
          class="fileSelect float-end mt-1"
          @change="fileChange($event)"
        />
</div>

Then add the fileChange method to the component's method object:

 fileChange: function (e) {
      if (this._spread) {
        const fileDom = e.target || e.srcElement;
        const excelIO = new IO();
        //const spread = this._spread;
        const store = this.$store;

        excelIO.open(fileDom.files[0], (data) => {
          const newSalesData = extractSheetData(data);

          store.commit("updateRecentSales", newSalesData);
        });
      }
    },

After selecting the file, import it using ExcelIO in SpreadJS. Get the json data in it. Pass in the custom function extractSheetData, extract the required data from it, and then submit it back to the Vuex store to update the recentSales data.

The extractSheetData function can be found in the src/util.util.js file. The extractSheetData function assumes that the data in the imported worksheet has the same columns as the original dataset. If someone uploads a spreadsheet that doesn't meet this requirement, it won't be parsed. This should be an acceptable limit for most clients. When the data does not match, you can also try to give the customer a prompt message.

In addition, you also need to add updateRecentSales to the Vuex store in main.js to update the data,
The modified store is as follows:

 const store = new Vuex.Store({
  state: {
    recentSales
  },
  mutations: {
    updateRecentSales (state,param) {
      
      let sales=state.recentSales;
      let arr=sales.map(function(o){return o.id});
      param.forEach((newsale)=>{
          if(arr.indexOf(newsale.id)>0){
            console.log("update");
            state.recentSales[arr.indexOf(newsale.id)]=newsale;
          }
          else{
            console.log("add");
            state.recentSales.push(newsale);
          }
      });
      console.log(state.recentSales);
    }
  },
  actions: {
    updateRecentSales ({commit},param) {
      commit('updateRecentSales',param)
    }
  }
});

It can be seen that after the Vuex store calls commit, it will trigger the updateRecentSales method to update recentSales, update it when the id is the same, and add a new id when there is a new one.
Finally, the SpreadJS sheet and all dashboard panels are updated synchronously to reflect the new data.

The final project download address:
https://gcdn.grapecity.com.cn/forum.php?mod=attachment&aid=MjI1NzA2fDcxYzkxZjZmfDE2NjAxMTUxMjF8NjI2NzZ8OTk3MTg%3D

The combination of Vue, Vuex and SpreadJS makes the enhancement development of this application very convenient. With Vue's templating and data binding, Vuex's managed shared state, responsive data storage, and pure front-end interactive spreadsheets, complex enterprise JavaScript applications can be created in minutes.

If you are interested, you can visit more online examples:
https://demo.grapecity.com.cn/spreadjs/gc-sjs-samples/index.html