java 解析poi导包 java poi导入

转载

mob6454cc6b8546 2023-08-01 18:13:48

文章标签 java 解析poi导包 excel java poi 数据 文章分类 Java 后端开发

首先导入依赖

<dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.0.3</version>
  </dependency>
  <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.0.3</version> </dependency>
  <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.0.3</version>
  </dependency>

通过文件的方式

public String importExcelDate(@RequestParam("file") MultipartFile file) {
  try {
      XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());//导入.xlxs
      //HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());//导入.xls
      //获取第一个sheet表，下标是从0开始的
      XSSFSheet sheet = xwb.getSheetAt(0);
      int sheetNum = xwb.getNumberOfSheets();//获取总共有几个sheet表
      List<ShelterVo> list = new ArrayList<>();
      //int rowNum = sheet.getPhysicalNumberOfRows();//获取有数据的行数 ，如果有数据的是第n行，前面m行没有数据，则返回n-m.
      int rowNum = sheet.getLastRowNum();   //获取excel最后有数据的一行为n，返回n-1.返回数据行的下标 从0开始
      for (int i = 1; i <= rowNum; i++) {
          ShelterVo vo = new ShelterVo();
          XSSFRow row = sheet.getRow(i); //获取excel第i行，下标从0开始,第一行的下标为0
          //XSSFCell cell = row.getCell(0);//获取第i行的第一个单元格
          if (row.getCell(0) != null) {
              row.getCell(0).setCellType(CellType.STRING);//设置第i行第一个单元格数据为String类型
              String shelterName = row.getCell(0).getStringCellValue(); //获取第一个单元格的值
              vo.setShelterName(shelterName);
          }
          if (row.getCell(1) != null) {
              row.getCell(1).setCellType(CellType.STRING);
              String shelterAddress = row.getCell(1).getStringCellValue();  //获取String类型的值
              vo.setShelterAddress(shelterAddress);
          }
          if (row.getCell(3) != null) {
              //对于想要获取日期的值，可以把单元格设置为日期类型，但是如果直接设置为日期类型，表格中的数据不是日期类型，则会导致数据变为1970年或者其他不准确的年份
              //先判断数据是否为日期类型
              int type = row.getCell(3).getCellType();
              //1为公式类型
              if (type != 1) {
                  boolean isDate = DateUtil.isCellDateFormatted(row.getCell(3));
                  if (isDate) {
                      row.getCell(3).setCellType(CellType.FORMULA);
                      
                      Date setDate = row.getCell(3).getDateCellValue(); //获取日期类型的值
                      vo.setSetTime(setDate);
                  }
              }
          }
          if (row.getCell(4) != null) {
              row.getCell(4).setCellType(CellType.NUMERIC);
             
              double capacity = row.getCell(4).getNumericCellValue();  //获取数值类型的值
              vo.setCapacity((int) capacity);
          }
          list.add(vo);
      }
     
      return "成功";
  } catch (IOException e) {
      e.printStackTrace();
      return e.toString();
  } catch (IllegalStateException e) {
      e.printStackTrace();
      return e.toString();
  } catch (Exception e) {
      e.printStackTrace();
      return e.toString();
  }

}

通过Url的方式

/**
* 读取数据Excel中的数据
*
* @param required
* @return
*/
public String importExcelDate(ExcelRequired required) {
   try {
       InputStream stream = null;
       stream = ExcelUploadUtil.getInputStreamByUrl(required.getUrl());
       int success = 0;
       int fail = 0;
       XSSFWorkbook xwb = null; // 导入.xlxs
       xwb = new XSSFWorkbook(stream);
       // 获取第一个sheet表，下标是从0开始的
       XSSFSheet sheet = xwb.getSheetAt(0);
       int sheetNum = xwb.getNumberOfSheets(); // 获取总共有几个sheet表
       int rowNum = sheet.getLastRowNum();   // 获取excel最后有数据的一行为n，返回n-1.返回数据行的下标 从0开始
       for (int i = 2; i <= rowNum; i++) {
           XSSFRow row = sheet.getRow(i); // 获取excel第i行，下标从0 开始, 第一行的下标为0
           if (row != null) {
               if (row.getCell(0) != null) {
                   row.getCell(0).setCellType(CellType.STRING);
                   String wmId = row.getCell(0).getStringCellValue();//获取字符串类型的值
                   if (StringUtils.isNotBlank(wmId)) {
                       // 给用户绑定角色
                       boolean permission = setPermission(wmId, required.getActId());
                       if (permission) {
                           success++;
                           continue; // 手机号或 wmId，如果两个都有值则以 wmId 为准 ！
                       } else {
                           fail++;
                       }
                   }
               }
               if (row.getCell(1) != null) {
                   row.getCell(1).setCellType(CellType.STRING);
                   String phone = row.getCell(1).getStringCellValue();//获取字符串类型的值
                   if (StringUtils.isNotBlank(phone)) {
                       // 给用户绑定角色
                       boolean permission = setPermission(phone, required.getActId());
                       if (permission) {
                           success++;
                       } else {
                           fail++;
                       }
                   }
               }
           }
       }
       return "成功: " + success + " 条记录，失败：" + fail + " 条记录！";
   }catch (Exception e) {
       e.printStackTrace();
       return e.toString();
   }
}

ExcelRequired

/**
 * 读取 Excel请求参数
 * Created by Lance on 2020/11/19 10:26
 */
@Data
@ApiModel("读取 Excel请求参数")
public class ExcelRequired {

    @ApiModelProperty(value = "Excel的url地址")
    private String url;
    @ApiModelProperty(value = "活动ID")
    private String actId;
}

ExcelUploadUtil 工具类

public class ExcelUploadUtil {
/**
 * 导出文件时为Writer生成OutputStream
 *
 * @param fileName 文件名
 * @param response HttpServletResponse
 * @return 返回
 */
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    try {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    } catch (IOException e) {
        throw new Exception("导出excel表格失败!", e);
    }
}

public static String getFileName() {
    return new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
}

public static void setExcelHeader(HttpServletResponse response, Workbook workbook, String filename) {

    try {
        OutputStream out = response.getOutputStream();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(out);
        String fileNameStr = filename + format.format(new Date()) + ".xlsx";
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileNameStr, "utf-8"));
        bufferedOutPut.flush();
        workbook.write(bufferedOutPut);
        bufferedOutPut.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

public static OutputStream setResponseHeader(HttpServletResponse response, String fileName) throws Exception {
    try {
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + format.format(new Date()) + ".xlsx");
        return response.getOutputStream();
    } catch (Exception ex) {
        // 重置responsex
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = new HashMap<String, String>();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + ex.getMessage());
        response.getWriter().println(JSONObject.toJSONString(map));
        throw new BusinessException("获取文件流信息失败");
    }
}


public static InputStream getInputStreamByUrl(String urlPath) {
    InputStream inputStream = null;
    HttpURLConnection httpURLConnection = null;
    try {
        URL url = new URL(urlPath);
        httpURLConnection = (HttpURLConnection) url.openConnection();
        httpURLConnection.setConnectTimeout(3000);
        httpURLConnection.setDoInput(true);
        httpURLConnection.setRequestMethod("GET");
        int responseCode = httpURLConnection.getResponseCode();
        if (responseCode == 200) {
            inputStream = httpURLConnection.getInputStream();
        } else {
            throw new BusinessException("文件下载失败");
        }
    } catch (Exception e) {
        throw new BusinessException("文件下载失败");
    }
    return inputStream;
}


public static void setExcelOctetHeader(HttpServletResponse response, Workbook workbook, String filename) {

    try {
        OutputStream out = response.getOutputStream();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(out);
        String fileNameStr = filename + format.format(new Date()) + ".xlsx";
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileNameStr, "utf-8"));
        bufferedOutPut.flush();
        workbook.write(bufferedOutPut);
        bufferedOutPut.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

获取值和设置类型时需要注意的点

获取excel时下标是从0开始的

//获取第一个sheet表
XSSFSheet sheet = xwb.getSheetAt(0);
//获取第一行
XSSFRow row = sheet.getRow(0);
//获取第一个单元格
XSSFCell cell = row.getCell(0);

给单元格设置数据类型时先判断数据是否存在，单元格没有值，但是任然设置数据类型会报空指针

//设置第一个单元格数据为String类型
if (row.getCell(0) != null) {
   row.getCell(0).setCellType(CellType.STRING);
                 
 }
 //设置第三个单元格为日期类型
 if (row.getCell(3) != null) {
     //先判断数据是否为日期类型
     int type = row.getCell(3).getCellType();
     //1为公式类型
     if (type != 1) {
         boolean isDate = DateUtil.isCellDateFormatted(row.getCell(3));
         if (isDate) {
         row.getCell(3).setCellType(CellType.FORMULA);//设置第三个单元格为日期类型
         }
     }
}
//设置第四个单元格为数值类型
if (row.getCell(4) != null) {
   row.getCell(4).setCellType(CellType.NUMERIC);        
}

对于想要获取日期的值，可以把单元格设置为日期类型，但是如果直接设置为日期类型，表格中的数据不是日期类型，则会导致数据变为1970年或者其他不准确的年份

//获取String类型的值
String shelterName = row.getCell(0).getStringCellValue();
//获取日期类型的值
 Date setDate = row.getCell(3).getDateCellValue();
 //获取数值类型的值
double capacity = row.getCell(4).getNumericCellValue();

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。