Apache POI 操作Excel文件

Apache POI 操作Excel文件：

POI简介：

Apache POI[1] 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

web开发中，经常会遇到数据的导入和导出功能。我们通过POI技术操作一定规则的Excel文档就可以完成对所需功能的实现。例如，某模块报表的导出，某模块数据的录入等。

POI可以很好的操作2003版的xls文件以及2007版的xlsx文件，所这也是选择Apache POI的原因。

Apache POI 3.16 官网下载地址：https://poi.apache.org/download.html#POI-3.16/

下载下来目录结构如下：

我们Eclipse 新建java project 项目后，把以下jar文件引入到lib里即可使用。

• poi-3.16.jar
• poi-ooxml-3.16.jar
• poi-ooxml-schemas-3.16.jar
• curvesapi-1.04.jar
• xmlbeans-2.6.0.jar
• commons-collections4-4.1.jar

也就是根目录的poi-3.16.jar，poi-ooxml-3.16.jar，poi-ooxml-schemas-3.16.jar，和ooxml-lib目录下的curvesapi-1.04.jar，xmlbeans-2.6.0.jar以及lib目录下的commons-collections4-4.1.jar

创建数据库以及链接数据库：

Mysql 语句:   CREATE TABLE user(    id INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(20),    address VARCHAR(30),    email VARCHAR(20),    phone VARCHAR(20),    age INT,    pass VARCHAR(20));

JDBCutils,记得引入mysql驱动包:

public class JDBCutils {    static {        try {            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            e.printStackTrace();        }     }    public static Connection getConnection(){        try {            return DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }}

简单测试，从数据库中读取数据然后写入到excel文件中，代码如下

@org.junit.Testpublic void testWrite() throws SQLException{    //先从数据库读取数据    String sql = "select * from user";    ResultSet executeQuery = JDBCutils.getConnection().createStatement().executeQuery(sql);    ArrayList<UserBean> arrayList = new ArrayList<UserBean>();    //将数据封装成java bean放到集合中，方便后续使用    while(executeQuery.next()){        int id = executeQuery.getInt(1);        String name = executeQuery.getString(2);        String address = executeQuery.getString(3);        String email = executeQuery.getString(4);        String phone = executeQuery.getString(5);        int age = executeQuery.getInt(6);        String password = executeQuery.getString(7);        arrayList.add(new UserBean(id, name, address, email, phone, password, age));    }    //开始写入到excel文件中    //1.创建工作薄    XSSFWorkbook book = new XSSFWorkbook();    //2.创建工作布    XSSFSheet createSheet = book.createSheet();    //3.title --- 用户管理列表    XSSFCellStyle titleStyle = book.createCellStyle();    titleStyle.setAlignment(HorizontalAlignment.CENTER);    XSSFFont titleFont = book.createFont();    titleFont.setBold(true);    titleFont.setFontHeightInPoints((short) 20);    titleStyle.setFont(titleFont);    CellRangeAddress region = new CellRangeAddress(0, 1, 0, 6);    createSheet.addMergedRegion(region);    XSSFCell createCell = createSheet.createRow(0).createCell(0);    createCell.setCellValue("用户管理列表");    createCell.setCellStyle(titleStyle);    //4.设置列描述    XSSFRow headerRow = createSheet.createRow(2);    headerRow.createCell(0).setCellValue("用户ID");    headerRow.createCell(1).setCellValue("名称");    headerRow.createCell(2).setCellValue("地址");    headerRow.createCell(3).setCellValue("邮箱");    headerRow.createCell(4).setCellValue("手机号码");    headerRow.createCell(5).setCellValue("年龄");    headerRow.createCell(6).setCellValue("密码");    for(int i = 0; i < arrayList.size() ; i ++){        //5.创建行        XSSFRow createRow = createSheet.createRow(i+3);        UserBean bean = arrayList.get(i);        //6.设置每列的数据        createRow.createCell(0).setCellValue(bean.getId());        createRow.createCell(1).setCellValue(bean.getName());        createRow.createCell(2).setCellValue(bean.getAddress());        createRow.createCell(3).setCellValue(bean.getEmail());        createRow.createCell(4).setCellValue(bean.getPhone());        createRow.createCell(5).setCellValue(bean.getAge());        createRow.createCell(6).setCellValue(bean.getPassword());    }    try {        //7.写入，这里测试使用XSSFWorkbook对象写xls以及xlsx可以        FileOutputStream fileOutputStream = new FileOutputStream(new File("D://test.xls"));        book.write(fileOutputStream);        book.close();        fileOutputStream.close();    } catch (IOException e) {        e.printStackTrace();    }}

运行以后，打开文件进行测试

读取测试，把刚才写入的Excel文件在读取出来，然后写入到数据库中。

@org.junit.Testpublic void textRead() throws FileNotFoundException, IOException, SQLException{    String filePath = "D://test.xlsx";    FileInputStream input = new FileInputStream(new File(filePath));    Workbook workBook = null;    //读取xls和xlsx文件的时候，使用的实例对象并不一样，我们这里进行判断    if(filePath.endsWith(".xlsx")){ // 2007版本的excel        workBook = new XSSFWorkbook(input);    }else{ //2003版的excel        workBook = new HSSFWorkbook(input);    }    ArrayList<UserBean> arrayList = new ArrayList<UserBean>();    //获取工作布    int numberOfSheets = workBook.getNumberOfSheets();    for(int i = 0; i < numberOfSheets ; i ++){        Sheet sheetAt = workBook.getSheetAt(i);        int lastRowNum = sheetAt.getLastRowNum();        //根据规则，我们从第4行开始读取        for(int j = 3 ; j <= lastRowNum ; j ++){            Row row = sheetAt.getRow(j);            int id = (int) row.getCell(0).getNumericCellValue();            String username = row.getCell(1).getStringCellValue();            String address = row.getCell(2).getStringCellValue();            String email = row.getCell(3).getStringCellValue();            String phone = row.getCell(4).getStringCellValue();            int age = (int) row.getCell(5).getNumericCellValue();            String pass = row.getCell(6).getStringCellValue();            arrayList.add(new UserBean(id,username,address,email,phone,pass,age));        }    }    input.close();    input = null;    workBook.close();    workBook = null;    //写入到数据库中    Connection connection = JDBCutils.getConnection();    String sql = "insert into user(username,phone,email,address,age,pass) value(?,?,?,?,?,?)";    PreparedStatement prepareStatement = connection.prepareStatement(sql);    for(int i = 0 ;i < arrayList.size(); i ++){        UserBean bean = arrayList.get(i);        prepareStatement.setString(1, bean.getName());        prepareStatement.setString(2, bean.getPhone());        prepareStatement.setString(3, bean.getEmail());        prepareStatement.setString(4, bean.getAddress());        prepareStatement.setInt(5, bean.getAge());        prepareStatement.setString(6, bean.getPassword());        //添加到批处理中        prepareStatement.addBatch();    }    //执行批处理    int[] executeBatch = prepareStatement.executeBatch();    System.out.println(executeBatch);}

查看数据库中，数据正确的写入了进去，到此基本的POI操作完毕。