之前有写过一点关于java实现写Excel文件的方法，但是现在看来，那种方式用起来不是太舒服，还很麻烦。所以最近又参考其他，就写了一个新版，用起来不要太爽。
代码不需要解释，惯例直接贴下来：
1 public class ExcelExport implements Closeable { 2 3 private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExport.class); 4 5 public static final String EXCEL_SUFFIX = ".xlsx"; // 目前只支持xlsx格式 6 7 private static final String SHEET_FONT_TYPE = "Arial"; 8 9 private Workbook workbook; 10 11 private Sheet sheet; 12 13 private int rowNum; 14 15 private Map styles; 16 17 private List columns; 18 19 public ExcelExport createSheet(String sheetName, String title, Class clazz) throws Exception { 20 this.workbook = createWorkbook(); 21 this.columns = createColumns(); 22 Field[] fields = clazz.getDeclaredFields(); 23 for (Field field : fields) { 24 ExcelField excelField = field.getAnnotation(ExcelField.class); 25 if (excelField != null) { 26 this.columns.add(new ColumnField(excelField.title(), field.getName(), field.getType(), excelField.width())); 27 } 28 } 29 if (CollectionUtils.isEmpty(this.columns)) throw new Exception("Excel's headerList are undefined"); 30 this.sheet = workbook.createSheet(StringUtils.defaultString(sheetName, StringUtils.defaultString(title, "Sheet1"))); 31 this.styles = createStyles(workbook); 32 this.rowNum = 0; 33 if (StringUtils.isNotBlank(title)) { 34 Row titleRow = sheet.createRow(rowNum++); 35 titleRow.setHeightInPoints(30); 36 Cell titleCell = titleRow.createCell(0); 37 titleCell.setCellStyle(styles.get("title")); 38 titleCell.setCellValue(title); 39 sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), 40 this.columns.size() - 1)); 41 } 42 Row headerRow = sheet.createRow(rowNum++); 43 headerRow.setHeightInPoints(16); 44 for (int i = 0; i < this.columns.size(); i++) { 45 int width = this.columns.get(i).width; 46 this.sheet.setColumnWidth(i, 256 * width + 184); 47 Cell cell = headerRow.createCell(i); 48 cell.setCellStyle(styles.get("header")); 49 cell.setCellValue(this.columns.get(i).title); 50 } 51 return this; 52 } 53 54 public ExcelExport setDataList(List dataList) throws IllegalAccessException { 55 for (E data : dataList) { 56 int column = 0; 57 Row row = this.addRow(); 58 Map map = toMap(data); 59 for (ColumnField field : this.columns) { 60 Class paramType = field.getParamType(); 61 if (map.containsKey(field.getParam())) { 62 Object value = map.get(field.getParam()); 63 this.addCell(row, column++, value, paramType); 64 } 65 } 66 } 67 LOGGER.debug("add data into {} success", this.sheet.getSheetName()); 68 return this; 69 } 70 71 private Cell addCell(Row row, int column, Object value, Class type) { 72 Cell cell = row.createCell(column); 73 if (value == null) { 74 cell.setCellValue(""); 75 } else if (type.isAssignableFrom(String.class)) { 76 cell.setCellValue((String) value); 77 } else if (type.isAssignableFrom(Integer.class)) { 78 cell.setCellValue((Integer) value); 79 } else if (type.isAssignableFrom(Double.class)) { 80 cell.setCellValue((Double) value); 81 } else if (type.isAssignableFrom(Long.class)) { 82 cell.setCellValue((Long) value); 83 } else if (type.isAssignableFrom(Float.class)) { 84 cell.setCellValue((Float) value); 85 } else if (type.isAssignableFrom(Date.class)) { 86 Date time = (Date) value; 87 String timer = DateUtils.formatDate(time, "yyyy-MM-dd HH:mm:ss"); 88 cell.setCellValue(timer); 89 } else { 90 cell.setCellValue(Objects.toString(value)); 91 } 92 cell.setCellStyle(styles.get("data")); 93 return cell; 94 } 95 96 private Map toMap(Object entity) throws IllegalAccessException { 97 Map row = Maps.newHashMap(); 98 if (null == entity) return row; 99 Class clazz = entity.getClass();100 Field[] fields = clazz.getDeclaredFields();101 for (Field field : fields) {102 field.setAccessible(true);103 row.put(field.getName(), field.get(entity));104 }105 return row;106 }107 108 private Row addRow() {109 return sheet.createRow(rowNum++);110 }111 112 public ExcelExport write(OutputStream os) {113 try {114 workbook.write(os);115 } catch (IOException ex) {116 LOGGER.error(ex.getMessage(), ex);117 } finally {118 if (null != os) {119 try {120 os.close();121 } catch (IOException e) {122 LOGGER.error("close Output Stream failed: {}", e.getMessage());123 }124 }125 }126 return this;127 }128 129 public ExcelExport write(HttpServletResponse response, String fileName) {130 response.reset();131 try {132 response.setContentType("application/octet-stream; charset=utf-8");133 response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, UTF8));134 write(response.getOutputStream());135 } catch (IOException ex) {136 LOGGER.error(ex.getMessage(), ex);137 }138 return this;139 }140 141 public ExcelExport writeFile(String name) throws IOException {142 FileOutputStream os = new FileOutputStream(name);143 this.write(os);144 return this;145 }146 147 private Workbook createWorkbook() {148 return new SXSSFWorkbook();149 }150 151 private List createColumns() {152 return Lists.newLinkedList();153 }154 155 private Map createStyles(Workbook workbook) {156 Map styleMap = Maps.newHashMap();157 158 CellStyle style = workbook.createCellStyle();159 style.setAlignment(HorizontalAlignment.CENTER);160 style.setVerticalAlignment(VerticalAlignment.CENTER);161 Font titleFont = workbook.createFont();162 titleFont.setFontName(SHEET_FONT_TYPE);163 titleFont.setFontHeightInPoints((short) 16);164 titleFont.setBold(true);165 style.setFont(titleFont);166 styleMap.put("title", style);167 168 style = workbook.createCellStyle();169 style.setVerticalAlignment(VerticalAlignment.CENTER);170 style.setBorderRight(BorderStyle.THIN);171 style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());172 style.setBorderLeft(BorderStyle.THIN);173 style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());174 style.setBorderTop(BorderStyle.THIN);175 style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());176 style.setBorderBottom(BorderStyle.THIN);177 style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());178 Font dataFont = workbook.createFont();179 dataFont.setFontName(SHEET_FONT_TYPE);180 dataFont.setFontHeightInPoints((short) 10);181 style.setFont(dataFont);182 styleMap.put("data", style);183 184 style = workbook.createCellStyle();185 style.cloneStyleFrom(styleMap.get("data"));186 style.setWrapText(true);187 style.setAlignment(HorizontalAlignment.CENTER);188 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());189 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);190 Font headerFont = workbook.createFont();191 headerFont.setFontName(SHEET_FONT_TYPE);192 headerFont.setFontHeightInPoints((short) 10);193 headerFont.setBold(true);194 headerFont.setColor(IndexedColors.WHITE.getIndex());195 style.setFont(headerFont);196 style.setBorderRight(BorderStyle.THIN);197 styleMap.put("header", style);198 199 return styleMap;200 }201 202 public Workbook getWorkbook() {203 return workbook;204 }205 206 public void setWorkbook(Workbook workbook) {207 this.workbook = workbook;208 }209 210 public Sheet getSheet() {211 return sheet;212 }213 214 public void setSheet(Sheet sheet) {215 this.sheet = sheet;216 }217 218 public int getRowNum() {219 return rowNum;220 }221 222 public void setRowNum(int rowNum) {223 this.rowNum = rowNum;224 }225 226 public Map getStyles() {227 return styles;228 }229 230 public void setStyles(Map styles) {231 this.styles = styles;232 }233 234 public List getColumns() {235 return columns;236 }237 238 public void setColumns(List columns) {239 this.columns = columns;240 }241 242 @Override243 public void close() throws IOException {244 if (workbook instanceof SXSSFWorkbook && ((SXSSFWorkbook) workbook).dispose())245 workbook.close();246 }247 248 class ColumnField {249 private String title;250 private String param;251 private Class paramType;252 private int width;253 254 ColumnField(String title, String param, Class paramType, int width) {255 this.title = title;256 this.param = param;257 this.paramType = paramType;258 this.width = width;259 }260 261 public String getTitle() {262 return title;263 }264 265 public void setTitle(String title) {266 this.title = title;267 }268 269 public String getParam() {270 return param;271 }272 273 public void setParam(String param) {274 this.param = param;275 }276 277 public Class getParamType() {278 return paramType;279 }280 281 public void setParamType(Class paramType) {282 this.paramType = paramType;283 }284 285 public int getWidth() {286 return width;287 }288 289 public void setWidth(int width) {290 this.width = width;291 }292 }293 } 以下是两个注解
1 @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE}) 2 @Retention(RetentionPolicy.RUNTIME) 3 public @interface ExcelField { 4 5 /** 6 * 导出字段标题 7 */ 8 String title(); 9 10 /**11 * 列宽12 */13 int width() default 10; // 后面还可以添加其他的属性，添加后再修改上面那个代码就行了14 } 以上。
使用方式为：
1 import com.xxx.utils.ExcelField; 2 3 public class ExcelDataModel { 4 5 @ExcelField(title = "ID", width = 4) 6 private String id; 7 8 @ExcelField(title = "序号", width = 4) 9 private Integer serial;10 11 @ExcelField(title = "名字", width = 8)12 private String name; 13 ... (getter\setter) @GetMapping(value = "export/post") public void exportPost(@ModelAttribute RequestModel model, HttpServletResponse response) { try ( ExcelExport excelExport = new ExcelExport(); OutputStream out = response.getOutputStream() ) { List data = xxxService.selectExportData(model); response.setContentType("octets/stream"); response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("xx列表", "UTF-8") + DateUtils.formatDate(new Date(), "yyyyMMddHHmmss") + ExcelExport.EXCEL_SUFFIX); String title = "xx列表"; excelExport.createSheet("xx列表", title, ExcelDataModel.class); excelExport.setDataList(data); excelExport.write(out); } catch (Exception e) { e.printStackTrace(); } } over.