一.怎么定义通用工具?
具体做法如下
- 定义注解类
- 导出工具类
二.怎么样实现？
- 注解类
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
/**
* excel列表注解，包括头，体，尾
* 头，体，尾只有加粗不加粗差别
* @author lilong
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
/**
* 表头列名，必填
* @return
*/
public String title() default "";
/**
* 字段位置 第几个
* @return
*/
public int order() default Integer.MIN_VALUE;
/**
* 列宽度 默认25
* @return
*/
public int width() default 25;
/**
* 自适应宽度 默认true
* 注：自适应优先级高于固定宽度
* @return
*/
public boolean autoWidth() default true;
/**
* 内容数据类型，目前只支持String.class和Double.class
* 注：如果不是double那就是String
* @return
*/
public Class<?> dataType() default String.class;
/**
* 文字样式 默认居左
* 其他风格查看CellStyle类
* @return
*/
public short align() default CellStyle.ALIGN_LEFT;
/**
* 单元格是否需要边框 环绕包围 默认false
* @return
*/
public boolean border() default false;
/**
* 单元格背景色 默认白色 HSSFColor.WHITE.index
* 还可以支持WHITE,BLACK,BLUE,RED,YELLOW等，具体可以查看HSSFColor类
* @return
*/
public short styleColor() default HSSFColor.WHITE.index;
/**
* 字体颜色 默认黑色 HSSFColor.BLACK.index
* 还可以支持BLACK,BLUE,RED,YELLOW等，具体可以查看HSSFColor类
* @return
*/
public short fontColor() default HSSFColor.BLACK.index;
/**
* 字体大小 默认12
* @return
*/
public short fontSize() default 12;
/**
* 字体 默认微软雅黑
* @return
*/
public String fontName() default "微软雅黑";
/**
* 头字体是否加粗及title内容 默认不加粗
* @return
*/
public boolean headBold() default true;
/**
* 体字体是否加粗及数据内容 默认不加粗
* @return
*/
public boolean bodyBold() default false;
/**
* 尾字体是否加粗及最后一行数据 默认不加粗
* @return
*/
public boolean tailBold() default false;
}
- 工具类
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.ConvertUtils;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 导出对象T集合
* 注：不支持导出属性来源于父类
* @author lilong
*
* @param <T>
*/
public class ExcelUtil {
/**
* 通过response导出excel,单个sheet,包含汇总行
* @param fileName 文件名称
* @param sheetName sheet名称
* @param data 数据
* @param response
*/
public static <T> void exportAndTotal(String fileName,String sheetName,List<T> data,HttpServletResponse response) {
Map<String, List<T>> datas = new HashMap<>();
datas.put(sheetName, data);
exportAndTotal(fileName, datas, response);
}
/**
* 通过response导出excel,单个sheet,不包含汇总行
* @param fileName 文件名称
* @param sheetName sheet名称
* @param data 数据
* @param response
*/
public static <T> void export(String fileName,String sheetName,List<T> data,HttpServletResponse response) {
Map<String, List<T>> datas = new HashMap<>();
datas.put(sheetName, data);
export(fileName, datas, response);
}
/**
* 通过response导出excel,多个sheet,包含汇总行
* @param datas
* @param response
*/
public static <T> void exportAndTotal(String fileName,Map<String, List<T>> datas,HttpServletResponse response) {
try {
//设置response类型,进行转码，使其支持中文文件名
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
export(datas, true, response.getOutputStream());
}catch (RuntimeException e) {
throw e;
}catch (Exception e) {
throw new RuntimeException("导出excel异常",e);
}
}
/**
* 通过response导出excel,多个sheet,不包含汇总行
* @param datas
* @param response
*/
public static <T> void export(String fileName,Map<String, List<T>> datas,HttpServletResponse response) {
try {
//设置response类型,进行转码，使其支持中文文件名
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
export(datas, false, response.getOutputStream());
}catch (RuntimeException e) {
throw e;
}catch (Exception e) {
throw new RuntimeException("导出excel异常",e);
}
}
/**
* 导出excel
* @param datas key：sheet名称,value：每页对应的数据
* @param isTotal 是否包含汇总行
* @param os 输出流
*/
static <T> void export(Map<String, List<T>> datas,boolean isTotal,OutputStream os) {
//有数据才导出
if(datas != null && datas.size() > 0) {
HSSFWorkbook wk = new HSSFWorkbook();
for(Map.Entry<String, List<T>> data : datas.entrySet()) {
HSSFSheet sheet = wk.createSheet(data.getKey());
List<T> lists = data.getValue();
if(lists.size() > 0) {
//获取元素解析字段
Map<Integer, Column> map = parse(lists.get(0).getClass());
//创建头样式和列样式，目前头和数据样式一样，头样式字体必须加粗
Map<Integer,HSSFCellStyle> heads = new HashMap<>();
Map<Integer,HSSFCellStyle> bodys = new HashMap<>();
Map<Integer,HSSFCellStyle> tails = new HashMap<>();
for(int i = 0;i < map.size();i ++) {
Column column = map.get(i);
HSSFCellStyle headCellStyle = createCellStyle(wk, column, column.isHeadBold());
heads.put(i, headCellStyle);
HSSFCellStyle bodyCellStyle = createCellStyle(wk, column, column.isBodyBold());
bodys.put(i, bodyCellStyle);
HSSFCellStyle tailCellStyle = createCellStyle(wk, column, column.isTailBold());
tails.put(i, tailCellStyle);
}
//创建head,body,tail
createHead(sheet, heads, map);
if(isTotal) {
createBody(sheet, bodys, map, lists,true);
createTail(sheet, tails, map, lists);
}else {
createBody(sheet, bodys, map, lists,false);
}
//设置列宽度
setColumnWidth(sheet, map);
}
}
try {
wk.write(os);
}catch (Exception e) {
throw new RuntimeException("导出excel异常",e);
}
}
}
/**
* 创建头
* @param sheet
* @param heads 头样式
* @param map 头标题
*/
static void createHead(HSSFSheet sheet,Map<Integer,HSSFCellStyle> heads,Map<Integer, Column> map) {
HSSFRow headRow = sheet.createRow(0);
for(int i = 0;i < map.size();i ++) {
Column column = map.get(i);
HSSFCell cell = headRow.createCell(i);
cell.setCellStyle(heads.get(i));
cell.setCellValue(new HSSFRichTextString(column.getTitle()));
setWidth(column, cell.getStringCellValue());
}
}
/**
* 创建体
* @param sheet
* @param bodys 体样式
* @param map 获取列值
* @param lists 导出数据
* @param isTotal 是否包含汇总行，包含汇总行不会输出汇总行
*/
static <T> void createBody(HSSFSheet sheet,Map<Integer,HSSFCellStyle> bodys,
Map<Integer, Column> map,List<T> lists,boolean isTotal) {
for(int i = 1;i < lists.size();i ++) {
write(sheet, map, bodys, i, lists.get(i - 1));
}
if(!isTotal) {
write(sheet, map, bodys, lists.size(), lists.get(lists.size() - 1));
}
}
/**
* 创建体
* @param sheet
* @param bodys 体样式
* @param map 获取列值
* @param lists 导出数据
*/
static <T> void createTail(HSSFSheet sheet,Map<Integer,HSSFCellStyle> tails,Map<Integer, Column> map,List<T> lists) {
write(sheet, map, tails, lists.size(), lists.get(lists.size() - 1));
}
/**
* 设置列宽度
* @param sheet
* @param map
*/
static void setColumnWidth(HSSFSheet sheet,Map<Integer, Column> map) {
for(int i = 0;i < map.size();i ++) {
Column column = map.get(i);
sheet.setColumnWidth(i, column.getWidth() * 256);
}
}
/**
*
* @param sheet excel sheet
* @param map 导出对象
* @param bodys 数据样式
* @param row 行号
* @param t 对象
*/
static <T> void write(HSSFSheet sheet,Map<Integer, Column> map,Map<Integer,HSSFCellStyle> bodys,int row,T t) {
HSSFRow line = sheet.createRow(row);
//创建body
for(int i = 0;i < map.size();i ++) {
HSSFCell cell = line.createCell(i);
Column column = map.get(i);
//设置样式和属性值
cell.setCellStyle(bodys.get(i));
setCellValue(cell, t, column);
}
}
/**
* 根据column定义设置cell样式
* @param wk excel对象
* @param cell 列对象
* @param column 样式结构体
* @param isBold 是否加粗，由于头，体，尾都不一样所有需要传值更方便
*/
static <T> HSSFCellStyle createCellStyle(HSSFWorkbook wk,Column column,boolean isBold) {
short align = column.getAlign();
boolean border = column.isBorder();
short fontColor = column.getFontColor();
short fontSize = column.getFontSize();
String fontName = column.getFontName();
//设置样式
HSSFCellStyle style = wk.createCellStyle();
style.setAlignment(align);
style.setFillForegroundColor(fontColor);
if(border) {
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
}
//设置字体
HSSFFont font = wk.createFont();
font.setFontName(fontName);
font.setColor(fontColor);
//设置字体大小
font.setFontHeightInPoints(fontSize);
//设置字体是否加粗
if(isBold) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
//把字体应用到样式
style.setFont(font);
return style;
}
/**
* 通过字段名称从t中获取属性值设置cell值,通过修改tmpWidth
* @param cell 类对象
* @param t 对象信息
* @param fieldName 字段信息
* 注：只读取当前类中的属性值
*/
static <T> void setCellValue(HSSFCell cell,T t,Column column) {
try {
Field field = t.getClass().getDeclaredField(column.getFieldName());
field.setAccessible(true);
//转换任意类型为String
String value = ConvertUtils.convert(field.get(t));
setWidth(column, value);
if(column.getDataType().equals(Double.class)) {
try {
cell.setCellValue(Double.parseDouble(value));
}catch (Exception e) {
HSSFRichTextString text = new HSSFRichTextString(value);
cell.setCellValue(text);
}
}else {
HSSFRichTextString text = new HSSFRichTextString(value);
cell.setCellValue(text);
}
}catch (Exception e) {
throw new RuntimeException("属性获取或者操作错误",e);
}
}
/**
* 自动生成宽度则修改width
* @param column
* @param value
*/
static <T> void setWidth(Column column,String value){
if(column.isAutoWidth() && value != null && value.length() > 0) {
Double length = 0.0;
for(int i = 0;i < value.length();i ++) {
char c = value.charAt(i);
if(isChinese(c)) {
length += 2.8;
}else {
length += 1.4;
}
}
if(length.intValue() > column.getWidth()) {
column.setWidth(length.intValue());
}
}
}
static boolean isChinese(char c) {
Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
|| ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
|| ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
|| ub == Character.UnicodeBlock.GENERAL_PUNCTUATION
|| ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
|| ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS) {
return true;
}
return false;
}
/**
* 把类中需要生成excel的字段生成order和属性的映射
* @param clazz
* @return
*/
static <T> Map<Integer, Column> parse(Class<T> clazz){
List<Column> columns = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields) {
if(field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
Column column = new Column();
column.setFieldName(field.getName());
column.setTitle(annotation.title());
column.setOrder(annotation.order());
column.setAutoWidth(annotation.autoWidth());
column.setDataType(annotation.dataType());
column.setWidth(annotation.autoWidth()?0:annotation.width());
column.setAlign(annotation.align());
column.setBorder(annotation.border());
column.setFontColor(annotation.fontColor());
column.setFontSize(annotation.fontSize());
column.setFontName(annotation.fontName());
column.setHeadBold(annotation.headBold());
column.setBodyBold(annotation.bodyBold());
column.setTailBold(annotation.tailBold());
columns.add(column);
}
}
//由于order可以乱填，需要把order处理成一个从0开始的连续的数字，然后生成一个order和字段注解属性的关系
Collections.sort(columns, new Comparator<Column>() {
@Override
public int compare(Column c1, Column c2) {
return c1.getOrder() - c2.getOrder();
}
});
for(int i = 0;i < columns.size();i ++) {
columns.get(i).setOrder(i);
}
//根据order生成一个映射关系
Map<Integer, Column> map = new HashMap<>();
for(Column column : columns) {
map.put(column.getOrder(), column);
}
return map;
}
static class Column {
private String fieldName; //属性名称
private String title; //标题
private int order; //排序
private int width; //宽度
private boolean autoWidth; //自适应宽度
private Class<?> dataType;//文本框类型
private short align; //位置
private boolean border; //边框
private short fontColor; //字体颜色
private short fontSize; //字体大小
private String fontName; //字体名称
private boolean headBold; //头字体是否加粗
private boolean bodyBold; //体字体是否加粗
private boolean tailBold; //尾字体是否加粗
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
public boolean isAutoWidth() {
return autoWidth;
}
public void setAutoWidth(boolean autoWidth) {
this.autoWidth = autoWidth;
}
public Class<?> getDataType() {
return dataType;
}
public void setDataType(Class<?> dataType) {
this.dataType = dataType;
}
public short getAlign() {
return align;
}
public void setAlign(short align) {
this.align = align;
}
public boolean isBorder() {
return border;
}
public void setBorder(boolean border) {
this.border = border;
}
public short getFontColor() {
return fontColor;
}
public void setFontColor(short fontColor) {
this.fontColor = fontColor;
}
public short getFontSize() {
return fontSize;
}
public void setFontSize(short fontSize) {
this.fontSize = fontSize;
}
public String getFontName() {
return fontName;
}
public void setFontName(String fontName) {
this.fontName = fontName;
}
public boolean isHeadBold() {
return headBold;
}
public void setHeadBold(boolean headBold) {
this.headBold = headBold;
}
public boolean isBodyBold() {
return bodyBold;
}
public void setBodyBold(boolean bodyBold) {
this.bodyBold = bodyBold;
}
public boolean isTailBold() {
return tailBold;
}
public void setTailBold(boolean tailBold) {
this.tailBold = tailBold;
}
}
}
注：代码使用了lombok注解
三.怎么样使用？
- 1.定义导出类
import java.math.BigDecimal;
import org.apache.poi.ss.usermodel.CellStyle;
import lombok.Data;
/**
* test excel
* @author lilong
*/
@Data
public class TestExcelVO {
@ExcelColumn(title = "编号", order = -1)
protected String num;
@ExcelColumn(title = "名称", order = 0)
protected String name;
@ExcelColumn(title = "年龄", order = 1, dataType = Double.class)
protected Integer age;
@ExcelColumn(title = "性别", order = 2)
protected String sex;
@ExcelColumn(title = "零花钱", order = 3,align=CellStyle.ALIGN_RIGHT)
protected BigDecimal amount;
}
- 2.调用工具类
@RequestMapping(value="/test")
public void test(HttpServletResponse response){
List<TestExcelVO> lt = Lists.newArrayList();
//导出十条数据
BigDecimal total = BigDecimal.ZERO;
for(int i = 0;i < 10;i ++) {
TestExcelVO vo = new TestExcelVO();
vo.setNum(i+"");
vo.setAge(10);
vo.setSex(i/2==0?"男":"女");
vo.setName("张三"+i);
BigDecimal amount = new BigDecimal(i/2);
total = total.add(amount);
vo.setAmount(amount);
lt.add(vo);
}
//汇总行以数据形式添加
TestExcelVO vo = new TestExcelVO();
vo.setNum("汇总");
vo.setAmount(total);
lt.add(vo);
ExcelUtil.exportAndTotal("测试excel", "测试excel", lt, response);
}
- 3.学习其他接口
注解类和接口其他方法请自行理解使用
