package cn.bevis.poi;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.annotation.Annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @ClassName ExcelExportUtils
* @Description:基于POI的Excel导出通用方法
* @author CP_biyongfei
* @date 2017-8-18 下午3:56:52
*/
public class ExcelExportUtils {
private static final int PAGESIZE = 65530;
private int defaultColumnWidth = 10;//默认列宽
private static final short FONT_COLOR = HSSFColor.BLACK.index;//默认字体颜色,黑色;
private ExcelExportUtils() {
super();
}
private ExcelExportUtils(int defaultColumnWidth) {
super();
this.defaultColumnWidth = defaultColumnWidth;
}
/**
* 默认列宽是10（即10个10号汉字的宽度）
* @return
*/
public static ExcelExportUtils getInstance() {
return new ExcelExportUtils();
}
/**
* 自定义列宽（即defaultColumnWidth个10号汉字的宽度）
* @param defaultColumnWidth
* @return
*/
public static ExcelExportUtils getInstance(int defaultColumnWidth) {
return new ExcelExportUtils(defaultColumnWidth);
}
private int totalColumn;//记录列的总个数
private Map<Integer,Integer> specialColumn = new HashMap<Integer,Integer>();
/**
* 需要单独为某一列设置列宽的方法，从0开始，第一列为0，第二列为1...
* 可以多次调用，设定不同的列的列宽，
* 例如：ExcelExportUtils.getInstance().setColumnWidth(1,15).setColumnWidth(2,20).export(...);
* @param columnNum:哪一列？列数
* @param columnWidth：列宽，默认列宽是10（即10个10号汉字的宽度）
* @return
*/
@SuppressWarnings("unused")
private ExcelExportUtils setColumnWidth(int columnNum,int columnWidth) {
specialColumn.put(columnNum, columnWidth);
return this;
}
/**
* @Fields specialColumnStyle : 记录values(rown)需要特殊指定文字内容左对齐的列
*/
private List<Integer> specialColumnStyle = new ArrayList<Integer>();
/**
* @Title setColumnTextLeft
* @Description：设置values(rown)需要特殊指定文字内容左对齐的列
* 需要单独为某一列设置文字内容左对齐的方法，从0开始，第一列为0，第二列为1...
* 可以多次调用，设定不同的列的列宽，
* 例如：ExcelExportUtils.getInstance().setColumnTextLeft(1).setColumnTextLeft(2).export(...);
* 表示第一列，第二列左对齐
* 例如：ExcelExportUtils.getInstance().setColumnTextLeft(1,2,3).export(...);
* 表示第一列，第二列，第三列左对齐
* 例如：ExcelExportUtils.getInstance().setColumnTextLeft().setColumnTextLeft(2).export(...);
* 未指定则表示居中对齐
* @param columnNums 哪一列？列数
* @return
* @user CP_biyongfei 2017年9月20日
* @updater：
* @updateTime：
*/
@SuppressWarnings("unused")
private ExcelExportUtils setColumnTextLeft(int ... columnNums) {
if(columnNums.length > 0) {
for (int i : columnNums) {
specialColumnStyle.add(i);
}
}
return this;
}
/**
* @Title export
* @Description：不指定firstHeader,默认第一列firstHeader是序号,firstValues对应的是行号
* @param request
* @param response
* @param excelName 导出Excel的文件名字，也是sheet的部分名字，如果title=true,也是首行title的名字
* @param title
* @param header
* @param values
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void export(HttpServletRequest request,
HttpServletResponse response, String excelName, String sheetName, boolean title, List<String> header,
List<List<String>> values) {
export(request, response, excelName, sheetName, title, "序号", null, header, values);
}
/**
* @Description：数据导出公用方法
* @param excelName
* :导出Excel的文件名字，也是sheet的部分名字，如果title=true且sheetName="",也是首行title的名字
* @Param sheetName
* :sheet的名字
* @param title
* :文档标题行(第一行)，title为true且sheetName="",首行会将excelName作为首行标题，false,title（首行）不创建，下面的内容向上偏移一行
* @param firstHeader
* :序号
* @param firstValues
* :1,2...
* @param header
* :表格标题行(第二行)
* @param values
* :表格标题行对应的值(第三行,第四行...)
* @return
* @user CP_biyongfei 2017-7-18
* @updater：
* @updateTime：
*/
private void export(HttpServletRequest request, HttpServletResponse response, String excelName, String sheetName, boolean title, String firstHeader,
List<String> firstValues, List<String> header,List<List<String>> values) {
HSSFWorkbook wb = new HSSFWorkbook();
validate(excelName, firstHeader, firstValues, header, values);
initAndValidateSpecialColumnWidth(header);
fillDate(wb, StringUtils.isBlank(sheetName)?excelName:sheetName, title, firstHeader, firstValues, header, values);
try {
responseWriteExcel(request, response, excelName, wb);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 初始化需要特殊设定列宽的列
* @param header
*/
private void initAndValidateSpecialColumnWidth(List<String> header) {
this.totalColumn = header.size() + 1;//为总列数赋值
for (Integer columnNum : specialColumn.keySet()) {
if (columnNum > (totalColumn - 1)) {
throw new RuntimeException("设定列宽的列的列数超出总列数！");
}
}
for (Integer columnNum : specialColumnStyle) {
if (columnNum > (totalColumn - 1)) {
throw new RuntimeException("设定列宽的列的列数超出总列数！");
}
}
}
/**
* @Title responseWriteExcel
* @Description：响应并写出Excel数据
* @param request
* @param response
* @param excelName
* @param wb
* @throws IOException
* @throws UnsupportedEncodingException
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void responseWriteExcel(HttpServletRequest request,
HttpServletResponse response, String excelName, HSSFWorkbook wb)
throws IOException, UnsupportedEncodingException {
response.reset();
// 设置相应头信息，以附件形式下载并且指定文件名
response.setContentType("application/msexcel;charset=UTF-8");
String finalName = "attachment;";
finalName += " filename=\"" + encodeURIComponent(request, excelName+".xls") +"\";";
finalName += " filename*=utf-8‘‘" + encodeURIComponent(request, excelName+".xls");
response.setHeader("Cache-Control", "must-revalidate,post-check=0,pre-check=0");
response.setHeader("Pragma", "public");
response.setDateHeader("Expires", (System.currentTimeMillis()+1000));
response.setHeader("Content-Disposition", finalName);
OutputStream os = new BufferedOutputStream(response.getOutputStream());
wb.write(os);
os.flush();
os.close();
}
private void responseWriteExcel(String dirStr, String excelName, HSSFWorkbook wb)
throws IOException, UnsupportedEncodingException {
// 设置文件名
String finalName = excelName+".xls";
File dir = new File(dirStr);
if(!dir.exists() || dir.isFile()) {
dir.mkdirs();
}
File finalFile = new File(dir, finalName);
FileOutputStream in = new FileOutputStream(finalFile);
OutputStream os = new BufferedOutputStream(in);
wb.write(os);
os.flush();
os.close();
}
/**
* 根据不同的浏览器设置下载附件的文件名，防止中文名称乱码！
* <pre>
* 符合 RFC 3986 标准的“百分号URL编码”
* 在这个方法里，空格会被编码成%20，而不是+
* 和浏览器的encodeURIComponent行为一致
* </pre>
* @param request
* @param filename
* @return
*/
private String encodeURIComponent(HttpServletRequest request, String filename) {
try {
String agent = request.getHeader("User-Agent").toLowerCase();
if(agent.indexOf("safari") > 0){//苹果
return new String(filename.getBytes("UTF-8"),"ISO-8859-1");
} else if(agent.indexOf("firefox") > 0) {//火狐
return new String(filename.getBytes("UTF-8"),"ISO-8859-1");
} else if(agent.indexOf("chrome") > 0) {//谷歌
return new String(filename.getBytes("UTF-8"),"ISO-8859-1");
} else if(agent.indexOf("opera") > 0) {//欧朋
return URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
} else if(agent.indexOf("msie") > 0) {//IE
return URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
} else {//其他
return URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
return null;
}
}
/**
* @Title validate
* @Description：检验数据
* @param excelName
* @param firstHeader
* @param firstValues
* @param header
* @param values
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void validate(String excelName, String firstHeader,
List<String> firstValues, List<String> header,
List<List<String>> values) {
if (StringUtils.isBlank(excelName)) {
throw new RuntimeException("excelName不能为空!");
}
if (StringUtils.isBlank(firstHeader)) {
throw new RuntimeException("firstHeader不能为空!");
}
if (!"序号".equals(firstHeader) && null == firstValues) {
throw new RuntimeException("firstValues不能为null!");
}
if (null == header || null == values) {
throw new RuntimeException("firstValues,header,values参数不能为null!");
}
if ((null !=firstValues && firstValues.size() == 0) || header.size() == 0 || values.size() == 0) {
throw new RuntimeException("firstValues,header,values的size不能为0!");
}
int size = 0;
for (int i = 0; i < values.size(); i++) {
if (0 != i && values.get(i).size() != size) {
throw new RuntimeException("values里的每个list的size不相同!");
}
if (values.get(i).size() == 0) {
throw new RuntimeException("参数values里面的任何一个list的size不能为0!");
}
size = values.get(i).size();
}
if (header.size() != values.get(0).size()) {
throw new RuntimeException("参数values里面的任何一个list的size与header的size不相同!");
}
if (null !=firstValues && firstValues.size() != values.size()) {
throw new RuntimeException("参数firstValues的size与values的size不相同!");
}
}
/**
* @Title getIndexByPage
* @Description：根据当前页码,每页显示条数,总条数,计算当前页的内容对应values的开始index和结束index
* @param pagenum:当前页码
* @param pageSize:每页显示条数
* @param total:总条数
* @return
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private List<Integer> getIndexByPage(int pagenum, int pageSize, int total) {
int sindex = 0;//开始角标
int eindex = 0;//结束角标
if (total<=pageSize) {
sindex = 0;
eindex = total;
return Arrays.asList(sindex,eindex);
}
int pages = total%pageSize==0?total/pageSize:(total/pageSize)+1;
if (pagenum == pages && total%pageSize!=0) {
sindex = (pagenum - 1)*pageSize;
eindex = sindex+total%pageSize;
return Arrays.asList(sindex,eindex);
}
sindex = (pagenum - 1)*pageSize;
eindex = sindex+pageSize;
return Arrays.asList(sindex,eindex);
}
/**
* @Fields firstRow : 默认是title,第一行，行标0，-1表示没有title行
*/
private int firstRowIndexOff = 0;//默认是0表示有title行，-1表示首行向上偏移一行，title行去除
/**
* @Title fillDate
* @Description：向Excel填充数据
* @param wb
* @param excelName
* @param title
* @param firstHeader
* @param firstValues
* @param header
* @param values
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void fillDate(HSSFWorkbook wb, String sheetName, boolean title, String firstHeader,
List<String> firstValues, List<String> header,
List<List<String>> values) {
int total = values.size();
int pages = total/PAGESIZE==0?1:(total%PAGESIZE==0?total/PAGESIZE:(total/PAGESIZE)+1);
for (int i = 1; i <= pages; i++) {
HSSFSheet sheet = wb.createSheet(sheetName+"-"+i);
initDefaultColumnStyle(wb, sheet, totalColumn);
if(title){
row0(wb, sheet, sheetName, header.size());
} else {
firstRowIndexOff = -1;
}
row1(wb, firstHeader, header, sheet);
List<Integer> pageinfo = getIndexByPage(i,PAGESIZE,total);
rown(firstValues, values, sheet, pageinfo);
}
}
/**
* @Title rown
* @Description：第三行,第四行...填充数据
* @param firstValues
* @param values
* @param sheet
* @param pageinfo
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void rown(List<String> firstValues,
List<List<String>> values, HSSFSheet sheet, List<Integer> pageinfo) {
for (int i = pageinfo.get(0); i < pageinfo.get(1); i++) {// 表格标题行对应的值(第三行,第四行...)
HSSFRow rown = sheet.createRow(i - pageinfo.get(0) + 2 + firstRowIndexOff);
for (int j = 0; j <= values.get(i).size(); j++) {
if (j != 0) {
rown.createCell(j).setCellValue(values.get(i).get(j - 1));
} else {
if (firstValues == null) {// 序号
rown.createCell(j).setCellValue(String.valueOf(i + 1));
} else {
rown.createCell(j).setCellValue(firstValues.get(i));
}
}
}
}
}
/**
* @Title row1
* @Description：第二行填充数据
* @param wb
* @param firstHeader
* @param header
* @param sheet
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void row1(HSSFWorkbook wb, String firstHeader, List<String> header,
HSSFSheet sheet) {
HSSFRow row1 = sheet.createRow(1 + firstRowIndexOff);
row1.setHeightInPoints(15);//设置行高20px
for (int i = 0; i <= header.size(); i++) {
if (i != 0) {
HSSFCell cell = row1.createCell(i);
cell.setCellStyle(getHeaderCellStyle(wb));
cell.setCellValue(header.get(i - 1));// 设置第二行,表格标题行
} else {
HSSFCell cell = row1.createCell(i);
cell.setCellStyle(getHeaderCellStyle(wb));
cell.setCellValue(firstHeader);// 设置第二行,表格标题行第一格
}
}
}
/**
* @Title initDefaultColumnStyle
* @Description：初始化默认样式
* @param wb
* @param sheet
* @param totalColumn
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void initDefaultColumnStyle(HSSFWorkbook wb, HSSFSheet sheet, int totalColumn) {
for (int i = 0; i < totalColumn; i++) {
if (specialColumn.containsKey(i)) {
sheet.setColumnWidth(i, specialColumn.get(i) * 512);// 需要特殊指定的列宽,512*几就表示几个汉字的宽度
} else {
sheet.setColumnWidth(i, defaultColumnWidth * 512);// 默认列宽,约10个汉字,512*几就表示几个汉字的宽度
}
if (specialColumnStyle.contains(i)) {
sheet.setDefaultColumnStyle(i, getSpecialValuesCellStyle(wb));
} else {
sheet.setDefaultColumnStyle(i, getValuesCellStyle(wb));
}
}
}
/**
* @Title row0
* @Description：第一行填充数据
* @param wb
* @param sheet
* @param excelName
* @param titleColspan
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private void row0(HSSFWorkbook wb, HSSFSheet sheet, String excelName,
int titleColspan) {
HSSFRow row0 = sheet.createRow(firstRowIndexOff);
row0.setHeightInPoints(20);//设置行高20px
HSSFCell cell = row0.createCell(0);
cell.setCellStyle(getTitleCellStyle(wb));
cell.setCellValue(excelName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColspan));// 设置第一行,标题行
}
/**
* @Title getTitleCellStyle
* @Description：定义title样式
* @param wb
* @return
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private HSSFCellStyle getTitleCellStyle(HSSFWorkbook wb) {//定义title样式
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 16);
font.setColor(FONT_COLOR);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
//cellStyle.setFillBackgroundColor(HSSFColor.CORAL.index);
return cellStyle;
}
/**
* @Title getHeaderCellStyle
* @Description：定义header的样式
* @param wb
* @return
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private HSSFCellStyle getHeaderCellStyle(HSSFWorkbook wb) {//定义header的样式
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
font.setFontHeightInPoints((short) 12);
font.setColor(FONT_COLOR);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
/**
* @Title getValuesCellStyle
* @Description：定义values的样式(居中对齐)
* @param wb
* @return
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private HSSFCellStyle getValuesCellStyle(HSSFWorkbook wb) {//定义values的样式
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 10);
font.setColor(FONT_COLOR);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
/**
* @Title getSpecialValuesCellStyle
* @Description：定义values的样式(左对齐)
* @param wb
* @return
* @user CP_biyongfei 2017-8-18
* @updater：
* @updateTime：
*/
private HSSFCellStyle getSpecialValuesCellStyle(HSSFWorkbook wb) {//定义values的样式
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 10);
font.setColor(FONT_COLOR);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
/**
* @ClassName ExcelUtilsFields
* @Description: 自定义注解
* @author CP_biyongfei
* @date 2017-8-31 下午3:50:58
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public static @interface ExcelExport {
/**
* @Title excelName
* @Description：导出Excel的文件名字，也是sheet的部分名字，如果title=true,也是首行title的名字
* @return
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
public String excelName();
/**
* @Title sheetName
* @Description：sheet的名字
* @return
* @user CP_biyongfei 2017年9月26日
* @updater：
* @updateTime：
*/
public String sheetName();
/**
* @Title title
* @Description：是否显示创建,文档标题行(第一行),默认为true,表示创建
* @return
* @user CP_biyongfei 2017年9月21日
* @updater：
* @updateTime：
*/
public boolean title() default true;
/**
* @Title firstHeader
* @Description：表格标题行(第二行，第一列)默认序号
* @return
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
public String firstHeader() default "序号";
/**
* @Title firstValues
* @Description：可以不赋值，不赋值即null。
* 表格标题行(第二行，第一列)，对应表格中第二行下面第一列的值
* @return
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
public String[] firstValues() default {};
/**
* @Title header
* @Description：表格标题行(第二行),导出的Excel将按照你填写的顺序
* @return
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
public String[] header();
/**
* @Title field
* @Description：List<T> 类型数据，T 对象fieldNames, 需要和header一一对应
* @return
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
public String[] fieldNames();
}
/**
* 多值时需要特殊指定的列宽的注解
* 多值时需要特殊指定的内容左对齐的列的注解
* @author bevis
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public static @interface SpecialColumn {
/**
* 定义格式如下："0-15,1-20,...,5-10"
* 表示第0列15个汉字宽,第1列20个汉字宽,...,第5列10个汉字宽（10号汉字）
* @return
*/
public String value() default "0-5";
/**
* 例如:居左对其，不设置默认居中
* columnLeft={0,1,7}，表示第0，1，7列居左对齐
*/
public int[] columnLeft() default {};
}
private List<Map<Integer,Integer>> mSpecialColumnWidth = new ArrayList<Map<Integer,Integer>>();
private List<List<Integer>> mSpecialColumnStyle = new ArrayList<List<Integer>>();
/**
* @Title export
* @Description：根据List<T> 数据格式，以及注解，导出Excel
* 注解使用示例：@@ExcelExport(title="文档标题",header={"标题一","标题二"},fieldNames={"field1","field2"})
* 此注解只能使用在导出的方法上，并且export(HttpServletResponse response, List<T> result)方法必须由该 导出方法 直接调用
* fieldNames，是T对象的field的名字的数组，导出后即header的标题一，标题二...对应的值
*
* 快速入门：
*
* package zz.cn.bevis.test;
*
* import cn.bevis.poi.ExcelExportUtils.ExcelExportUtilsAnnotation;
*
* @SpecialColumn(value="0-5,1-7",columnLeft={0,1,7}) 设置列宽以及居左对齐，可选
* @ExcelExport(excelName="测试文档",header={"班级英语","班级语文","班级数学"},fieldNames={"c","a","b"})
* public class Person {
* private String a = "语文85";
* private String b = "数学90";
* private String c = "英语99";
* public String getA() {
* return a;
* }
* public void setA(String a) {
* this.a = a;
* }
* public String getB() {
* return b;
* }
* public void setB(String b) {
* this.b = b;
* }
* public String getC() {
* return c;
* }
* public void setC(String c) {
* this.c = c;
* }
* @Override
* public String toString() {
* return "Person [a=" + a + ", b=" + b + ", c=" + c + "]";
* }
* }
*
* Servlet 请求示例
*
* package zz.cn.bevis.servlet;
*
* import java.io.IOException;
* import java.util.ArrayList;
* import java.util.List;
* import javax.servlet.ServletException;
* import javax.servlet.http.HttpServlet;
* import javax.servlet.http.HttpServletRequest;
* import javax.servlet.http.HttpServletResponse;
* import zz.cn.bevis.test.Person;
* import cn.bevis.poi.ExcelExportUtils;
*
* public class TestServlet extends HttpServlet {
*
* private static final long serialVersionUID = -8023831766863687042L;
*
* @Override
* protected void doGet(HttpServletRequest request,
* HttpServletResponse response) throws ServletException, IOException {
*
*
* List<Person> result = new ArrayList<Person>();
* result.add(new Person());
* ExcelExportUtils.getInstance().export(request,response,result);
*
* }
* }
*
* @param request
* @param response
* @param result
* @user CP_biyongfei 2017-8-31
* @updater：
* @updateTime：
*/
@SuppressWarnings({ "unused", "unchecked" })
private <T> void export(HttpServletRequest request, HttpServletResponse response, List<T> result) {
try {
if (null == result || result.size() <= 0) {
export(request, response, "查询无结果", "查询无结果", true, Arrays.asList("查询无结果"), Arrays.asList(Arrays.asList("查询无结果")));
} else {
if (null == response) {
throw new RuntimeException("参数response不能为null！");
}
//获取实体类上的注解
Annotation annotation = result.get(0).getClass().getAnnotation(ExcelExport.class);
ExcelExport ann = (ExcelExport)annotation;
if (null == ann) {
throw new RuntimeException("List<T> result T对象类上需要@ExcelExport注解！");
}
String excelName = ann.excelName();
String sheetName = ann.sheetName();
boolean title = ann.title();
String firstHeader = ann.firstHeader();
String[] firstValues = ann.firstValues();
String[] header = ann.header();
String[] fieldNames = ann.fieldNames();
List<List<String>> values = new ArrayList<List<String>>();
for (T t : result) {
List<String> line = new ArrayList<String>();
for (String fieldName : fieldNames) {
Field field = t.getClass().getDeclaredField(fieldName);
field.setAccessible(true);//对私有方法解除安全限制
Object object = field.get(t);
line.add(null==object?"":object.toString());
}
values.add(line);
}
export(request, response, excelName, sheetName, title, firstHeader, firstValues.length==0?null:Arrays.asList(firstValues), Arrays.asList(header), values);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @Description：多数据导出公用方法
* @param excelName
* :导出Excel的文件名字，也是sheet的部分名字
* @param titles
* :是否显示title行（集合）
* @param sheetNames（集合）
* :导出excel的sheetName定义
* @param mheader（集合）
* :表格标题行(第二行)
* @param mvalues（集合）
* :表格标题行对应的值(第三行,第四行...)
* @return
* @user CP_biyongfei 2017-7-18
* @updater：
* @updateTime：
*/
@SuppressWarnings("unused")
private void export(HttpServletRequest request, HttpServletResponse response, String excelName, List<Boolean> titles,
List<String> sheetNames, List<List<String>> mheader,List<List<List<String>>> mvalues) {
List<String> mfirstHeader = new ArrayList<String>();
List<List<String>> mfirstValues = new ArrayList<List<String>>();
for (int i = 0; i < sheetNames.size(); i++) {
mfirstHeader.add("序号");
mfirstValues.add(null);
}
export(request, response, excelName, titles, mfirstHeader, mfirstValues, sheetNames, mheader, mvalues);
}
/**
* @Description：多数据导出公用方法
* @param excelName
* :导出Excel的文件名字，也是sheet的部分名字
* @param titles
* :是否显示title行（集合）
* @param sheetNames（集合）
* :导出excel的sheetName定义
* @param mfirstHeader（集合）
* :序号
* @param mfirstValues（集合）
* :1,2...
* @param mheader（集合）
* :表格标题行(第二行)
* @param mvalues（集合）
* :表格标题行对应的值(第三行,第四行...)
* @return
* @user CP_biyongfei 2017-7-18
* @updater：
* @updateTime：
*/
private void export(HttpServletRequest request, HttpServletResponse response, String excelName, List<Boolean> titles,
List<String> mfirstHeader, List<List<String>> mfirstValues,
List<String> sheetNames, List<List<String>> mheader,List<List<List<String>>> mvalues) {
if(null == sheetNames || null == mfirstHeader || null == mfirstValues) {
throw new RuntimeException("sheetNames,mfirstHeader,mfirstValues不能为null!");
} else if(sheetNames.size() == 0 || sheetNames.size() != mheader.size()
|| sheetNames.size() != mvalues.size() || sheetNames.size() != titles.size()
|| sheetNames.size() != mfirstHeader.size() || sheetNames.size() != mfirstValues.size()) {
throw new RuntimeException("sheetNames.size()需要大于0并且sheetNames.size()需要等于mheader.size()且等于mvalues.size()且等于titles.size()且等于mfirstHeader.size()且等于mfirstValues.size()!");
}
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < mheader.size(); i++) {
String firstHeader = mfirstHeader.get(i);
List<String> firstValues = mfirstValues.get(i);
List<String> header = mheader.get(i);
List<List<String>> values = mvalues.get(i);
String sheetName = sheetNames.get(i);
if(StringUtils.isBlank(sheetName)) {
throw new RuntimeException("sheetName不能为null!");
}
validate(excelName, firstHeader, firstValues, header, values);
initAndValidateSpecialColumnWidth(header);
if(null!=mSpecialColumnWidth&&mSpecialColumnWidth.size()==mheader.size()){
specialColumn.clear();
specialColumn.putAll(mSpecialColumnWidth.get(i));
specialColumnStyle.clear();
specialColumnStyle.addAll(mSpecialColumnStyle.get(i));
}
fillDate(wb, sheetName, titles.get(i), firstHeader, firstValues, header, values);
}
try {
responseWriteExcel(request, response, excelName, wb);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private void export(String dirStr, String excelName, List<Boolean> titles,
List<String> mfirstHeader, List<List<String>> mfirstValues,
List<String> sheetNames, List<List<String>> mheader,List<List<List<String>>> mvalues) {
if(null == sheetNames || null == mfirstHeader || null == mfirstValues) {
throw new RuntimeException("sheetNames,mfirstHeader,mfirstValues不能为null!");
} else if(sheetNames.size() == 0 || sheetNames.size() != mheader.size()
|| sheetNames.size() != mvalues.size() || sheetNames.size() != titles.size()
|| sheetNames.size() != mfirstHeader.size() || sheetNames.size() != mfirstValues.size()) {
throw new RuntimeException("sheetNames.size()需要大于0并且sheetNames.size()需要等于mheader.size()且等于mvalues.size()且等于titles.size()且等于mfirstHeader.size()且等于mfirstValues.size()!");
}
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < mheader.size(); i++) {
String firstHeader = mfirstHeader.get(i);
List<String> firstValues = mfirstValues.get(i);
List<String> header = mheader.get(i);
List<List<String>> values = mvalues.get(i);
String sheetName = sheetNames.get(i);
if(StringUtils.isBlank(sheetName)) {
throw new RuntimeException("sheetName不能为null!");
}
validate(excelName, firstHeader, firstValues, header, values);
initAndValidateSpecialColumnWidth(header);
if(null!=mSpecialColumnWidth&&mSpecialColumnWidth.size()==mheader.size()){
specialColumn.clear();
specialColumn.putAll(mSpecialColumnWidth.get(i));
specialColumnStyle.clear();
specialColumnStyle.addAll(mSpecialColumnStyle.get(i));
}
fillDate(wb, sheetName, titles.get(i), firstHeader, firstValues, header, values);
}
try {
responseWriteExcel(dirStr, excelName, wb);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
@SuppressWarnings("rawtypes")
public static final class Resource {
private List<List> mresults = new ArrayList<List>();
private Resource(List ... results) {
for (List result : results) {
mresults.add(result);
}
}
public static final Resource getInstance(List ... results) {
return new Resource(results);
}
public final List<List> getResults() {
return mresults;
}
}
/**
* @Title export
* @Description：多数据导出注解方法
*
* @Description：根据List<T> 数据格式，以及注解，导出Excel
* 注解使用示例：@@ExcelExport(title="文档标题",header={"标题一","标题二"},fieldNames={"field1","field2"})
* 此注解只能使用在导出的方法上，并且export(HttpServletResponse response, List<T> result)方法必须由该 导出方法 直接调用
* fieldNames，是T对象的field的名字的数组，导出后即header的标题一，标题二...对应的值
*
* 快速入门：
*
* package zz.cn.bevis.test;
*
* import cn.bevis.poi.ExcelExportUtils.ExcelExportUtilsAnnotation;
*
* @SpecialColumn(value="0-5,1-7",columnLeft={0,1,7}) 设置列宽以及居左对齐，可选
* @ExcelExport(excelName="测试文档",header={"班级英语","班级语文","班级数学"},fieldNames={"c","a","b"})
* public class Person {
* private String a = "语文85";
* private String b = "数学90";
* private String c = "英语99";
* public String getA() {
* return a;
* }
* public void setA(String a) {
* this.a = a;
* }
* public String getB() {
* return b;
* }
* public void setB(String b) {
* this.b = b;
* }
* public String getC() {
* return c;
* }
* public void setC(String c) {
* this.c = c;
* }
* @Override
* public String toString() {
* return "Person [a=" + a + ", b=" + b + ", c=" + c + "]";
* }
* }
*
* Servlet 请求示例
*
* package zz.cn.bevis.servlet;
*
* import java.io.IOException;
* import java.util.ArrayList;
* import java.util.List;
* import javax.servlet.ServletException;
* import javax.servlet.http.HttpServlet;
* import javax.servlet.http.HttpServletRequest;
* import javax.servlet.http.HttpServletResponse;
* import zz.cn.bevis.test.Person;
* import cn.bevis.poi.ExcelExportUtils;
* import cn.bevis.poi.ExcelExportUtils.Resource;
*
* public class TestServlet extends HttpServlet {
*
* private static final long serialVersionUID = -8023831766863687042L;
*
* @Override
* protected void doGet(HttpServletRequest request,
* HttpServletResponse response) throws ServletException, IOException {
*
*
* List<Person> result = new ArrayList<Person>();
* result.add(new Person());
*
* Resource resource = new Resource().getInstance(result);//一个或多个list都支持
* ExcelExportUtils.getInstance().export(request,response,resource);
*
* }
* }
*
* @param results
* @param request
* @param response
* @user CP_biyongfei 2017年9月26日
* @updater：
* @updateTime：
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void export(HttpServletRequest request, HttpServletResponse response, Resource mres) {
String mexcelName = "";
List<Boolean> titles = new ArrayList<Boolean>();
List<String> sheetNames = new ArrayList<String>();
List<String> mfirstHeader = new ArrayList<String>();
List<List<String>> mfirstValues = new ArrayList<List<String>>();
List<List<String>> mheader = new ArrayList<List<String>>();
List<List<List<String>>> mvalues = new ArrayList<List<List<String>>>();
try {
List<List> results = mres.getResults();
for (int i = 0; i < results.size(); i++) {
List result = results.get(i);
if (null == result || result.size() <= 0) {
if(i != (results.size()-1)) {
titles.add(true);
sheetNames.add("查询无结果-"+i);
mfirstHeader.add("序号");
mfirstValues.add(null);
mheader.add(Arrays.asList("查询无结果"));
mvalues.add(Arrays.asList(Arrays.asList("查询无结果")));
} else {
titles.add(true);
sheetNames.add("查询无结果-"+i);
mfirstHeader.add("序号");
mfirstValues.add(null);
mheader.add(Arrays.asList("查询无结果"));
mvalues.add(Arrays.asList(Arrays.asList("查询无结果")));
mexcelName = StringUtils.isBlank(mexcelName)?"查询无结果":mexcelName;
}
} else {
if (null == response) {
throw new RuntimeException("参数response不能为null！");
}
//获取实体类上的注解
Annotation annotation = result.get(0).getClass().getAnnotation(ExcelExport.class);
ExcelExport ann = (ExcelExport)annotation;
if (null == ann) {
throw new RuntimeException("List<T> result T对象类上需要@ExcelExport注解！");
}
//处理需要特殊指定的列宽
Annotation specannotation = result.get(0).getClass().getAnnotation(SpecialColumn.class);
SpecialColumn specann = (SpecialColumn)specannotation;
if (null != specann) {
Map<Integer, Integer> specwidthmap = new HashMap<Integer, Integer>();
String specvalue = specann.value();
if (StringUtils.isNotBlank(specvalue)) {
String[] specmap = specvalue.split(",");
for (String spm : specmap) {
String[] sma = spm.split("-");
specwidthmap.put(Integer.valueOf(sma[0]), Integer.valueOf(sma[1]));
}
mSpecialColumnWidth.add(specwidthmap);
} else {
mSpecialColumnWidth.add(new HashMap<Integer,Integer>());
}
int[] specLeft = specann.columnLeft();
List<Integer> specLeftList = new ArrayList<Integer>();
for (Integer spi : specLeft) {
specLeftList.add(spi);
}
mSpecialColumnStyle.add(specLeftList);
} else {
mSpecialColumnWidth.add(new HashMap<Integer,Integer>());
mSpecialColumnStyle.add(new ArrayList<Integer>());
}
//处理excel相关数据
String excelName = ann.excelName();
if(i != 0 && StringUtils.isNotBlank(excelName) && !excelName.equals(mexcelName)) {
throw new RuntimeException("List<T> result T对象类上@ExcelExport注解excelName的值不一致");
} else {
if(StringUtils.isBlank(excelName)) {
throw new RuntimeException("List<T> result T对象类上@ExcelExport注解excelName不能为空");
}
}
mexcelName = excelName;
String sheetName = ann.sheetName();
if(StringUtils.isBlank(sheetName)) {
throw new RuntimeException("List<T> result T对象类上需要@ExcelExport注解！sheetName需要指定");
}
sheetNames.add(sheetName);
boolean title = ann.title();
titles.add(title);
String firstHeader = ann.firstHeader();
mfirstHeader.add(firstHeader);
String[] firstValues = ann.firstValues();
if(firstValues.length == 0) {
mfirstValues.add(null);
} else {
mfirstValues.add(Arrays.asList(firstValues));
}
String[] header = ann.header();
mheader.add(Arrays.asList(header));
String[] fieldNames = ann.fieldNames();
List<List<String>> values = new ArrayList<List<String>>();
for (Object t : result) {
List<String> line = new ArrayList<String>();
for (String fieldName : fieldNames) {
Field field = t.getClass().getDeclaredField(fieldName);
field.setAccessible(true);//对私有方法解除安全限制
Object object = field.get(t);
if(object instanceof Date) {
line.add(null==object?"":new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
} else {
line.add(null==object?"":object.toString());
}
}
values.add(line);
}
mvalues.add(values);
}
}
export(request, response, mexcelName, titles, mfirstHeader, mfirstValues, sheetNames, mheader, mvalues);
} catch (Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public void export(String dirStr, Resource mres) {
String mexcelName = "";
List<Boolean> titles = new ArrayList<Boolean>();
List<String> sheetNames = new ArrayList<String>();
List<String> mfirstHeader = new ArrayList<String>();
List<List<String>> mfirstValues = new ArrayList<List<String>>();
List<List<String>> mheader = new ArrayList<List<String>>();
List<List<List<String>>> mvalues = new ArrayList<List<List<String>>>();
try {
List<List> results = mres.getResults();
for (int i = 0; i < results.size(); i++) {
List result = results.get(i);
if (null == result || result.size() <= 0) {
if(i != (results.size()-1)) {
titles.add(true);
sheetNames.add("查询无结果-"+i);
mfirstHeader.add("序号");
mfirstValues.add(null);
mheader.add(Arrays.asList("查询无结果"));
mvalues.add(Arrays.asList(Arrays.asList("查询无结果")));
} else {
titles.add(true);
sheetNames.add("查询无结果-"+i);
mfirstHeader.add("序号");
mfirstValues.add(null);
mheader.add(Arrays.asList("查询无结果"));
mvalues.add(Arrays.asList(Arrays.asList("查询无结果")));
mexcelName = StringUtils.isBlank(mexcelName)?"查询无结果":mexcelName;
}
} else {
//获取实体类上的注解
Annotation annotation = result.get(0).getClass().getAnnotation(ExcelExport.class);
ExcelExport ann = (ExcelExport)annotation;
if (null == ann) {
throw new RuntimeException("List<T> result T对象类上需要@ExcelExport注解！");
}
//处理需要特殊指定的列宽
Annotation specannotation = result.get(0).getClass().getAnnotation(SpecialColumn.class);
SpecialColumn specann = (SpecialColumn)specannotation;
if (null != specann) {
Map<Integer, Integer> specwidthmap = new HashMap<Integer, Integer>();
String specvalue = specann.value();
if (StringUtils.isNotBlank(specvalue)) {
String[] specmap = specvalue.split(",");
for (String spm : specmap) {
String[] sma = spm.split("-");
specwidthmap.put(Integer.valueOf(sma[0]), Integer.valueOf(sma[1]));
}
mSpecialColumnWidth.add(specwidthmap);
} else {
mSpecialColumnWidth.add(new HashMap<Integer,Integer>());
}
int[] specLeft = specann.columnLeft();
List<Integer> specLeftList = new ArrayList<Integer>();
for (Integer spi : specLeft) {
specLeftList.add(spi);
}
mSpecialColumnStyle.add(specLeftList);
} else {
mSpecialColumnWidth.add(new HashMap<Integer,Integer>());
mSpecialColumnStyle.add(new ArrayList<Integer>());
}
//处理excel相关数据
String excelName = ann.excelName();
if(i != 0 && StringUtils.isNotBlank(excelName) && !excelName.equals(mexcelName)) {
throw new RuntimeException("List<T> result T对象类上@ExcelExport注解excelName的值不一致");
} else {
if(StringUtils.isBlank(excelName)) {
throw new RuntimeException("List<T> result T对象类上@ExcelExport注解excelName不能为空");
}
}
mexcelName = excelName;
String sheetName = ann.sheetName();
if(StringUtils.isBlank(sheetName)) {
throw new RuntimeException("List<T> result T对象类上需要@ExcelExport注解！sheetName需要指定");
}
sheetNames.add(sheetName);
boolean title = ann.title();
titles.add(title);
String firstHeader = ann.firstHeader();
mfirstHeader.add(firstHeader);
String[] firstValues = ann.firstValues();
if(firstValues.length == 0) {
mfirstValues.add(null);
} else {
mfirstValues.add(Arrays.asList(firstValues));
}
String[] header = ann.header();
mheader.add(Arrays.asList(header));
String[] fieldNames = ann.fieldNames();
List<List<String>> values = new ArrayList<List<String>>();
for (Object t : result) {
List<String> line = new ArrayList<String>();
for (String fieldName : fieldNames) {
Field field = t.getClass().getDeclaredField(fieldName);
field.setAccessible(true);//对私有方法解除安全限制
Object object = field.get(t);
if(object instanceof Date) {
line.add(null==object?"":new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
} else {
line.add(null==object?"":object.toString());
}
}
values.add(line);
}
mvalues.add(values);
}
}
export(dirStr, mexcelName, titles, mfirstHeader, mfirstValues, sheetNames, mheader, mvalues);
} catch (Exception e) {
e.printStackTrace();
}
}
}