Java tutorial
/* * Commons-Utils * Copyright (c) 2017. * * Licensed under the Apache License, Version 2.0 (the "License") */ package com.ykun.commons.utils.excel; import com.ykun.commons.utils.excel.annotation.ExcelField; import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.net.URLEncoder; import java.text.MessageFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; import static com.ykun.commons.utils.constant.Constant.CHARSET_UTF8; /** * ?apache-poi??sheet?sheet?1048576xlsx * <p> * Excel 07-2003?65536165536;?256?AZAAAZBABZIAIV?255 * Excel 2007???104857616384 * * @author Ykun 2017-02-08 11:04 */ public class ExcelUtils { private final static Log logger = LogFactory.getLog(ExcelUtils.class); /** * ? */ private final static SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(); /** * get? */ private final static String PREFIX_GETTER = "get"; /** * xls */ private final static String CONTENT_TYPE = "application/vnd.ms-excel;charset=utf-8"; /** * header */ private final static String HEADER_CONTENT_DISPOSITION = "Content-Disposition"; /** * Disposition */ private final static String PATTERN_DISPOSITION = "attachment; filename={0}.xlsx"; /** * xlsheadersExcelField???? * * @param list the list * @param out the out */ public static <T> void export(List<T> list, OutputStream out) { export(list, getHeaders(list), out); } /** * xlsheaders * * @param list the list * @param headers the headers * @param out the out */ public static <T> void export(List<T> list, String[] headers, OutputStream out) { export(list, Arrays.asList(headers), out); } /** * xlsheaders * * @param list the list * @param headers the headers * @param out the out */ public static <T> void export(List<T> list, List<String> headers, OutputStream out) { // ? if (list == null || list.size() == 0) { return; } try { Workbook workbook = new XSSFWorkbook(); // XSSFWorkbook Sheet sheet = workbook.createSheet(); // ?Sheet // ? int rowNo = 0; CellStyle headerStyle = createHeaderStyle(workbook); if (headers != null && headers.size() > 0) { Row row = sheet.createRow(rowNo++); for (int i = 0; i < headers.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(headers.get(i)); } } // ? CellStyle normalStyle = createNormalStyle(workbook); for (T t : list) { Row row = sheet.createRow(rowNo++); Field[] fields = t.getClass().getDeclaredFields(); int column = 0; for (int i = 0; i < fields.length; i++) { Object value; Field field = fields[i]; ExcelField excelField = field.getAnnotation(ExcelField.class); if (excelField != null && !excelField.ignore()) { String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable? Method method = t.getClass().getMethod(methodName, new Class[] {}); value = method.invoke(t, new Object[] {}); } else if (excelField != null && excelField.ignore()) { continue; } else { String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable? Method method = t.getClass().getMethod(methodName, new Class[] {}); value = method.invoke(t, new Object[] {}); } row.setRowStyle(normalStyle); addCell(row, column++, value, excelField); } } workbook.write(out); } catch (Exception e) { logger.error("Export error:", e); throw new RuntimeException(e); } } /** * ?? * * @param workbook Workbook * @return CellStyle */ private static CellStyle createHeaderStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font headerFont = workbook.createFont(); headerFont.setBold(Boolean.TRUE); headerFont.setColor(IndexedColors.BLACK.getIndex()); style.setFont(headerFont); return style; } /** * ???? * * @param workbook * @return HSSFCellStyle */ private static CellStyle createNormalStyle(Workbook workbook) { return workbook.createCellStyle(); } /** * ???headersExcelField * * @param <T> the type parameter * @param list ?? * @return List headers */ public static <T> List<String> getHeaders(List<T> list) { List<String> headers = new ArrayList<String>(); if (list != null && list.size() > 0) { T t = list.get(0); Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { Field field = fields[i]; ExcelField excelField = field.getAnnotation(ExcelField.class); if (excelField != null && !excelField.ignore()) { headers.add(excelField.value()); } else if (excelField != null && excelField.ignore()) { } else { headers.add(field.getName()); } } } return headers; } /** * ?cell? * * @param row * @param column * @param value * @param excelField * @return Cell */ private static Cell addCell(Row row, int column, Object value, ExcelField excelField) { Cell cell = row.createCell(column); if (value == null) { cell.setCellValue(""); } else { if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Long) { cell.setCellValue((Long) value); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else if (value instanceof Float) { cell.setCellValue((Float) value); } else if (value instanceof Date) { Date date = (Date) value; if (excelField != null) { SIMPLE_DATE_FORMAT.applyPattern(excelField.dateFormatter()); cell.setCellValue(SIMPLE_DATE_FORMAT.format(date)); } } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else { // cell.setCellValue((String) value); } } return cell; } /** * xls * * @param <T> the type parameter * @param response the response * @param list the list * @param fileName the file name * @throws IOException the io exception */ public static <T> void download(HttpServletResponse response, List<T> list, String fileName) throws IOException { setContentType(response, fileName); export(list, response.getOutputStream()); } /** * xls * * @param <T> the type parameter * @param response the response * @param list the list * @param headers the headers * @param fileName the file name * @throws IOException the io exception */ public static <T> void download(HttpServletResponse response, List<T> list, String[] headers, String fileName) throws IOException { setContentType(response, fileName); export(list, headers, response.getOutputStream()); } /** * xls * * @param <T> the type parameter * @param response the response * @param list the list * @param headers the headers * @param fileName the file name * @throws IOException the io exception */ public static <T> void download(HttpServletResponse response, List<T> list, List<String> headers, String fileName) throws IOException { setContentType(response, fileName); export(list, headers, response.getOutputStream()); } /** * xlscontent-type * * @param response * @param fileName * @throws IOException */ private static void setContentType(HttpServletResponse response, String fileName) throws IOException { response.setContentType(CONTENT_TYPE); response.setHeader(HEADER_CONTENT_DISPOSITION, MessageFormat.format(PATTERN_DISPOSITION, URLEncoder.encode(fileName, CHARSET_UTF8))); } }