SpringBoot利用POI实现Excel表导入导出功能

POI（Poor Obfuscation Implementation）是一个Java库，用于读取和写入Microsoft Office格式文件，如Excel、Word和PowerPoint等；对于批量的数据操作，利用Excel表的导入导出功能对于用户来说更方便，更便捷。

本功能是用POI将数据库数据导出成一个xls文件，也可以将xls文件导入回数据库，首先根据数据库关键字创建一个导出工具类，POIUtils

导出流程：

1. 创建一个 Workbook 对象，指定要导出的文件格式和版本。可以使用 HSSFWorkbook（xls 格式）或 XSSFWorkbook（xlsx 格式）。

Workbook workbook = new XSSFWorkbook(); // 创建一个 XSSFWorkbook 对象

 2.用 createSheet() 方法创建一个新 Sheet对象。

Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个新 Sheet

3. 创建表头行（第一行），并设置每个单元格的值。可以用 createRow() 方法创建一个新的 Row，然后使用 createCell() 方法创建单元格。

 Row headerRow = sheet.createRow(0); // 创建第一行
Cell cell1 = headerRow.createCell(0); // 创建第一列单元格
cell1.setCellValue("列1");
Cell cell2 = headerRow.createCell(1); // 创建第二列单元格
cell2.setCellValue("列2");

 4.遍历数据列表，创建数据行，并逐行将数据填充到单元格中。

for (int i = 0; i < dataList.size(); i++) {
    Row dataRow = sheet.createRow(i + 1); // 创建数据行
    Data data = dataList.get(i);
    Cell cell1 = dataRow.createCell(0); // 创建第一列单元格
    cell1.setCellValue(data.getValue1());
    Cell cell2 = dataRow.createCell(1); // 创建第二列单元格
    cell2.setCellValue(data.getValue2());
}

5.设置单元格样式，例如字体、颜色、边框等。可以使用 createCellStyle() 方法创建一个新的 CellStyle 对象，并在此基础上设置样式属性。

CellStyle headerStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
headerStyle.setFont(font);
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setBorderTop(BorderStyle.THICK);
headerStyle.setBorderLeft(BorderStyle.THICK);
headerStyle.setBorderBottom(BorderStyle.THICK);
headerStyle.setBorderRight(BorderStyle.THICK);
cell1.setCellStyle(headerStyle);

6.将 Workbook 对象写入输出流，以生成导出文件 

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=demo.xlsx");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();

 完整导出功能：

    public static ResponseEntity<byte[]> ExportExcel(List<TbStaRecordsEntity> list) {
        HSSFWorkbook excel = new HSSFWorkbook(); //创建xls文件
        HSSFSheet sheet = excel.createSheet(); //创建一个新表
        HSSFRow row = sheet.createRow(0); //创建表头行（第0行）
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //设置时间格式
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("设备编号");
        row.createCell(2).setCellValue("设备状态");
        row.createCell(3).setCellValue("状态开始时间");
        row.createCell(4).setCellValue("状态结束时间");
        row.createCell(5).setCellValue("状态持续时间");
        //写入表头各个元素
        for (int i = 0; i < list.size(); i++) {
        //遍历数据插入到表中
            TbStaRecordsEntity entity = list.get(i);
            HSSFRow row1 = sheet.createRow(i + 1); //跳过第0行，为第n个对象创建第n+1行
            row1.createCell(0).setCellValue(entity.getRecordId());
            row1.createCell(1).setCellValue(entity.getDeviceId());
            switch (entity.getStatus()) {
                case 0:
                    row1.createCell(2).setCellValue("绿灯");
                    break;
                case 1:
                    row1.createCell(2).setCellValue("黄灯");
                    break;
                case 2:
                    row1.createCell(2).setCellValue("红灯");
                    break;
            }
            row1.createCell(3).setCellValue(sdf.format(entity.getStartTime()));
            row1.createCell(4).setCellValue(sdf.format(entity.getEndTime()));
            row1.createCell(5).setCellValue(entity.getTime());
            //插入数据
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();

        try {
            headers.setContentDispositionFormData("attachment", new String("设备历史状态记录表.xls"
                    .getBytes("UTF-8"), "ISO-8859-1")); //防止乱码

            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            excel.write(baos);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.OK);
    }

导出前端代码：

exportExcel () {
      //发送异步请求
      this.$http({
        url: this.$http.adornUrl(`/sys/staRecords/export/${this.deviceId}`),
        //将id传回后端，后端根据id查询相关数据
        mothed: 'post',
        responseType: 'blob'
        //设置返回数据格式
      }).then(res => {
        if (res.data.size > 0) {
          var blob = new Blob([res.data]);
          var href = URL.createObjectURL(blob);
          //生成url
          var downloadElement = document.createElement('a');
          downloadElement.href = href;
          downloadElement.download = `${this.deviceId}号设备历史状态记录表` + "." + 'xls'; // 下载后文件名
          document.body.appendChild(downloadElement);
          downloadElement.click(); // 点击下载
          document.body.removeChild(downloadElement); // 下载完成移除元素
          window.URL.revokeObjectURL(href); // 释放掉blob对象
        } else {
          this.$message.error('文件或图片失效请重新上传')
        }
      },
        this.$message({
          message: '操作成功',
          type: 'success',
          duration: 1500,
          onClose: () => {
            this.visible = false
          }
        })
      )
    },

完整导入功能：

 public static List<TbStaRecordsEntity> ImportExcel(MultipartFile file) {
        List<TbStaRecordsEntity> list = new ArrayList<>();
        TbStaRecordsEntity entity = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //设置时间格式
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            int numberOfSheets = workbook.getNumberOfSheets();
            //获取每个sheet对象
            for (int i = 0; i < numberOfSheets; i++){
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                int rows = sheetAt.getPhysicalNumberOfRows();
                //获取sheet对象下的每一行
                for(int j=0;j<rows;j++){
                    if(j==0){
                        continue;//跳过第一行表头
                    }
                    HSSFRow row = sheetAt.getRow(j);
                    if(row == null){
                        continue;//跳过空行
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    //获取每一行元素数量
                    entity = new TbStaRecordsEntity();
                    for(int k=0;k<cells;k++){
                        HSSFCell cell = row.getCell(k);
                        switch (k){
                        //按照对应列set对象属性
                            case 1:
                               entity.setDeviceId((int)cell.getNumericCellValue());
                                break;
                            case 2:
                                String status = cell.getStringCellValue();
                                if(status.equals("绿灯")){
                                    entity.setStatus(0);
                                }else if(status.equals("黄灯")){
                                    entity.setStatus(1);
                                }else {
                                    entity.setStatus(2);
                                }
                                break;
                            case 3:
                                entity.setStartTime(sdf.parse(cell.getStringCellValue()));
                                break;
                            case 4:
                                entity.setEndTime(sdf.parse(cell.getStringCellValue()));
                                break;
                            case 5:
                                entity.setTime(entity.getEndTime().getTime()-entity.getStartTime().getTime());
                                break;
                        }
                    }
                    list.add(entity);
                    //数据集合添加对象
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return list;
    }

导入前端代码：

前端显示代码：

<el-upload :action="url"
               accept=".xls"
               :limit="1"
               :auto-upload="false"
               :on-change="onUploadChange"
               :show-file-list="true">
      <el-button slot="trigger"
                 size="small"
                 type="primary">选取</el-button>
      <el-button style="margin-left: 10px;"
                 size="small"
                 type="success"
                 @click="submit()">上传</el-button>
    </el-upload>

前端js代码：

methods: {
    submit () {
      this.$http({
        url: this.$http.adornUrl('/sys/staRecords/import'),
        method: 'post',
        headers: { 'Content-Type': 'multipart/form-data' },
        data: this.formDatas,
      }).then(({ data }) => {
        if (data && data.code === 0) {
          this.$message({
            message: '操作成功',
            type: 'success',
            duration: 1500,
            onClose: () => {
              this.visible = false
            }
          })
        }
      })
    },
    onUploadChange (item) {
      this.formDatas.append('file', item.raw) //文件加入表单数据
    },
}