preface

Recently, I listened to the head of the business department tell the students at the back end of a demand publicity meeting: “Could you research and development students help us realize this function? Specifically, there are multiple sheets in an Excel file summarized by some local and municipal branches. The first sheet is the summary data, and the other sheets are the data of each branch. So what we need to do is to carefully check whether the sum of the data of each cell of each branch is equal to the data recorded by the cashier of the head office, and then report it up to the CBRC. This can not make a mistake, or it will be very troublesome, so now we are manual check. Our master who came back from The UK wasted a lot of time doing these jobs every day. Now he hopes to save some time with the help of science and technology and let him do other more meaningful things. I would appreciate it if you could help me check the data. If there is an error cell, the background will be red. If there is no error cell, it will be normal.” The Excel file is displayed at ⏬ Special instructionsThe bank is very strict in information security management, so the above data areFake data.

The backend students look at this table, indicating that it can be done, but the data header and table row need to be uploaded can not be so complex, need to be a regular row by row, column by column of data, such as they can not do. I looked, hey! The front end of this function is completely able to do ah, and do not need so much they say the table header table line restrictions, maybe what kind of check can be returned. “Quietly into the village, not the gun.” “, do not have a definite grasp of the matter is silent, no voice at the meeting, will play a pre-study, and successfully achieve this demand.

Realize the principle of

In order to achieve the total score verification function of multiple sheets in such an Excel file, the principle is actually very simple, the core API is to use XLSX readFile() and xLSX-style style setting function. Combine this with Node.js’s writeFile and write it to the specified file. Excel function is strong, its macro function is similar to the operation program, also can achieve more than sheet table of the elements in a total list sheet inside the function of the additive, it can only make some auxiliary operation work, article for comparative analysis of tens of thousands of data and then mark style the subjective operation software itself alone or helpless. Also need to use tools to achieve subjective operation, extract useful information in accordance with the set intention to analyze and compare, and finally get the expected results.

xlsx

If you are familiar with the XLSX NPM package, you should know that it is a powerful tool for front-end processing Excel files. At present, the official website of NPM provides the open source community version, and the enhanced Pro version requires payment. We don’t need the Pro version here, just understand the community version. There are many input and output formats supported, such as CSV/HTML/JSON. I will not post the API on the official website, but take a closer look if you are interested, and mainly record the implementation process. Stepped on some pits, but also more understand the importance of the foundation.

Parsing Workbooks 1.Parsing Workbooks

The first step in parsing a file is to read it, which involves taking the data source and importing it into the library. Some common scenarios are node.js reads, or Photoshop extension scripts, Ajax requests, browser extensions, or browser file uploads.Here we use the first scenario, using Node.js to read the file and fetch the data source for analysis and validation.Pay attention toThe type used here is buffer, which is convenient to write when setting the style later. It was read correctly using other types, but the style setting was not effective.

const XLSX = require('xlsx');
const fs = require('fs');
const path = require('path');
const XLSXStyle = require('xlsx-style');
const wb = XLSX.readFile(path.resolve(__dirname, './test99.xls'), {type: "buffer"});
Copy the code

Due to the large size of the table, only partial results are taken as follows:From SheetNames you can see that hidden sheets are read, and from sheets you can see that hidden or folded rows are read. Here, we do not need to care about the hidden sheet, but only need to pay attention to the total, branch 1, branch 2 and branch 3 sheets to complete the total score verification function. Print the total table separately, and intercept partial results as follows:V = raw value, T = type, n = number, w = formatted text. F is the cell formula encoded as an A1-style string (if applicable). For example, the f of cell G9 is F9/C9, which refers to the formula assigned to the cell in the original sheet.

2.Working with the Workbook

This section explains how to read a specific cell, add a sheet to an Excel file, and create a new workbook from scratch.But this is not the function we need, the author is to traverse the cell to check whether the total score check is equal.

Here, we need to check from C6 to L32. Since all the forged data cannot be verified, all the errors are verified. Therefore, only the first three lines (C6-L8) can be verified. Note:

    1. 0.1 + 0.2! == 0.3, which we all know, so we need to add the line data to integers. There are a number of branches of data, take the data Max which has the largest number of decimal places, each data and Max after multiplying the sum can avoid the problem of accuracy loss? It’s not. Look at this:

So you need to figure out how many decimals there are and then round them up, and if you round them up in batches and then add them up, you’re not going to get the right result.

    1. Excel file cells have their own format, so the data you see is not necessarily its real data. Take a look at a cell like this:The format on it is D9/C9, and the calculated result is 0.018490754622689, but the actual result is 1.85%, that is, 0.0185. The calculation should be compared according to 0.0185, but the fact is that the computer and program are very strict, it will ignore the format restrictions of cells. However, 0.018490754622689 is used for calculation, so the comparison result is wrong. So in the comparison code above, if we have more than four decimal places, we need to round them.

I want to Styling the FIT sheets.

Once we find the cell with the error, it’s time to style it. So what we’re using here is thetaxlsx-styleThis plugin.

4.Writing Workbooks

Once the style is set, we need to write it to the specified Excel file and get the result. The official documentation gives a variety of application scenarios for writing, so here we use Node.js for writing.

Get results

The result file in the current project root directory is as follows:The seventh line is hidden line, comparison down all wrong, expected. Cell C6 was deliberately miswritten and detected successfully. Then tested the percentage cell, decimal point cell, in addition to the cell, can get the expected results, are not posted. At this point, the realization of the total score verification function is completed.

Want to upload and download it?

The most core functions are realized, upload and download feel are auxiliary functions. If you want to upload the source file – > compare – > download, you can use FileSaver to do this. The specific steps are to use Express or KOA to achieve a pseudo background, obtain the uploaded files for parsing, and then perform data verification, transfer the file buffer to the client browser, and then use FileSaver. SaveAs method to save the files.