java操作poi导出excel

两个依赖版本保持一样就行。
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

导出

public XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
       XSSFCellStyle cellStyle = wb.createCellStyle();
       //水平居中
       cellStyle.setAlignment(HorizontalAlignment.CENTER);
       //垂直对齐
       cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
       //背景颜色
       cellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
       XSSFFont font = wb.createFont();
       //字体加粗
       font.setBold(true);
       // 设置字体类型
       font.setFontName("黑体");
       // 设置字体大小
       font.setFontHeightInPoints((short) 15);
       cellStyle.setFont(font);
       return cellStyle;
}

 private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
     XSSFCellStyle cellStyle = wb.createCellStyle();
     //垂直居中
     cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
     //水平居中
     cellStyle.setAlignment(HorizontalAlignment.CENTER);
     // 设置自动换行
     cellStyle.setWrapText(true);
     XSSFFont font = wb.createFont();
     cellStyle.setFont(font);
     return cellStyle;
 }
 
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Sheet1");
//设置大标题
XSSFRow row = sheet1.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("批量编辑");
cell.setCellStyle(createTitleCellStyle(wb));
// 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));
//设置标题
public void settingExcelTitle(XSSFSheet sheet, boolean flag, XSSFWorkbook wb){
        XSSFRow row = sheet.createRow(1);
        for (int i = 0; i <= 15(列数); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(createContentCellStyle(wb));
            switch (i) {
                case 0:
                    cell.setCellValue(flag ? "..." : "....");
                    break;
                 case 2:
                  XSSFCellStyle hssfCellStyleDate = wb.createCellStyle();
                     XSSFDataFormat df = wb.createDataFormat();// 单独设置日期格式
                     hssfCellStyleDate.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm:ss"));
                     cell.setCellStyle(hssfCellStyleDate);
                     cell.setCellValue(importSpotPlanIntentDataDto.getPostTime());
                     break;
                  default: break; 
            }
       }
}
//操作第二个sheet,和上面一样
XSSFSheet sheet2 = wb.createSheet("KOCSheet");

File wby = null;
try {
    wby = File.createTempFile("wby", ".xlsx", new File(importExcelTempPath));
} catch (Exception e) {
    log.error("创建临时文件IO流false:", e);
    throw new ServiceException(20012, "创建临时文件IO流false");
}
try (FileOutputStream fileOutputStream = new FileOutputStream(wby)) {
       wb.write(fileOutputStream);
   } catch (IOException e) {
       log.error("创建导入Excel文件日志false:", e);
       throw new ServiceException(20012, "创建导入Excel文件日志false");
   } finally {
       try {
           wb.close();
       } catch (IOException e) {
           e.printStackTrace();
       }
}

导入

 XSSFWorkbook wb = null;
 try {
     wb = new XSSFWorkbook(inputStream);
 } catch (Exception e) {
     e.printStackTrace();
     throw new ServiceException(20010, "excel解析失败");
 }
//sheet个数
int numberOfSheets = wb.getNumberOfSheets();
      XSSFSheet sheetAt = wb.getSheetAt(0);
      int rowNum = sheetAt.getLastRowNum();
      int k = 0;
      //从2开始,是因为前两行是标题
      for (int j = 2; j <= rowNum; j++) {
          XSSFRow row = sheetAt.getRow(j);
          int colNum = row.getLastCellNum();
          ImportDto res = new ImportDto();
          while (k < colNum) {
              settingExcelResolvingResult(res, row, k);
              k++;
          }
          //finalResult.add(res); 结果保存
          k = 0;
}

/**
 * 设置excel解析结果
 * @param row 行
 * @param k 列号
 */
public void settingExcelResolvingResult(ImportDto r, XSSFRow row, int k) {
      XSSFCell cell = row.getCell(k);
      switch (k) {
          case 0:
              if (cell != null) {
                  String str = cell.toString();
                  r.setBusinessCode(StringUtils.isNotBlank(str) ?  str : null);
              }
              break;
          case 1:
              if (cell != null) {
                  Date dateCellValue = cell.getDateCellValue();
                  if (dateCellValue != null) {
                      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                      r.setTime(sdf.format(dateCellValue));
                  } else {
                      r.setTime(null);
                  }
              }
              break;
          default:
              break;
      }
}

其它

1、EasyPOI 可以百度去看。
2、阿里巴巴EasyExcel。
3、myexcel

 <dependency>
     <groupId>com.github.liaochong</groupId>
     <artifactId>myexcel</artifactId>
     <version>3.11.4</version>
 </dependency>