POI操作Excel总结

来源：互联网发布：一台机器多个ip linux 编辑：程序博客网时间：2024/05/01 03:47

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数据

获取excel数据
解析excel数据
插入数据到数据库

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

从数据库中读取用户数据
创建excel并写入数据
将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();            }        }}

0 0