Java reads and writes Excel files through poi

Java reads and writes Excel files through poi

In enterprise projects, Java is often used to read and write Excel or Word documents. Excel is an important product of Microsoft Office and a very popular product. Go now
10 people on the street may not know what Java is, but 10 may know how to operate Excel. There is a reason why Excel is popular. It is very convenient, common, and very simple.

How can such a good thing not belong to Java? So there is a POI package.

First? Poi is the contribution of the Java open-source organization (because of the open-source Java, many people are studying it, and many open-source organizations have emerged .); We don't need to know the specific organization, but we need to use it.

Poi
To put it bluntly, some of the Excel class operations written by others are written into a. jar package! Since people have already written it, we don't need to know its internal stuff. We just need to use poi
We can leave the interface. If you want to know that the internal operation is like this, let's look at the next SRC package. This helps you better understand poi. So first to http://apache.justdn.org/jakarta/poi

 
My post is poi-bin-3.0-final-20070503.zip

As you have to listen to me, it is recommended that you unify the file version. Then, decompress the file and enter the decompressed file. Three. Jar packages will be found. We recommend that you create a folder, for example, mylib.
Copy the three. Jar packages to the mylib folder, and then open S to import the three. Jar files.

The so-called Excel operation is nothing more than writing our information into the Excel file and getting the information in the Excel file. This is our focus.
First, create an Excel file.
Static public void main (string Arg []) throws ioexception

{

Hssfworkbook = new hssfworkbook ();
//
We recommend that you use an unknown class or interface to create an object of this class or interface in lower case, which is easy to record. Learn faster

Fileoutputstream = new
Fileoutputstream ("D: exceltext.xls ");
//
Create a file to be output through the Java IOV command, but the file name must end with "..xls"

Hssfworkbook. Write (fileoutputstream );
//
Call the write method of the hssfworkbook object to produce new files according to the specifications of the Excel book. Note: it does not mean that you end a file with a type of file.

Fileoutputstream. Close ();
//
Close the file stream after the output is complete. It is necessary to save system resources and avoid unnecessary troubles. Then execute this program.

}

The output result is displayed.
Now that we want to operate on Excel, we don't need to create an empty Excel file, so we don't have to worry about it. We 'd better use Excel directly. The point is that we want to write our information into excel.

In Excel
Is placed in a sheet. No sheet is an object for Java, but the generation of this object depends on the EXCEL object, that is
Hssfworkbook object. Do you want to place the sheet page there? What is the sheet Page Object in poi? In poi, sheet is mapped to an hssfsheet object. As I have already said, the premise of the existence of the sheet page object is the existence of the hssfworkbook object.

The method is as follows: hssfwrokbook hssfworkbook = new hssfworkbook ();
Hssfsheet
Hssfsheet =
Hssfworkbook. createsheet ("sheet_one ");

// Here we create the hssfsheet object through the hssfworkbook object. Also, name this sheet as sheet_ont.
You can also create the sheet page as follows: hssfworkbook. createsheet ();
Hssfworkbook. setsheetname (0, ") where 0 represents the number of the sheet page, each sheet page in the hssfworkbook object
It is stored as an array, so the array subscript of the first sheet page should be 0. You can see from the method name setsheetname that this method is for the existing sheet page
If the sheet page has not been created, an exception occurs when you define a name for a sheet that does not exist.

// Then we output a social file as above

Fileoutputstream = new
Fileoutputstream ("D: exceltext.xls ");

Hssfworkbook. Write (fileoutputstream );

Fileoutputstream. Close ();

Then, open text.xls in the Excel folder on the ddrive.



// In earlier versions, Chinese characters may not be supported. You can skip some segments using version 3.0.

In this way, we create an Excel file and a sheet page in the Excel file. However, when using

Hssfworkbook. createsheet (""); If the sheet page to be created is named in Chinese, garbled characters may occur. The cause of this situation is: the default encoding rule for Excel files is encoding_compressed_unicode.

However, Chinese characters are not supported or the encoding rule does not support Chinese characters. What should I do? Can I give up because he does not support Chinese characters! The answer is no. What should I do? transcoding is implemented as follows:

Hssfworkbook. createsheet ();

Hssfworkbook. setsheetname (0, "Haha Chinese", hssfworkbook. encoding_utf_16 );

// Then we output a social file as above

Fileoutputstream = new
Fileoutputstream ("D: exceltext.xls ");

Hssfworkbook. Write (fileoutputstream );

Fileoutputstream. Close ();

 

Now we have output an Excel file that includes the Chinese naming sheet page. But our purpose
Is this output? Our purpose is to output information. The information in the Excel file is stored in this way. First, the file is stored in each
In cells, the hssfrow object is used in poi to map rows in Excel. (This can also be said to be a substitute.) How does one map columns? This shows the wisdom of the poi creators.
After obtaining the row object, poi creates a grid object through this row object and stores the information in the hssfcell object, at the same time, the number of grids is used to map columns.
. The specific method is as follows.

Hssfworkbook = new
Hssfworkbook ();

Hssfsheet
Hssfsheet =
Hssfworkbook. createsheet ("frist ");

Hssfrow
Hssfrow
= Hssfsheet. createrow (0 );

//
Needless to say, here we use the createrow (int
(I) The method creates a row object and specifies that it is the first row.

Hssfcell
Hssfcell
= Hssfrow. createcell (short) 0 );

// Create a cell object on the row object, that is, the hssfcell object. The parameters of the hssfrow. createcell () method are of the short type. Here we must pay attention to the transformation.

Hssfcell. setcellvalue ("Haha is also Chinese ");

// Set the value for the Cell Object

Hssfrow. createcell (short) 1). setcellvalue (new date ());

Hssfrow. createcell (short) 2). setcellvalue (false );

Hssfrow. createcell (short) 3). setcellvalue (12.00 );

Fileoutputstream = new
Fileoutputstream ("D: excela.xls ");

Hssfworkbook. Write (fileoutputstream );

Fileoutputstream. Close ();

 

At this time, we found that the Chinese characters of the first cell were not displayed, and the date data was not correctly displayed. Let's solve the problem one by one. The reason for the Chinese problem has just been described as the character problem. Replace the blue part with the following:

Hssfcell. setencoding (hssfworkbook. encoding_utf_16 );

Hssfcell. setcellvalue ("Haha is also Chinese ");

 

Note: hssfcell. setencoding (hssfworkbook. encoding_utf_16 );

This sentence must be written in front of the set value for the cell. I suggest writing this method directly after the cell is created, regardless of whether the cell contains Chinese characters or not.

 

Now that the problem of Chinese is solved, we have to deal with the problem of Date:

The string of numbers we see in Excel is not a date-type garbled code, but Excel uses the date-type
Long TYPE to process, so we need to get the date we want?
Java contains a package called Java. in text, most of them are rule methods for transferring operation Characters, dates, numbers, etc. It is recommended that you have more time to look at them, but it cannot be used in poi, so poi gave
A special object is an Excel Style table object. The so-called style table is used to display data in a pre-defined manner and is often used on pages, if someone with energy still needs to check out,
Although it is of little significance to Java programmers, is it true?

Well, let alone define the date style in Excel: Excel also provides an hssfcellseyle object, so we can operate on this object. The method is as follows:

Hssfworkbook = new
Hssfworkbook ();

Hssfsheet
Hssfsheet =
Hssfworkbook. createsheet ();

Hssfworkbook. setsheetname (0,
"Haha Chinese", hssfworkbook. encoding_utf_16 );

Hssfrow
Hssfrow
= Hssfsheet. createrow (0 );

Hssfcell
Hssfcell
= Hssfrow. createcell (short) 0 );

Hssfcell. setencoding (hssfworkbook. encoding_utf_16 );

Hssfcell. setcellvalue ("Haha is also Chinese ");

Hssfcellstyle =
Hssfworkbook. createcellstyle ();

Hssfcellstyle. setdataformat (hssfdataformat. getbuiltinformat ("m/D/yy
H: mm "));

Hssfcell celldate = hssfrow. createcell (short) 1 );

Celldate. setcellvalue (new date ());

Celldate. setcellstyle (hssfcellstyle );

Hssfrow. createcell (short) 2). setcellvalue (false );

Hssfrow. createcell (short) 3). setcellvalue (12.00 );

Fileoutputstream = new
Fileoutputstream ("D: exceltext.xls ");

Hssfworkbook. Write (fileoutputstream );

Fileoutputstream. Close ();

The blue area is the area to be modified.

 

 

Here we can use poi to operate the information output part of an Excel file.

 

The purpose of information output is to better read the information (derived from "Today's retreat is to win tomorrow !")

Since we regard the information as output of one or more objects, we can read the information from one or more objects. If you know how to output the data, the input is relatively simple. You only need to know a few methods.

Example:

String filepath =
"D: excela.xls ";

Hssfworkbook = new hssfworkbook (New
Fileinputstream (filepath ));

Hssfsheet
Hssfsheet =
Hssfworkbook. getsheetat (0 );

Int I = hssfsheet. getlastrownum ();

For (int K = 0; k <= I; k ++)

{

Hssfrow = hssfsheet. getrow (k );

For (int
J = 0; j

{

Hssfcell = hssfrow. getcell (short) J );

System. Out. println (hssfcell. getdatecellvalue ());

}

}

Explanation: filepath
It is the Excel file you want to operate on. We can get an existing Excel file through the hssfworkbook () constructor with parameters, just like opening an Excel file.
Because an Excel file may have multiple sheet pages. First, locate the sheet page. Method: hssfworkbook. getsheetat
(0 );

This method is actually a process of creating a sheet page, but because the object already exists (in display), we can reference it.

The operation information is required after the sheet page to be operated is obtained, int I =
Hssfsheet. getlastrownum (); this method is very important, that is, we get the maximum number of rows under this object through the hssfsheet object. Then we can use this index to traverse all rows by using hssfrow.
Hssfrow =
Hssfsheet. getrow (k); in this way, each row object is obtained in sequence, and then the row object is

Hssfrow. getlastcellnum
() This method can obtain the maximum number of cells in the row, and then use hssfcell =
Hssfrow. getcell (short) J); this method gets the content of the cell. Note that the red position is also a short type.