Using the POI Library to manipulate excel in Java development

first, let's focus on the two different formats of Excel, which directly affect how we use the POI to manipulate Excel. Be sure to first clear the version of Excel you want to work with, remember! 1,excel the two formats differ by a), the *.xls file is a spreadsheet saved using Microsoft Excel 2003 or a previous version, using a storage format of Biff (Binary Interchange file format), A special binary format file. b) The *.xlsx file is a spreadsheet saved using Microsoft Office 2007 or later, using a storage format of Ooxml (Office Open XML), a compressed format (through the hex editor UltraEdit can see its file header as #504b0304For more information on *.xls and *.XLSX, refer to (*_ _ wall _): http://www.differencebetween.net/technology/difference-between-xls-and-xlsx/ #说明:-03 for Version 2003 or earlier, 07+ for 2007 or later2. Create a new workbook by using the POI new Excel a):

// -03 New  New fileoutputstream ("Workbook.xls"); Wb.write (fileout); Fileout.close (); // 07+ New  New fileoutputstream ("workbook.xlsx"); Wb.write (fileout); Fileout.close ();

b), new table

New // or New Xssfworkbook (); Sheet Sheet1 = Wb.createsheet ("New Sheet"= Wb.createsheet ("Second Sheet"//  The Workbookutil class can check that your table name is legitimate String safename = Workbookutil.createsafesheetname ("[O ' Brien ' s sales*?]"  =new fileoutputstream ("Workbook.xls"); Wb.write (fileout); Fileout.close ();

c), new cell

Workbook WB =NewHssfworkbook ();//Workbook wb = new Xssfworkbook ();Creationhelper Createhelper =Wb.getcreationhelper (); Sheet Sheet= Wb.createsheet ("New Sheet");//Create a new row in the first row of the table (note that its index starts at 0)Row row = Sheet.createrow (( Short) 0);//Create a new cell in the first column of the first row and set its content to 1 (note that its index starts at 0)Cell cell = Row.createcell (0); Cell.setcellvalue (1);//another, more concise way to createRow.createcell (1). Setcellvalue (1.2); Row.createcell (2). Setcellvalue (Createhelper.createrichtextstring ("This is a string")); Row.createcell (3). Setcellvalue (true);//output a table to a fileFileOutputStream fileout =NewFileOutputStream ("Workbook.xls"); Wb.write (fileout); Fileout.close ();

3. Use POI to read Excel

//-03InputStream INP =NewFileInputStream ("Workbook.xls");//07+//InputStream INP = new FileInputStream ("workbook.xlsx");Workbook WB=workbookfactory.create (INP);//gets the first table of the file (note the subscript is 0)Sheet Sheet = wb.getsheetat (0);//get the third rowRow row = Sheet.getrow (2);//gets the fourth cell of the third rowCell cell = Row.getcell (3);if(Cell = =NULL) Cell= Row.createcell (3); Cell.setcelltype (cell.cell_type_string); Cell.setcellvalue ("A Test"); FileOutputStream Fileout=NewFileOutputStream ("Workbook.xls"); Wb.write (fileout); Fileout.close ();

For more information on POI, please refer to the official documentation: http://poi.apache.org/

Using the POI Library to manipulate excel in Java development