文章目录
- apache poi
-
- 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 indexToStr = excelColIndexToStr(1 + hidColIndex);
String formula = hiddenSheetName + "!$" + indexToStr + "$2:$" + indexToStr + "$" + (fieldInfo.getKeyIds().size() + 1);
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);
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) {
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);
String toExcelColumn(int columnIndex) {
String left = "";
if (columnIndex >= 26) {
left = toExcelColumn(columnIndex / 26 - 1);
}
columnIndex %= 26;
return left + (char) (columnIndex + 65);
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)