POI Excel and poiexcel

POI Excel and poiexcel
Introduction to POI and Excel

There are two mainstream JAVA tool kits for operating Excel: JXL and POI. Jxl only supports Excel 95 and 97,200, that is, excel with the suffix .xls. Poi can operate Excel 95 and later versions, and then use excel with the suffix of .xls(03)and .xlsx (07. Therefore, if you want to import and export Excel files in your project, you may need POI. let's take a look at several concepts of Excel, which is also the basis for POI operations. an excel file is a workbook. Multiple worksheets can be created in one workbook, and one worksheet contains multiple cells. These cells are Row) composition.

Jar package I used

Poi-ooxml-3.10.1-20140818.jar, poi-ooxml-schemas-3.10.1-20140818.jar, and xmlbeans-2.6.0.jar under the ooxml-lib directory.

Basic Cases

My principle is hands-on operations. all I have heard of is always "I seem to be", and only by myself is "I will ". so let's not talk about it. The following is an example of writing 03Excel and 07Excel. Once you perform this operation, you will understand the basic write operations of POI.

Write 03 Excel

Public void testWrite03Excel () throws Exception {// create version 03 workbook HSSFWorkbook workbook = new HSSFWorkbook (); // create a worksheet named hello (sheet) HSSFSheet sheet = workbook. createSheet ("hello"); // create 3rd rows HSSFRow row = sheet. createRow (2); // create HSSFCell = row in column 3rd. createCell (2); // set the cell value. setCellValue ("hello world"); // create an output stream for writing to the hard disk FileOutputStream outputStream = new FileOutputStream ("F:/my/tempTest/test .xls"); // write to the workbook. write (outputStream); // close the stream outputStream. close (); workbook. close ();}

View Code

 

　　Write 07 Excel

Public void testWrite07Excel () throws Exception {// create workbook () XSSFWorkbook workbook = new XSSFWorkbook (); // create a worksheet (sheet) XSSFSheet sheet = workbook. createSheet ("hello"); // create 3rd rows XSSFRow row = sheet. createRow (2); // create the 3rd column XSSFCell cell = row. createCell (2); // set the cell value. setCellValue ("hello world"); // create an output stream to write to FileOutputStream outputStream = new FileOutputStream ("F:/my/tempTest/test .xlsx"); workbook. write (outputStream); outputStream. close (); workbook. close ();}

View Code

From the two, we can see that the suffixes of the generated files are different and their classes are also different. The operation version 03 is Hxx and the operation Version 07 is Xxx. So you may want to ask, there are no identical classes or interfaces to operate on them. The answer is yes, but we need to differentiate them in the new workbook because they only implement public interfaces, the following is an explanation for reading data.

 

　　Read 03 and 07 Excel

// Excel should be passed in as a parameter. For Test convenience, the parameter @ Test public void testRead03And07Excel () throws Exception {String fileName = "F: /my/tempTest/test .xls "; FileInputStream inputStream = new FileInputStream (fileName); Workbook workbook = null; // read the workbook. Determine the version of the Workbook Based on the suffix if (fileName. endsWith (". xls ") workbook = new HSSFWorkbook (inputStream); else if (fileName. endsWith (". xlsx ") workbook = new XSSFWorkbook (inputStream); // read 1st worksheets (sheet) Sheet sheet = workbook. getSheetAt (0); // read 3rd rows Row = sheet. getRow (2); // read the Cell cell = row in the 3rd column. getCell (2); // read value String value = cell. getStringCellValue (); System. out. println ("the value of column 3rd in row 3rd is" + value); workbook. close (); inputStream. close ();}

View Code

When you receive an Excel file, you can determine its version, and then use different workbooks to receive it. The subsequent operations are represented by their common interfaces. In this way, you can read the Excel files of different versions. one row of data I read here is definitely more than one row of data when importing an Excel file. Pay attention to reading it cyclically, the starting row to be read, and the type of data to be read.

Case after adding a style

Public void testExcelStyle () throws Exception {// create a work thin HSSFWorkbook workbook = new HSSFWorkbook (); // create a cell style. The style is a work thin and used in cell HSSFCellStyle cellStyle = workbook. createCellStyle (); // horizontally centered cellStyle. setAlignment (HSSFCellStyle. ALIGN_CENTER); // vertically centered cellStyle. setverticalignment (HSSFCellStyle. VERTICAL_CENTER); // create a font. The font is thin and applies to cell HSSFFont font = workbook. createFont (); // font size. setFontHeightInPoints (short) 16); // font color. setColor (HSSFFont. COLOR_RED); // bold font. setBold (true); // use the cellStyle font. setFont (font); // The foreground color, background color, and fill mode must be set for the background color; otherwise, there may be no effect. // set the foreground color cellStyle. setFillForegroundColor (HSSFColor. BLUE_GREY.index); // sets the background color cellStyle. setFillBackgroundColor (HSSFColor. YELLOW. index); // you can set the filling mode. The previous scenery prevails. You can also set other filling modes. The result is that the foreground color and background color are displayed in cellStyle. setFillPattern (HSSFCellStyle. SOLID_FOREGROUND); // create a merged Cell Object // merged cell, which is a thin working table. The four parameters used in the worksheet // constructor are firstRow, lastRow, and firstCol, lastCol CellRangeAddress rangeAddress = new CellRangeAddress (2, 2, 2, 3); // create a worksheet named hello HSSFSheet = workbook. createSheet ("hello"); // sheet uses the merged Cell Object sheet. addMergedRegion (rangeAddress); // set the width of the 3rd column // sheet. setColumnWidth (2, 12000); // create 3rd rows HSSFRow row = sheet. createRow (2); // row. setHeightInPoints (short) 80); // set the row Height // create 3rd columns HSSFCell cell = row. createCell (2); // set the cell value. setCellValue ("hello world"); // use the cell style. setCellStyle (cellStyle); // create an output stream to write to FileOutputStream outputStream = new FileOutputStream ("F:/my/tempTest/test style.xls"); workbook. write (outputStream); outputStream. close (); workbook. close ();}

View Code

There are so many basic Excel operations for POI operations. Then, you can import and export Excel files by combining the upload and download of files.