Using POI databases for Excel and poiexcel in Java Development

Using POI databases for Excel and poiexcel in Java Development
First of all, we should focus on the two different Excel formats, which directly affects the way we operate Excel using POI. Make sure that you know the version of the Excel file you want to use. Remember!1. Differences between two Excel formats ),*. xls files are workbooks saved in Microsoft Excel 2003 or earlier versions. They are stored in the Format of BIFF (Binary Interchange File Format), a special Binary Format File. B )*. the xlsx file is a workbook saved in Microsoft Office 2007 or later versions. It is stored in OOXML (Office Open XML) format ), A compressed format (the #504B0304 file header can be seen in the hexadecimal editor UltraEdit) about *. xls and *. more differences between xlsx can be referred to (* _ flip _ wall _): http://www.differencebetween.net/technology/difference-between-xls-and-xlsx/# Note:-03 is version 2003 or earlier, and 07 + is version 2007 or later2. Use POI to create Excel a) to create a workbook:

//-03Workbook wb = new HSSFWorkbook();FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();//07+Workbook wb = new XSSFWorkbook();FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");wb.write(fileOut);fileOut.close();

 

B). Create a table.

Workbook wb = new HSSFWorkbook (); // or new XSSFWorkbook (); Sheet sheet1 = wb. createSheet ("new sheet"); Sheet sheet2 = wb. createSheet ("second sheet"); // The WorkbookUtil class can check whether your table name is valid String safeName = WorkbookUtil. createSafeSheetName ("[O 'Brien's sales *?] "); Sheet sheet3 = wb. createSheet (safeName); FileOutputStream fileOut = new FileOutputStream (" workbook.xls "); wb. write (fileOut); fileOut. close ();

 

C), create a cell

Workbook wb = new HSSFWorkbook (); // 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 from 0) Row row Row = sheet. createRow (short) 0); // create a Cell in the first column of the first row, and set its content to 1 (note that its index starts from 0) cell Cell = row. createCell (0); cell. setCellValue (1); // another more concise method for creating row. createCell (1 ). setCellValue (1.2); row. createCell (2 ). setCellValue (createHelper. createRichTextString ("This is a string"); row. createCell (3 ). setCellValue (true); // output the table to the file FileOutputStream fileOut = new FileOutputStream ("workbook.xls"); wb. write (fileOut); fileOut. close ();

 

3. Use POI to read Excel

//-03 InputStream indium = new FileInputStream ("workbook.xls"); // 07 + // InputStream indium = new FileInputStream ("workbook.xlsx"); Workbook wb = WorkbookFactory. create (indium); // obtain the first table of the file (note that the subscript is 0) Sheet sheet = wb. getSheetAt (0); // get the Row of the third row = sheet. getRow (2); // obtain the fourth Cell cell = row in the third row. getCell (3); if (cell = null) cell = row. createCell (3); cell. setCellType (Cell. CELL_TYPE_STRING); cell. setCellValue ("a test"); FileOutputStream fileOut = new FileOutputStream ("workbook.xls"); wb. write (fileOut); fileOut. close ();