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

2.1 controller层

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

2.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){
       /* //获取第一行的第一列
        int firstCellNum = row.getFirstCellNum();
        //获取第一行的最后列
        short lastCellNum = row.getLastCellNum();
        for (int j=firstCellNum;j<lastCellNum+1;j++){
          //放入集合中需要可以用这种方法
          String cellValue = getValue(row.getCell(firstCellNum));
        }*/
        //这里我就直接赋值
        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

3.1 controller层

/**
* 导出
* @param response
* @return
* @throws Exception
*/
@GetMapping("/export")
public Result userExport2(HttpServletResponse response) throws Exception{
  Result result=userService.userExportExcel(response);
  return result;
}

3.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);
  JzWhzlIxC}

  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&恰卡编程网lt;/groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
</dependency>

2.导入demo

/**
  * Excel导入
*/
@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

/**
  * Excel导出 方法一
  */
  @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 outputStr恰卡编程网eam=response.getOutputStream();
    writer.flush(outputStream,true);
    outputStream.close();
    writer.close();
    return Result.success();
  }

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。