Using POI to implement import and export of Excel __javaee

In the project open, we often need to use Excel to import the exported data, and POI technology is a choice to achieve our needs.

POI supports all versions of Office

POI Full name poorobfuscation implementation, literally "poor fuzzy implementation", uses the POI interface to manipulate the read and write capabilities of Microsoft Office suite tools through Java. Official website: http://poi.apache.org,

Download jar packs and source packs from the website.

Introduction to Jar Packs : ① for only 2003 and previous versions of Excel, only Poi-3.10.1-20140818.jar

② If you need to work on both 2007 and later versions, you need Poi-ooxml-3.10.1-20140818.jar,poi-ooxml-schemas-3.10.1-20140818.jar, and the Ooxml-lib directory Xmlbeans-2.6.0.jar,dom4j-1.6.1.jar.

The following is an instance of version 03, which creates a HelloWorld-named worksheet, writes Hellowrold in the third row of the table, and writes the Excel file locally. And then read the newly saved Excel locally, find the first table (that is, the HelloWorld table, because you just created one), find the third row, read out its contents and write it in the console.

Package poi;
Import Java.io.File;
Import Java.io.FileInputStream;

Import java.io.IOException;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import Org.apache.poi.ss.usermodel.CellType;

Import Org.junit.Test; public class TestPOI03 {@Test public void writeExcel03 () throws ioexception{//Create workbook Hssfworkbook workbook = new HS
		Sfworkbook ();
		The name of the worksheet that was created is helloWorld hssfsheet sheet = workbook.createsheet ("HelloWorld");
		Create row, line 3rd hssfrow row = Sheet.createrow (2);
		Create cell, Operation third row third column Hssfcell cell = Row.createcell (2, celltype.string);
		
		Cell.setcellvalue ("HelloWorld");
		
		Workbook.write (New File ("d:\\poi\\ test. xls")); Workbook.close ()//finally remember to close the workbook} @Test public void ReadExcel03 () throws ioexception{fileinputstream InputStream = NE
		W FileInputStream (New File ("d:\\poi\\ test. xls")); Reading workbooks Hssfworkbook workbook = new HSsfworkbook (InputStream);
		Read sheet Hssfsheet sheet = workbook.getsheetat (0);
		Read row Hssfrow row = Sheet.getrow (2);
		Reading cell Hssfcell cell = Row.getcell (2);
		
		String value = Cell.getstringcellvalue ();
		
		System.out.println (value);
		Inputstream.close ();
 Workbook.close ()//finally remember to close the workbook}}

Next to the 07 version of the method, the same demonstration of an example, the two instances of the difference, just the use of the class is different, the principle is the same

Package poi;
Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.io.FileOutputStream;


Import java.io.IOException;
Import Org.apache.poi.ss.usermodel.CellType;
Import Org.apache.poi.xssf.usermodel.XSSFCell;
Import Org.apache.poi.xssf.usermodel.XSSFRow;
Import Org.apache.poi.xssf.usermodel.XSSFSheet;
Import Org.apache.poi.xssf.usermodel.XSSFWorkbook;

Import Org.junit.Test; public class TestPOI07 {@Test public void writeExcel07 () throws ioexception{//Create workbook Xssfworkbook workbook = new XS
		Sfworkbook ();
		create worksheet Xssfsheet sheet = workbook.createsheet ("HelloWorld");
		Create row Xssfrow row = Sheet.createrow (2);
		Create cell, Operation third row third column Xssfcell cell = Row.createcell (2, celltype.string);
		
		Cell.setcellvalue ("HelloWorld");
		FileOutputStream outputstream = new FileOutputStream (New File ("d:\\poi\\ test. xlsx"));
		
		Workbook.write (OutputStream); Workbook.close ()//Remember to close the workbook} @Test public void readExcel07 () throws ioexception{FileInputStream InputStream = new FileinpUtstream (New File ("d:\\poi\\ test. xlsx"));
		Read workbook Xssfworkbook workbook = new Xssfworkbook (InputStream);
		Read sheet Xssfsheet sheet = workbook.getsheetat (0);
		Read row Xssfrow row = Sheet.getrow (2);
		Reading cell Xssfcell cell = Row.getcell (2);
		
		String value = Cell.getstringcellvalue ();
		
		System.out.println (value);
	Inputstream.close ();//Close Workbook workbook.close ();
 }
}

The last thing to come up is a bit of something, style. For example, common operations in Excel, such as merging cells, horizontal center, vertical Center, change font size, bold, set cell background color, and so on.

According to the front end of the argument can also be called style, all can be achieved through the POI.

in the next example before, to understand the poi of the style of understanding, it is important to remember, know that again to perform the beautification of Excel is simply soeasy!:

1. Merge cells, belong to worksheets, create independently, apply to worksheets

2. Style, sheet, created by workbook, applied to cell

3. Fonts, belong to worksheets, created by workbooks, applied to styles

4. Set the background color, be sure to set the color fill mode first

The following example is to do before the further in-depth, using the above several points to add some landscaping work

Package poi;
Import Java.io.File;
Import Java.io.FileOutputStream;

Import java.io.IOException;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import Org.apache.poi.hssf.usermodel.HSSFCellStyle;
Import Org.apache.poi.hssf.usermodel.HSSFFont;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import Org.apache.poi.hssf.util.HSSFColor;
Import Org.apache.poi.ss.usermodel.FillPatternType;
Import org.apache.poi.ss.usermodel.HorizontalAlignment;
Import org.apache.poi.ss.usermodel.VerticalAlignment;
Import org.apache.poi.ss.util.CellRangeAddress;

Import Org.junit.Test; The public class Testpoiexcelstyle {@Test the public void Testexcelstyle () throws ioexception{//1. Create Workbook Hssfworkbook Wor
		
		Kbook = new Hssfworkbook ();
		Create a merged Cell object cellrangeaddress rangeaddress = new Cellrangeaddress (2, 2, 2, 4);
		Create a style Hssfcellstyle style = Workbook.createcellstyle (); Style.setalignment (horizontalalignmenT.center);
		Style.setverticalalignment (Verticalalignment.center);
		Create fonts Hssffont font = Workbook.createfont ();
		Font.setfontheightinpoints ((short) 16); Font.setfontheight ((short) 320); The effect is the same as above.
		Use this method to set the size, the value to be set to the font size * 20 times times, the specific view API document Font.setcolor (HSSFCOLOR.GREEN.INDEX);
		Font.setbold (TRUE);
		Style.setfont (font);
		Set Background Style.setfillpattern (Fillpatterntype.solid_foreground);
		
		Style.setfillforegroundcolor (HSSFColor.RED.index);
		2. Create worksheet Hssfsheet sheet = workbook.createsheet ("HelloWorld");
		
		Add Merge Area sheet.addmergedregion (rangeaddress);
		3. Create row Hssfrow row = Sheet.createrow (2);
		4. Create cells Hssfcell cell = Row.createcell (2);
		Cell.setcellvalue ("HelloWorld");
		
		Cell.setcellstyle (style);
		Output FileOutputStream outputstream = new FileOutputStream (New File ("d:\\poi\\ test. xls"));
		
		Workbook.write (OutputStream);
		Workbook.close ();
	Outputstream.close ();
 }
}