POI Operations EXCEL03 and EXCEL07 versions

Source: Internet
Author: User

There are two main toolkits for working with Excel in Java: JXL and POI. JXL can only manipulate Excel 95, 97, or 2000, which is the. xls suffix of Excel, which the individual believes is already obsolete, and that the POI can manipulate Excel 95 and later versions of Excel with the suffix. xls and. xlsx two formats.

POI full name Poor obfuscation implementation, literal translation "poor fuzzy implementation", of course, is not pathetic and very practical, using the POI interface can be used in Java to operate the Microsoft Office suite tools read and write functionality. Official website: http://poi.apache.org, POI supports all versions of Office, and in the next demo you need to import the user-uploaded Excel file from the front-end page, so choose Poi to explain. On the official website, download POI: For Excel that only operates on 2003 and previous versions, only need to copy if you need to operate on both 2007 and later versions

The following jar packages are introduced:
Poi-3.10.1-20140818.jar (if the requirement is 03, the following two jar packages do not need to be introduced)
Poi-ooxml-3.10.1-20140818.jar,
Poi-ooxml-schemas-3.10.1-20140818.jar, and copy the Xmlbeans-2.6.0.jar,dom4j-1.6.1.jar in the Ooxml-lib directory.
There are several main objects in the POI package that correspond to several objects in Excel:

(more than 07 of the versions are used Xssfworkbook,xssfsheet,xssfrow,xssfcell)
Attached code:
 

 PackageCn.test;ImportJava.io.FileInputStream;ImportJava.io.FileOutputStream;ImportOrg.apache.poi.hssf.usermodel.HSSFCell;ImportOrg.apache.poi.hssf.usermodel.HSSFCellStyle;ImportOrg.apache.poi.hssf.usermodel.HSSFFont;ImportOrg.apache.poi.hssf.usermodel.HSSFRow;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;Importorg.apache.poi.hssf.util.CellRangeAddress;ImportOrg.apache.poi.hssf.util.HSSFColor;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Sheet;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.usermodel.XSSFCell;ImportOrg.apache.poi.xssf.usermodel.XSSFRow;ImportOrg.apache.poi.xssf.usermodel.XSSFSheet;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook;ImportOrg.junit.Test; Public  class testpoi2excel {    /** * Write 03 version of Excel * @throws Exception */    @Test     Public void Testwrite03excel()throwsException {/** * Workbooks-worksheets- rows-cells * */        // WorkbookHssfworkbook Workbook =NewHssfworkbook ();//WorksheetHssfsheet sheet = Workbook.createsheet ("Hello World");//Line--index starting from 0Hssfrow row = Sheet.createrow (2);//Create cell--index starting from 0Hssfcell cell = Row.createcell (2); Cell.setcellvalue ("Hello World");//output to hard diskFileOutputStream FileOutputStream =NewFileOutputStream ("D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\ test. xls");//The Excel input to the specific addressWorkbook.write (FileOutputStream);//OffWorkbook.close ();    Fileoutputstream.close (); }/** * Read 03 version of Excel * @throws Exception */    @Test     Public void Testread03excel()throwsException {/** * Workbooks-worksheets- rows-cells * */FileInputStream FileInputStream =NewFileInputStream ("D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\ test. xls");//Read workbookHssfworkbook Workbook =NewHssfworkbook (FileInputStream);//Read the first worksheetHssfsheet sheet = Workbook.getsheetat (0);//Read the third line--index starting from 0Hssfrow row = Sheet.getrow (2);//Read cell--index starting from 0Hssfcell cell = Row.getcell (2); System.out.println (Cell.getstringcellvalue ());//OffWorkbook.close ();    Fileinputstream.close (); }/** * Write 07 version of Excel * @throws Exception */    @Test     Public void Testwrite07excel()throwsException {/** * Workbooks-worksheets- rows-cells * */        // WorkbookXssfworkbook Workbook =NewXssfworkbook ();//WorksheetXssfsheet sheet = Workbook.createsheet ("Hello World");//Line--index starting from 0Xssfrow row = Sheet.createrow (2);//Create cell--index starting from 0Xssfcell cell = Row.createcell (2); Cell.setcellvalue ("Hello World");//output to hard diskFileOutputStream FileOutputStream =NewFileOutputStream ("D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\test.xlsx");//The Excel input to the specific addressWorkbook.write (FileOutputStream);//OffWorkbook.close ();    Fileoutputstream.close (); }/** * Read 07 version of Excel * @throws Exception */    @Test     Public void Testread07excel()throwsException {/** * Workbooks-worksheets- rows-cells * */FileInputStream FileInputStream =NewFileInputStream ("D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\test.xlsx");//Read workbookXssfworkbook Workbook =NewXssfworkbook (FileInputStream);//Read the first worksheetXssfsheet sheet = Workbook.getsheetat (0);//Read the third line--index starting from 0Xssfrow row = Sheet.getrow (2);//Read cell--index starting from 0Xssfcell cell = Row.getcell (2); System.out.println (Cell.getstringcellvalue ());//OffWorkbook.close ();    Fileinputstream.close (); }/** * Read 07 version of Excel * @throws Exception */    @Test     Public void Testread03and07excel()throwsException {/** * Workbooks-worksheets- rows-cells * */String FileName ="D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\test.xls";if(Filename.matches ("^.+\\. (? i) ((xls) | (xlsx)) $")){//Determine if Excel            BooleanIs03excel = Filename.matches ("^.+\\. (? i) (XLS) $ "); FileInputStream FileInputStream =NewFileInputStream (FileName);//Read workbookWorkbook Workbook = is03excel?NewHssfworkbook (FileInputStream):NewXssfworkbook (FileInputStream);//Read the first worksheetSheet Sheet = Workbook.getsheetat (0);//Read the third line--index starting from 0Row row = Sheet.getrow (2);//Read cell--index starting from 0Cell cell = Row.getcell (2); System.out.println (Cell.getstringcellvalue ());//OffWorkbook.close ();        Fileinputstream.close (); }    }/** * Create merged cell * @throws Exception */    @Test     Public void Testexcelstyle()throwsException {/** * Workbooks-worksheets- rows-cells * */        // WorkbookHssfworkbook Workbook =NewHssfworkbook ();//1.1 Create merged cell objects merge third row third column to fifth columnCellrangeaddress cellrangeaddress =NewCellrangeaddress (2,2,2,4);//firstrow, LastRow, Firstcol, Lastcol        //1.2 Creating cell StylesHssfcellstyle style = Workbook.createcellstyle (); Style.setalignment (Hssfcellstyle.align_center);//Horizontal CenterStyle.setverticalalignment (Hssfcellstyle.vertical_center);//1.3 Setting FontsHssffont font = Workbook.createfont ();        Font.setboldweight (Hssffont.boldweight_bold); Font.setfontheightinpoints (( Short) -);//Loading fontsStyle.setfont (font);//Cell background        //style.setfillpattern (hssfcellstyle.diamonds);//Fill mode        //style.setfillbackgroundcolor (HSSFColor.YELLOW.index);//Background color        //2, WorksheetsHssfsheet sheet = Workbook.createsheet ("Hello World");//2.1 Loading Merge single-cell objectsSheet.addmergedregion (cellrangeaddress);//3. Line--index starting from 0Hssfrow row = Sheet.createrow (2);//4. Creating cells--index starting from 0Hssfcell cell = Row.createcell (2);        Cell.setcellstyle (style); Cell.setcellvalue ("Hello World");//output to hard diskFileOutputStream FileOutputStream =NewFileOutputStream ("D:\\workspaces\\myeclipse 2015\\itcasttax\\webroot\\upload\\test2.xls");//The Excel input to the specific addressWorkbook.write (FileOutputStream);//OffWorkbook.close ();    Fileoutputstream.close (); }}

POI Operations EXCEL03 and EXCEL07 versions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.