POI Operations Excel

Introduction to POI and Excel

　　There are two main toolkits for working with Excel in Java: JXL and POI. JXL can only operate Excel, with the. xls suffix of Excel. The POI can operate in Excel 95 and later versions of Excel, with the suffix. xls (version 03) and. xlsx (07) formats. So, if you need to import and export excel in your project, you might need a poi. Learn some of the concepts of Excel before you explain it, which is also the basis for POI operations. An Excel file is a workbook workbook, where you can create multiple sheet sheet in a workbook, and a worksheet that contains multiple cell cells that are composed of column rows (row).

The jar bag I used

　　Poi-ooxml-3.10.1-20140818.jar,Poi-ooxml-schemas-3.10.1-20140818.jar, and Ooxml-lib Directory of Xmlbeans-2.6.0.jar,Dom4j-1.6.1.jar.

Basic case

My rule is to do hands-on. What I've seen and heard is always "I seem to be", only the real self-action is "I will." So there's not much to say, the following example of writing 03Excel and writing 07Excel you will experience the basic write operation of the POI once

Write 03Excel

     Public voidTestwrite03excel ()throwsexception{//Create a version 03 workbook (Workbook)Hssfworkbook Workbook =NewHssfworkbook (); //Create a worksheet named Hello (sheet)Hssfsheet sheet = workbook.createsheet ("Hello"); //Create line 3rdHssfrow row = Sheet.createrow (2); //Create the 3rd columnHssfcell cell = Row.createcell (2); //set the value of a cellCell.setcellvalue ("Hello World"); //create an output stream to write to the hard diskFileOutputStream OutputStream =NewFileOutputStream ("f:/my/temptest/test. xls"); //WriteWorkbook.write (OutputStream); //Close the streamOutputstream.close ();    Workbook.close (); }

View Code

　　Write 07Excel

     Public voidTestwrite07excel ()throwsexception{//Create a version 07 workbook (Workbook)Xssfworkbook Workbook =NewXssfworkbook (); //Create a worksheet (sheet)Xssfsheet sheet = workbook.createsheet ("Hello"); //Create line 3rdXssfrow row = Sheet.createrow (2); //Create the 3rd columnXssfcell cell = Row.createcell (2); //Setting the valueCell.setcellvalue ("Hello World"); //create an output stream to write toFileOutputStream OutputStream =NewFileOutputStream ("f:/my/temptest/test. xlsx");        Workbook.write (OutputStream);        Outputstream.close ();    Workbook.close (); }

View Code

From these two can be seen, the suffix of the generated file is different, operation their class is not the same, Operation 03 version is HXX, Operation 07 version is XXX, then you may want to ask, there is no same class or interface to operate them, the answer is yes, but in the new Workbook (workbook) to distinguish between , because they all just implement a common interface, which is explained when read below.

　　Read 03 and 07Excel

    //This should be the Excel as a parameter passed in, in order to test convenience, do not pass the reference@Test Public voidTestread03and07excel ()throwsexception{String fileName= "f:/my/temptest/test. xls"; FileInputStream InputStream=NewFileInputStream (fileName); Workbook Workbook=NULL; //Read the workbook (workbook), judging the version of the workbook according to the suffix        if(Filename.endswith (". xls")) Workbook=NewHssfworkbook (InputStream); Else if(Filename.endswith (". xlsx"))) Workbook=NewXssfworkbook (InputStream); //reading the 1th worksheet (sheet)Sheet Sheet = workbook.getsheetat (0); //Read Line 3rdRow row = Sheet.getrow (2); //read 3rd columnCell cell = Row.getcell (2); //Read ValueString value =Cell.getstringcellvalue (); System.out.println ("The value of the 3rd row 3rd column is" +value);        Workbook.close ();    Inputstream.close (); }

View Code

　When you receive Excel, you judge its version, then receive it with a different workbook, and the next operation is represented by their common interface, which implements a way to read different versions of Excel. I read a row of data here, in the actual import of Excel must be more than a row of data, you need to pay attention to the loop read, read the starting line and read the type of data.

Add a case after a style

     Public voidTestexcelstyle ()throwsException {//Create a workbookHssfworkbook Workbook =NewHssfworkbook (); //Create cell styles, styles belong to a workbook, apply to cellsHssfcellstyle CellStyle =Workbook.createcellstyle (); //Center Horizontallycellstyle.setalignment (Hssfcellstyle.align_center); //Center Verticallycellstyle.setverticalalignment (Hssfcellstyle.vertical_center); //Create font, font belongs to workbook, apply to cellHssffont Font =Workbook.createfont (); //Font SizeFont.setfontheightinpoints (( Short) 16); //Font ColorFont.setcolor (hssffont.color_red); //BoldFont.setbold (true); //using FontsCellstyle.setfont (font); //background color needs to set foreground color, background color, and fill mode, otherwise it may not have effect//Set foreground colorCellstyle.setfillforegroundcolor (HSSFColor.BLUE_GREY.index); //Set Background colorCellstyle.setfillbackgroundcolor (HSSFColor.YELLOW.index); //set the fill mode, the previous color, or you can set other fill mode, the result is the foreground color backgroundCellstyle.setfillpattern (Hssfcellstyle.solid_foreground); //To create a merged cell object//merged cells, which belong to the workbook, apply to worksheets//the 4 parameters of the construction method were FirstRow, LastRow, Firstcol, LastcolCellrangeaddress rangeaddress =NewCellrangeaddress (2, 2, 2, 3); //Create a worksheet with a name of HelloHssfsheet sheet = workbook.createsheet ("Hello"); //sheet using merged cell objectssheet.addmergedregion (rangeaddress); //set the width of the 3rd column//sheet.setcolumnwidth (2, 12000); //Create line 3rdHssfrow row = Sheet.createrow (2); //row.setheightinpoints ((short));//Set Row Height//Create the 3rd columnHssfcell cell = Row.createcell (2); //Setting the valueCell.setcellvalue ("Hello World"); //Apply StyleCell.setcellstyle (CellStyle); //create an output stream to write toFileOutputStream OutputStream =NewFileOutputStream ("f:/my/temptest/test Style.xls");         Workbook.write (OutputStream);         Outputstream.close ();             Workbook.close (); }

View Code

　　Poi operation of the basic operation of Excel so much, and then combined with the file upload download can be achieved through the import and export of Excel.

　

POI Operations Excel