poi导出excel时带有超链接

川长思鸟来 2021-11-13 23:20 419阅读 0赞

两种方式,第一种适合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;
    
    	}
    }