用到apache.poi
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xlsXSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsxSXSSFWorkbook 是专门用来处理大量数据写入 Excel2007的问题的读取仍然是“XSSFWorkbook”，写入则为“SXSSFWorkbook”代码：
1、自定义Excel注解类 import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD,ElementType.TYPE}) public @interface ExcelAttribute { /** * excel里的sheet名，默认是"sheet1" * @return */ String sheetName() default "sheet1"; /** * excel里对应的列名，默认为"" * @return */ String columnName() default ""; /** * 列对应的排序序号，默认是0 * @return */ int order() default 0; enum DataType { String, Number, Date } /** * 数据类型，可以是String，Number(数字型)，Date等类型 * @return */ DataType type() default DataType.String; /** * 日期格式，默认是"yyyy-MM-dd HH:mm:ss" * @return */ String datePattern() default "yyyyMMdd HH:mm:ss"; /** * 保留小数点后的位数，默认是0 * @return */ int decimalNums() default 0; /** * 背景颜色,默认为白色"FFFFFF"， * 表示形式为颜色的十六进制字符串，常见的： * red: "FF0000",Orange: "FFA500",yellow: "FFFF00", * green: "008000",blue: "0000FF",purple: "800080" * @return */ String fillColor() default "FFFFFF"; /** * 字段是否放弃存储到excel里，默认为false * @return */ boolean skip() default false; } 2、类上加注解 import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute; import lombok.AllArgsConstructor; import lombok.Data; import java.util.Date; @Data @AllArgsConstructor @ExcelAttribute(sheetName = "stuSheet") public class Stu { @ExcelAttribute(columnName="学号",order=0,fillColor = "FF0000") private Integer stuNo; @ExcelAttribute(columnName="姓名",order=2,skip = true) private String name; @ExcelAttribute(columnName="成绩",order=5, type = ExcelAttribute.DataType.Number,decimalNums = 4) private Double grade; @ExcelAttribute(columnName="注册时间",order=0,datePattern = "yyyy-MM-dd HH:mm",type = ExcelAttribute.DataType.Date) private Date loginDate; @ExcelAttribute(columnName="是否男孩",order=4,fillColor = "FFFF00") private Boolean isBoy; } 3、ExcelUtil.java import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute; import com.creditease.microloan.mil.tasks.exceptions.BusinessRuntimeException; import org.apache.commons.beanutils.BeanUtils; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Component; import org.springframework.util.CollectionUtils; import java.awt.Color; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.lang.reflect.Field; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; import java.util.List; @Component public class ExcelUtil<T> { /** * 根据filePath和dataset创建excel文件 * @param filePath * @param dataset * @param <T> */ public static <T> void createFile(String filePath, List<T> dataset) { XSSFWorkbook wb = new XSSFWorkbook(); createExcel( wb, dataset); FileOutputStream out = null; try { out = new FileOutputStream(filePath); wb.write(out); } catch (Exception e){ throw new BusinessRuntimeException("文件创建失败！",e); } finally { try { out.close(); } catch (Exception e) { throw new BusinessRuntimeException("关闭文件输出流失败！",e); } } } /** * 添加新数据到已有的excel * @param filePath * @param dataset * @param <T> */ public static <T> void appendDataToExcel(String filePath, List<T> dataset) { if(CollectionUtils.isEmpty(dataset) ) { return; } FileOutputStream out = null; try{ XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(filePath)); // 原来的sheet Sheet sheet = workbook.getSheetAt(0); T t = dataset.get(0); // 获取t的excelFields 和 attributes信息 List<Object> list = getExcelFieldsAndAttributes(t); List<Field> excelFields = (List<Field>)list.get(0); List<ExcelAttribute> attributes = (List<ExcelAttribute>)list.get(1); addDataToExcel(workbook,dataset,excelFields, attributes,sheet); out = new FileOutputStream(filePath); workbook.write(out); } catch (Exception e){ throw new BusinessRuntimeException("excel文件不存在！",e); } finally { try { out.close(); } catch (Exception e) { throw new BusinessRuntimeException("关闭文件输出流失败！",e); } } } /** * 创建sheet，并添加数据到里面 * @param wb * @param dataset * @param <T> */ private static <T> void createExcel(XSSFWorkbook wb, List<T> dataset ) { if(CollectionUtils.isEmpty(dataset) ) { return; } T t = dataset.get(0); // 整个类的注解,得到了定义的sheetName ExcelAttribute classAttribute = t.getClass().getAnnotation(ExcelAttribute.class); XSSFSheet sheet = wb.createSheet(classAttribute.sheetName()); // 获取t的excelFields 和 attributes信息 List<Object> list = getExcelFieldsAndAttributes(t); List<Field> excelFields = (List<Field>)list.get(0); List<ExcelAttribute> attributes = (List<ExcelAttribute>)list.get(1); addColumnLineToExcel(wb,dataset,excelFields, attributes,sheet); addDataToExcel(wb,dataset,excelFields, attributes,sheet); // 自动调整列宽 //sheet.trackAllColumnsForAutoSizing(); for(int i=0;i<excelFields.size();i++) { sheet.autoSizeColumn(i); } } /** * 获取需要存储的类的excelFields 和 attributes信息 * @param t * @return */ private static <T> List<Object> getExcelFieldsAndAttributes(T t){ List<Object> res = new ArrayList<>(); // 一个field表示一个属性 Field[] fields = t.getClass().getDeclaredFields(); // excel里存储类的部分属性和顺序号 Map<Field,Integer> map = new LinkedHashMap<>(); ExcelAttribute excelAttribute = null; for (Field field : fields) { // 某个属性上的注解,如果没写注解或者注解里的skip为true，表示该列不会存储到excel里 excelAttribute = field.getAnnotation(ExcelAttribute.class); if (excelAttribute != null) { if (!excelAttribute.skip()) { map.put(field, excelAttribute.order()); } } } // 排序 List<Map.Entry<Field,Integer>> list = new ArrayList<Map.Entry<Field,Integer>>(map.entrySet()); Collections.sort(list, (o1, o2) -> o1.getValue().compareTo(o2.getValue())); // 存储类的注解skip为false的排序后的属性对应的Field List<Field> excelFields = new ArrayList<>(); for(Map.Entry<Field,Integer> mapping:list){ excelFields.add(mapping.getKey()); } // excel里存储的列的ExcelAttribute List<ExcelAttribute> attributes = new ArrayList<>(); for (int j = 0; j < excelFields.size(); j++) { attributes.add(excelFields.get(j).getAnnotation(ExcelAttribute.class)); } res.add(excelFields); res.add(attributes); return res; } /** * 添加第一行标题栏到表格中 * @param wb * @param dataset * @param excelFields * @param attributes * @param sheet * @param <T> */ private static <T> void addColumnLineToExcel(XSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAttribute> attributes,Sheet sheet) { XSSFCellStyle style = (XSSFCellStyle)wb.createCellStyle(); // 居中 style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); // excel放入第一行列的名称 Row row = sheet.createRow(0); for (int j = 0; j < excelFields.size(); j++) { Cell cell = row.createCell(j); ExcelAttribute oneAttribute = attributes.get(j); cell.setCellValue(oneAttribute.columnName()); cell.setCellStyle(style); } } /** * 添加数据到excel中 * @param wb * @param dataset * @param excelFields * @param attributes * @param sheet * @param <T> */ private static <T> void addDataToExcel(XSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAttribute> attributes,Sheet sheet) { XSSFCellStyle style = null; // sheet原有行数 int rowNums = sheet.getLastRowNum()+1; // 添加数据到excel for(int i=0;i<dataset.size();i++) { // 数据行号从1开始,因为第0行放的是列的名称 Row row = sheet.createRow(i+rowNums); for(int j=0;j<attributes.size();j++) { Cell cell = row.createCell(j); ExcelAttribute oneAttribute = attributes.get(j); style = (XSSFCellStyle)wb.createCellStyle(); // 居中 style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); // 填充色 if(!"".equals(oneAttribute.fillColor())){ XSSFColor myColor = new XSSFColor(toColorFromString(oneAttribute.fillColor())); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(myColor); } cell.setCellStyle(style); try{ // 根据属性名获取属性值 String cellValue = BeanUtils.getProperty( dataset.get(i), excelFields.get(j).getName()); if(ExcelAttribute.DataType.Date.equals(oneAttribute.type())) { // CST格式的时间字符串转为Date对象 String CST_FORMAT = "EEE MMM dd HH:mm:ss z yyyy"; Date cstDate = new SimpleDateFormat(CST_FORMAT, Locale.US).parse(cellValue); DateFormat df = new SimpleDateFormat( oneAttribute.datePattern()); cell.setCellValue( df.format(cstDate) ); } else if(ExcelAttribute.DataType.Number.equals(oneAttribute.type())) { // 保留小数点后的位数 int decimalNums = oneAttribute.decimalNums(); StringBuilder format = new StringBuilder("#0"); for(int w=0;w<decimalNums;w++) { if(w==0) { format.append("."); } format.append("0"); } cell.setCellValue(String.valueOf(new DecimalFormat(format.toString()).format(Double.parseDouble(cellValue)))); } else { cell.setCellValue(cellValue); } } catch (Exception e) { throw new BusinessRuntimeException("获取类的属性值失败！", e); } } } } /** * 颜色的16进制字符串转换成Color对象 * @param colorStr 例如蓝色为"0000FF" * @return Color对象 * */ private static Color toColorFromString(String colorStr){ Color color = new Color(Integer.parseInt(colorStr, 16)) ; return color; } }