使用Java类库POI生成简易的Excel报表
1.需求
1.数据库生成报表需要转义其中字段的信息。比如 1,有效 2.无效等
2.日期格式的自数据需要转义其格式。
3.标题的格式和数据的格式需要分别设置
4.可能出现的实体类嵌套实体类的情况
5.需要在行尾添加统计数据
2.具体实现
首先创建一个用于存放标题、反射方法、转义列表、统计的辅助实体对象
import java.util.Map;
/**
* 列开头信息和结尾信息
*
* @author yanlong 2018年03月06日09:36:18
*
*
*/
public class BaseInf {
/*
* 标题
*/
private String titleName;
/*
* 行读取方法
*/
private String columMethod;
/*
* 转义列表
*/
private Map<String, String> map;
/*
* 行尾统计
*/
private String count;
/**
* 有转义列表 有统计的构造方法
* @param titleName 标题
* @param columMethod 获取方法
* @param map 转义列表
* @param count 统计
*/
public BaseInf(String titleName, String columMethod, Map<String, String> map, String count) {
super();
this.titleName = titleName;
this.columMethod = columMethod;
this.map = map;
this.count = count;
}
/**
* 有转义列表构造方法
*
* @param titleName 标题
* @param columMethod 获取方法
* @param map 转义列表
*/
public BaseInf(String titleName, String columMethod, Map<String, String> map) {
super();
this.titleName = titleName;
this.columMethod = columMethod;
this.map = map;
this.count = null;
}
/**
* 无转义列表 有合计数据
* @param titleName 标题
* @param columMethod 读取方法列表
* @param count 合计值
*/
public BaseInf(String titleName, String columMethod, String count) {
super();
this.titleName = titleName;
this.columMethod = columMethod;
this.map = null;
this.count = count;
}
/**
* 无转义列表无统计构造方法
*
* @param titleName
* 标题
* @param columMethod
* 获取方法
*/
public BaseInf(String titleName, String columMethod) {
super();
this.titleName = titleName;
this.columMethod = columMethod;
this.map = null;
this.count = null;
}
public String getTitleName() {
return titleName;
}
public String getColumMethod() {
return columMethod;
}
public Map<String, String> getMap() {
return map;
}
public String getCount() {
return count;
}
}
具体的创建过程将创建表单和传输方式分开 以便于可以本地下载或者服务器下载
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
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.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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;
public class ExcelUtil {
// 私有构造方法禁止new实例
private ExcelUtil() {
}
// 日志工具
// private static final Logger logger =
// LoggerFactory.getLogger(ExcelUtil.class);
// 默认日期格式
private static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日 hh点mm分ss秒";
// 默认行高
private static final Short DEFAULT_COLOUMN_HEIGHT = 400;
private static final Short DEFAULT_COLOUMN_WEIGHT = 170;
/**
* 将工作表输出到浏览器中
*
* @param response
* 响应流
* @param workbook
* 创建完成的工作表
* @param fileName
* 文件名
* @param sufferNm
* 文件后缀名
* @throws Exception
*/
public static void workbook2InputStream(HttpServletResponse response, Workbook workbook, String fileName,
String sufferNm) throws Exception {
response.setCharacterEncoding("utf-8");
response.setHeader("Content-type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO8859-1") + sufferNm);
// 设置下载头信息
response.setContentType("application nd.ms-excel; charset=utf-8");
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
/**
* 实体类单层嵌套
*/
public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list)
throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
return createWorkbook(version, sheetNm, baseInfList, list, null);
}
/**
* 创建一个数据表 实体类嵌套实体类
*
* @param version
* excel版本 2007 或者其他
* @param sheetNm
* sheet 名称
* @param baseInfList
* 数据基础信息
* @param list
* 数据
* @param innerMethod
* 实体类多层嵌套
*
* @see BaseInf
*
* @return 构建完成的数据表对象
*
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list,
String innerMethod) throws NoSuchMethodException, SecurityException, IllegalAccessException,
IllegalArgumentException, InvocationTargetException {
SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_PATTERN);
Workbook workbook = null;
if (version == 2007) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
Sheet sheet = workbook.createSheet(isEmpty(sheetNm) ? "sheet1" : sheetNm);
// 写入标题
CellStyle titleStyle = titleStyle(workbook);
// 创建标题行(第一行)
Row titleRow = sheet.createRow(0);
// 设置第一行的行高
titleRow.setHeight(DEFAULT_COLOUMN_HEIGHT);
Cell cell = null;
// 设置序号
sheet.setColumnWidth(0, DEFAULT_COLOUMN_WEIGHT);
cell = titleRow.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("序号");
cell.setCellStyle(titleStyle);
// 其他标题
for (int i = 0; i < baseInfList.size(); i++) {
String titleName = baseInfList.get(i).getTitleName();
// 设置单元格的宽
sheet.setColumnWidth(i, titleName.length() * 1500);
cell = titleRow.createCell(i + 1);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(titleName);
cell.setCellStyle(titleStyle);
}
/**
* 写入数据
*
* 写入数据按照先行 后列的的方式进行
*
*/
CellStyle dataStyle = dataStyle(workbook);
Row dataRow = null;
for (int i = 0; i < list.size(); i++) {
// 创建行
dataRow = sheet.createRow(i + 1);
// 创建列 此处为序号列
cell = dataRow.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(i + 1);
cell.setCellStyle(titleStyle);
// 序号列创建完毕 开始创建数据列
for (int j = 0; j < baseInfList.size(); j++) {
// 创建数据列
cell = dataRow.createCell(j + 1);
BaseInf baseInf = baseInfList.get(j);
// 设值
Method method;
Object value;
if (innerMethod != null) {
method = list.get(i).getClass().getMethod(innerMethod);
Object obj = method.invoke(list.get(i));
method = obj.getClass().getMethod(baseInf.getColumMethod());
value = method.invoke(obj);
} else {
method = list.get(i).getClass().getMethod(baseInf.getColumMethod());
value = method.invoke(list.get(i));
}
String returnType = method.getReturnType().getName().toLowerCase();
cell.setCellStyle(dataStyle);
// 转义列表
Map<String, String> transMap = baseInf.getMap();
// 判断是否需要转义
if (transMap == null) {
if (returnType.indexOf("string") != -1) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value == null ? "" : value.toString());
} else if (returnType.indexOf("integer") != -1 || returnType.indexOf("int") != -1
|| returnType.indexOf("bigdecimal") != -1 || returnType.indexOf("double") != -1
|| returnType.indexOf("long") != -1 || returnType.indexOf("float") != -1) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(value == null ? null : new Double(value.toString()));
} else if (returnType.indexOf("date") != -1) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value == null ? null : sdf.format((Date) value));
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value == null ? "" : value.toString());
}
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String cellValue = value == null ? "" : transMap.get(tse(value.toString()));
cell.setCellValue(cellValue == null ? tse(value.toString()) : cellValue);
}
}
}
// 创建统计行
// 创建行
dataRow = sheet.createRow(list.size() + 1);
// 创建列 此处为序号列
cell = dataRow.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("统计");
cell.setCellStyle(titleStyle);
for (int i = 0; i < baseInfList.size(); i++) {
BaseInf baseInf = baseInfList.get(i);
cell = dataRow.createCell(i + 1);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(dataStyle);
if (baseInf.getCount() != null) {
cell.setCellValue(baseInf.getCount());
} else {
cell.setCellValue("");
}
}
return workbook;
}
/**
*
* 删除字符串内的回车 空格和两端空白
*
* @author yanlong 2017-7-5
*
*/
private static String tse(String str) {
return str == null ? "" : str.replace(" ", "").replace("/r", "").replace("/n", "").trim();
}
// 判断非空
private static boolean isEmpty(String str) {
return str == null || "".equals(str.trim());
}
/**
* 设置标题样式
*
* @param workbook 工作表
* @return 标题样式
*/
private static CellStyle titleStyle(Workbook workbook) {
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); // 居中
titleStyle.setBorderLeft((short) 1);
titleStyle.setBorderRight((short) 1);
titleStyle.setBorderBottom((short) 1);
titleStyle.setBorderTop((short) 1);
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 12);// 设置字体大小
titleStyle.setFont(font);// 选择需要用到的字体格式
return titleStyle;
}
/**
* 数据样式
*
* @param workbook 工作表
* @return 数据样式
*/
private static CellStyle dataStyle(Workbook workbook) {
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setBorderBottom((short) 1);
dataStyle.setBorderLeft((short) 1);
dataStyle.setBorderRight((short) 1);
dataStyle.setBorderTop((short) 1);
dataStyle.setBottomBorderColor(HSSFColor.BLACK.index);
return dataStyle;
}
}
调用方式
//下载文件
List<BaseInf> baseInfList = new ArrayList<BaseInf>();
BaseInf baseInf = new BaseInf("商户编号","getTACCOUNTID");
baseInfList.add(baseInf);
baseInf = new BaseInf("商户名称","getMERNAME");
baseInfList.add(baseInf);
baseInf = new BaseInf("商户类型","getMERTYPE",Tmerinfo.merTypeTransMap());
baseInfList.add(baseInf);
baseInf = new BaseInf("商户状态","getSTATE",Tmerinfo.statesTransMap());
baseInfList.add(baseInf);
baseInf = new BaseInf("商户分级","getMERLEVEL",Tmerinfo.merlevelTransMap(ms));
baseInfList.add(baseInf);
baseInf = new BaseInf("企业全称","getFULLNAME");
baseInfList.add(baseInf);
baseInf = new BaseInf("创建时间","getOPENTIME");
baseInfList.add(baseInf);
baseInf = new BaseInf("开通时间","getREGTIME");
baseInfList.add(baseInf);
Workbook workbook = ExcelUtil.createWorkbook(2007, "商户管理-商户查询", baseInfList, mercertinfo,"getTmerinfoDTO");
ExcelUtil.workbook2InputStream(response, workbook, "userQuery", ".xls");