java excel导出（基于注解）
小白，做日志只是为了方便自己查看，能帮到别人当然更好，不喜勿喷。
上代码
依赖：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.10-FINAL</version> </dependency>
注解，使用了俩个注解，一个是sheet公用属性，以及单元格属性，只是简单的几个属性，可自行扩展。
sheet公用注解：
package com.authorize.utils.excel; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * * @filename ExcelBookAnnotation.java * @pakage com.authorize.utils.excel * @descption TODO(用一句话表述类的作用) * @author Pandong * @date 2019年4月8日 */ @Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelBookAnnotation { /** * 标题 * @return */ String title(); }
单元格注解：
package com.authorize.utils.excel; 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; /** * * @filename ExcelAnnotation.java * @pakage com.authorize.utils.excel * @descption TODO(用一句话表述类的作用) * @author Pandong * @date 2019年4月8日 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelColAnnotation { /** * 列名 * @return */ String text() default ""; /** * 列宽 * @return */ int colWidth() default 6000; /** * 字体颜色，默认黑色 * @return */ short color() default HSSFColor.BLACK.index; /** * 导出是是否忽略该字段,默认不忽略 * @return */ int ignore() default 0; }
实体类，其中使用了lombok，感兴趣可以百度一下，不用可以不相关注解删掉，自己写get/set等方法。
package com.authorize.utils.excel; import java.io.Serializable; import org.apache.poi.hssf.util.HSSFColor; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; /** * * @filename ExcelBean.java * @pakage com.authorize.utils.excel * @descption TODO(用一句话表述类的作用) * @author Pandong * @date 2019年4月8日 */ @Data @AllArgsConstructor @NoArgsConstructor @ExcelBookAnnotation(title = "日常工作表") public class ExcelBean implements Serializable{ @ExcelColAnnotation( ignore = 1 ) private static final long serialVersionUID = 4248622093488850427L; @ExcelColAnnotation(colWidth = 8000, text = "姓名",color = HSSFColor.RED.index) private String name; @ExcelColAnnotation( text = "年龄", colWidth = 2000) private int age; @ExcelColAnnotation( text = "地址", colWidth = 12000) private String addr; }
最后就是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"); } }
到这里就算完了，有不好的地方可以提出。
