本文分享了如何使用Java的POI库导出带有复杂表头的Excel文件，包括设置多行多列合并的表头，以及在JSP页面中导出Excel的方法。示例代码详细展示了数据的组织、样式设置和Excel文件的生成过程。
分享一个关于java使用poi导出Excel的方法和使用jsp页面导出的方法：
/*
*
* 导出数据
*
* */
public void exportData(String organId, String monthId, String organCode, String dataChoose, boolean flag, HttpServletResponse response) {
String title = "电力销售预算管控表";
ExportExcelUtil_SalesBudgetTable<SalesBudgetBean> excel = new ExportExcelUtil_SalesBudgetTable<SalesBudgetBean>();
//先从数据库查到要导出的数据，将其存在list中
List<SalesBudgetBean> dataList = getDataList(monthId, organId, organCode, dataChoose);
String[] fields = {"item_name", "item_id", "eq_year_number", "eq_budget_number", "eq_budget_finish_rate",
"eq_lastyear_number", "eq_year_on_year", "eq_year_on_year_rate", "eq_predictive_value", "eq_prodictive_budget_balance",
"eq_prodictive_yearnum_balance", "ep_year_number", "ep_budget_number", "ep_budget_finish_rate",
"ep_lastyear_number", "ep_year_on_year", "ep_year_on_year_rate", "ep_predictive_value", "ep_prodictive_budget_balance",
"ep_prodictive_yearnum_balance", "ec_year_number", "ec_budget_number", "ec_budget_finish_rate", "ec_lastyear_number",
"ec_year_on_year", "ec_year_on_year_rate", "ec_predictive_value", "ec_prodictive_budget_balance", "ec_prodictive_yearnum_balance"};
//调用方法，开始导出
excel.exportExcel(title, fields, dataList, organId, monthId, response);
}
/*
*先分享使用POI导出Excel的方法
*/
//先分享一个导出复杂表头的类，先看截图，再看代码
public class ExportExcelUtil_SalesBudgetTable<T> {
private static final Logger LOGGER = Logger.getLogger(ExportExcelUtil_SalesBudgetTable.class);
public void exportExcel(ExportExcelUtil_SalesBudgetTable<T> exp, String title, String[] fields,
List<T> dataList, String organId, String monthId, HttpServletResponse response) {
exp.exportExcel(title,fields, dataList, organId, monthId, response);
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param fields 表格属性列名
* @param dataList 需要显示的数据集合,集合中一定要放置符合javaBean风格的类的对象
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public void exportExcel(String title, String[] fields,
List<T> dataList, String organId, String monthId, HttpServletResponse response) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 18);
// 生成一个样式
Map<String, CellStyle> styles = createStyles(workbook);
//产生表格标题行
//XSSFRow row = sheet.createRow(0);
String[] title_twohang_specail_pro={"项目", "行号"};
//说明是项目进度，两行表头
XSSFRow row0 = sheet.createRow(0); //第一行
XSSFRow row1 = sheet.createRow(1);
//循环2次获取两行一列的表头
for(int j=0;j<2;j++){
sheet.addMergedRegion(new CellRangeAddress(0,1,(short)j,(short)j));
XSSFCell ce=row0.createCell((short)j);
ce.setCellStyle(styles.get("header"));
//表格的第一行第一列显示的数据
XSSFRichTextString text = new XSSFRichTextString(title_twohang_specail_pro[j]);
ce.setCellValue(text);
}
sheet.addMergedRegion(new CellRangeAddress(0,0,(short)(2),(short)(10)));
XSSFCell cell0 = row0.createCell((short) (2) );
XSSFRichTextString text = new XSSFRichTextString("售电量");
cell0.setCellValue(text);
cell0.setCellStyle(styles.get("header")); //样式
XSSFCell cell0_1 = row1.createCell((short) (2));
XSSFCell cell0_2 = row1.createCell((short) (3));
XSSFCell cell0_3 = row1.createCell((short) (4));
XSSFCell cell0_4 = row1.createCell((short) (5));
XSSFCell cell0_5 = row1.createCell((short) (6));
XSSFCell cell0_6 = row1.createCell((short) (7));
XSSFCell cell0_7 = row1.createCell((short) (8));
XSSFCell cell0_8 = row1.createCell((short) (9));
XSSFCell cell0_9 = row1.createCell((short) (10));
cell0_1.setCellValue("本年数");
cell0_1.setCellStyle(styles.get("header")); //样式
cell0_2.setCellValue("预算数");
cell0_2.setCellStyle(styles.get("header")); //样式
cell0_3.setCellValue("预算完成率");
cell0_3.setCellStyle(styles.get("header")); //样式
cell0_4.setCellValue("上年同期");
cell0_4.setCellStyle(styles.get("header")); //样式
cell0_5.setCellValue("同比增减额");
cell0_5.setCellStyle(styles.get("header")); //样式
cell0_6.setCellValue("同比增减率");
cell0_6.setCellStyle(styles.get("header")); //样式
cell0_7.setCellValue("预测数");
cell0_7.setCellStyle(styles.get("header")); //样式
cell0_8.setCellValue("预测数与预算数差额");
cell0_8.setCellStyle(styles.get("header")); //样式
cell0_9.setCellValue("预测数与本年数差额");
cell0_9.setCellStyle(styles.get("header")); //样式
sheet.addMergedRegion(new CellRangeAddress(0,0,(short)(11),(short)(19)));
XSSFCell cell2 = row0.createCell((short) (11) );
XSSFRichTextString text2 = new XSSFRichTextString("售电价");
cell2.setCellValue(text2);
cell2.setCellStyle(styles.get("header")); //样式
XSSFCell cell2_1 = row1.createCell((short) (11));
XSSFCell cell2_2 = row1.createCell((short) (12));
XSSFCell cell2_3 = row1.createCell((short) (13));
XSSFCell cell2_4 = row1.createCell((short) (14));
XSSFCell cell2_5 = row1.createCell((short) (15));
XSSFCell cell2_6 = row1.createCell((short) (16));
XSSFCell cell2_7 = row1.createCell((short) (17));
XSSFCell cell2_8 = row1.createCell((short) (18));
XSSFCell cell2_9 = row1.createCell((short) (19));
cell2_1.setCellValue("本年数");
cell2_1.setCellStyle(styles.get("header")); //样式
cell2_2.setCellValue("预算数");
cell2_2.setCellStyle(styles.get("header")); //样式
cell2_3.setCellValue("预算完成率");
cell2_3.setCellStyle(styles.get("header")); //样式
cell2_4.setCellValue("上年同期");
cell2_4.setCellStyle(styles.get("header")); //样式
cell2_5.setCellValue("同比增减额");
cell2_5.setCellStyle(styles.get("header")); //样式
cell2_6.setCellValue("同比增减率");
cell2_6.setCellStyle(styles.get("header")); //样式
cell2_7.setCellValue("预测数");
cell2_7.setCellStyle(styles.get("header")); //样式
cell2_8.setCellValue("预测数与预算数差额");
cell2_8.setCellStyle(styles.get("header")); //样式
cell2_9.setCellValue("预测数与本年数差额");
cell2_9.setCellStyle(styles.get("header")); //样式
sheet.addMergedRegion(new CellRangeAddress(0,0,(short)(20),(short)(28)));
XSSFCell cell3 = row0.createCell((short) (20));
XSSFRichTextString text3 = new XSSFRichTextString("售电费");
cell3.setCellValue(text3);
cell3.setCellStyle(styles.get("header")); //样式
XSSFCell cell3_1 = row1.createCell((short) (20));
XSSFCell cell3_2 = row1.createCell((short) (21));
XSSFCell cell3_3 = row1.createCell((short) (22));
XSSFCell cell3_4 = row1.createCell((short) (23));
XSSFCell cell3_5 = row1.createCell((short) (24));
XSSFCell cell3_6 = row1.createCell((short) (25));
XSSFCell cell3_7 = row1.createCell((short) (26));
XSSFCell cell3_8 = row1.createCell((short) (27));
XSSFCell cell3_9 = row1.createCell((short) (28));
cell3_1.setCellValue("本年数");
cell3_1.setCellStyle(styles.get("header")); //样式
cell3_2.setCellValue("预算数");
cell3_2.setCellStyle(styles.get("header")); //样式
cell3_3.setCellValue("预算完成率");
cell3_3.setCellStyle(styles.get("header")); //样式
cell3_4.setCellValue("上年同期");
cell3_4.setCellStyle(styles.get("header")); //样式
cell3_5.setCellValue("同比增减额");
cell3_5.setCellStyle(styles.get("header")); //样式
cell3_6.setCellValue("同比增减率");
cell3_6.setCellStyle(styles.get("header")); //样式
cell3_7.setCellValue("预测数");
cell3_7.setCellStyle(styles.get("header")); //样式
cell3_8.setCellValue("预测数与预算数差额");
cell3_8.setCellStyle(styles.get("header")); //样式
cell3_9.setCellValue("预测数与本年数差额");
cell3_9.setCellStyle(styles.get("header")); //样式
//遍历集合数据，产生数据行
Iterator<T> it = dataList.iterator();
int index = 1;
while (it.hasNext()) {
index++;
XSSFRow row = sheet.createRow(index);
T t = (T) it.next();
//利用反射，根据javaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(styles.get("normalCell"));
String fieldName = fields[i];
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
//判断值的类型后进行强制类型转换
XSSFRichTextString textValue = null;
if (value != null && i == 1 && value.toString().matches("^-?\\d+$")) {
value = Integer.parseInt(value.toString());
}
if (value != null && i != 0 && i != 1 && i != 4 && i != 7 && i != 13 && i != 16 && i != 22 && i != 25) {
value = Double.parseDouble(value.toString());
}
if (value != null && (i == 4 || i == 7 || i == 13 || i == 16 || i == 22 || i == 25)) {
value = Float.parseFloat(value.toString());
}
sheet.setColumnWidth(0,20*2*256); //手动设置第一列列宽。
sheet.setColumnWidth(1,3*2*256); //手动设置第二列列宽。
if (value instanceof Integer) {
cell.setCellStyle(styles.get("CountCell"));
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
cell.setCellStyle(styles.get("percentCell"));
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
cell.setCellStyle(styles.get("numCell"));
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
cell.setCellStyle(styles.get("numCell"));
long longValue = (Long) value;
cell.setCellValue(longValue);
} else{
//其它数据类型都当作字符串简单处理
textValue = value!=null ? new XSSFRichTextString(value.toString()) : null;
cell.setCellValue(textValue);
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
try {
OutputStream out = response.getOutputStream();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
String fileName = title + df.format(new Date()) + ".xlsx";
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
response.setHeader("Pragma", "No-cache");// 设置头
// response.setHeader("Cache-Control", "no-cache");// 设置头
response.setHeader("Cache-Control", "max-age=-1");// 设置头
response.setDateHeader("Expires", 0);// 设置日期头
response.setContentType("application/msexcel;charset=UTF-8");// 设置类型
workbook.write(out);
out.flush();
out.close();
LOGGER.info("*******文件导出成功********");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
styles.put("title", makeCellStyle(wb, "title", CellStyle.ALIGN_CENTER, true, false));
styles.put("header", makeCellStyle(wb, "header", CellStyle.ALIGN_CENTER, true, true));
styles.put("normalCell", makeCellStyle(wb, "normalCell", CellStyle.ALIGN_LEFT, false, true));
styles.put("numCell", makeCellStyle(wb, "numCell", CellStyle.ALIGN_RIGHT, false, true));
styles.put("percentCell", makeCellStyle(wb, "percentCell", CellStyle.ALIGN_RIGHT, false, true));
styles.put("CountCell", makeCellStyle(wb, "CountCell", CellStyle.ALIGN_CENTER, false, true));
return styles;
}
private static CellStyle makeCellStyle(Workbook wb, String textType, short align, boolean boldWeight, boolean border) {
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 10);
if ("title".equals(textType)) {// 主标题
font.setFontHeightInPoints((short) 18);
}else if ("head".equals(textType)) {// 主标题
font.setFontHeightInPoints((short) 12);
}else if ("numCell".equals(textType)) {
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0.00"));
}else if ("percentCell".equals(textType)){
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0.00%"));
} else if ("CountCell".equals(textType)){
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0"));
}
if (boldWeight) {
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
//
// if (border) {
// cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
// cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
// cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
// cellStyle.setBorderBottom(CellStyle.BORDER_THIN);// 下边框
// }
cellStyle.setFont(font);
cellStyle.setAlignment(align);// 左右居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setWrapText(true);// 自动换行
return cellStyle;
}
}
//再分享一个导出简单表头的类，先看截图，再看代码
public class ExportExcelUtil<T> {
private static final Logger LOGGER = Logger.getLogger(ExportExcelUtil.class);
public void exportExcel(ExportExcelUtil<T> exp, String title, String[] headers, String[] fields,
Collection<T> dataset, HttpServletResponse response) {
exp.exportExcel(title, headers, fields, dataset, "yyyy-MM-dd", response);
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param fields
* 表格属性列名
* @param dataset
* 需要显示的数据集合,集合中一定要放置符合javaBean风格的类的对象
* @param out
* 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
* 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd"
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public void exportExcel(String title, String[] headers, String[] fields, Collection<T> dataset, String pattern,
HttpServletResponse response) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 18);
// 生成一个样式
Map<String, CellStyle> styles = createStyles(workbook);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(styles.get("header"));
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(styles.get("normalCell"));
String fieldName = fields[i];
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
XSSFRichTextString textValue = null;
if (value instanceof Integer) {
cell.setCellStyle(styles.get("CountCell"));
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
cell.setCellStyle(styles.get("percentCell"));
float fValue = (Float) value/100;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
cell.setCellStyle(styles.get("numCell"));
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
cell.setCellStyle(styles.get("numCell"));
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = new XSSFRichTextString(sdf.format(date));
cell.setCellValue(textValue);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value != null ? new XSSFRichTextString(value.toString()) : null;
cell.setCellValue(textValue);
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
try {
OutputStream out = response.getOutputStream();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
String fileName = title + df.format(new Date()) + ".xlsx";
response.setHeader("Content-disposition",
"attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
response.setHeader("Pragma", "No-cache");// 设置头
// response.setHeader("Cache-Control", "no-cache");// 设置头
response.setHeader("Cache-Control", "max-age=-1");// 设置头
response.setDateHeader("Expires", 0);// 设置日期头
response.setContentType("application/msexcel;charset=UTF-8");// 设置类型
workbook.write(out);
out.flush();
out.close();
LOGGER.info("*******文件导出成功********");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
styles.put("title", makeCellStyle(wb, "title", CellStyle.ALIGN_CENTER, true, false));
styles.put("header", makeCellStyle(wb, "header", CellStyle.ALIGN_CENTER, true, true));
styles.put("normalCell", makeCellStyle(wb, "normalCell", CellStyle.ALIGN_LEFT, false, true));
styles.put("numCell", makeCellStyle(wb, "numCell", CellStyle.ALIGN_RIGHT, false, true));
styles.put("percentCell", makeCellStyle(wb, "percentCell", CellStyle.ALIGN_RIGHT, false, true));
styles.put("CountCell", makeCellStyle(wb, "CountCell", CellStyle.ALIGN_LEFT, false, true));
return styles;
}
private static CellStyle makeCellStyle(Workbook wb, String textType, short align, boolean boldWeight,
boolean border) {
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 10);
if ("title".equals(textType)) {// 主标题
font.setFontHeightInPoints((short) 18);
} else if ("head".equals(textType)) {// 主标题
font.setFontHeightInPoints((short) 12);
} else if ("numCell".equals(textType)) {
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0.00"));
} else if ("percentCell".equals(textType)){
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0.00%"));
} else if ("CountCell".equals(textType)){
DataFormat df = wb.createDataFormat();
cellStyle.setDataFormat(df.getFormat("#,##0"));
}
if (boldWeight) {
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
if (border) {
cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);// 下边框
}
cellStyle.setFont(font);
cellStyle.setAlignment(align);// 左右居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setWrapText(true);// 自动换行
return cellStyle;
}
}
/*
*再分享使用JSP页面导出Excel的方法
*/
//将数据存储到map中，在前端循环打印
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment; filename="
+ java.net.URLEncoder.encode("支出监控", "utf-8") + ".xls");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>导出支出表</title>
</head>
<body>
<c:if test="${not empty exportList }">
<table border="1">
<tr>
<th colspan="10">支出监控</th>
</tr>
<tr>
<th width="110" align="center">风险等级</th>
<th align="center">付款单位</th>
<th align="center">付款金额</th>
<th align="center">付款账号</th>
<th align="center">付款银行</th>
<th align="center">收款单位</th>
<th align="center">收款账号</th>
<th align="center">付款时间</th>
<th align="center">付款内容</th>
<th align="center">是否风险</th>
</tr>
<c:forEach var="export" items="${exportList}">
<tr>
<td <c:if test="${export.sffx=='否'}">style="color:#CCCCCC;"</c:if>
<c:if test="${export.sffx=='是'}">style="color:#FF0000;"</c:if>
<c:if test="${empty export.sffx}">style="color:#FF0000;"</c:if>>
${export.riskStar }
</td>
<td>${export.fkdw}</td>
<td>${export.je}</td>
<td>${export.accountId}</td>
<td>${export.bankName}</td>
<td>${export.skdw}</td>
<td>${export.skzh}</td>
<td>${export.fkDate}</td>
<td>${export.zfnr}</td>
<td>${export.sffx}</td>
</tr>
</c:forEach>
</table>
</c:if>
</body>
</html>
2641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
