apache poi 和EasyExcel 的使用

本文主要是介绍apache poi 和EasyExcel 的使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

apache poi
- 隐藏sheet做下拉列表
EasyExcel
- 超链接跳转sheet页
- 自定义类型转换
- 隐藏sheet做下拉列表

apache poi

官方文档：https://poi.apache.org/components/spreadsheet/index.html

隐藏sheet做下拉列表

XSSFSheet hiddenSheet = (XSSFSheet) wb.createSheet(hiddenSheetName);
Row hidRow0 = hiddenSheet.createRow(0);
wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true);

// 添加名称管理器
Name name = wb.createName();
//官方实例说可以不要，不能用时放开
//String nname = "name" + IdUtil.nextIdStr();
//name.setNameName(nname);
String indexToStr = excelColIndexToStr(1 + hidColIndex);
String formula = hiddenSheetName + "!$" + indexToStr + "$2:$" + indexToStr + "$" + (fieldInfo.getKeyIds().size() + 1);
//name.setRefersToFormula(formula);//创建数据验证助手
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(reportNameSheet);
//创建公式列表约束
DataValidationConstraint dataValidationConstraint = dvHelper.createFormulaListConstraint(formula);//应用下拉框的区域边界(行列范围)
CellRangeAddressList addressList = new CellRangeAddressList(2, 1000, repColIndex, repColIndex);
//下拉列表值验证
DataValidation dataValidation = dvHelper.createValidation(dataValidationConstraint, addressList);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("输入值有误", "请从下拉框选择");
reportNameSheet.addValidationData(dataValidation);

/*** 获取excel中的列标题** @param columnIndex 列序号* @return* @author lijiewei* @date 2021/4/16 10:38*/
private String excelColIndexToStr(int columnIndex) {if (columnIndex <= 0) {return null;}String columnStr = "";columnIndex--;do {if (columnStr.length() > 0) {columnIndex--;}columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;columnIndex = ((columnIndex - columnIndex % 26) / 26);} while (columnIndex > 0);return columnStr;
}

EasyExcel

官方文档：https://www.yuque.com/easyexcel/doc/read

超链接跳转sheet页

注册writeHandler写处理器

EasyExcel.writerSheet(0,"清单").registerWriteHandler(new InventoryCellWriteHandler()).head(Inventory.class).registerConverter(new LocalDateTimeConverter()).build();

InventoryCellWriteHandler 处理单元格，添加超链接

public class InventoryCellWriteHandler implements CellWriteHandler {...//在单元上的所有操作完成后调用@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 对“校验对象中文名称”列加超链接，跳转到对应sheet页if (!isHead && cell.getColumnIndex() == 0) {CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.DOCUMENT);int index = cell.getRowIndex() - 1;String cellValue = cell.getStringCellValue();//设置超链接，有下划线_，所以加'hyperlink.setAddress("#'"+index+"_"+cellValue+"'!A1");cell.setHyperlink(hyperlink);//设置连接样式Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();XSSFFont font = (XSSFFont) workbook.createFont();font.setColor(new XSSFColor(new java.awt.Color(0, 0, 204)));cellStyle.setFont(font);cell.setCellStyle(cellStyle);}}
}

自定义类型转换

EasyExcel.writerSheet(0,"清单").registerWriteHandler(new InventoryCellWriteHandler()).head(Inventory.class).registerConverter(new LocalDateTimeConverter()).build();

LocalDateTimeConverter 自定义的类型转换

public class LocalDateTimeConverter implements Converter<LocalDateTime> {@Overridepublic Class supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws ParseException {if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {return LocalDateTime.parse(cellData.getStringValue(),DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));} else {return LocalDateTime.parse(cellData.getStringValue(),DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat()));}}@Overridepublic CellData convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {return new CellData(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(value));} else {return new CellData(DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat()).format(value));}}
}

隐藏sheet做下拉列表

 //创建数据验证助手
DataValidationHelper helper = sheet.getDataValidationHelper();
String columnName = toExcelColumn(columnIndex);
//创建公式列表约束
DataValidationConstraint constraint = helper.createFormulaListConstraint(SHEETNAME_HIDDEN_DROPDOWN + "!$" + columnName + "$1:$" + columnName + "$" + values.length);
//应用下拉框的区域边界(行列范围)
CellRangeAddressList regions = new CellRangeAddressList(startRow, endRow, startColumn, endColumn);
DataValidation dataValidation = helper.createValidation(constraint, regions);
//设置验证错误显示
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(false);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);

//获取excel中的列标题
String toExcelColumn(int columnIndex) {String left = "";if (columnIndex >= 26) {left = toExcelColumn(columnIndex / 26 - 1);}columnIndex %= 26;return left + (char) (columnIndex + 65);
}