java 实现导出 java实现导出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;

	}
}