Since childhood, we all have a common understanding that if you open a particular type of file on your computer, you must download a relevant software to do so. Excel, for example, is one of them. But as the browser evolved, more and more file formats were previewed and edited directly in the browser. Suppose we receive a request to upload, download, or preview an Excel spreadsheet on the front end. What do we need to do?

Here are four common requirements:

1. Online Excel in the browser:

This kind of requirement basically needs to implement all the common functions of native Excel, can import and export, add, delete, modify and search, interface style and some extension functions can also be implemented. In this case, it is best to use a complete library already in place. Currently, there is no open source and fully functional one. It is suggested to use a paid library for production, which is more convenient for subsequent maintenance. Here are a few common ones:

  • Spreadsheet: All are included, and are researched by ourselves.

  • Handsontable:JS implementation of the highest spreadsheets rating library. The Excel section of the graphite document uses this library. The basic version is free, and some advanced features, such as exporting to Excel, are available in the commercial version for a fee.

  • SpreadJS: Similar to an earlier version of Microsoft’s SpreadJS, it is fully functional and has domestic agents.

2. Just read the Excel content for preview:

In this case, some need to import Excel files and conduct real-time preview. Similar including preview word, PDF and other format files can be used to solve this method.

  • Microsoft Online Preview: This is done directly by calling Microsoft online Preview and inserting iframe. Previewing your resources must be publicly accessible. The disadvantage is that the preview speed is slow and large files cannot be previewed.
/ / sets the iframe SRC to excel http://view.officeapps.live.com/op/view.aspx?src= online download address / / need to excel encodeURI processing, sample as below<iframe 
  src='http://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Flearn.bankofamerica.com%2Fcontent%2Fexcel%2FWedding_Budge t_Planner_Spreadsheet.xlsx' 
  width='100%' 
  height='100%' 
  frameborder='1'>
</iframe>
Copy the code

  • Baidu document service: there are similar document services of other websites, which can store, transcode and distribute office, WPS and other formats of documents, and also meet the needs of online browsing of multi-terminal documents on PC, WAP and APP. You can search and understand by yourself.

  • Privatized Deployment Related (WOPI) Apps:

The overall use is similar to the Microsoft online preview above, but you need to deploy office Web to your own server and configure it yourself. Two servers are required for document conversion: one is the conversion server and the other is the domain controller server. The server operating system must be Windows. If necessary, please find out for yourself.

3. Data reading, display and operation should be customized:

This requirement is to import excel files, and then read the relevant data and styles of Excel, and then display them in the format we want. For example, read the data of some fixed positions for calculation, and read the data style of relevant positions to display to their own tables and other places.

Normally, the back end reads and parses all the Excel data needed for presentation, and the front end calls the returned data to generate styles. Then you have to go through the back-end processing to parse Excel, which is an extra step.

But there are ways to implement pure front-end as well. After comparing current open source library solutions, the current preference is to use ExcelJS for implementation. This library can read almost all the data and styles required by daily Excel for use in the front end. Here is a pure front-end Excel import that I implemented with ExcelJS. The warehouse address is here

If YOU open Excel in WPS, the content will look like the following figure

Then UPLOAD the Excel file in my project, and you can preview the Excel file in the browser.

Click on different cells to display the contents of cells, but also support page zoom in and out function

At the same time also support the data after parsing, export into Excel for download or export into pictures for download, relevant implementation and operation can download the source code after their own try oh

However, due to the lack of processing of imported data in official documents, there are many problems in parsing Excel data

There are three types of import operations in official documents. The actual file is uploaded from the browser, and the data can be read and parsed by buffer. The specific operation is that you first write the upload file control, and then according to the returned data to convert.

<input type="file" name="file" @change="getUploadData"/>
// Omit some code, remember to install ExcelJS in your project

import Excel from "exceljs";
getUploadData(e){
    let fileData = e.target.files[0]
    
    let reader = new FileReader()
    reader.readAsArrayBuffer(fileData)
    reader.onloadend = e= > {
        let data = new Uint8Array(e.target.result)
        this.workbook = new Excel.Workbook()
        this.workbook.xlsx.load(data).then(() = > {
            //this.workbook is the total data of the Excel file
            if(this.workbook._themes.theme1){
                this.workbook.eachSheet(worksheet= > {
                    // The worksheet here is the data for each table we need to read})}else{
                /* By default, you can read the theme in Excel. Otherwise, the theme is incorrectly formatted. * /
                this.$Messgae.error('File format error cannot be parsed')}})}}Copy the code

The main point is to obtain the overall data of excel file and the data of each sheet in the file. After the import, we see the data for each table. The main data in Excel is in the model property.

Cols in the Model property is our column width data, and rows is our data for each row. The specific data styles for all cells are also in Rows

“Rows” is our data for each row, including the data for each grid. If we want to generate a tabular presentation page, we need to use the data

  • Width and height of each row
  • The content and style of each grid. This includes the color of each edge, its width, whether it is dashed or straight, etc.

None of this is explained in official documents. If you need more data you’ll have to sift through where the attributes are. I have dealt with excel’s height and width, styles, merged cells, XML format conversion of default stylesheets, and content of different data formats in the warehouse code. For details, you can view the source code.

The above demonstration is just a demo, Excel data format is rich and changeable, many cases are not processed, if you need to extend the code is best to patch oh.

4. Directly use JS to export the required data to excel format:

This requirement is usually used to export data directly from related tables or statistics. For example, export a front-end table component library made by yourself into An Excel file and download it in the browser. Then you can use ExcelJS introduced above. Create a data object directly in the code import, operation can be exported. You can refer to the official documentation for a detailed explanation of how to deal with it.

// This is just a simple DEMO, the specific style can be found in the official document one by one, here is not repeated.
//file-saver is a library used to download files

import { saveAs } from "file-saver";
import Excel from "exceljs";

    var workbook = new Excel.Workbook();
    var worksheet = workbook.addWorksheet('My Sheet');

    worksheet.columns = [
    { header: 'Id'.key: 'id'.width: 10 },
    { header: 'Name'.key: 'name'.width: 32 },
    { header: 'D.O.B.'.key: 'DOB'.width: 10.outlineLevel: 1}]; worksheet.addRow({id: 1.name: 'John Doe'.dob: new Date(1970.1.1)});
    worksheet.addRow({id: 2.name: 'Jane Doe'.dob: new Date(1965.1.7)});

    workbook.xlsx.writeBuffer().then(buffer= > {
        // done
        saveAs(new Blob([buffer]), `The ${Date.now()}_feedback.xlsx`);
    });
Copy the code

After executing the code above, it will execute the browser’s download function. Open the downloaded Excel and you can see the following:

If there are any mistakes in the article, I hope you can correct them. Thank you. You can ask me if you think there is something unclear. If you like it, please give me a thumbs-up.