最近遇到一个需求,生成execl还要给特别的字符标注一下颜色。发现easyPOi完全没有颜色标注这个功能。
就写了个工具类。废话不多说直接上代码吧。
想引入poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
1、核心类 ExcelUtil
package com.information.platform.controller.execl;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSON;
import com.information.platform.utils.DateUtils;
import net.sf.json.JSONArray;
/**
*
* @Description: excel导出封装类
* @author G
* @date 2016年8月24日
*/
public class ExcelUtil {
private static final short height = 30*20;
private static final short height_A = 60*20;
// 生成excel,list导出的数据,list里的实体class,sumData合计数据
public static <Q> XSSFWorkbook createExcel(List<Q> list, Class<Q> cls, Q sumData,XSSFWorkbook wb,String sheetname,String title,String time)
throws IOException, IllegalArgumentException, IllegalAccessException {
Field[] fields = cls.getDeclaredFields();
ArrayList<String> headList = new ArrayList<String>();
// 添加合计数据
if (sumData != null) {
list.add(sumData);
}
for (Field f : fields) {
ExcelField field = f.getAnnotation(ExcelField.class);
if (field != null) {
headList.add(field.title());
}
//list集合
ExcelCollection annotation = f.getAnnotation(ExcelCollection.class);
if(annotation!=null) {
Class<?> cls2 = annotation.cls();
int size = annotation.size();
for (int i = 0; i < size; i++) {
Field[] declaredFields = cls2.getDeclaredFields();
for (Field f2 : declaredFields) {
ExcelField ffs = f2.getAnnotation(ExcelField.class);
if (ffs != null) {
headList.add(ffs.title());
}
}
}
}
}
XSSFCellStyle style = getCellStyle(wb);
XSSFCellStyle style1 = getCellStyles(wb);
XSSFSheet sheet = wb.createSheet();
int numberOfSheets = wb.getNumberOfSheets();
wb.setSheetName(numberOfSheets-1, sheetname);
sheet.createFreezePane( 0, 3, 0, 3);
// 设置Excel表的第一行即表头
XSSFRow createRow = sheet.createRow(0);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, headList.size()-1);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(region);
createRow.setHeight(height_A);
for (int i = 0; i < headList.size(); i++) {
XSSFCell headCell1 = createRow.createCell(i);
if(i==0) {
headCell1.setCellValue(title);
headCell1.setCellStyle(style1);// 设置表头样式
}
headCell1.setCellStyle(style1);// 设置表头样式
}
XSSFRow createRow2 = sheet.createRow(1);
CellRangeAddress regiona = new CellRangeAddress(1, 1, 0, headList.size()-1);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(regiona);
for (int i = 0; i < headList.size(); i++) {
XSSFCell headCell2 = createRow2.createCell(i);
if(i==0) {
headCell2.setCellValue(time);
}
headCell2.setCellStyle(style1);// 设置表头样式
createRow2.setHeight(height);
}
//栏目
XSSFRow row = sheet.createRow(2);
for (int i = 0; i < headList.size(); i++) {
XSSFCell headCell = row.createCell(i);
row.setHeight(height);
headCell.setCellType(Cell.CELL_TYPE_STRING);
headCell.setCellStyle(style);// 设置表头样式
headCell.setCellValue(String.valueOf(headList.get(i)));
// sheet.autoSizeColumn((short) i);// 设置单元格自适应
sheet.setColumnWidth(i, (int)(headList.get(i).getBytes().length * 1.2d * 256 > 12 * 256 ? headList.get(i).getBytes().length * 1.2d * 400 : 12 * 400));
}
for (int i = 0; i < list.size(); i++) {
XSSFRow rowdata = sheet.createRow(i + 3);// 创建数据行
rowdata.setHeight(height);
Q q = list.get(i);
Field[] ff = q.getClass().getDeclaredFields();
int j = 0;
for (Field f : ff) {
ExcelField field = f.getAnnotation(ExcelField.class);
ExcelCollection annotation = f.getAnnotation(ExcelCollection.class);
//list集合
if(annotation!=null) {
f.setAccessible(true);
List<Q> object = (ArrayList) f.get(q);
for (int k=0;k<object.size();k++) {
Q s = object.get(k);
Field[] declaredFields = s.getClass().getDeclaredFields();
for (Field field2 : declaredFields) {
ExcelField fie = field2.getAnnotation(ExcelField.class);
if (field2 == null) {
continue;
}
/* field2.setAccessible(true);
Object object2 = field2.get(s);
XSSFCell cell = rowdata.createCell(j);
XSSFCellStyle styles = getBaseCellStyle(wb);
cell.setCellValue(String.valueOf(object2));
cell.setCellStyle(styles);//
j++;*/
boolean creatTable = creatTable(fie, field2, rowdata, s, j, wb, style1);
if(!creatTable) {
continue;
}
j++;
}
}
//
}
boolean creatTable = creatTable(field, f, rowdata, q, j, wb, style1);
if(!creatTable) {
continue;
}
j++;
//
}
}
if (sumData != null) {
int rowIndex = list.size();
XSSFRow sumRow = sheet.getRow(rowIndex);
XSSFCell sumCell = sumRow.getCell(0);
sumCell.setCellStyle(style);
sumCell.setCellValue("合计");
}
return wb;
}
public static <Q> boolean creatTable( ExcelField field ,Field f, XSSFRow rowdata, Q q,int j,XSSFWorkbook wb,XSSFCellStyle style) throws IllegalArgumentException, IllegalAccessException {
if (field == null) {
return false;
}
f.setAccessible(true);
Object obj = f.get(q);
XSSFCell cell = rowdata.createCell(j);
XSSFCellStyle styles = getBaseCellStyle(wb);
if(StringUtils.isNotBlank(field.style())) {
String replace = field.style();
String[] split = replace.split(",");
for (String str : split) {
String[] split2 = str.split("_");
if(String.valueOf(obj).equals(split2[0])) {
if(split2[1].equals("red")) {
Font font = wb.createFont();
font.setColor(HSSFColor.RED.index);
styles.setFont(font);
cell.setCellStyle(styles);//
}else {
}
}
}
/**/
}else {
Font font = wb.createFont();
font.setColor(HSSFColor.GREY_80_PERCENT.index);
styles.setFont(font);
cell.setCellStyle(styles);//
}
//设置背景色
if(StringUtils.isNotBlank(field.background())) {
String replace = field.background();
String[] split = replace.split(",");
for (String str : split) {
String[] split2 = str.split("_");
if(String.valueOf(obj).equals(split2[0])) {
if(split2[1].equals("red")) {
//背景色
styles.setFillForegroundColor(HSSFColor.CORAL.index);
styles.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(styles);//
}else if(split2[1].equals("blur")){
styles.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
styles.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(styles);//
}
}
}
/**/
}else {
styles.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(styles);//
}
cell.setCellType(Cell.CELL_TYPE_STRING);
// 当数字时
if (obj instanceof Integer) {
if(StringUtils.isNotBlank(field.replace())) {
String replace = field.replace();
String[] split = replace.split(",");
for (String str : split) {
String[] split2 = str.split("_");
if(((Integer)obj).equals(Integer.valueOf(split2[1]))) {
cell.setCellValue(split2[0]);
}
}
}else {
cell.setCellValue((Integer)obj);
}
// 将序号替换为123456
/*if (j == 0)
cell.setCellValue(i+ 1);*/
}
// 当为字符串时
else if (obj instanceof String)
cell.setCellValue((String)obj);
// 当为布尔时
else if (obj instanceof Boolean)
cell.setCellValue((Boolean)obj);
// 当为时间时
else if (obj instanceof Date)
if(field.format()!=null) {
cell.setCellValue(DateUtils.getymdhms((Date)obj,field.format()));
}else {
cell.setCellValue(DateUtils.getymdhms((Date)obj));
}
// 当为时间时
else if (obj instanceof Calendar)
cell.setCellValue((Calendar)obj);
// 当为小数时
else if (obj instanceof Double)
cell.setCellValue((Double)obj);
return true;
}
public static void creatWorkBook() {
XSSFWorkbook wb = new XSSFWorkbook();
}
// 导出
public static void writeExcel(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
response.setHeader("filename",fileName);
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "No-cache");
response.setDateHeader("Expires", 0);
OutputStream ouputStream = null;
try {
ouputStream = response.getOutputStream();
wb.write(ouputStream);
} finally {
ouputStream.close();
}
}
/**
* 基础样式
*
* @return
*/
private static XSSFCellStyle getBaseCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
// 表头样式
public static XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
XSSFCellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)12);// 设置字体大小
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置自动换行
style.setWrapText(true);
style.setFont(font);
return style;
}
// 表头样式
public static XSSFCellStyle getCellStyles(XSSFWorkbook wb) {
XSSFCellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)14);// 设置字体大小
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//背景色
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置自动换行
style.setWrapText(true);
style.setFont(font);
return style;
}
}
2、然后倒入注解类两个
package com.information.platform.controller.execl;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* @Description: excel导出注解类
* @author kang
* @date 2016年8月24日
*/
@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField
{
//导出字段在excel中的名字
String title();
/**
*
*
* 例如:红色:缺卡_0xa, 黑色:正常_2
* @return
*/
String style() default "";
/**
* 枚举
*
* 例如:男_1,女_2
*
* @return
*/
String replace() default "";
/**
*
* Default @see com.alibaba.TypeUtil
* if default is not meet you can set format
* 只对date生效
*
* @return format
*/
String format() default "";
/**
*
*
* 例如:红色:缺卡_0xa, 蓝色:正常_2
* @return
*/
String background() default "";
}
package com.information.platform.controller.execl;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* @Description: excel导出注解类
* @author kang
* @date 2016年8月24日
*/
@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCollection
{
//导出字段在excel中的名字
String title();
/**
* 当前的class
* @return
*/
Class<?> cls() ;
/**
* list的size
* @return
*/
int size() default 0;
}
3、实体类
package com.information.platform.controller.execl.original;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.information.platform.controller.execl.ExcelCollection;
import com.information.platform.controller.execl.ExcelField;
public class DayOrigExecl {
/**
* 姓名
*/
@ExcelField(title="姓名")
private String name;
/**
* 班次
*/
@ExcelField(title="班次")
private String shifts;
/**
* 打卡时间
*/
/*@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@ExcelField(title="打卡时间",format = "yyyy-MM-dd HH:mm:ss" )*/
private Date clock;
/**
* 打卡结果
*/
//@ExcelField(title="打卡结果" ,background="缺卡_red,正常_blur")//,style="缺卡_red"
private String type;
/**
* 坐标
*/
@ExcelField(title="坐标")
private String coordinate;
/**
* 打卡设备
*/
@ExcelField(title="打卡设备")
private String equipment;
/**
* 打卡地址
*/
@ExcelField(title="打卡地址")
private String address;
/**
* 0上午 1下午
*/
//@ExcelField(title="时间段",replace = "上午_0,下午_1")
private Integer noon;
/**
* 考勤时间
*/
@ExcelField(title="考勤时间", format = "yyyy-MM-dd EEE" )
private Date attendanceTime;
//是否工作日 0 上班 1周末 2节假日
@ExcelField(title="是否工作日",replace = "上班_0,休息_1,放假_2",style="1_red,2_red")
private Integer workingDay;
private Date createtime;
private Integer userid;
@ExcelCollection(title="列表",cls=NoonOrigExecl.class,size=2)
@JsonIgnore
private List<NoonOrigExecl> noonExecls=new ArrayList<NoonOrigExecl>();
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public List<NoonOrigExecl> getNoonExecls() {
return noonExecls;
}
public void setNoonExecls(List<NoonOrigExecl> noonExecls) {
this.noonExecls = noonExecls;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getShifts() {
return shifts;
}
public void setShifts(String shifts) {
this.shifts = shifts;
}
public Date getClock() {
return clock;
}
public void setClock(Date clock) {
this.clock = clock;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getCoordinate() {
return coordinate;
}
public void setCoordinate(String coordinate) {
this.coordinate = coordinate;
}
public String getEquipment() {
return equipment;
}
public void setEquipment(String equipment) {
this.equipment = equipment;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getNoon() {
return noon;
}
public void setNoon(Integer noon) {
this.noon = noon;
}
public Date getAttendanceTime() {
return attendanceTime;
}
public void setAttendanceTime(Date attendanceTime) {
this.attendanceTime = attendanceTime;
}
public Integer getWorkingDay() {
return workingDay;
}
public void setWorkingDay(Integer workingDay) {
this.workingDay = workingDay;
}
}
4、导出main方法
public static void main(String[] args) throws IllegalArgumentException, IllegalAccessException, IOException {
XSSFWorkbook wb =new XSSFWorkbook();
List list=new ArrayList();
DayExecl dayExecl = new DayExecl();
dayExecl.setName(“张三”);
dayExecl.setNoon(0);
list.add(dayExecl);
ExcelUtil.createExcel(list, DayExecl.class, null,wb,“月度汇总”,"月度汇总 ",“报表生成时间:”+DateUtils.getymdhms(new Date()));
}
DateUtils
/**
- 将date类型转换为String
- @param time
- @return
/
public static String getymdhms(Date time) {
return new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”).format(time);
}
/* - 将date类型转换为String
- @param time
- @return
*/
public static String getymdhms(Date time,String format) {
return new SimpleDateFormat(format).format(time);
}
导出样式