概览
HSSF提供读写Microsoft Excel XLS格式档案的功能。
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
楼主此处只写了hssf的demo，适合多表头组合
有需要的可以自行改写成xssf及兼容2者
jar
<!-- poi excel导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
工具类及常量类
package com.cn.utils;
import com.cn.base.common.CommonBaseConstant;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.util.*;
/**
* @ClassName ExcelUtils
* @Description
* @Author 52fighting
* @Date 2020/05/15 09:17
* @Version 1.0
**/
public class ExcelUtils {
private static final Logger logger = LogManager.getLogger(ExcelUtils.class);
private static ExcelUtils excelUtils = new ExcelUtils();
private ExcelUtils() {
}
public static ExcelUtils getInstance() {
return excelUtils;
}
/**
* 根据字段名获取字段对象
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
//如果本类中存在该字段，则返回
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
//递归
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
/**
* 根据字段名获取字段值
*
* @param fieldName 字段名
* @param o 对象
* @return 字段值
* @throws Exception 异常
*/
public static Object getFieldValueByName(String fieldName, Object o)
throws Exception {
Object value = null;
//根据字段名得到字段对象
Field field = getFieldByName(fieldName, o.getClass());
//如果该字段存在，则取出该字段的值
if (field != null) {
field.setAccessible(true);//类中的成员变量为private,在类外边使用属性值，故必须进行此操作
value = field.get(o);//获取当前对象中当前Field的value
} else {
throw new Exception(o.getClass().getSimpleName() + CommonBaseConstant.POI_EXCEL_NOT_EXSIT_NAME
+ fieldName);
}
return value;
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名，
* 如userName等，又接受带路径的属性名，如student.department.name等
*
* @param fieldNameSequence 带路径的属性名或简单属性名
* @param o 对象
* @return 属性值
* @throws Exception 异常
*/
public static Object getFieldValueByNameSequence(String fieldNameSequence,
Object o) throws Exception {
Object value = null;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split(CommonBaseConstant.POI_EXCEL_PATTERN_MATCHER);
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, o);
} else {
// 根据数组中第一个连接属性名获取连接属性对象，如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], o);
//截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence
.substring(fieldNameSequence.indexOf(CommonBaseConstant.POI_EXCEL_PATTERN_MATCHER_POINT) + 1);
//递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 设置导出文件为excel
* @param excelName
* @param response
* @return
*/
public static HttpServletResponse findExcelMessage(String excelName, HttpServletResponse response, HttpServletRequest request){
// 设置response头信息，DateUtil.formatDate(new Date())设置默认文件名为当前时间：年月日时分秒
response.reset();
response.setContentType(CommonBaseConstant.POI_EXCEL_RESPONSE_CONTENT_TYPE); // 改成输出excel文件
try {
//防止出现跨域
//允许任何域名使用
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN_VALUE);
//允许任何方法
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS_VALUE);
//返回结果可以用于缓存的最长时间，单位是秒
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_MAX_AGE, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_MAX_AGE_VALUE);
//响应头设置
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS_VALUE);
//设置文件名称
String agent = request.getHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_USER_AGENT).toLowerCase();
String fileName = excelName + DateUtil.formatDate(new Date());
String codedFileName = java.net.URLEncoder.encode(fileName, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_UTF);
if (agent.contains(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_FIRE_FOX)) {
response.setCharacterEncoding(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_UTF);
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_NAME, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_VALUE + new String(fileName.getBytes(), CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_ISO)
+ CommonBaseConstant.POI_EXCEL_RESPONSE_FILE_EXTENSION_XLS);
} else {
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_NAME, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_VALUE + codedFileName + CommonBaseConstant.POI_EXCEL_RESPONSE_FILE_EXTENSION_XLS);
}
} catch (UnsupportedEncodingException e1) {
logger.info(e1.getMessage());
}
return response;
}
/**
* 设置导出文件为excel
* @param customExcelName 前端传的自定义excel名称
* @param response
* @return
*/
public static HttpServletResponse findCustomExcelMessage(String customExcelName, HttpServletResponse response, HttpServletRequest request){
// 设置response头信息，DateUtil.formatDate(new Date())设置默认文件名为当前时间：年月日时分秒
response.reset();
response.setContentType(CommonBaseConstant.POI_EXCEL_RESPONSE_CONTENT_TYPE); // 改成输出excel文件
try {
//防止出现跨域
//允许任何域名使用
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN_VALUE);
//允许任何方法
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS_VALUE);
//返回结果可以用于缓存的最长时间，单位是秒
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_MAX_AGE, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_MAX_AGE_VALUE);
//响应头设置
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS_VALUE);
//设置文件名称
String agent = request.getHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_USER_AGENT).toLowerCase();
String fileName = customExcelName;
String codedFileName = java.net.URLEncoder.encode(fileName, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_UTF);
if (agent.contains(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_FIRE_FOX)) {
response.setCharacterEncoding(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_UTF);
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_NAME, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_VALUE + new String(fileName.getBytes(), CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_ISO)
+ CommonBaseConstant.POI_EXCEL_RESPONSE_FILE_EXTENSION_XLS);
} else {
response.setHeader(CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_NAME, CommonBaseConstant.POI_EXCEL_RESPONSE_HEADER_VALUE + codedFileName + CommonBaseConstant.POI_EXCEL_RESPONSE_FILE_EXTENSION_XLS);
}
} catch (UnsupportedEncodingException e1) {
logger.info(e1.getMessage());
}
return response;
}
/**
* 多级表头通用方法（已经通过四级表头测试）
* 测试demo
* String[][] propertyDes = {{"姓名","所属部门","职位","入职日期","转正日期","年假","","带薪病假","","调休剩余可休天数","本月加班天数","本月已休","","","","","",""},
* {"","","","","","基数","剩余可休天数","基数","剩余可休天数","","","年假","调休","带薪病假","婚假","产假","丧假","事假"}};
* @param excelName
* @param propertyDes
* @return
*/
public static Map<String, Object> createWorkBook(String excelName, String[][] propertyDes) {
Map<String, Object> mapObj = new HashMap<>();
//创建一个WorkBook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
try{
//在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
HSSFSheet sheet = wb.createSheet(excelName);
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
//创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
//设置字体
Font font = wb.createFont();
font.setFontHeightInPoints((short) 13); // 字体高度
font.setBold(true);
style.setFont(font);
int mergerNum = 0 ; //合并数
//给单元格设置值
for(int i=0; i< propertyDes.length; i++){
HSSFRow row = sheet.createRow(i);
row.setHeight((short)700);
for(int j=0; j<propertyDes[i].length; j++){
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(propertyDes[i][j]);
// 调整每一列宽度
sheet.autoSizeColumn((short) i);
sheet.autoSizeColumn((short) j);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 17 / 10);
}
}
Map<Integer,List<Integer>> map = new HashMap<Integer, List<Integer>>(); // 合并行时要跳过的行列
//合并列
for(int i=0; i<propertyDes[propertyDes.length-1].length; i++){
if("".equals(propertyDes[propertyDes.length-1][i])){
for(int j=propertyDes.length-2; j >=0 ;j--){
if(!"".equals(propertyDes[j][i])){
sheet.addMergedRegion(new CellRangeAddress(j,propertyDes.length-1,i,i)); // 合并单元格
break;
}else{
if(map.containsKey(j)){
List<Integer> list = map.get(j);
list.add(i);
map.put(j, list);
}else{
List<Integer> list = new ArrayList<Integer>();
list.add(i);
map.put(j, list);
}
}
}
}
}
//合并行
for(int i=0; i< propertyDes.length-1; i++){
for(int j=0; j<propertyDes[i].length; j++){
List<Integer> list = map.get(i);
if(list==null ||(list!=null&&!list.contains(j))){
if("".equals(propertyDes[i][j])){
mergerNum++ ;
if(mergerNum != 0 && j == (propertyDes[i].length-1)){
sheet.addMergedRegion(new CellRangeAddress(i,i,j-mergerNum,j)); // 合并单元格
mergerNum = 0 ;
}
}else {
if(mergerNum != 0){
sheet.addMergedRegion(new CellRangeAddress(i,i,j-mergerNum-1,j-1)); // 合并单元格
mergerNum = 0 ;
}
}
}
}
}
mapObj.put(CommonBaseConstant.POI_EXCEL_EXPORT_WORK_BOOK, wb);
mapObj.put(CommonBaseConstant.POI_EXCEL_EXPORT_SHEET, sheet);
} catch (Exception e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
}
return mapObj;
}
/**
* 填充excel内容
* @param mapObj
* @param list
* @param tClass
* @param <T>
* @return
*/
public static <T> HSSFSheet findSheet(Map<String, Object> mapObj, List<T> list, int contentIndex, Class<T> tClass){
HSSFSheet sheet = null;
try {
if (null != mapObj) {
HSSFWorkbook wb = (HSSFWorkbook) mapObj.get(CommonBaseConstant.POI_EXCEL_EXPORT_WORK_BOOK);
sheet = (HSSFSheet) mapObj.get(CommonBaseConstant.POI_EXCEL_EXPORT_SHEET);
// 填充内容
HSSFRow row = null;
//创建单元格，设置表格内容居中
HSSFCellStyle style2 = wb.createCellStyle();
//创建一个居中格式
style2.setAlignment(HorizontalAlignment.CENTER);
//设置边框
style2.setBorderBottom(BorderStyle.THIN); //下边框
style2.setBorderLeft(BorderStyle.THIN);//左边框
style2.setBorderTop(BorderStyle.THIN);//上边框
style2.setBorderRight(BorderStyle.THIN);//右边框
//得到所有属性
Field[] field = tClass.getDeclaredFields();
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + contentIndex);
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < field.length; i++) {
Object objValue = getFieldValueByNameSequence(field[i].getName(), item);
// String fieldValue = objValue == null ? "" : objValue.toString();
//ranking导出时默认字段，与VIEW中的排序字段ranking对应
String fieldValue = objValue == null ? "" : objValue.toString();
if (field[i].getName().equalsIgnoreCase(CommonBaseConstant.POI_EXCEL_EXPORT_RANKING_SORT) && !fieldValue.equals(CommonBaseConstant.POI_EXCEL_EXPORT_RANKING_TOTAL)) {
fieldValue = NumberEnumeration.getValueByKey(index + 1);
}
HSSFCell cell2 = row.createCell(i);
cell2.setCellStyle(style2);
cell2.setCellValue(fieldValue);
}
}
}
} catch (Exception e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
}
return sheet;
}
/**
*
* 导出excel
* @param mapObj
* @param response
* @param <T>
*/
public static <T> void downToExcel(Map<String, Object> mapObj, HttpServletResponse response){
OutputStream ouputStream = null;
try {
if (null != mapObj) {
HSSFWorkbook wb = (HSSFWorkbook) mapObj.get(CommonBaseConstant.POI_EXCEL_EXPORT_WORK_BOOK);
//将文件输出
ouputStream = response.getOutputStream();
wb.write(ouputStream);
}
} catch (Exception e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
} finally {
try {
if(null != ouputStream) {
ouputStream.flush();
ouputStream.close();
}
} catch (IOException e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
}
}
}
public static <T> void fileDataToExport(Map<String, Object> mapObj, List<T> list, HttpServletResponse response, Class<T> tClass){
OutputStream ouputStream = null;
try {
if (null != mapObj) {
HSSFWorkbook wb = (HSSFWorkbook) mapObj.get(CommonBaseConstant.POI_EXCEL_EXPORT_WORK_BOOK);
HSSFSheet sheet = (HSSFSheet) mapObj.get(CommonBaseConstant.POI_EXCEL_EXPORT_SHEET);
// 填充内容
HSSFRow row = null;
//创建单元格，设置表格内容居中
HSSFCellStyle style2 = wb.createCellStyle();
//创建一个居中格式
style2.setAlignment(HorizontalAlignment.CENTER);
//得到所有属性
Field[] field = tClass.getDeclaredFields();
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 1);
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < field.length; i++) {
Object objValue = getFieldValueByNameSequence(field[i].getName(), item);
// String fieldValue = objValue == null ? "" : objValue.toString();
//ranking导出时默认字段，与VIEW中的排序字段ranking对应
String fieldValue = objValue == null ? "" : objValue.toString();
if (field[i].getName().equalsIgnoreCase(CommonBaseConstant.POI_EXCEL_EXPORT_RANKING_SORT) && !fieldValue.equals(CommonBaseConstant.POI_EXCEL_EXPORT_RANKING_TOTAL)) {
fieldValue = NumberEnumeration.getValueByKey(index + 1);
}
HSSFCell cell2 = row.createCell(i);
cell2.setCellStyle(style2);
cell2.setCellValue(fieldValue);
}
}
//将文件输出
ouputStream = response.getOutputStream();
wb.write(ouputStream);
}
} catch (Exception e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
} finally {
try {
if(null != ouputStream) {
ouputStream.flush();
ouputStream.close();
}
} catch (IOException e) {
logger.error(CommonBaseConstant.POI_EXCEL_EXPORT_FAILED, e.getMessage());
}
}
}
}
//常量
public interface CommonBaseConstant {
//poi excel
String POI_EXCEL_RESPONSE_CONTENT_TYPE = "application/vnd.ms-excel;charset=utf-8";
String POI_EXCEL_RESPONSE_HEADER_NAME = "Content-disposition";
String POI_EXCEL_RESPONSE_HEADER_VALUE = "attachment; filename=";
String POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_GB = "gb2312";
String POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_ISO = "ISO-8859-1";
String POI_EXCEL_RESPONSE_HEADER_CHARSET_NAME_UTF = "utf-8";
String POI_EXCEL_RESPONSE_HEADER_USER_AGENT = "USER-AGENT";
String POI_EXCEL_RESPONSE_HEADER_FIRE_FOX = "firefox";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN = "Access-Control-Allow-Origin";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_ORIGIN_VALUE = "*";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS = "Access-Control-Allow-Methods";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_METHODS_VALUE = "POST, GET, OPTIONS, DELETE";
String POI_EXCEL_RESPONSE_HEADER_MAX_AGE = "Access-Control-Max-Age";
String POI_EXCEL_RESPONSE_HEADER_MAX_AGE_VALUE = "3600";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS = "Access-Control-Allow-Headers";
String POI_EXCEL_RESPONSE_HEADER_ALLOW_HEADERS_VALUE = "x-requested-with";
String POI_EXCEL_RESPONSE_FILE_EXTENSION_XLS = ".xls";
String POI_EXCEL_EXPORT_FAILED = "导出Excel失败！";
String POI_EXCEL_NOT_EXSIT_NAME = "类不存在字段名 ";
String POI_EXCEL_PATTERN_MATCHER = "\\.";
String POI_EXCEL_PATTERN_MATCHER_POINT = ".";
String POI_EXCEL_EXPORT_WORK_BOOK = "workBook";
String POI_EXCEL_EXPORT_SHEET = "sheet";
String POI_EXCEL_EXPORT_RANKING_SORT = "ranking";
String POI_EXCEL_EXPORT_RANKING_TOTAL = "汇总";
}
实现层impl调用
public class UserServiceImpl implements UserService{
public void exportUserList(HttpServletResponse response, HttpServletRequest request) {
String excelName = "user 清单";
String[][] propertyDes = {
{"姓名","年龄","性别","地址"}
};
//二级表头
//String[][] propertyDes = {
// {"姓名","详情","",""}
// ,{"","年龄","性别","地址"}
// };
//查询导出的数据
List<User> userList = findUserList();
//设置response
ExcelUtils excelUtils = ExcelUtils.getInstance();
response = excelUtils.findExcelMessage(excelName, response, request);
//创建workbook
Map<String, Object> mapObj = excelUtils.createWorkBook(excelName, propertyDes);
//填充内容
excelUtils.findSheet(mapObj, userList, propertyDes.length, User.class);
//导出
excelUtils.downToExcel(mapObj, response);
}
}
结果
一级表头
二级表头
可以自行调整excel表头、边框、内容等样式
737
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
