POI操作Excel总结

POI操作Excel总结

---

POI需要导入jar包

Maven项目POI导入相应的jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>

POI实现导入Excel的内容和导出数据到Excel

导入Excel数据

1. 获取excel数据
2. 解析excel数据
3. 插入数据到数据库

public void push() throws IOException{
//1、获取excel文件
UploadFile file = getFile("userExcel");
        if(file != null){
            //2、导入
            FileInputStream fileInputStream = new FileInputStream(file.getFile());
            //2.1、读取工作簿
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            //2.2、读取工作表
            Sheet sheet = workbook.getSheetAt(0);
            //2.3、读取行
            if(sheet.getPhysicalNumberOfRows() > 2){
                for(int k = 1; k < sheet.getPhysicalNumberOfRows(); k++){
                    User model = new User();
                    String id = UUID.randomUUID().toString().replace("-", "");
                    model.setId(id);
                    //2.4、读取单元格
                    Row row = sheet.getRow(k);
                    //用户名
                    Cell cell0 = row.getCell(0);
                    model.setName(cell0.getStringCellValue());
                    //帐号
                    Cell cell1 = row.getCell(1);
                    model.setAccount(cell1.getStringCellValue());
                    //所属部门
                    Cell cell2 = row.getCell(2);
                    model.setDept(cell2.getStringCellValue());

                    //3、保存数据
                    model.save();
                }
            }
            workbook.close();
            fileInputStream.close();
        }
redirect("/user");
}

---

导出数据到Excel

1. 从数据库中读取用户数据
2. 创建excel并写入数据
3. 将excel输出到浏览器提供用户下载

public void pull(){
    //提供下载
    File file = new File("text.xls");
       //1、从数据库读取数据
       List<User> users = User.me.find("select * from user");
       PoiUtil.exportUserExcel(users,file);

       renderFile(file);
}

//2、新建PoiUtil类创建excel写入数据
public class PoiUtil {
    public static void exportUserExcel(List<User> userList, File file) {
            try {
                //1、创建工作簿
                HSSFWorkbook workbook = new HSSFWorkbook();

                //2、创建工作表
                HSSFSheet sheet = workbook.createSheet("用户列表");

                //3、创建列标题行
                HSSFRow row2 = sheet.createRow(1);
                String[] titles = {"用户名","帐号", "所属部门"};
                for(int i = 0; i < titles.length; i++){
                    HSSFCell cell2 = row2.createCell(i);
                }

                //4、操作单元格；将用户列表写入excel
                if(userList != null){
                    for(int j = 0; j < userList.size(); j++){
                        HSSFRow row = sheet.createRow(j);
                        HSSFCell cell11 = row.createCell(0);
                        cell11.setCellValue(userList.get(j).getName());
                        HSSFCell cell12 = row.createCell(1);
                        cell12.setCellValue(userList.get(j).getAccount());
                        HSSFCell cell13 = row.createCell(2);
                        cell13.setCellValue(userList.get(j).getDept());
                    }
                }
                //5、输出
                workbook.write(file);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
}