Definition of POI

There are two mainstream toolkits for operating Excel in JAVA: jxl and poi. jxl can only operate Excel 95, 97, 2000, that is, excel suffixed with. xls. poi can operate Excel 95 and later versions, and can operate excel with suffixes of. xls and. xlsx.

POI is called Poor Obfuscation Implementation, literally translated as "Poor Fuzzy Implementation". Using POI interface, you can operate the reading and writing function of Microsoft office suite tool through JAVA. Official website: http://poi.apache.org POI supports all versions of office. First, download the following interface from the official website:

After downloading, open "poi-bin-3.15-20160924.tar.gz" to get the jar packages needed to operate excel, and copy these jar packages into the project. For excel, only poi-3.15.jar is required for 2003 and previous versions, and replication is required for both 2007 and later versions.

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 there is no dom4j.jar in my jar file), or add dom4j.jar to prevent errors.

2. Using junit to operate Excel test

Firstly, we define Excel Workbook objects, worksheet objects, row objects, and cell objects.

The specific code is as follows: It should be clear whether it was before the 2007 version or after the 2007 version (including the 2007 version). The following code is before the 2007 version:

This code simply writes the data to the Excel file to create

public static void main(String[] args) throws Exception {
        /**
         * Note that this is just the suffix name of the excel file corresponding to the previous version 07.xls
         * 07 The suffix of excel file is. xlsx
         */
        //Create a new workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //New worksheet
        HSSFSheet sheet = workbook.createSheet("hello");
        //Create rows,Line numbers are passed as parameters to createRow()Method,The first line starts at 0.
        HSSFRow row = sheet.createRow(0);
        //Creating Cells,row The line number has been determined.,Column numbers are passed as parameters to createCell(),The first column starts at 0.
        HSSFCell cell = row.createCell(2);
        //Set the value of the cell,Namely C1 Value(first line,Column 3)
        cell.setCellValue("hello sheet");
        //Output to disk
        FileOutputStream fos = new FileOutputStream(new File("E:\\root\\sheet\\11.xls"));
        workbook.write(fos);
        workbook.close();
        fos.close();
    }

The results are as follows:

Similarly, you can read Excel files, get the data of Excel files, and print them out. The code is as follows:

@Test
    public void testReadExcel() throws Exception
    {
        //Create an input stream
        FileInputStream fis = new FileInputStream(new File("E:\\root\\sheet\\11.xls"));
        //Passing parameters through constructors
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        //Get the worksheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        //Get rows,Line numbers are passed as parameters to getRow Method,The first line starts at 0.
        HSSFRow row = sheet.getRow(0);
        //Get cells,row The line number has been determined.,Column numbers are passed as parameters to getCell,The first column starts at 0.
        HSSFCell cell = row.getCell(2);
        //Set the value of the cell,Namely C1 Value(first line,Column 3)
        String cellValue = cell.getStringCellValue();
        System.out.println("The value of the first row and the third column is"+cellValue);
        workbook.close();
        fis.close();
    }

The results are as follows:

The above operations are all Excel files before version 07, that is, the suffix is. xls, and after version 07 and 07, the object name of Excel files suffix is. xlsx corresponding workbook is changed to:

//Create A Workbook
        XSSFWorkbook workbook = new XSSFWorkbook();

The code is as follows. Create excel file and save data to excel file.

@Test
    public void write07() throws Exception
    {
        //Create A Workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        //New worksheet
        XSSFSheet sheet = workbook.createSheet("hello");
        //Create rows,0 Represents the first line
        XSSFRow row = sheet.createRow(0);
        //Create cell line numbers from row Sure?,Column numbers are passed as parameters to createCell;The first column starts at 0.
        XSSFCell cell = row.createCell(2);
        //Assign values to cells
        cell.setCellValue("hello sheet");
        //Create an output stream
        FileOutputStream fos = new FileOutputStream(new File("E:\\root\\sheet\\hello.xlsx"));
        workbook.write(fos);
        workbook.close();
        fos.close();
    }

Corresponding to the read data, the code is as follows:

@Test
    public void read07() throws Exception
    {
        //Create an input stream
        FileInputStream fis = new FileInputStream(new File("E:\\root\\sheet\\hello.xlsx"));
        //Get the workbook from the input stream
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        //Get a worksheet
        XSSFSheet sheet = workbook.getSheet("hello");
        //Get the line,0 Represents the first line
        XSSFRow row = sheet.getRow(0);
        //Create cell line numbers from row Sure?,Column numbers are passed as parameters to createCell;The first column starts at 0.
        XSSFCell cell = row.getCell(2);
        //Assign values to cells
        String cellValue = cell.getStringCellValue();
        System.out.println("C1 The value is"+cellValue);
        int a[][] = new int[10][30];
        for(int i=0;i<a.length;i++)
        {
            System.out.println(i);
        }
        workbook.close();
        fis.close();
    }

The problem arises, which can also be interpreted as a requirement: when we are not sure whether to read Excel files before 07 (e.g. 2003, 95, 97, 2000) or after 07, we certainly hope that the program can automatically identify and create corresponding objects to manipulate excel files. The code is as follows:

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

After learning the above examples, the next step is to apply it. We often need to export and export data in batches in a page. Here we deal with the operation of excel files, and of course, other file formats. We use an lList < User > list to save it. Here we write an excel Util 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 to support bulk export
 * @author lizewu
 *
 */
public class ExcelUtil {
    
    /**
     * Import user information into 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 merged cell objects
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//Starting line,End line,Starting column,End column
            //1.2 Header Header Style
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3 Column heading style
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //2.Create a worksheet
            HSSFSheet sheet = workbook.createSheet("User List");
            //2.1 Load merge cell objects
            sheet.addMergedRegion(callRangeAddress);
            //Set default column width
            sheet.setDefaultColumnWidth(25);
            //3.Create rows
            //3.1 Create header header rows;And set the header title
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
        
            //Load cell style
            cell.setCellStyle(headStyle);
            cell.setCellValue("User List");
            
            //3.2 Create column headings;And set the column title
            HSSFRow row2 = sheet.createRow(1);
            String[] titles = {"User name","Account number","Subordinate departments","Gender","Mail box"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //Load cell style
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            
            
            //4.Operating cells;Write the user list excel
            if(userList != null)
            {
                for(int j=0;j<userList.size();j++)
                {
                    //Create data rows,There are two lines ahead.,Header header row and 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);
                    cell4.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
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//Vertical Centralization
        //Create fonts
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints(fontsize);
        //Load fonts
        style.setFont(font);
        return style;
    }
}

The next step is to 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 an input stream
        try {
            FileInputStream inputStream = new FileInputStream(file);
            boolean is03Excel = excelFileName.matches("^.+\\.(?i)(xls)$");
            //1.Read workbooks
            Workbook workbook = is03Excel?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);
            //2.Reading worksheet
            Sheet sheet = workbook.getSheetAt(0);
            //3.Read rows
            //Judging that the number of rows is greater than two,Because the data is inserted from the third line
            if(sheet.getPhysicalNumberOfRows() > 2)
            {
                User user = null;
                //Skip the first two lines
                for(int k=2;k<sheet.getPhysicalNumberOfRows();k++ )
                {
                    //Read cells
                    Row row0 = sheet.getRow(k);
                    user = new User();
                    //User name
                    Cell cell0 = row0.getCell(0);
                    user.setName(cell0.getStringCellValue());
                    //Account number
                    Cell cell1 = row0.getCell(1);
                    user.setAccount(cell1.getStringCellValue());
                    //Subordinate departments
                    Cell cell2 = row0.getCell(2);
                    user.setDept(cell2.getStringCellValue());
                    //Setting Sex
                    Cell cell3 = row0.getCell(3);
                    boolean gender = cell3.getStringCellValue() == "male"?true:false;
                    user.setGender(gender);
                    //Setting up 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);
                    //Setting up E-mail
                    Cell cell5 = row0.getCell(5);
                    user.setEmail(cell5.getStringCellValue());
                    //The default user password is 123456
                    user.setPassword("123456");
                    //User default status is valid
                    user.setState(User.USER_STATE_VALIDE);
                    //Save users
                    save(user);
                }
            }
            workbook.close();
            inputStream.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Finally, the service method is called in Action:

//Export User List
        public void exportExcel()
        {
            try
            {
                //1.Find 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("User List.xls".getBytes(), "ISO-8859-1"));
                ServletOutputStream outputStream = response.getOutputStream();
                userService.exportExcel(userList, outputStream);
                if(outputStream != null)
                    outputStream.close();
            }catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        
        public String importExcel()
        {
            if(userExcel!= null)
            {
                //Judging whether it is Excel file
                if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
                {
                    userService.importExcel(userExcel, userExcelFileName);
                }
            }
            return"list";
        }

Note that you should use the ServletOutputStream class, and finally realize the batch export and import of data.

Export user results as follows.

The import results are as follows.

Before introduction:

Results after importation;

ok, so far as the POI operation EXCEL file is concerned