"Springmvc+mybatis project" Jie Xin Business-24. Magical POI

What does POI do? First start from our business, to gradually understand and master the POI.

Business: Build the shipping table
Function:
Monthly statistics on purchase and sale contracts, according to the schedule of statistics.
Let's see what our shipment looks like at the end of the list:


You can see that the properties of the columns owned by our shipping table contain information about the following tables: Contract form, cargo table, manufacturer's table, attachment table. 4 tables are associated.
Redundant design only needs to get data from contracts, goods tables, data is more convenient, the code is more simple.

So how do we get the data we want to print out of the database table?
Analysis of the table, found that the customer name and order number are duplicated, there is no need to re-fetch data in the Contract table, so we have to cycle the cargo table information more convenient to achieve printing data.

Finally our content to be placed in the Excel spreadsheet for printing (customer requirements), what should we do?

There are two main ways in which Excel operates in the Java language:
1.POI: Apache It is used to operate all Office software (excel/word/ppt/...), and all versions are supported.
2.JXL: It is used to manipulate Excel, only supports versions below 2003, and version 2007 is not supported.

Early Microsoft Office series, when using the OLE2 document structure (pure 2 binary files, the file header has a specification).
When Microsoft developed the office2007 version, it made a major overhaul, rewriting office. Use the OOXML document structure. Now the Excel file is actually an XML format file.

POI supports OLE2 format files, also supports Ooxml, and is greatly optimized in ooxml format files. JXL only supports OLE2 format files.
Why does poi appear earlier than JXL but JXL can still get up? , because POI has a fatal weakness when the data is super large (massive data), performance and its reduction. Before the POI was innovated, JXL appeared, which solved the problem of high data volume performance, so JXL fire for some time. But the poi caught up in the high version and added a patch---ooxml support to solve a large number of performance problems.

POI's official website is http://poi.apache.org/index.html poi All-round operation
JXL's website is http://www.andykhan.com/jexcelapi/index.html JXL Import and Export

Let's start with a little poi example to see how POI plays (POI primer)

We have previously introduced the jar packages required for POI: Poi-3.9.jar, Poi-ooxml-3.9.jar, Poi-ooxml-schemas-3.9.jar

Our small example needs: write a content to the work Department, the content is written in Excel, the concrete content is "the technical department sends the dividend". The effect is similar to:

The following are the main steps we need to complete the Excel file for such a content:
First step: Create an Excel file, called a workbook (workbook)
Step two: When the Excel file is created, 3 worksheets are created by default sheet
Step Three: Locating rows
Fourth step: Locate the column and get the cell
Fifth step: Fill in the content
Sixth step: Save, close the file

The development steps for the POI are as follows:

Package Cn.edu.hpu.jk.test;import Java.io.file;import Java.io.fileoutputstream;import java.io.IOException;import Java.io.outputstream;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.ss.usermodel.Cell; Import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.junit.test;public class Testpoidemo {@Testpublic void HSSF () throws ioexception{//First step: Create a workbook Excel file workbook wb=new hssfworkbook ();//HSSF operation Excel2003 The following version//second step: Create a worksheet sheetsheet sheet =wb.createsheet ();///The third step: Create a Row object (we put it on line 4th) row Nrow=sheet.createrow (3);//start from 0//Fourth step: Create a Cell object, specify two parameters for the column//createcell, One is the first column one is the parameter type,//Here we use the default parameter type cell Ncell=nrow.createcell (2);//Fifth: Set the contents of the cell ncell.setcellvalue ("technical Department bonus");//Sixth step: Save OutputStream os=new FileOutputStream (New File ("F:\\testpoi.xls")); wb.write (OS);//Seventh step: Close Os.close ();}}

We tested this method, and after we ran it, we found this file on the F-disk.

And then open it, and find the content exactly as we thought it would be.

POI Test Success!

What do we do if we want to modify the font? Change the code here:

@Testpublic void Hssfstyle () throws Ioexception{workbook wb=new Hssfworkbook ();//HSSF operation Excel2003 The following version sheet sheet= Wb.createsheet (); Row Nrow=sheet.createrow (3);//starting from 0 cell Ncell=nrow.createcell (2); Ncell.setcellvalue ("Technical department bonuses");//Create a cell style ( Style entire workbook generic) CellStyle Titlestyle=wb.createcellstyle ();//Create a Font object font Titlefont=wb.createfont (); Titlefont.setfontname ("Microsoft Jas Black");//Set Font titlefont.setfontheightinpoints ((short) 26);//Set Word size Titlestyle.setfont ( Titlefont); Ncell.setcellstyle (titlestyle); OutputStream os=new FileOutputStream (New File ("F:\\testpoi.xls")); Wb.write (OS); Os.close ();}

Then run and find that the font size and font styles in our Excel content have changed

We now want to add a paragraph below, and this text is a URL, and the font is Roman (Times New Roman)



What should I do? The code is modified as follows:

Formatted @testpublic void Hssfstyle () throws Ioexception{workbook wb=new Hssfworkbook ();//HSSF operation Excel2003 The following versions sheet sheet= Wb.createsheet (); Row Nrow=sheet.createrow (3);//starting from 0 cell Ncell=nrow.createcell (2); Ncell.setcellvalue ("Technical department bonuses");//Create a cell style ( Style entire workbook generic) CellStyle Titlestyle=wb.createcellstyle ();//Create a Font object font Titlefont=wb.createfont (); Titlefont.setfontname ("Microsoft Jas Black");//Set Font titlefont.setfontheightinpoints ((short) 26);//Set Word size Titlestyle.setfont ( Titlefont); Ncell.setcellstyle (titlestyle);//Create another cell row Xrow=sheet.createrow (4); Cell Xcell=xrow.createcell (3); Xcell.setcellvalue ("www.baidu.com"); CellStyle Textstyle=wb.createcellstyle (); Font Textfont=wb.createfont (); Textfont.setfontname ("Times News Roman"); Textfont.setfontheightinpoints ((short) 12); Textstyle.setfont (Textfont); Xcell.setcellstyle (TextStyle); OutputStream os=new FileOutputStream (New File ("F:\\ Testpoi.xls ")); wb.write (OS); Os.close ();}

Let's go to the F-drive and see that the end result is:

Build succeeded!


However, the above code is problematic
1) These objects created by POI are all in memory
2) Row object, Column object, style object, Font object, duplicate creation

Let's solve the 2nd problem first, and we'll streamline the code:

Formatted compact @testpublic void Hssfstylemore () throws Ioexception{workbook wb=new Hssfworkbook ();//HSSF operation Excel2003 The following versions sheet Sheet=wb.createsheet ();//Create a cell row Nrow=sheet.createrow (3);//starting from 0 cell Ncell=nrow.createcell (2); Ncell.setcellvalue ("Technical department bonuses");//Create cell style CellStyle tstyle=wb.createcellstyle (); Font Nfont=wb.createfont (); Ncell.setcellstyle (This.titlestyle (Wb,tstyle,nfont));//Create a cell nrow=sheet.createrow ( 4); Ncell=nrow.createcell (3); Ncell.setcellvalue ("www.baidu.com");//Create cell style Tstyle=wb.createcellstyle (); nfont= Wb.createfont (); Ncell.setcellstyle (This.textstyle (Wb,tstyle,nfont)); OutputStream os=new FileOutputStream (New File ("F:\\testpoi.xls")); Wb.write (OS); Os.close ();} Title Style public CellStyle titlestyle (Workbook wb,cellstyle tstyle,font nfont) {nfont.setfontname ("Microsoft Jas Black");// Set Font nfont.setfontheightinpoints ((short) 26);//Set Word size Tstyle.setfont (nfont); return tstyle;} Content Style public CellStyle textStyle (Workbook wb,cellstyle tstyle,font nfont) {Nfont.setfontname ("Times News Roman"); Nfont.setfontheightinpoints (ShoRT); Tstyle.setfont (Nfont); return tstyle;} 

Repeating styles are leveraged so that not only the memory is saved, but our code looks very regulation Dhamma.

The beginning of the POI is finished, and we are going to formally start writing the export function of our shipping table.

Reprint Please specify source: http://blog.csdn.net/acmman/article/details/48678599

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"Springmvc+mybatis project" Jie Xin Business-24. Magical POI