apache poi 和EasyExcel 的使用

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 {
	...
	
	//在单元上的所有操作完成后调用
	@Override
	public 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> {
    @Override
    public Class supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public 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()));
        }
    }

    @Override
    public 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);
}