【POI】 最简POI导出 一

1.导入坐标

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.5.RELEASE</version>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

2.启动类

@SpringBootApplication
public class PoiApplication {

  public static void main(String[] args) {
    SpringApplication.run(PoiApplication.class,args);
  }
}

3.数据实体

@Data
@ToString
public class User {

  private String id;
  private String name;
  private Double age;
  private String address;
  private Date time;
}

4.准备Excel表

 

5.方法

本示例使用数据物理地址作为模拟用户手动上传文件

如果用手动上传的话，使用 MultipartFile file  file.getInputStream来替换FileInputStream

@RestController
@RequestMapping("/excel")
public class UserController {

  @GetMapping("/user")
  public void importExcel(HttpServletResponse response) throws Exception {

    FileInputStream fileInputStream = new FileInputStream(new File("C:\\Users\\lenovo\\Desktop\\user.xlsx"));

    response.setContentType("text/html;charset=utf-8");

    //1.根据上传流信息创建工作簿
    Workbook workbook = WorkbookFactory.create(fileInputStream);

    //2.获取sheet
    Sheet sheet = workbook.getSheetAt(0);

    //3.构造数据集合
    List<User> userList = new ArrayList<>();

    //4.从第二行开始获取数据
    for(int i = 1 ; i < sheet.getLastRowNum(); i ++){
      //获取行
      Row row = sheet.getRow(i);
      //5.从第一列获取数据
      User user = new User();
      user.setId(getCellValue(row.getCell(0)).toString());
      user.setName(getCellValue(row.getCell(1)).toString());
      user.setAge((double)getCellValue(row.getCell(2)));
      user.setAddress(getCellValue(row.getCell(3)).toString());
      user.setTime(new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").parse(getCellValue(row.getCell(4)).toString()));
      userList.add(user);
    }
    for (User user : userList) {
      System.out.println(user.toString());
    }
  }




  public static Object getCellValue(Cell cell) {
    //1.获取到单元格的属性类型
    CellType cellType = cell.getCellType();
    //2.根据单元格数据类型获取数据
    Object value = null;
    switch (cellType) {
      case STRING:
        value = cell.getStringCellValue();
        break;
      case BOOLEAN:
        value = cell.getBooleanCellValue();
        break;
      case NUMERIC:
        if(DateUtil.isCellDateFormatted(cell)) {
          //日期格式
          value = cell.getDateCellValue();
        }else{
          //数字
          value = cell.getNumericCellValue();
        }
        break;
      case FORMULA: //公式
        value = cell.getCellFormula();
        break;
      default:
        break;
    }
    return value;
  }
}