两个依赖版本保持一样就行。
<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>