Java用策略模式優雅地匯出Excel

依賴包

• common-lang
• poi

ExcelExport介面

public interface ExcelExport {

    boolean printHeader();//是否列印表頭

    Map<String, Column> getHeaderMap();//返回bean欄位和列名的對映

    String getOutputFilePath();//excel輸出路徑

    int getStartRowIndex();//從第幾行開始列印

    String getOutputFileName();//excel檔名

    String getSheetName();//表名

    CellStyle getHeaderCellStyle(XSSFWorkbook workbook);//表頭的格式

    //add more if necessary

}

export函式

public static<T> void export(List<T> list, ExcelExport export) throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    workbook.createCellStyle();
    XSSFSheet sheet = workbook.createSheet(export.getSheetName());
    Map<String, Column> headerMap = export.getHeaderMap();

    int rowIndex = export.getStartRowIndex() - 1;
    if(!export.printHeader()) {
        rowIndex--;
    }else {
        XSSFRow row = sheet.createRow(rowIndex);
        Object[] colNames = headerMap.values().toArray();
        for (int colIndex = 0; colIndex < colNames.length; colIndex++) {
            XSSFCell cell = row.createCell(colIndex);
            Column column = (Column)colNames[colIndex];
            sheet.setColumnWidth(colIndex, column.getWidth());
            cell.setCellStyle(export.getHeaderCellStyle(workbook));
            cell.setCellValue(((Column)colNames[colIndex]).getColumnDisplayName());
        }
    }

    for (int listIndex = 0; listIndex < list.size(); listIndex++) {
        int colIndex = 0;
        T rowObj = list.get(listIndex);
        XSSFRow row = sheet.createRow(++rowIndex);
        for (String colField : headerMap.keySet()) {
            Object colVaule = PropertyUtils.getProperty(rowObj, colField);
            XSSFCell cell = row.createCell(colIndex);
            cell.setCellValue(colVaule.toString());
            colIndex++;
        }
    }

    FileOutputStream fos = new FileOutputStream(export.getOutputFilePath());
    workbook.write(fos);
    fos.flush();
    fos.close();
}

抽象ExcelExport類

主要作用是設定預設值（行為）

public abstract class AbstractExcelExport implements ExcelExport {

    private boolean printHeader = true;

    private int startRowIndex = 0;

    private String outputFilePath = "d://";

    private String sheetName = "default sheet name";

    private String outputFileName = "default file name";

    public boolean printHeader() {
        return printHeader;
    }

    public void setPrintHeader(boolean printHeader) {
        this.printHeader = printHeader;
    }

    public int getStartRowIndex() {
        return startRowIndex;
    }

    public void setStartRowIndex(int startRowIndex) {
        this.startRowIndex = startRowIndex;
    }

    public String getOutputFilePath() {
        return outputFilePath;
    }

    public void setOutputFilePath(String outputFilePath) {
        this.outputFilePath = outputFilePath;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public String getOutputFileName() {
        return outputFileName;
    }

    public void setOutputFileName(String outputFileName) {
        this.outputFileName = outputFileName;
    }

}

具體實現類

繼承自抽象類，實現介面

public class CourseExport extends AbstractExcelExport {

    @Override
    public LinkedHashMap<String, Column> getHeaderMap() {
        return new LinkedHashMap<String, Column>(){{
            put("id", new Column("Course Id", 10000));
            put("name", new Column("Course Name", 10000));
        }};
    }


    @Override
    public CellStyle getHeaderCellStyle(XSSFWorkbook workbook) {
        CellStyle cs = workbook.createCellStyle();
        Font titleFont = workbook.createFont();  
        titleFont.setFontHeightInPoints((short) 18);  
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);  
        cs.setAlignment(CellStyle.ALIGN_CENTER);  
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
        cs.setFont(titleFont);  

        return cs;
    }

}

測試程式碼

public static void testExportExcel() throws Exception {
        Course c1 = new Course("WSDFERHI_43DFG", "Math");
        Course c2 = new Course("DFOIDOIH_345IS", "Mandarin");
        List<Course> ca = new ArrayList<>();
        ca.add(c1);
        ca.add(c2);
        CourseExport ex = new CourseExport();
        ex.setPrintHeader(false);
        ex.setStartRowIndex(1);
        ExcelUtils.export(ca, ex);
    }