Java后端Excel文件的上传读取和导出文件（个人记录）

一，Excel上传解析读取

POI依赖

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

HSSFWorkbook对应 .xls后缀文件
XSSFWorkbook对应.xlsx后缀文件
从文件服务器读取。

/**
 * @author left
 */
public List<Object> readENExcel(String path) {
    // 输入流对象
    InputStream is = null;
    // 单元格，最终按字符串处理
    String cellStr = null;
    //进行数据筛选
    List<Object> resList = new ArrayList<>();
    Object enRes = null;
    // 服务器返回的状态
    int HttpResult;
    try {
      // 创建URL
      URL url = new URL(path);
      // 试图连接并取得返回状态码
      URLConnection urlconn = url.openConnection();
      urlconn.connect();
      HttpURLConnection httpconn = (HttpURLConnection) urlconn;
      HttpResult = httpconn.getResponseCode();
      if (HttpResult != HttpURLConnection.HTTP_OK) {
        log.info("无法连接！");
      } else {
        is = urlconn.getInputStream();
        // 创建Excel2007文件对象
        XSSFWorkbook workbook2007 = new XSSFWorkbook(is);
        // 开始循环遍历行，表头不处理，从1开始
        for (int s = 0; s < workbook2007.getNumberOfSheets(); s++) {
          //读取第二个单元格
          if(s == 0){
            continue;
          }
          //初始数据存储
          List<Object> list = new ArrayList<>();
          // 取出工作表，索引0开始
          XSSFSheet sheet = workbook2007.getSheetAt(s);
          for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            enRes = new Object();
            // 获取行对象
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
              break;
            }
            // row如果不为空，循环遍历单元格
            for (int j = 0; j < row.getLastCellNum(); j++) {
              XSSFCell cell = row.getCell(j);
              if (cell == null) {
                cellStr = "";
              } else if (CellType.BOOLEAN.equals(cell.getCellTypeEnum())) {
                cellStr = String.valueOf(cell.getBooleanCellValue());
              } else if (CellType.NUMERIC.equals(cell.getCellTypeEnum())) {
                cellStr = cell.getNumericCellValue() + "";
              } else {// 其余按照字符串处理
                cellStr = cell.getStringCellValue();
              }
              // 下面按照数据出现位置封装到对象中
              if (j == 0) {
              //TODO
                continue;
              }
              if (j == 1) {
               //TODO
                continue;
              }
              if (j == 2) {
               //TODO
                continue;
              }
              if (j == 3) {
                //TODO
                continue;
              }
             ...
            }
            resList.add(enRes);
          }
        }
      }
    } catch (Exception e) {
      Log.error(e.toString());
      e.printStackTrace();
    } finally {// 关闭文件流
      try {
        if (is != null) {
          is.close();
        }
      } catch (Exception e2) {
        e2.printStackTrace();
      }
    }
    return resList;
    }

二，文件的导出

/**
 * @author left
 */
@Component
public class ExportExcelUtil {

  //创建excel
  public XSSFWorkbook createExcel(String sheetName,
                                  List<String> title,
                                  List data) throws Exception {

    //创建HSSFWorkbook对象 .xls
    //创建XSSFWorkbook对象 .xlsx
    XSSFWorkbook wb = new XSSFWorkbook();
    //创建sheet对象
    XSSFSheet sheet = wb.createSheet(sheetName);
    //在sheet里创建第一行，这里即是表头
    XSSFRow rowTitle = sheet.createRow(0);
    //写入表头的每一个列
    for (int i = 0; i < titleList.size(); i++) {
      //创建单元格
      rowTitle.createCell(i).setCellValue(titleList.get(i));
    }
    //写入每一行的记录
    for (int i = 0; i < dataList.size(); i++) {
      //创建新的一行，递增
      XSSFRow rowData = sheet.createRow(i + 1);
      //通过反射，获取POJO对象
      Class cl = dataList.get(i).getClass();
      //获取类的所有字段
      Field[] fields = cl.getDeclaredFields();
      for (int j = 0; j < fields.length; j++) {
        //设置字段可见，否则会报错，禁止访问
        fields[j].setAccessible(true);
        //创建单元格
        rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
      }
    }
    return wb;
  }
}

实例：

/**
 * @author left
 */
@RestController
public class ExcelController {

  @Autowired
  private ExportExcelUtil exportExcelUtil;

  @GetMapping(value = "/out")
  public String outExcel(HttpServletResponse response) throws Exception {
    String fileName = "文件名称";
    String sheetName = "sheet名称";
    //表头集合
    List<String> title = new ArrayList<>();
    title.add("xxx");
    title.add("xxx");
    title.add("xxx");
    ...
    //数据对象 Object含有啊a b c三个属性
    Object o = new Object ();
    Object .setA("xxx");
    Object .setB("xxx");
    Object .setC("xxx");
    ...
    //将两个对象加入到集合中
    List<Object > dataList = new ArrayList<>();
    dataList .add(o);

    //创建文件
    XSSFWorkbook workbook = exportExcelUtil.createExcel(sheetName, title, dataList);

    //输出
    OutputStream output = response.getOutputStream();
    response.reset();
    //中文名称要进行编码处理
    response.setCharacterEncoding("UTF-8");
      fileName = URLEncoder.encode(fileName,"UTF-8");
      response.setHeader("Content-disposition", "attachment; filename="+ fileName +".xlsx");
      response.setContentType("application/vnd.ms-excel");
    workbook.write(output);
    output.close();
    return null;
  }
}