poi导出excel时带有超链接-蒲公英云

两种方式,第一种适合xls,不过当数据量超出五百条后,超链接的样式会不起作用.
第二种比较适合xlsx
两种方式都会或多或少有问题,大家在用的时候需要根据自身情况进行选择和调整,

第一种方式

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
 * poi导出excel待超链接
 * 
 * @author zys2042
 *
 */
public class POIToExcelLink {
    public void toExcel() throws Exception {
        // params为运行本规则时所传入的参数
        // 获取数据
        List<Map> list = getDate();
        String fileName = "";
        String[] cellNameArr = { "姓名", "年龄", "超链接" };
        String[] cellValueArr = { "name", "age", "linkText", "linkAddress" };
        exportXls(list, fileName, cellNameArr, cellValueArr);
    }
    /**
     * 获取数据
     * 
     * @return
     */
    private List<Map> getDate() {
        // TODO Auto-generated method stub
        return null;
    }
    private String exportXls(List<Map> list, String filename, String[] cellNameArr, String[] cellValueArr) {
        if (list.size() == 0 || list.isEmpty()) {
            return "没有数据,请重新查询!";
        }
        // 生成excel
        // 生成excel文件的头 .xls
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet(filename);
        // 表头
        CellStyle styleHead = getColumnTopStyle(hssfWorkbook);
        CellStyle stylebody = getStyle(hssfWorkbook);
        // 列标题行
        HSSFRow headRow = sheet.createRow(0);
        return setExva(list, filename, cellNameArr, cellValueArr, sheet, styleHead, stylebody, headRow, hssfWorkbook);
    }
    private String setExva(List<Map> list, String filename, String[] cellNameArr, String[] cellValueArr, Sheet sheet,
            CellStyle styleHead, CellStyle stylebody, Row headRow, Workbook hssfWorkbook) {
        // 列名称
        for (int i = 0; i < cellNameArr.length; i++) {
            Cell cell1 = headRow.createCell(i);
            cell1.setCellValue(cellNameArr[i]);
            cell1.setCellStyle(styleHead);
            sheet.setColumnWidth(i, 20 * 256);
        }
        // 列值
        for (Map map : list) {
            Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            for (int i = 0; i < cellValueArr.length; i++) {
                Cell cell1 = dataRow.createCell(i);
                if (i == 2) {
                    // 超链接
                    cell1.setCellStyle(getLinkStyle(hssfWorkbook));
                    cell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    CreationHelper createHelper = hssfWorkbook.getCreationHelper();
                    Hyperlink hyperlink1 = createHelper.createHyperlink(Hyperlink.LINK_URL);
                    cell1.setHyperlink(hyperlink1);
                    hyperlink1.setAddress((String) map.get("linkAddress"));
                    cell1.setHyperlink(hyperlink1);// 链接
                    cell1.setCellValue((String) map.get(cellValueArr[i]));// 文本
                } else {
                    // 普通数据
                    cell1.setCellValue((String) map.get(cellValueArr[i]));
                    cell1.setCellStyle(stylebody);
                }
            }
        }
        return outToEx(filename, hssfWorkbook);
    }
    private String outToEx(String filename, Workbook hssfWorkbook) {
        // 第六步，将文件存到指定位置
        try {
            FileOutputStream fout = new FileOutputStream("文件父路径" + filename + ".xls");
            hssfWorkbook.write(fout);
            fout.close();
            // BeanCtx.out("导出成功");
            return "导出成功!";
        } catch (Exception e) {
            e.printStackTrace();
            return "导出失败!";
        }
    }
    public CellStyle getColumnTopStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // int length = str.getBytes().length;
        // sheet.setColumnWidth((short)1,(short)(length*256));
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    /*
     * 列数据信息单元格样式
     */
    public CellStyle getStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    public CellStyle getLinkStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        font.setColor(HSSFColor.BLUE.index);
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
}

第二种方式

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * poi导出excel待超链接
 * 
 * @author zys2042
 *
 */
public class POIToExcelLink2 {
    public void toExcel() throws Exception {
        // params为运行本规则时所传入的参数
        // 获取数据
        List<Map> list = getDate();
        String fileName = "";
        String[] cellNameArr = { "姓名", "年龄", "超链接" };
        String[] cellValueArr = { "name", "age", "linkText", "linkAddress" };
        exportXls(list, fileName, cellNameArr, cellValueArr);
    }
    /**
     * 获取数据
     * 
     * @return
     */
    private List<Map> getDate() {
        // TODO Auto-generated method stub
        return null;
    }
    private String exportXls(List<Map> list, String filename, String[] cellNameArr, String[] cellValueArr) {
        if (list.size() == 0 || list.isEmpty()) {
            return "没有数据,请重新查询!";
        }
        // 生成excel
        // 生成excel文件的头 .xlsx
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
        Sheet sheet = hssfWorkbook.createSheet(filename);
        // 表头
        CellStyle styleHead = getColumnTopStyle(hssfWorkbook);
        CellStyle stylebody = getStyle(hssfWorkbook);
        // 列标题行
        Row headRow = sheet.createRow(0);
        return setExva(list, filename, cellNameArr, cellValueArr, sheet, styleHead, stylebody, headRow, hssfWorkbook);
    }
    private String setExva(List<Map> list, String filename, String[] cellNameArr, String[] cellValueArr, Sheet sheet,
            CellStyle styleHead, CellStyle stylebody, Row headRow, Workbook hssfWorkbook) {
        // 列名称
        for (int i = 0; i < cellNameArr.length; i++) {
            Cell cell1 = headRow.createCell(i);
            cell1.setCellValue(cellNameArr[i]);
            cell1.setCellStyle(styleHead);
            sheet.setColumnWidth(i, 20 * 256);
        }
        CellStyle linkStyle= getLinkStyle(hssfWorkbook);
        // 列值
        for (Map map : list) {
            Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            for (int i = 0; i < cellValueArr.length; i++) {
                Cell cell1 = dataRow.createCell(i);
                if (i == 2) {
                    // 超链接
                    cell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    cell1.setCellFormula("HYPERLINK(\"" + (String) map.get("linkAddress")+ "\",\"" + (String) map.get(cellValueArr[i])+ "\")");
                    cell1.setCellStyle(linkStyle);
                } else {
                    // 普通数据
                    cell1.setCellValue((String) map.get(cellValueArr[i]));
                    cell1.setCellStyle(stylebody);
                }
            }
        }
        return outToEx(filename, hssfWorkbook);
    }
    private String outToEx(String filename, Workbook hssfWorkbook) {
        // 第六步，将文件存到指定位置
        try {
            FileOutputStream fout = new FileOutputStream("文件父路径" + filename + ".xlsx");
            hssfWorkbook.write(fout);
            fout.close();
            // BeanCtx.out("导出成功");
            return "导出成功!";
        } catch (Exception e) {
            e.printStackTrace();
            return "导出失败!";
        }
    }
    public CellStyle getColumnTopStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // int length = str.getBytes().length;
        // sheet.setColumnWidth((short)1,(short)(length*256));
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    /*
     * 列数据信息单元格样式
     */
    public CellStyle getStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    public CellStyle getLinkStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        font.setColor(HSSFColor.BLUE.index);
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色;
        // style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色;
        // style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
        // style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
        // style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
}