java excel导出（基于注解）

package com.authorize.utils.excel;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.usermodel.XSSFWorkbook;import com.authorize.utils.CommonUtils;
import com.authorize.utils.exception.CustomException;public class ExcelUtils<T> { private static final Log log = LogFactory.getLog(ExcelUtils.class); /**
* 行高
*/
private int rowHeight = 400;
/**
* 列宽
*/
private int colWidth = 8500;
/**
* 起始位置
*/
private int rowIndex = 0;
/**
* 默认标题
*/
private String title = "defaultExcel"; private Workbook workbook;
private Sheet sheet;
/**
* 公共列样式
*/
private CellStyle cellStyle;
/**
* 操作的实体类
*/
private T obj;
/**
* 列属性集合
*/
private List<Map<String,Object>> colList = new ArrayList<>();
/**
* 列样式集合
*/
private List<CellStyle> styleList = new ArrayList<>(); private ClassUtils util = new ClassUtils(); public ExcelUtils( T obj ) {
this.obj = obj;
initWorkbook();
} public ExcelUtils(T obj,int rowHeight, int colWidth, int rowIndex, String title) {
this(obj);
this.rowHeight = rowHeight;
this.colWidth = colWidth;
this.rowIndex = rowIndex;
this.title = title;
} /**
* 默认文档设置文档
*/
private void initWorkbook() {
if ( CommonUtils.isEmpty(this.obj) ) {
throw new CustomException("未指定导出实体类,无法进行导出操作");
}
util.parseBookAnnotation();
workbook = new XSSFWorkbook();
sheet = workbook.createSheet(this.title); // 创建工作页
sheet.setDefaultColumnWidth(colWidth); // 设置默认列宽
cellStyle = createCellStyle();
cellStyle.setFont(createFont(null, (short)0, (short)0));
titleSetting();
} /**
* 标题、列名相关设置
*/
private void titleSetting() {
Row topRow = createRow((short)600);
mergedRegion(0, 0, 0, this.colList.size()-1); // 合并标题行
Cell cell = createCell(topRow,0,cellStyle);
cell.setCellValue(this.title);
Row textRow = createRow((short)0);
for ( int i = 0; i < colList.size(); i++ ) {
Map<String,Object> fieldMap = colList.get(i);
sheet.setColumnWidth(i, Integer.parseInt(fieldMap.get("width").toString()));
Cell cell1 = createCell(textRow,i,cellStyle);
cell1.setCellValue(fieldMap.get("text").toString());
addColStlye(null, (short)0, Short.parseShort(fieldMap.get("color").toString()));
}
} /**
* 创建字体对象
* @param fontName
* 字体库名称
* @param fontSize
* 字体大小-传0默认14
* @param color
* 字体颜色参考{@link HSSFColor.BLACK.index}
* @return
*/
private Font createFont(String fontName, short fontSize,short color) {
Font font = this.workbook.createFont();
if ( CommonUtils.isEmpty(fontName) ) {
fontName = "宋体";
}
if ( fontSize == 0 ) {
fontSize = (short)14;
}
if ( color == 0 ) {
color = HSSFColor.BLACK.index;
}
font.setFontName("宋体"); //设置为宋体字
font.setFontHeightInPoints(fontSize); //设置字体大小
font.setColor(color);
return font;
} /**
* 创建列样式
* @param alignment
* @param vertical
* @return
*/
private CellStyle createCellStyle(short ...alignments) {
CellStyle style = this.workbook.createCellStyle();
short alignment = HSSFCellStyle.ALIGN_CENTER_SELECTION,vertical = HSSFCellStyle.VERTICAL_CENTER;
if ( alignments.length > 0 ) {
alignment = alignments[0];
if ( alignments.length > 1 ) {
vertical = alignments[1];
}
}
//水平居中
style.setAlignment(alignment);
//垂直居中
style.setVerticalAlignment(vertical);
return style;
} /**
* 合并行、列
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
private void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
CellRangeAddress region = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol); // 合并行
sheet.addMergedRegion(region);
} /**
* 创建单元格
* @param row
* @param index
* @param style
* @return
*/
private Cell createCell ( Row row,int index,CellStyle style ) {
Cell cell = row.createCell(index);
if ( CommonUtils.isNotEmpty(style) ) {
cell.setCellStyle(style);
}
return cell;
} /**
* 创建行
* @return
*/
private Row createRow ( short rowHeight ) {
Row row = sheet.createRow(this.rowIndex); if ( rowHeight == 0 ) {
rowHeight = (short)this.rowHeight;
}
row.setHeight(rowHeight);
this.rowIndex ++;
return row;
} /**
* 创建每一列的样式
* @param fontName
* @param fontSize
* @param color
* @param alignments
*/
private void addColStlye(String fontName, short fontSize,short color,short ...alignments) {
Font ft = createFont(fontName, fontSize, color);
CellStyle style = createCellStyle(alignments);
style.setFont(ft);
styleList.add(style);
} /**
* 生成Excel表
* @param list
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public void createExcel(List<T> list,String parentPath) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
for ( T temp : list ) {
Row row = createRow((short)0);
for (int i = 0; i < colList.size(); i ++ ) {
Map<String,Object> cl = colList.get(i);
Cell cell = createCell(row,i,styleList.get(i));
String methodName = cl.get("methodName").toString();
Object obj = util.valueToGet(temp, methodName);
cell.setCellValue(obj.toString());
}
}
File file = new File(parentPath);
if (!file.exists()){
file.createNewFile();
}
FileOutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
outputStream.close();
} public List<ExcelBean> getListBean(){
List<ExcelBean> list = new ArrayList<>();
ExcelBean bean = null;
Random random = new Random();
for ( int i = 0; i < 100; i++ ) {
bean = new ExcelBean();
bean.setAddr(UUID.randomUUID().toString().substring(0, 15));
bean.setAge(random.nextInt(100));
bean.setName("张三"+(i + 1) +"号");
list.add(bean);
}
return list;
} class ClassUtils{ private ClassUtils() {} /**
* 反射获取value
* @param object
* @return
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
private Object valueToGet( Object object, String methodName ) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
Method method = object.getClass().getDeclaredMethod(methodName);
return method.invoke(object);
} /**
* 首字母大写
* @param fieldName
* @return
*/
public String convertMethodName( String fieldName ) {
String newField = fieldName.substring(1, fieldName.length());
return fieldName.substring(0,1).toUpperCase()+newField;
} /**
* 通过注解获取导出sheet相关注解属性
*/
public void parseBookAnnotation( ) {
Annotation[] ans = ExcelUtils.this.obj.getClass().getAnnotations();
for ( Annotation temp : ans ) {
if ( temp instanceof ExcelBookAnnotation) {
String title = ((ExcelBookAnnotation) temp).title();
ExcelUtils.this.title = title;
parseFielAnnotation( );
}
}
} /**
* 通过注解获取列相关注解属性
*/
public void parseFielAnnotation( ) {
Field [] fiels = ExcelUtils.this.obj.getClass().getDeclaredFields();
for ( Field temp : fiels ) {
Annotation[] ans = temp.getAnnotations();
for ( Annotation tempAn : ans ) {
if ( tempAn instanceof ExcelColAnnotation ) {
ExcelColAnnotation col = ((ExcelColAnnotation) tempAn);
int ignore = col.ignore();
if ( ignore == 0 ) {
Map<String,Object> fieldMap = new HashMap<>();
fieldMap.put("width", col.colWidth());
fieldMap.put("color", col.color());
fieldMap.put("text", col.text());
fieldMap.put("methodName", "get"+convertMethodName(temp.getName()));
ExcelUtils.this.colList.add(fieldMap);
}
}
}
}
} } public static void main(String[] args) throws IOException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
ExcelUtils<ExcelBean> excel = new ExcelUtils<ExcelBean>( new ExcelBean());
List<ExcelBean> list = excel.getListBean();
excel.createExcel(list, "C:\\Users\\Administrator\\Desktop\\test_bak\\test.xlsx");
}}