Java uses POI to Operate excel files for batch export, import, and poiexcel

Java uses POI to Operate excel files for batch export, import, and poiexcel
I. Definition of POI

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 perform operations on Excel 95 and later versions, and then perform operations on excel files suffixed with. xls and. xlsx.

POI is the full name of Poor Obfuscation Implementation, which is literally "Poor fuzzy Implementation". Using the POI interface, you can use JAVA to operate the Read and Write Functions of Microsoft office suite tools. Official Website: http://poi.apache.org, POI support all versions of office, first go to the official website to download the following interface:

After the download, open “poi-bin-3.15-20160924.tar.gz to obtain the jar packages required for the excel operation, and copy these jar packages to the project. For excel that only operates versions 2003 and earlier, you only need a poi-3.15.jar, and if you want to operate both versions 2007 and later, you need to copy

Poi-ooxml-3.15.jar
Poi-ooxml-schemas-3.15.jar

And copy the xmlbeans-2.6.0.jar in the ooxml-lib directory (but somehow, my jar file does not have dom4j. jar) this file, or add dom4j. jar to prevent errors.

 

2. Use junit for Excel testing

First, define the Excel Workbook object, Worksheet object, row object, and cell object.

The specific code is as follows: make sure that it is earlier than version 2007 or later than version 2007 (including version 2007). The following code is earlier than version 2007:

This Code only writes data to an Excel file and creates

Public static void main (String [] args) throws Exception {/*** example * The suffix of the Excel file is .xlsx * // create a new workbook HSSFWorkbook workbook = new HSSFWorkbook (); // create a worksheet HSSFSheet sheet = workbook. createSheet ("hello"); // create a row. The row number is passed as a parameter to the createRow () method. The first row starts to calculate HSSFRow row = sheet from 0. createRow (0); // create a cell. row has determined the row number. The column number is passed as a parameter to createCell (). The first column starts to calculate HSSFCell cell = row from 0. createCell (2); // set the cell value, that is, the value of C1 (first row, third column) cell. setCellValue ("hello sheet"); // output to the disk: FileOutputStream fos = new FileOutputStream (new File ("E: \ root \ sheet \ 11.xls"); workbook. write (fos); workbook. close (); fos. close ();}

The result is as follows:

 

You can also read the Excel file, get the data of the Excel file, and print it out. The Code is as follows:

 

@ Test public void testReadExcel () throws Exception {// create the input stream FileInputStream FCM = new FileInputStream (new File ("E: \ root \ sheet \ 11.xls ")); // pass the parameter HSSFWorkbook workbook = new HSSFWorkbook (AIS) through the constructor; // obtain the worksheet HSSFSheet sheet = workbook. getSheetAt (0); // get the row. The row number is passed as a parameter to the getRow method. The first row starts to calculate HSSFRow row = sheet. getRow (0); // get the cell. row has determined the row number, and the column number is passed to getCell as a parameter. The first column starts to calculate HSSFCell cell = row from 0. getCell (2); // set the cell value, that is, the value of C1 (first row, third column) String cellValue = cell. getStringCellValue (); System. out. println ("the value of the third column in the first row is" + cellValue); workbook. close (); FCM. close ();}

The result is as follows:

 

Modify the Object Name of the corresponding workbook:

// Create a workbook XSSFWorkbook workbook = new XSSFWorkbook ();

The Code is as follows: Create an excel file and save the data to an excel file:

@ Test public void write07 () throws Exception {// create a workbook XSSFWorkbook workbook = new XSSFWorkbook (); // create a worksheet XSSFSheet sheet = workbook. createSheet ("hello"); // create a row. 0 indicates the first row XSSFRow row = sheet. createRow (0); // The row number of the created cell is determined by row, and the column number is passed to createCell as a parameter. The first column starts to calculate XSSFCell cell = row from 0. createCell (2); // assign cell to cells. setCellValue ("hello sheet"); // create the output stream FileOutputStream fos = new FileOutputStream (new File ("E: \ root \ sheet \ hello.xlsx"); workbook. write (fos); workbook. close (); fos. close ();}

 

The code for reading data is as follows:

@ Test public void read07 () throws Exception {// create the input stream FileInputStream FCM = new FileInputStream (new File ("E: \ root \ sheet \ hello.xlsx ")); // obtain the workbook XSSFWorkbook workbook = new XSSFWorkbook (AIS) from the input stream. // obtain the worksheet XSSFSheet = workbook. getSheet ("hello"); // get the row. 0 indicates the first row XSSFRow row = sheet. getRow (0); // create the row number of the cell determined by the row. The column number is passed to createCell as a parameter. The first column starts to calculate XSSFCell cell = row from 0. getCell (2); // assign String cellValue = cell to the cell. getStringCellValue (); System. out. println ("C1 value is" + cellValue); int a [] [] = new int [10] [30]; for (int I = 0; I <. length; I ++) {System. out. println (I);} workbook. close (); FCM. close ();}

When the problem arises, it can also be explained as a requirement: if you cannot determine whether to read the Excel files earlier than 07 (for example,) or after 07, we certainly hope that the program can be automatically identified, create the corresponding object and operate the excel file. The Code is as follows:

@ Test public void reda03and07 () throws Exception {// read String filePath = "E: \ root \ sheet \ hello.xlsx" of version 03 or 07; if (filePath. matches ("^. + \\. (? I) (xls) | (xlsx) $ ") {FileInputStream FD = new FileInputStream (filePath); boolean is03Excell = filePath. matches ("^. + \\. (? I) (xls) $ ")? True: false; Workbook workbook = is03Excell? New HSSFWorkbook (fiis): new XSSFWorkbook (fiis); Sheet sheet = workbook. getSheetAt (0); Row row = sheet. getRow (0); Cell cell Cell = row. getCell (2); System. out. println ("the data in the first column of the First row is:" + cell. getStringCellValue ());}}

After learning the above examples, we will apply them. We often need to export and export data in batches on a page. Here we will involve operations on excel files, of course, there are other file formats. We use a lList <User> list to save them. Here we write an ExcelUtil tool class: the code is as follows:

 

 

Package com. ittax. core. util; import java. util. list; import javax. servlet. servletOutputStream; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hssf. usermodel. HSSFCellStyle; import org. apache. poi. hssf. usermodel. HSSFFont; import org. apache. poi. hssf. usermodel. HSSFHeader; import org. apache. poi. hssf. usermodel. HSSFRow; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. Usermodel. HSSFWorkbook; import org. apache. poi. ss. util. cellRangeAddress; import com. ittax. nsfw. user. entity. user;/*** excel tool class, supports batch export of * @ author lizewu **/public class ExcelUtil {/*** to import user information to an excel file * @ param userList user list * @ param out output table */public static void exportUserExcel (List <User> userList, servletOutputStream out) {try {// 1. create a workbook HSSFWorkbook workbook = new HSSFWorkbook (); // 1.1 create a merged Cell Object CellRangeAddress callRangeAddress = new CellRangeAddress (1.2,); // start row, end row, start column, end column // header Header style HSSFCellStyle headStyle = createCellStyle (workbook, (short) 16); // 1.3 Column Title style HSSFCellStyle colStyle = createCellStyle (workbook, (short) 13); // 2. create a worksheet HSSFSheet sheet = workbook. createSheet ("User List"); // 2.1 load the merged Cell Object sheet. addMergedRegion (callRangeAddress); // sets the default column width sheet. setdefacolumcolumnwidth (25); // 3. create a row // 3. 1. Create a header row and set the header HSSFRow row = sheet. createRow (0); HSSFCell cell = row. createCell (0); // load the cell style cell. setCellStyle (headStyle); cell. setCellValue ("User List"); // 3.2 Create a column title, and set the column title HSSFRow row2 = sheet. createRow (1); String [] titles = {"username", "Account", "department", "gender", "email "}; for (int I = 0; I <titles. length; I ++) {HSSFCell cell2 = row2.createCell (I); // load the cell style cell2.setCellStyle (colStyle); cell2.setCellValue (titles [I]);} // 4. Operate the cell; write the user list to the excel if (userList! = Null) {for (int j = 0; j <userList. size (); j ++) {// create a data row. There are two rows in front. The header row and the column header row HSSFRow row3 = sheet. createRow (j + 2); HSSFCell cell1 = row3.createCell (0); cell1.setCellValue (userList. get (j ). getName (); HSSFCell cell2 = row3.createCell (1); cell2.setCellValue (userList. get (j ). getAccount (); HSSFCell cell3 = row3.createCell (2); cell3.setCellValue (userList. get (j ). getDept (); HSSFCell cell4 = row3.createCell (3); c Ell4.setCellValue (userList. get (j). isGender ()? "Male": "female"); HSSFCell cell5 = row3.createCell (4); cell5.setCellValue (userList. get (j ). getEmail () ;}// 5. output workbook. write (out); workbook. close (); // out. close ();} catch (Exception e) {e. printStackTrace () ;}}/***** @ param workbook * @ param fontsize * @ return cell style */private static HSSFCellStyle createCellStyle (HSSFWorkbook workbook, short fontsize) {// TODO Auto-generated method stub HSSFCellStyle style = workbook. createCellStyle (); style. setAlignment (HSSFCellStyle. ALIGN_CENTER); // horizontally centered style. setverticalignment (HSSFCellStyle. VERTICAL_CENTER); // vertical center // create the font HSSFFont font = workbook. createFont (); font. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); font. setFontHeightInPoints (fontsize); // load the font style. setFont (font); return style ;}}

 

Next, call the method in UseService and write the exportExcel method:

@ Override public void exportExcel (List <User> userList, ServletOutputStream out) {// TODO Auto-generated method stub ExcelUtil. exportUserExcel (userList, out) ;}@ Override public void importExcel (File file, String excelFileName) {// TODO Auto-generated method stub // 1. create input stream try {FileInputStream inputStream = new FileInputStream (file); boolean is03Excel = excelFileName. matches ("^. + \\. (? I) (xls) $ "); // 1. Read the Workbook workbook Workbook = is03Excel? New HSSFWorkbook (inputStream): new XSSFWorkbook (inputStream); // 2. read worksheet Sheet sheet = workbook. getSheetAt (0); // 3. read row // determine that the number of rows is greater than two because the data is inserted from the third row if (sheet. getPhysicalNumberOfRows ()> 2) {User user = null; // skip the first two rows for (int k = 2; k <sheet. getPhysicalNumberOfRows (); k ++) {// read the Row row0 = sheet. getRow (k); user = new User (); // username Cell cell0 = row0.getCell (0); user. setName (cell0.getStringCellValue (); // account Cell cell1 = row0.getCell (1); user. setAccount (cell1.getStringCellValue (); // department Cell cell2 = row0.getCell (2); user. setDept (cell2.getStringCellValue (); // set the gender Cell cell3 = row0.getCell (3); boolean gender = cell3.getStringCellValue () = "male "? True: false; user. setGender (gender); // set the mobile phone String mobile = ""; Cell cell4 = row0.getCell (4); try {mobile = cell4.getStringCellValue ();} catch (Exception e) {// TODO Auto-generated catch block double dmoblie = cell4.getNumericCellValue (); mobile = BigDecimal. valueOf (dmoblie ). toString ();} user. setMobile (mobile); // set Cell cell5 = row0.getCell (5); user. setEmail (cell5.getStringCellValue (); // The Default user Password is 123456 user. setPassword ("123456"); // The Default user status is valid. setState (User. USER_STATE_VALIDE); // save the user save (user) ;}} workbook. close (); inputStream. close ();} catch (Exception e) {// TODO Auto-generated catch block e. printStackTrace ();}}

Finally, call the service method in the Action:

// Export the public void exportExcel () {try {// 1. search for the user list userList = userService. findObjects (); // 2. export HttpServletResponse response = ServletActionContext. getResponse (); // the file format set here is application/x-excel response. setContentType ("application/x-excel"); response. setHeader ("Content-Disposition", "attachment; filename =" + new String (" .xls ". getBytes (), "ISO-8859-1"); ServletOutputStream outputStream = res Ponse. getOutputStream (); userService. exportExcel (userList, outputStream); if (outputStream! = Null) outputStream. close ();} catch (Exception e) {e. printStackTrace () ;}} public String importExcel () {if (userExcel! = Null) {// determine whether it is an Excel file if (userExcelFileName. matches ("^. + \\.(? I) (xls) | (xlsx) $ ") {userService. importExcel (userExcel, userExcelFileName) ;}} return" list ";}

Note that the class ServletOutputStream should be used to export and import data in batches.

Export user results as shown in;

The import result is as follows;

Before import:

 

 

The imported result;

OK. This is the time to operate the EXCEL file on POI.