1.自定义注解类
package cn.com.test.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义注解
* @author: Rodge
* @time: 2017年12月26日 下午8:32:10
* @version: V1.0.0
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface CellField {
/**
* 文件列名
*/
public String name() default "";
}
2.excel工具类
package cn.com.javatest.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ReflectionUtils;
import cn.com.javatest.annotation.CellField;
/**
* Excel工具类
*
* @author: Rodge
* @time: 2017年12月26日 下午8:54:10
* @version: V1.0.0
*/
public final class ExcelUtils {
/** 日志对象 **/
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
/** 私有无参构造方法 **/
private ExcelUtils() { }
/**
* 生成excel2007文档
*
* @param filePath 文件保存路径 ,例如:D:/excel/test.xlsx
* @param beans 实体对象
*/
public static <T> void createExcel(String filePath, List<T> beans) {
String fileName = filePath.substring(filePath.lastIndexOf('/') + 1);
Workbook wb = createWorkbook(beans, fileName);
File file = createFile(filePath);
try (OutputStream os = new FileOutputStream(file)) {
wb.write(os);
} catch (IOException e) {
LOGGER.error("生成excel2007文件失败", e);
}
}
/**
* 导出excel2007文件
*
* @param response 响应对象
* @param beans 实体对象集合
*/
public static <T> void exportExcel(HttpServletResponse response, List<T> beans) {
// 以当前时间作为文件名
String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
// 创建Workbook对象
Workbook wb = createWorkbook(beans, fileName);
// 导出excel
try (OutputStream os = response.getOutputStream()) {
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Expose-Headers", "*");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
wb.write(os);
} catch (IOException e) {
LOGGER.error("导出excel2007文件失败", e);
}
}
/**
* 导出excel2007文件
*
* @param response 响应对象
* @param beans 实体对象集合
* @param fileName 文件名称 ,如:测试名称
*/
public static <T> void exportExcel(HttpServletResponse response, List<T> beans, String fileName) {
// 创建Workbook对象
Workbook wb = createWorkbook(beans, fileName);
// 导出excel
try (OutputStream os = response.getOutputStream()) {
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Expose-Headers", "*");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;