前端時間由于對導出資料的需求比較多，excel或者pdf的都有，是以整理了下常用的動能，完成了對apache.poi的簡單封裝，以此來達到快速開發的目的，本文主要介紹EXCEL的導出實作，下面會提供API文檔以及源碼，歡迎交流指正（vx：lv630512957）。

1. API文檔

使用手冊和注意事項源碼+測試類+測試檔案

2. 代碼

2.1 參數配置類

public class ExcelConfig {
    public static final String XLSX = "xlsx";
    public static final String XLS = "xls";
    public int startLine = 0;
    public String dateFormate = "yyyy-MM-dd";
    public String suffix = "xlsx";
    public int[] columnWidth;
    public String sheetName = "sheet1";
    public int sheetIndex = 0;

    public ExcelConfig() {
    }

    public ExcelConfig(int startLine, String dateFormate, String suffix, int[] columnWidth, String sheetName, int sheetIndex) {
        this.startLine = startLine;
        this.dateFormate = dateFormate;
        this.suffix = suffix;
        this.columnWidth = columnWidth;
        this.sheetName = sheetName;
        this.sheetIndex = sheetIndex;
    }

    public int getStartLine() {
        return this.startLine;
    }

    public void setStartLine(int startLine) {
        this.startLine = startLine;
    }

    public String getDateFormate() {
        return this.dateFormate;
    }

    public void setDateFormate(String dateFormate) {
        this.dateFormate = dateFormate;
    }

    public String getSuffix() {
        return this.suffix;
    }

    public void setSuffix(String suffix) {
        this.suffix = suffix;
    }

    public int[] getColumnWidth() {
        return this.columnWidth;
    }

    public void setColumnWidth(int[] columnWidth) {
        this.columnWidth = columnWidth;
    }

    public String getSheetName() {
        return this.sheetName;
    }

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

    public int getSheetIndex() {
        return this.sheetIndex;
    }

    public void setSheetIndex(int sheetIndex) {
        this.sheetIndex = sheetIndex;
    }
}

2.2 無模闆導出類

import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExport {
    private static final int LEN_LIMIT = 500000;

    public ExcelExport() {
    }

    public static Workbook export(ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (arrFields != null && arrFields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (arrFields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                Workbook workbook = new XSSFWorkbook();
                Sheet sheet = workbook.createSheet(config.getSheetName());
                CellStyle headStyle = ExcelStyle.headerCellStyle(workbook);
                CellStyle cellStyle = ExcelStyle.cellStyle(workbook);
                Row row_field = sheet.createRow(config.getStartLine());
                int[] columeWidth = config.getColumnWidth();
                int i;
                int len;
                if (columeWidth != null && columeWidth.length == arrFields.length) {
                    i = 0;

                    for(len = arrFields.length; i < len; ++i) {
                        sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184);
                    }
                }

                i = 0;

                for(len = arrFields.length; i < len; ++i) {
                    Cell cell = row_field.createCell(i);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(arrFields[i].getValue());
                }

                i = 0;

                for(len = lstModel.size(); i < len; ++i) {
                    Row row = sheet.createRow(i + config.getStartLine() + 1);
                    Object rDto = lstModel.get(i);
                    Map map;
                    if (rDto instanceof Map) {
                        map = (Map)rDto;
                    } else {
                        map = ExcelUtils.objectToMap(rDto);
                    }

                    for(int j = 0; j < arrFields.length; ++j) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config));
                    }
                }

                return workbook;
            }
        } else {
            throw new Exception("表頭資料為空!");
        }
    }

    public static Workbook export(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (arrFields != null && arrFields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (arrFields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                Sheet sheet = workbook.createSheet(config.getSheetName());
                CellStyle headStyle = ExcelStyle.headerCellStyle(workbook);
                CellStyle cellStyle = ExcelStyle.cellStyle(workbook);
                Row row_field = sheet.createRow(config.getStartLine());
                int[] columeWidth = config.getColumnWidth();
                int i;
                int len;
                if (columeWidth != null && columeWidth.length == arrFields.length) {
                    i = 0;

                    for(len = arrFields.length; i < len; ++i) {
                        sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184);
                    }
                }

                i = 0;

                for(len = arrFields.length; i < len; ++i) {
                    Cell cell = row_field.createCell(i);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(arrFields[i].getValue());
                }

                i = 0;

                for(len = lstModel.size(); i < len; ++i) {
                    Row row = sheet.createRow(i + config.getStartLine() + 1);
                    Object rDto = lstModel.get(i);
                    Map map;
                    if (rDto instanceof Map) {
                        map = (Map)rDto;
                    } else {
                        map = ExcelUtils.objectToMap(rDto);
                    }

                    for(int j = 0; j < arrFields.length; ++j) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config));
                    }
                }

                return workbook;
            }
        } else {
            throw new Exception("表頭資料為空!");
        }
    }
}

2.3 有模闆導出類

import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelExportTemplate {
    private static final int LEN_LIMIT = 500000;

    public ExcelExportTemplate() {
    }

    public static Workbook exportByTemplate(InputStream in, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (in != null && fields != null && fields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (fields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                Workbook workbook = ExcelUtils.getWorkBookTemplate(config.getSuffix(), in);
                return fillData(workbook, fields, lstModel, config);
            }
        } else {
            throw new Exception("模闆檔案為空或表頭資料為空!");
        }
    }

    public static Workbook exportByTemplate(Workbook workbook, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (workbook != null && fields != null && fields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (fields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                return fillData(workbook, fields, lstModel, config);
            }
        } else {
            throw new Exception("模闆檔案為空或表頭資料為空!");
        }
    }

    public static Workbook exportByTemplateDynamicTitle(InputStream in, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (in != null && arrFields != null && arrFields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (arrFields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                Workbook workbook = ExcelUtils.getWorkBookTemplate(config.getSuffix(), in);
                return fillDataDynamicTitle(workbook, arrFields, lstModel, config);
            }
        } else {
            throw new Exception("模闆檔案為空或表頭資料為空!");
        }
    }

    public static Workbook exportByTemplateDynamicTitle(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception {
        if (workbook != null && arrFields != null && arrFields.length != 0) {
            if (lstModel == null) {
                throw new Exception("清單資料為空!");
            } else if (arrFields.length * lstModel.size() > 500000) {
                throw new Exception("excel資料量過大，請分批次重新生成!確定cell數量不大于500000");
            } else {
                if (config == null) {
                    config = new ExcelConfig();
                }

                return fillDataDynamicTitle(workbook, arrFields, lstModel, config);
            }
        } else {
            throw new Exception("模闆檔案為空或表頭資料為空!");
        }
    }

    private static Workbook fillData(Workbook workbook, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception {
        Sheet sheet = workbook.getSheetAt(config.getSheetIndex());
        CellStyle cellStyle = ExcelStyle.cellStyle(workbook);
        if (lstModel != null && lstModel.size() != 0) {
            if (sheet == null) {
                throw new Exception("檔案異常！");
            } else {
                int[] columeWidth = config.getColumnWidth();
                int i;
                int len;
                if (columeWidth != null && columeWidth.length == fields.length) {
                    i = 0;

                    for(len = fields.length; i < len; ++i) {
                        sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184);
                    }
                }

                i = 0;

                for(len = lstModel.size(); i < len; ++i) {
                    Row row = sheet.createRow(i + config.getStartLine());
                    Object rDto = lstModel.get(i);
                    Map map;
                    if (rDto instanceof Map) {
                        map = (Map)rDto;
                    } else {
                        map = ExcelUtils.objectToMap(rDto);
                    }

                    for(int j = 0; j < fields.length; ++j) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(fields[j]), config));
                    }
                }

                return workbook;
            }
        } else {
            return workbook;
        }
    }

    private static Workbook fillDataDynamicTitle(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception {
        Sheet sheet = workbook.getSheetAt(config.getSheetIndex());
        CellStyle headStyle = ExcelStyle.headerCellStyle(workbook);
        CellStyle cellStyle = ExcelStyle.cellStyle(workbook);
        if (lstModel != null && lstModel.size() != 0) {
            if (sheet == null) {
                throw new Exception("檔案異常！");
            } else {
                int[] columeWidth = config.getColumnWidth();
                int i;
                if (columeWidth != null && columeWidth.length == arrFields.length) {
                    int i = 0;

                    for(i = arrFields.length; i < i; ++i) {
                        sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184);
                    }
                }

                Row row_field = sheet.createRow(config.getStartLine());
                i = 0;

                int len;
                for(len = arrFields.length; i < len; ++i) {
                    Cell cell = row_field.createCell(i);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(arrFields[i].getValue());
                }

                i = 0;

                for(len = lstModel.size(); i < len; ++i) {
                    Row row = sheet.createRow(i + config.getStartLine() + 1);
                    Object rDto = lstModel.get(i);
                    Map map;
                    if (rDto instanceof Map) {
                        map = (Map)rDto;
                    } else {
                        map = ExcelUtils.objectToMap(rDto);
                    }

                    for(int j = 0; j < arrFields.length; ++j) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config));
                    }
                }

                return workbook;
            }
        } else {
            return workbook;
        }
    }
}

2.4 基本樣式類

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelStyle {
    public ExcelStyle() {
    }

    public static CellStyle headerCellStyle(Workbook workbook) {
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom((short)1);
        headerStyle.setBorderLeft((short)1);
        headerStyle.setBorderRight((short)1);
        headerStyle.setBorderTop((short)1);
        headerStyle.setAlignment((short)2);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short)12);
        headerFont.setBoldweight((short)700);
        headerStyle.setFont(headerFont);
        return headerStyle;
    }

    public static CellStyle cellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom((short)1);
        cellStyle.setBorderLeft((short)1);
        cellStyle.setBorderRight((short)1);
        cellStyle.setBorderTop((short)1);
        cellStyle.setAlignment((short)2);
        cellStyle.setVerticalAlignment((short)1);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight((short)400);
        cellStyle.setFont(cellFont);
        return cellStyle;
    }
}

2.5 表頭model類

public class ExcelTitle {
    private String key;
    private String value;

    public ExcelTitle() {
    }

    public String getKey() {
        return this.key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return this.value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

2.6 工具類

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import org.apache.commons.beanutils.BeanMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
    public ExcelUtils() {
    }

    public static Workbook getWorkBook(String suffix) throws Exception {
        return getWorkBookTemplate(suffix, (InputStream)null);
    }

    public static Workbook getWorkBookTemplate(String suffix, InputStream in) throws Exception {
        Object workbook = null;

        try {
            if ("xls".equalsIgnoreCase(suffix)) {
                workbook = new HSSFWorkbook(in);
            } else if ("xlsx".equalsIgnoreCase(suffix)) {
                workbook = new XSSFWorkbook(in);
            }

            return (Workbook)workbook;
        } catch (Throwable var4) {
            throw new Exception("建立excel工作簿workBook失敗");
        }
    }

    public static Map<?, ?> objectToMap(Object obj) {
        return obj == null ? null : new BeanMap(obj);
    }

    public static String cellvalueFormat(Object obj, ExcelConfig config) throws Exception {
        try {
            if (obj == null) {
                return "";
            } else if (!(obj instanceof Date) && !(obj instanceof java.sql.Date)) {
                return String.valueOf(obj);
            } else {
                SimpleDateFormat sdf = new SimpleDateFormat(config.getDateFormate());
                return sdf.format(obj);
            }
        } catch (Throwable var3) {
            throw new Exception("日期格式不符合規範!", var3);
        }
    }
}

Java導出EXCEL的封裝1. API文檔2. 代碼

1. API文檔

2. 代碼

2.1 參數配置類

2.2 無模闆導出類

2.3 有模闆導出類

2.4 基本樣式類

2.5 表頭model類

2.6 工具類

繼續閱讀

關于Gradle配置的小結

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method