前言
1.jeesite 自带的 excel 工具类默认导出一行表头
2.统计业务属性过多,全部导出一行表头,数据冗余

3.工具类扩展后,导出复合表头

正文
1.@ExcelField 注解扩展属性:subtitle
package com.thinkgem.jeesite.common.utils.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
String value() default "";
String title();
String subtitle() default "";
int type() default 0;
int align() default 0;
int sort() default 0;
String dictType() default "";
Class<?> fieldType() default Class.class;
int[] groups() default {};
}
2.ExportExcel 工具类扩展(扩展代码-注释查找 复合表头扩展)
package com.thinkgem.jeesite.common.utils.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
public class ExportExcel {
private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
private SXSSFWorkbook wb;
private Sheet sheet;
private Map<String, CellStyle> styles;
private int rownum;
List<Object[]> annotationList = Lists.newArrayList();
public ExportExcel(String title, Class<?> cls){
this(title, cls, 1);
}
public ExportExcel(String title, Class<?> cls, int type, int... groups){
Field[] fs = cls.getDeclaredFields();
for (Field f : fs){
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==type)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, f});
}
}
}
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms){
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==type)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, m});
}
}
}
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
List<String> headerList = Lists.newArrayList();
List<String> subheaderList = Lists.newArrayList();
for (Object[] os : annotationList){
String t = ((ExcelField)os[0]).title();
if (type==1){
String[] ss = StringUtils.split(t, "**", 2);
if (ss.length==2){
t = ss[0];
}
}
headerList.add(t);
String subt = ((ExcelField)os[0]).subtitle();
if(subt!=null&&!subt.equals("")){
subheaderList.add(subt);
}
}
if(subheaderList.size()==0){
initialize(title, headerList);
}else{
initialize(title, headerList,subheaderList);
}
}
public ExportExcel(String title, String[] headers) {
initialize(title, Lists.newArrayList(headers));
}
public ExportExcel(String title, List<String> headerList) {
initialize(title, headerList);
}
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet(title);
this.styles = createStyles(wb);
if (StringUtils.isNotBlank(title)){
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
}
if (headerList == null){
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length==2){
cell.setCellValue(ss[0]);
Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
}else{
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i)*2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
private void initialize(String title, List<String> headerList,List<String> subheaderList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet(title);
this.styles = createStyles(wb);
if (StringUtils.isNotBlank(title)){
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
}
if (headerList == null){
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("composite_header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length==2){
cell.setCellValue(ss[0]);
Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
}else{
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i)*2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
if (subheaderList == null || subheaderList.size()==0){
return;
}
Row subheaderRow = sheet.createRow(rownum++);
subheaderRow.setHeightInPoints(16);
for (int i = 0; i < subheaderList.size(); i++) {
Cell cell = subheaderRow.createCell(i);
cell.setCellStyle(styles.get("composite_header"));
cell.setCellValue(subheaderList.get(i));
sheet.autoSizeColumn(i);
}
for (int i = 0; i < subheaderList.size(); i++) {
int colWidth = sheet.getColumnWidth(i)*2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font compositeHeaderFont = wb.createFont();
compositeHeaderFont.setFontName("Arial");
compositeHeaderFont.setFontHeightInPoints((short) 10);
compositeHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
compositeHeaderFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(compositeHeaderFont);
styles.put("composite_header", style);
return styles;
}
public Row addRow(){
return sheet.createRow(rownum++);
}
public Cell addCell(Row row, int column, Object val){
return this.addCell(row, column, val, 0, Class.class);
}
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
Cell cell = row.createCell(column);
String cellFormatString = "@";
try {
if(val == null){
cell.setCellValue("");
}else if(fieldType != Class.class){
cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
}else{
if(val instanceof String) {
cell.setCellValue((String) val);
}else if(val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString = "0";
}else if(val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString = "0";
}else if(val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString = "0.00";
}else if(val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString = "0.00";
}else if(val instanceof Date) {
cell.setCellValue((Date) val);
cellFormatString = "yyyy-MM-dd HH:mm";
}else {
cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
}
}
if (val != null){
CellStyle style = styles.get("data_column_"+column);
if (style == null){
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
styles.put("data_column_" + column, style);
}
cell.setCellStyle(style);
}
} catch (Exception ex) {
log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
cell.setCellValue(val.toString());
}
return cell;
}
public <E> ExportExcel setDataList(List<E> list){
for (E e : list){
int colunm = 0;
Row row = this.addRow();
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
ExcelField ef = (ExcelField)os[0];
Object val = null;
try{
if (StringUtils.isNotBlank(ef.value())){
val = Reflections.invokeGetter(e, ef.value());
}else{
if (os[1] instanceof Field){
val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
}else if (os[1] instanceof Method){
val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
}
}
if (StringUtils.isNotBlank(ef.dictType())){
val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
}
}catch(Exception ex) {
log.info(ex.toString());
val = "";
}
this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
sb.append(val + ", ");
}
log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
}
return this;
}
public ExportExcel write(OutputStream os) throws IOException{
wb.write(os);
return this;
}
public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
write(response.getOutputStream());
return this;
}
public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
FileOutputStream os = new FileOutputStream(name);
this.write(os);
return this;
}
public ExportExcel dispose(){
wb.dispose();
return this;
}
public ExportExcel mergeCell(List<String> param){
for (int i = 0; i < param.size(); i++) {
String[] temp = param.get(i).split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
this.sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
}
return this;
}
}
3.实体类 PayInfo
package com.thinkgem.jeesite.modules.demo.entity;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
public class PayInfo {
private String date;
private Integer jdCount;
private Integer tbCount;
private Integer pddCount;
private String jdMoney;
private String tbMoney;
private String pddMoney;
@ExcelField(title="时间",subtitle="时间", align=1,sort=1)
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
@ExcelField(title="订单数量",subtitle="京东", align=1,sort=2)
public Integer getJdCount() {
return jdCount;
}
public void setJdCount(Integer jdCount) {
this.jdCount = jdCount;
}
@ExcelField(title="订单数量",subtitle="淘宝", align=1,sort=3)
public Integer getTbCount() {
return tbCount;
}
public void setTbCount(Integer tbCount) {
this.tbCount = tbCount;
}
@ExcelField(title="订单数量",subtitle="拼多多", align=1,sort=4)
public Integer getPddCount() {
return pddCount;
}
public void setPddCount(Integer pddCount) {
this.pddCount = pddCount;
}
@ExcelField(title="总金额",subtitle="京东", align=1,sort=5)
public String getJdMoney() {
return jdMoney;
}
public void setJdMoney(String jdMoney) {
this.jdMoney = jdMoney;
}
@ExcelField(title="总金额",subtitle="淘宝", align=1,sort=6)
public String getTbMoney() {
return tbMoney;
}
public void setTbMoney(String tbMoney) {
this.tbMoney = tbMoney;
}
@ExcelField(title="总金额",subtitle="拼多多", align=1,sort=7)
public String getPddMoney() {
return pddMoney;
}
public void setPddMoney(String pddMoney) {
this.pddMoney = pddMoney;
}
@Override
public String toString() {
return "PayInfo [date=" + date + ", jdCount=" + jdCount + ", tbCount=" + tbCount + ", pddCount=" + pddCount
+ ", jdMoney=" + jdMoney + ", tbMoney=" + tbMoney + ", pddMoney=" + pddMoney + "]";
}
}
4.控制器 controller
package com.thinkgem.jeesite.modules.demo.web;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import com.thinkgem.jeesite.common.utils.excel.ExportExcel;
import com.thinkgem.jeesite.common.web.BaseController;
import com.thinkgem.jeesite.modules.demo.entity.PayInfo;
@Controller
@RequestMapping(value = "${adminPath}/pay")
public class PayInfoController extends BaseController{
@RequestMapping(value = "export")
public String export(HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = "复合表头统计.xlsx";
ExportExcel excel = new ExportExcel("线上消费统计", PayInfo.class);
List<String> mergeCell = new ArrayList<String>();
mergeCell.add("1,2,0,0");
mergeCell.add("1,1,1,3");
mergeCell.add("1,1,4,6");
excel.mergeCell(mergeCell);
List<PayInfo> list = new ArrayList<PayInfo>();
PayInfo item1=new PayInfo("2019-11-08", 1, 2, 3, "100", "200", "300");
PayInfo item2=new PayInfo("2019-11-09", 1, 2, 3, "100", "200", "300");
list.add(item1);
list.add(item2);
excel.setDataList(list).write(response, fileName).dispose();
return null;
} catch (Exception e) {
e.printStackTrace();
addMessage(redirectAttributes, "导出失败!失败信息:"+e.getMessage());
}
return "";
}
}
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于