preface

Recently, the author finally completed the initial construction of H5-Dooring background management system, and has the preliminary data acquisition and data analysis ability. Next, we will review several knowledge points involved in it, and one by one explain its solution in Dooring H5 visual editor. The author will divide it into three articles, and the main scenarios are as follows:

  • How to use JavaScript to achieve front-end import and export Excel files (H5 editor actual combat duplicate disk)
  • How does the front end generate multi-dimensional data visualization analysis report based on the data in the table
  • How to realize the permission route and permission menu under the member management system

The above scenario is also a front end engineer in the development of background management system often encountered or will encounter problems, this article is the first of the above introduction, you will learn:

  • Use JavaScript to implement front-end import Excel files and automatically generate editable Table components
  • Using JavaScript to achieve front-end one-key export of Excel files based on Table data
  • Basic use of XLSX and Js-export-Excel

The body of the

The content materials for the rest of this article are based on screenshots from the H5 Visual Editor (H5-Dooring) project, and if you want to experience it for real, visit the H5-Dooring website. Let’s go straight to the implementation of our solution.

1. UseJavaScriptImplement front-end importExcel fileAnd automatically generateEditable Table component

Before we start the implementation, let’s look at the implementation.

1.1 Implementation Effect

Import an Excel file and pass theantdtheThe table componentRender the table:Edit the table component:Real-time rendering visualization chart after saving table data:The above is the complete process of importing excel files, editing tables, and finally dynamically generating charts.

1.2 Implement one-click import of Excel files and table generation

The importexcelWe can use the function of the filejavascriptA native way to implement parsing, such as usingfileReaderThese apis are native, but for the sake of development efficiency and later maintenance, I use them hereantdtheThe Upload componentandXLSXTo upload files and parse the function. Because we useantdthetableComponent to render data, so we need to manually convert the parsed data intotableSupported data formats. The general process is as follows:So what we need to do is we need toUploadGet file data toxlsxBy thexlsxGenerate the parse object, and finally we usejavascriptAlgorithm willxlsxThe ant table supports the data format. So here we useFileReaderObject to convert the file toBinaryStringAnd then we can usexlsxthebinaryMode to readexcelData, code as follows:

// Parse and extract excel data
let reader = new FileReader();
reader.onload = function(e) {
  let data = e.target.result;
  let workbook = XLSX.read(data, {type: 'binary'});
  let sheetNames = workbook.SheetNames; // Set of worksheet names
  let draftObj = {}
  sheetNames.forEach(name= > {
    // Get the specified worksheet by the worksheet name
    let worksheet = workbook.Sheets[name]; 
    for(let key in worksheet) {
      // v reads the original value of the cell
      if(key[0]! = ='! ') {
        if(draftObj[key[0]]) {
          draftObj[key[0]].push(worksheet[key].v)
        }else {
          draftObj[key[0]] = [worksheet[key].v]
        }
      }
    }
  });
  // Generate the data format supported by ant-table
  let sourceData = Object.values(draftObj).map((item,i) = > ({ key: i + ' '.name: item[0].value: item[1]}))
Copy the code

After the above processing, we get the sourceData is the data structure available for ant-table, so we have implemented the function of table import.

1.3 Table Editing function

The implementation of table editing function is actually very simple, we only need to follow the implementation method of customized rows and cells provided by the TABLE component of ANTD. The implementation scheme of editable table is also available on antD website, as follows:Those of you who are interested can study the following privately. Of course, it is also very easy to implement editable tables, and there are many ways, such as usingcolumntherenderFunction to dynamically switch the edit state of the table, or use popover editing, etc.

1.4 Dynamically generate charts according to the edited table data

This need according to the table data dynamically generated chart has certain conventions, we need to conform to the graphics library data specification, but we have the table data, processing data specification, it is very simple things, of course, the author of the visual library adopts antv f2 implementation, so you need to do a layer of adaptation to our data enables f2 to consumption.

Another point is that in order to be able to use multiple charts, we need to unify the charts for F2 and make them into a visual component library for our application scenario.

Let’s first look at the data format used by F2:

const data = [
  { genre: 'Sports'.sold: 275 },
  { genre: 'Strategy'.sold: 115 },
  { genre: 'Action'.sold: 120 },
  { genre: 'Shooter'.sold: 350 },
  { genre: 'Other'.sold: 150}];Copy the code

This data format will render as a chart like this:Therefore, we can conclude that there are two main latitude indicators, including their area chart, pie chart and line chart, and their formats are basically the same. Therefore, we can encapsulate them into visual components based on this point, as follows:

import { Chart } from '@antv/f2';
import React, { memo, useEffect, useRef } from 'react';
import ChartImg from '@/assets/chart.png';

import styles from './index.less';
import { IChartConfig } from './schema';

interface XChartProps extends IChartConfig {
  isTpl: boolean;
}

const XChart = (props: XChartProps) = > {
  const { isTpl, data, color, size, paddingTop, title } = props;
  const chartRef = useRef(null);
  useEffect(() = > {
    if(! isTpl) {const chart = new Chart({
        el: chartRef.current || undefined.pixelRatio: window.devicePixelRatio, // Specify the resolution
      });

      // Step 2: Process data
      const dataX = data.map(item= > ({ ...item, value: Number(item.value) }));

      // Step 2: Load the data source
      chart.source(dataX);

      // Step 3: Create a graph syntax and draw a histogram. The genre and sold attributes determine the location of the graph, with genre mapped to the X-axis and sold mapped to the Y-axis
      chart
        .interval()
        .position('name*value')
        .color('name');

      // Step 4: Render the diagram
      chart.render();
    }
  }, [data, isTpl]);
  return (
    <div className={styles.chartWrap}>
      <div className={styles.chartTitle} style={{ color.fontSize: size.paddingTop}} >
        {title}
      </div>
      {isTpl ? <img src={ChartImg} alt="dooring chart" /> : <canvas ref={chartRef}></canvas>}
    </div>
  );
};

export default memo(XChart);
Copy the code

Of course, other visual components can be encapsulated in the same pattern, and I will not use any examples here. The above components are encapsulated using react hooks and vue components. The basic principles are the same.

2. UseJavaScriptImplement front-end basedTableData can be exported with one clickExcel file

Similarly, our implementation of one-click export of table data to Excel is similar, but with a different approach, let’s first look at how it works in Dooring.

2.1 One-click Export to Excel

The above is the data collected by the user based on the background and exported with one keyexcelThe flow of the file, the last diagram is generatedexcelThe file inofficePresentation in software.

2.2 One-click Excel File Export using javascript

The one-click export function is mainly used inH5-DooringIn the background management page, users can easily export data. We can still use XLSX to achieve the export function here, but after a comprehensive comparison, the author finds that there is a simpler solution, and the author will introduce it in detail next. First, let’s take a look at the process:Obviously our export process is much simpler than the import process, we just need to decompile the table data format into the data supported by the plug-in. I used it herejs-export-excelTo do file export, using it is very flexible, we can customize:

  • Customize the excel file name to be exported
  • User-defined Excel filtering fields
  • Customize the header name for each column in an Excel file

Since the data structure supported by JS-export-Excel is an array object, we need to spend some effort to convert the data of table into an array object. It should be noted that the value corresponding to the key in ant’s table data structure can be an array. However, the value of js-export-Excel key is a string, so we need to convert the array to a string, such as [a,b,c] to ‘a,b,c’, so we need to convert the data format, the specific implementation is as follows:

const generateExcel = () = > {
    let option = {};  // Option stands for the Excel file
    let dataTable = [];  // The data content in the Excel file
    let len = list.length;
    if (len) {
        for(let i=0; i<len; i++) {
            let row = list[i];
            let obj:any = {};
            for(let key in row) {
                if(typeof row[key] === 'object') {
                    let arr = row[key];
                    obj[key] = arr.map((item:any) = > (typeof item === 'object' ? item.label : item)).join(', ')}else {
                    obj[key] = row[key]
                }
            }
            dataTable.push(obj);  // Set the data source for each column in Excel}}let tableKeys = Object.keys(dataTable[0]);
    option.fileName = tableName;  // Excel file name
    option.datas = [
          {
            sheetData: dataTable,  // The data source in the Excel file
            sheetName: tableName,  // Name of the sheet page in the Excel file
            sheetFilter: tableKeys,  // Column data to be displayed in the Excel file
            sheetHeader: tableKeys,  // The header name of each column in the Excel file}]let toExcel = new ExportJsonExcel(option);  // Generate the Excel file
    toExcel.saveExcel();  // Download the Excel file
  }
Copy the code

Note that the solution I implemented above works with any table component and can be used directly with the above code in most scenarios. At this point, we have implemented the functionality of using JavaScript to implement front-end import and export of Excel files.

So, are you learned again today?

The last

The above tutorial has been integrated into H5-Dooring, and some of the more complex interactive features are also possible with proper design, which you can explore on your own.

Github: H5 Online editor H5-Dooring

If you want to learn more H5 games, Webpack, node, gulp, CSS3, javascript, nodeJS, Canvas data visualization and other front-end knowledge and practical, welcome to “Interesting Talk front-end” to learn and discuss together, and explore the boundary of the front-end.

More recommended

  • React+Koa based h5 page visualization editor -Dooring
  • How to realize H5 editor real-time preview and real machine scan code preview function
  • H5 editor image upload and image library design scheme
  • Introduction to online IDE development: Implement an online code editor from scratch
  • How to design the template library in the H5 editor and realize the automatic generation of cover art