java采坑之路-导出excel超链接

使用Apache-poi操作Excel

• xls格式----HSSF

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("添加超链接");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
link.setAddress("https://github.com/550690513");
cell.setHyperlink(link);// 设置超链接
cell.setCellValue("Fork me on Github");

• xlsx格式----HSSF

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
// 使用creationHelpper来创建XSSFHyperlink对象
CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink  link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("https://github.com/550690513");
cell.setHyperlink(link);
cell.setCellValue("Fork me on Github");

运行效果展示：

效果展示
使用easyExcel增加超链接，easyExcel提供了com.alibaba.excel.write.handler包，提供了对单元格，行，sheet和workbook的接口供特殊情形下扩展。

/**
 * @author jack
 * @description easyExcel自定义处理器
 * @date 2020/6/8 22:03
 */
public class CustomerRowWriteHandler implements RowWriteHandler {
    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean isHead) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {
        if(!isHead){
            //赊销状态
            if(StringUtils.isBlank(row.getCell(4).getStringCellValue())){
                row.getCell(4).setCellValue("失效");
            }
            //进行中的赊销流程
            row.getCell(6).setCellType(CellType.STRING);
            if(StringUtils.compare(row.getCell(6).getStringCellValue(),"0")>0){
                row.getCell(6).setCellValue("有");
            }else {
                row.getCell(6).setCellValue("无");
            }
            //设置超链接
            CreationHelper creationHelper = getWorkbook(writeSheetHolder).getCreationHelper();
            Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress(row.getCell(12).getStringCellValue());
            row.getCell(11).setHyperlink(hyperlink);
            row.getCell(11).setCellStyle(getLinkStyle(writeSheetHolder));
        }
        //移除第12列
        row.getCell(12).setCellValue(row.getCell(13).getStringCellValue());
        row.removeCell(row.getCell(13));
    }

    private Workbook getWorkbook(WriteSheetHolder writeSheetHolder) {
        return writeSheetHolder.getSheet().getWorkbook();
    }

    /**
     * 设置超链接风格
     * @param writeSheetHolder
     * @return
     */
    private CellStyle getLinkStyle(WriteSheetHolder writeSheetHolder){
        Workbook workbook = getWorkbook(writeSheetHolder);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        Font font = workbook.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 12);
        font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        font.setUnderline(Font.U_SINGLE);
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setFont(font);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
}

public class ExportUtil {

    public static <T> String exportExcel(String savePath, String title, List<T> list, Class cls) {
        File file = new File(savePath + File.separator + title + ExcelTypeEnum.XLS.getValue());
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }

        EasyExcel.write(file, cls)
                .registerWriteHandler(getHorizontalCellStyleStrategy())
                .registerWriteHandler(new CustomerRowWriteHandler())
                .sheet("Sheet0").doWrite(list);
        return file.getAbsolutePath();
    }

    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //文字
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}