Java如何利用POI实现导入导出Excel表格

这篇文章主要介绍“Java如何利用POI实现导入导出excel表格”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“Java如何利用POI实现导入导出Excel表格”文章能帮助大家解决问题。

一、Java利用POI实现导入导出Excel表格demo

1.引入依赖

<dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml</artifactId>       <version>4.1.2</version></dependency>

2.导入demo

1 controller层

    @PostMapping("/import")    public Result userImport2(@RequestParam("file") MultipartFile file) throws Exception{        Result result=userService.userImportExcel(file);        return result;    }

2 service实现类层

public Result userImportExcel(MultipartFile file){    try {        InputStream inputStream = file.getInputStream();        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);        //获取表单sheet 第一个        XSSFSheet sheetAt = sheets.getSheetAt(0);        //获取第一行        int firstRowNum = sheetAt.getFirstRowNum();        //最后一行        int lastRowNum = sheetAt.getLastRowNum();        //存入数据集合        List<User> users=new ArrayList<>();        //遍历数据        for(int i=firstRowNum+1;i<lastRowNum+1;i++){            XSSFRow row = sheetAt.getRow(i);            if(row!=null){                               //这里我就直接赋值                User user = new User();                user.setUname(row.getCell(0).getStrinGCellValue());                user.setUpassword(row.getCell(1).getStringCellValue());                user.setUsex(row.getCell(2).getStringCellValue());                user.setRole(row.getCell(3).getStringCellValue());                user.setUlove((int) row.getCell(4).getNumericCellValue());                user.setUphoto(row.getCell(5).getStringCellValue());                user.setUaddress(row.getCell(6).getStringCellValue());                users.add(user);            }        }        //保存数据        saveBatch(users);        return Result.success();    }catch (Exception e){        e.printStackTrace();        log.info("error:{}",e);    }    return Result.error("300","导入失败");}public String getValue(HSSFCell cell) {    if(cell==null){        return "";    }    String cellValue= "";    try {        DecimalFORMat df=new DecimalFormat("0.00");        if(cell.getCellType()== CellType.NUMERIC){            //日期时间转换            if(HSSFDateUtil.isCellDateFormatted(cell)){                cellValue=DateFormatUtils.format(cell.getDateCellValue(),"yyyy-MM-dd");            }else{                NumberFormat instance = NumberFormat.getInstance();                cellValue=instance.format(cell.getNumericCellValue()).replace(",","");            }        }else if(cell.getCellType() == CellType.STRING){            //字符串            cellValue=cell.getStringCellValue();        }else if(cell.getCellType() == CellType.BOOLEAN){            //Boolean            cellValue= String.valueOf(cell.getBooleanCellValue());        }else if(cell.getCellType() == CellType.ERROR){            //错误        }else if(cell.getCellType() == CellType.FORMULA){            //转换公式 保留两位            cellValue=df.format(cell.getNumericCellValue());        }else{            cellValue=null;        }    } catch (Exception e) {        e.printStackTrace();        cellValue="-1";    }    return cellValue;}

3.导出demo

1 controller层

@GetMapping("/export")public Result userExport2(httpservletResponse response) throws Exception{    Result result=userService.userExportExcel(response);    return result;}

2 service实现类

public Result userExportExcel(HttpServletResponse response) {    try {        //创建excel        XSSFWorkbook sheets = new XSSFWorkbook();        //创建行        XSSFSheet sheet = sheets.createSheet("用户信息");        //格式设置        XSSFCellStyle cellStyle = sheets.createCellStyle();        //横向居中        cellStyle.setAlignment(HorizontalAlignment.CENTER);        //创建单元格第一列        XSSFRow row = sheet.createRow(0);        //表头        this.titleExcel(row,cellStyle);        //查询全部的用户数据  mybatis-plus        List<User> list = list();        //遍历设置值        for(int i=0;i<list.size();i++){            XSSFRow rows = sheet.createRow(i+1);            User user=list.get(i);            //表格里赋值            this.titleExcelValue(user,rows,cellStyle);        }        //设置浏览器响应格式        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");        String filName= URLEncoder.encode("用户信息","UTF-8");        response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");        ServletOutputStream outputStream=response.getOutputStream();        sheets.write(outputStream);        outputStream.close();        sheets.close();        return Result.success();    }catch (Exception e){        e.printStackTrace();        log.info("error:{}",e);    }    return Result.error("300","导出失败");}public void titleExcelValue(User user, XSSFRow row,XSSFCellStyle cellStyle) {    XSSFCell cellId = row.createCell(0);    cellId.setCellValue(user.getUid());    cellId.setCellStyle(cellStyle);    XSSFCell cellUserName = row.createCell(1);    cellUserName.setCellValue(user.getUname());    cellUserName.setCellStyle(cellStyle);    XSSFCell cellPassWord = row.createCell(2);    cellPassword.setCellValue(user.getUpassword());    cellPassword.setCellStyle(cellStyle);    XSSFCell cellSex = row.createCell(3);    cellSex.setCellValue(user.getUsex());    cellSex.setCellStyle(cellStyle);    XSSFCell cellRole = row.createCell(4);    cellRole.setCellValue(user.getRole());    cellRole.setCellStyle(cellStyle);    XSSFCell cellLoveValue = row.createCell(5);    cellLoveValue.setCellValue(user.getRole());    cellLoveValue.setCellStyle(cellStyle);    XSSFCell cellPhone = row.createCell(6);    cellPhone.setCellValue(user.getUphoto());    cellPhone.setCellStyle(cellStyle);    XSSFCell cellAddress = row.createCell(7);    cellAddress.setCellValue(user.getUaddress());    cellAddress.setCellStyle(cellStyle);}public void titleExcel(XSSFRow row,XSSFCellStyle cellStyle){    XSSFCell cellId = row.createCell(0);    cellId.setCellValue("用户ID");    cellId.setCellStyle(cellStyle);    XSSFCell cellUserName = row.createCell(1);    cellUserName.setCellValue("用户名");    cellUserName.setCellStyle(cellStyle);    XSSFCell cellPassword = row.createCell(2);    cellPassword.setCellValue("密码");    cellPassword.setCellStyle(cellStyle);    XSSFCell cellSex = row.createCell(3);    cellSex.setCellValue("性别");    cellSex.setCellStyle(cellStyle);    XSSFCell cellRole = row.createCell(4);    cellRole.setCellValue("角色");    cellRole.setCellStyle(cellStyle);    XSSFCell cellLoveValue = row.createCell(5);    cellLoveValue.setCellValue("爱心值");    cellLoveValue.setCellStyle(cellStyle);    XSSFCell cellPhone = row.createCell(6);    cellPhone.setCellValue("电话号码");    cellPhone.setCellStyle(cellStyle);    XSSFCell cellAddress = row.createCell(7);    cellAddress.setCellValue("地址");    cellAddress.setCellStyle(cellStyle);}

二、Hutool工具类封装方法导出导入Excel

1.引入依赖

把poi封装到工具类方法里面

<!-- hutool  -->        <dependency>            <groupId>cn.hutool</groupId>            <artifactId>hutool-all</artifactId>            <version>5.7.20</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>4.1.2</version></dependency>

2.导入demo

@PostMapping("/import")public Result userImport(@RequestParam("file") MultipartFile file) throws Exception{        System.out.println(file.toString());        //InputStream inputStream = multipartFile.getInputStream();        InputStream inputStream = file.getInputStream();        ExcelReader reader = ExcelUtil.getReader(inputStream);        //读取表的内容        List<List<Object>> list = reader.read(1);        List<User> users = new ArrayList<>();        for(List<Object> row : list){            User user = new User();            user.setUname(row.get(0).toString());            user.setUpassword(row.get(1).toString());            user.setUsex(row.get(2).toString());            user.setRole(row.get(3).toString());            user.setUlove(Integer.valueOf(row.get(4).toString()));            user.setUphoto(row.get(5).toString());            user.setUaddress(row.get(6).toString());            users.add(user);        }        //批量插入用户信息 mybatis-plus        userService.saveBatch(users);        return Result.success();    }

3.导出demo

     @GetMapping("/export")    public Result userExport(HttpServletResponse response) throws Exception{        //查询全部的用户数据        List<User> list = userService.list();        //在内存里做操作，保存到浏览器        ExcelWriter writer = ExcelUtil.getWriter(true);        //自定义标题别名        writer.addHeaderAlias("uname","用户名");        writer.addHeaderAlias("upassword","密码");        writer.addHeaderAlias("usex","性别");        writer.addHeaderAlias("role","角色");        writer.addHeaderAlias("ulove","爱心值");        writer.addHeaderAlias("uphoto","电话号码");        writer.addHeaderAlias("uaddress","地址");        //一次性写出list内的对象的Excel，使用默认样式，强制输出标题        writer.write(list,true);        //设置浏览器响应格式        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");        String filName= URLEncoder.encode("用户信息","UTF-8");        response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");        ServletOutputStream outputStream=response.getOutputStream();        writer.flush(outputStream,true);        outputStream.close();        writer.close();        return Result.success();    }

关于“Java如何利用POI实现导入导出Excel表格”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注编程网精选频道，小编每天都会为大家更新不同的知识点。