Excel导出工具类

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

public class ExportExcelUtil {

    private ExportExcelUtil() {
    }

    /**
     * @param workbook
     * @param sheetName
     * @param fields
     * @param dataList
     * @param pattern
     * @throws Exception
     */
    public static <T> void exportExcel(HSSFWorkbook workbook, String sheetName, Field[] fields, List<T> dataList, String pattern) throws Exception {

        if (CollectionUtils.isEmpty(dataList))
            return;

        HSSFSheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth((short) 20);

        HSSFCellStyle titleStyle = getTitleStyle(workbook);
        HSSFCellStyle contentStyle = getContentStyle(workbook);

        setTitleData(sheet, fields, titleStyle);
        setContentData(sheet, fields, dataList, pattern, contentStyle);
    }


    private static void setTitleData(HSSFSheet sheet, Field[] fields, HSSFCellStyle style) {
        List<String> headList = Arrays.stream(fields).map(Field::getName).collect(Collectors.toList());
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headList.get(i));
            cell.setCellValue(text);

        }
    }

    private static <T> void setContentData(HSSFSheet sheet, Field[] fields, List<T> dataList, String pattern, HSSFCellStyle style) throws IllegalAccessException {
        Iterator<T> it = dataList.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            HSSFRow row = sheet.createRow(index);
            T t = it.next();
            for (int i = 0; i < fields.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                fields[i].setAccessible(true);
                Object value = fields[i].get(t);
                String formatString = ObjectUtil.convertToFormatString(value, pattern);
                cell.setCellValue(formatString);

            }
        }
    }

    private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        // title style
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // title font
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private static HSSFCellStyle getContentStyle(HSSFWorkbook workbook) {
        // content style
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // content font
        HSSFFont font2 = workbook.createFont();
        font2.setBold(false);
        style.setFont(font2);
        return style;
    }

}

 

 

 

    public void exportExcelPositionMongo(@Context HttpServletResponse response) throws Exception {
        List<PositionMongo> positions = positionMongoProcessor.getCurrentDatePosition();
        if (CollectionUtils.isEmpty(positions)) {
            return;
        }

        logger.info("Mongo return position size:{}", positions.size());

        String fileName = "MongoPosition";
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
        response.setContentType("application/x-xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        Field[] declaredFields = PositionMongo.class.getDeclaredFields();
        declaredFields =
                Arrays.stream(declaredFields).filter(a -> !"serialVersionUID".equals(a.getName())).toArray(Field[]::new);
        ExportExcelUtil.exportExcel(workbook, fileName, declaredFields, positions, "yyyy-MM-dd HH:mm:ss");
        workbook.write(response.getOutputStream());
        response.flushBuffer();
    }