Java parsing Excel

Analysis

Parsing Excel begins with parsing the structure of Excel. Then analyze it with object-oriented thinking.

This is an Excel file. Let's analyze what you would do if you were to write this poi frame.
1. First, you have an object that represents the entire Excel file.
2. However, there are many pages in this Excel file. Sheet1, Sheet2 and so on, so we also need an object representation page.
3. In the page, there is a row, so you also need an object to represent the row.
4. In the row, subdivide the cell to the last.
5. There are many types of data in the cell. There are strings, numbers, times, and so on.

The object in the POI corresponds to the Excel object

There are several types of Excel files. Suffix with xls and xlsx

So for different types of files, you need to use different objects in the POI.
1. If you are parsing an XLS file

From the code is not difficult to find that the processing logic here is
1>. Get the IO stream of an Excel file first with InputStream
2>. Then create an in-memory Excel file Hssfworkbook type Object. This object represents the entire Excel file.
3>. Loop through every page of this Excel file
4>. Loop through each line on each page.
5>. For each cell in each row, get the value of the cell.
6>. Add the result of this line to a list array.
7>. Add the results of each row to the final total result.
8>. After parsing, a list< List < String > > type object is obtained.
2. If you are dealing with a file of the xlsx type

As above, I will not say.

Problems that exist

In fact, sometimes we want to get the data is the data in Excel, but finally found that the results are not ideal
If your data in Excel is a number, you will find that the corresponding in Java has become the scientific notation.
So you have to do some special processing when you get the value.
This guarantees that the obtained value is the value I want.
The online approach is to format data for numeric types and get the results you want.
In fact, it's not that troublesome. When I was doing it, I came up with a solution. For reference
Let's take a look at the ToString () method in the POI

This method is the method of poi, from the source code we can find that the processing process is
1. Get the type of cell
2. Format the data according to the type and output it. This does not suddenly cause a lot of what we want.
So we're going to have to change this method, like this.

That's what I'm doing.
1. For unfamiliar types, or null to return the "" Control string.
2. If it is a number, modify the cell type to string and return string. This ensures that the numbers are not formatted.
3. Although I do not know what the consequences of doing so, but success.

Note: The jar package that needs to be imported:

Java parsing Excel