Apache POI Component Operations Excel, make report (ii)

Now look at the basic setup of Excel, in 2007, for example, set the column width from the workbook, because the resulting table column should be fixed, and the row is a traversal build, so you can set the column width at the workbook level, then you can do as follows.
The first is to create workbooks and worksheets:

1   //Create a Excel2007 Workbook object2Xssfworkbook workbook2007 =NewXssfworkbook (); 3         //Create a Worksheet object and name it4Xssfsheet sheet = workbook2007.createsheet ("Student Information Tables"); 5 after that, format is set up:6         //set the default width and height for rows and columns7Sheet.setcolumnwidth (0, 32 * 80);//set width to column A of 80 pixels8Sheet.setcolumnwidth (1, 32 * 80); 9Sheet.setcolumnwidth (2, 32 * 80); TenSheet.setcolumnwidth (3, 32 * 80);  OneSheet.setcolumnwidth (4, 32 * 80);

Before you do this, you create a worksheet, sheet, and then you can set the column widths for each column. While line height generally for different rows have different settings, such as table header row, total rows, data rows, etc., then the settings will be better. Set the column width and row height, leaving the cell settings, such as center, border, font, and so on. When you set a style and apply the style to the cells you want, you get the overall effect, such as:

1 //Create a style2Xssffont Font =Workbook2007.createfont (); 3Xssfcellstyle HeaderStyle =Workbook2007.createcellstyle (); 4         //Set Vertical Center5 headerstyle.setalignment (HorizontalAlignment.Center); 6 headerstyle.setverticalalignment (Verticalalignment.center); 7         //Set Border8 headerstyle.setbordertop (Borderstyle.thin); 9 Headerstyle.setborderbottom (Borderstyle.thin); Ten Headerstyle.setborderleft (Borderstyle.thin);  One headerstyle.setborderright (Borderstyle.thin);  A         //Font Bold -Font.setbold (true);  -         //Set Long text wrap theHeaderstyle.setwraptext (true);  -Headerstyle.setfont (font);

Here we define the style of the table header, where the vertical center is set to set two times, and the method name is different, be aware. The border is very simple, the upper and lower left and right set four times, font bold is a Boolean setting, followed by text word wrap, meaning is the fixed cell length is not changed automatically fold line. Then add the font style to the cell style.
The following table header is designed for the previous example, as follows:

1 //Create a table header2Xssfrow headerrow = Sheet.createrow (0); 3Headerrow.setheightinpoints (25f);//Set Row Height4Xssfcell nameheader = Headerrow.createcell (0); 5Nameheader.setcellvalue ("name"); 6 Nameheader.setcellstyle (HeaderStyle); 7Xssfcell Genderheader = Headerrow.createcell (1); 8Genderheader.setcellvalue ("Gender"); 9 Genderheader.setcellstyle (HeaderStyle); TenXssfcell Ageheader = Headerrow.createcell (2);  OneAgeheader.setcellvalue ("Age");  A Ageheader.setcellstyle (HeaderStyle);  -Xssfcell Classheader = Headerrow.createcell (3);  -Classheader.setcellvalue ("Class"));  the Classheader.setcellstyle (HeaderStyle);  -Xssfcell Scoreheader = Headerrow.createcell (4);  -Scoreheader.setcellvalue ("Score");  -Scoreheader.setcellstyle (HeaderStyle);

Here the code will have a large section of the repetition, because it is a routine down, and finally do not forget to join the style on the line, so the table head is ready. The rest is traversing data travel.

1  for(inti = 0; I < studentlist.size (); i++) {  2Xssfrow row = Sheet.createrow (i + 1); 3 row.setheightinpoints (20f); 4Student Student =Studentlist.get (i); 5Xssfcell Namecell = Row.createcell (0); 6 Namecell.setcellvalue (Student.getname ()); 7 Namecell.setcellstyle (CellStyle); 8Xssfcell Gendercell = Row.createcell (1); 9 Gendercell.setcellvalue (Student.getgender ()); Ten Gendercell.setcellstyle (CellStyle);  OneXssfcell Agecell = Row.createcell (2);  A Agecell.setcellvalue (Student.getage ());  - Agecell.setcellstyle (CellStyle);  -Xssfcell Classcell = Row.createcell (3);  the Classcell.setcellvalue (Student.getsclass ());  - Classcell.setcellstyle (CellStyle);  -Xssfcell Scorecell = Row.createcell (4);  - Scorecell.setcellvalue (Student.getscore ());  + Scorecell.setcellstyle (CellStyle);  -}

To complete the task with a For loop, it is important to note that the loop variable is still starting at 0, which is for the convenience of traversing the collection, and the creation of the row is starting from i+1, since the 0 rows are the table headers that are already occupied. The following is the first set row height, then start to value, assignment, format, structure is also very uniform.
After the traversal, take a look at the merge, the merged cells in Excel is also a very common operation, such as in this example, the class is set to a class, then want to let the class this column merge, how to do?

Java code  Collection code //  Merge class   sheet.addmergedregion (new cellrangeaddress (1, 4, 3, 3));  

This is an operation on the sheet level, because it is the merging of cells on the sheet, or the addition of a merged area, which receives four parameters, that is, the starting row, the end row, the starting column, the end column, the position is generally more deterministic, or calculated using a cyclic variable. Class, starting from the second row, the fifth line ends, the corresponding index is 1, 4, and the column is unchanged, that is, the column is merged, it is 3.
At this point we have to traverse the data to be born into an Excel table, set the cell style has also been merged, then there are totals do not say, with the total can be directly set up the formula, can also be calculated directly after the program, the latter is generally used to compare complex reports, you do not have to set the location of the merge in the POI, reduce In conjunction with this example, we calculate the average age and the sum of the scores. As follows:

1 //Data Analysis Lines2         intDadarownum =Sheet.getlastrownum (); 3Xssfrow TotalRow = Sheet.createrow (dadarownum + 1);//gets the number of rows that are already there, plus 1 to go out of new rows4 totalrow.setheightinpoints (25f); 5Xssfcell Analyticscell = Totalrow.createcell (0); 6Analyticscell.setcellvalue ("Data Analysis"); 7 Analyticscell.setcellstyle (HeaderStyle); 8Xssfcell Avgagecell = Totalrow.createcell (1); 9Avgagecell.setcellvalue ("average age")); Ten Avgagecell.setcellstyle (HeaderStyle);  OneXssfcell Avgagevaluecell = Totalrow.createcell (2);  A Avgagevaluecell.setcellstyle (HeaderStyle);  -Avgagevaluecell.setcellformula ("AVERAGE (c2:c" + (Dadarownum + 1) + ")");  -Xssfcell Sumscorecell = Totalrow.createcell (3);  theSumscorecell.setcellvalue ("Total");  - Sumscorecell.setcellstyle (HeaderStyle);  -Xssfcell Sumscorevaluecell = Totalrow.createcell (4);  - Sumscorevaluecell.setcellstyle (HeaderStyle);  +Sumscorevaluecell.setcellformula ("SUM (e2:e" + (Dadarownum + 1) + ")");

When we use the Excel function, we already know the location of the data, directly using the code of the cell to do the operation, we should pay attention to the use of formulas when the method does not write = number, POI will be automatically added to us, here directly write the contents of the formula. In fact, there may be dynamic computing tasks, then according to the business complexity of the choice is to use the Excel formula or the program is evaluated directly after the assignment display.
Finally, the steps to generate the file, which have been introduced, are simple:

1     //Generating Files2File File =NewFile (FilePath); 3OutputStream OS =NULL; 4 Try {  5OS =Newfileoutputstream (file); 6 workbook2007.write (OS); 7}Catch(IOException e) {8 E.printstacktrace (); 9}finally {  Ten     if(OS! =NULL) {   One         Try {   A Os.close ();  -}Catch(IOException e) { -         }   the     }   -}

Finally, the test is performed and a main function is written to run:

1  Public Static void Main (string[] args) {  2         long start = system.currenttimemillis ();   3         generateExcel2007 (xlsx2007);   4         long end = system.currenttimemillis ();   5         System.out.println ((End-start) + "Ms Done!" );   6     }  

Wrapping all of the above in a static method generateExcel2007 (String FilePath), execute to get the generated Excel report, we get a report like this:

This is the end result. The basic setup of the POI operation Excel basically covers, the rest is the flexible use, to generate the report in line with their own needs.
The next article will be combined with a concrete example to describe the analysis and production of complex reports, complete the report as shown:

Apache POI Component Operations Excel, make report (ii)