List<Map>写入Excel，poi操作

前言：公司最近需要将所有的报表导出集中到报表中心系统中，需要做一个通用的Excel工具类，让各个业务系统简单高效的生成Excel报表。由于原先各个业务系统生成报表方式都不一样(有的地方还直接使用了CSV)，因此需要统一生成Excel。本来想用easy-poi，但是发现过于复杂，而且我们的需求也偏简单，因此直接使用的是poi原生，使用的是3.8，本来想用新的3.15，但是发现业务系统已经使用了poi3.8，如果我这边使用poui3.15就会出现问题(问题就是程序运行到那个高版本的代码处，而低版本没有那个方法的话，就直接结束，异常什么都没有，这是一个大坑)。

简单粗暴直接贴代码：pom.xml

        <!-- poi,excel导出的依赖包 -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.8</version>        </dependency>

工具类

package liwenguang.demo.poi.util;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map.Entry;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;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.ss.usermodel.WorkbookFactory;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellUtil;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil<T> {    /**     * 传入一个模板文件，将模板文件复制到另一个文件里，以后就直接拿复制之后的文件进行操作，而不要动模板文件     *      * @param templateFile     *            模板文件必须存在     * @param outPutUrl     *            输出文件如果存在则会被覆盖     */    public static void copyTemplate(File templateFile, String outPutUrl, ReportFileTypeEnum fileTyle) {        Workbook wb = null;        NPOIFSFileSystem npoifs = null;        OPCPackage pkg = null;        switch (fileTyle) {        case XLS:            try {                npoifs = new NPOIFSFileSystem(templateFile);                wb = WorkbookFactory.create(npoifs);            } catch (Exception e1) {                e1.printStackTrace();            } finally {            }            break;        case XLSX:            try {                pkg = OPCPackage.openOrCreate(templateFile);                wb = WorkbookFactory.create(pkg);            } catch (Exception e1) {                e1.printStackTrace();            } finally {            }            break;        default:            throw new RuntimeException();        }        try (FileOutputStream fileOut = new FileOutputStream(outPutUrl)) {            wb.write(fileOut);        } catch (Exception e) {            e.printStackTrace();        } finally {            if (npoifs != null) {                try {                    npoifs.close();                } catch (IOException e) {                    e.printStackTrace();                }            }            if (pkg != null) {                try {                    pkg.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }    }    /**     * 将文件变成带数据的文件     * @param wb     * @param fileTyle            * @param data           数据     * @param rowNo          从哪行开始写数据     * @param columnNo       从哪列开始写数据     * @param dataHeight     表头的高度       * @param headers        头     */    public static <T> void file2FileWithData(Workbook wb, ReportFileTypeEnum fileTyle, List<T> data, int rowNo,            int columnNo, int dataHeight,            Object... headers) {        switch (fileTyle) {        case XLS:            file2FileWithDataXls(wb, data, rowNo, columnNo, dataHeight, headers);            break;        case XLSX:            file2FileWithDataXlsx(wb, data, rowNo, columnNo, dataHeight, headers);            break;        default:            throw new RuntimeException();        }    }    // 字符基数，设置为5cm    private final static int WIDTH_BASE = (int) ((5 + 0.72) * 256);    /**     * 通过代码生成模板文件     *      * @param outTemplate     *            模板文件生成之后的地址     * @param dataCount     *            数据大小，用于生成sheet     * @param excelTitle     *            模板文件中的第一行的标题     * @param titleHeight     *            字体大小     * @param fileType     *            文件类型 JobReport.XLS/XLSX     * @param width     *            value为第几列，key为该列的宽度，基数为@WIDTH_BASE     * @param excelHeaders     *            模板文件中第二行的表头     */    public static <T> void code2Tempalte(String outTemplate, int dataCount, String excelTitle, int titleHeight,            ReportFileTypeEnum fileType, HashMap<Integer, Integer> width,            Object... excelHeaders) {        FileOutputStream fileOut = null;        Workbook wb = null;        // 如果数据量大于65000则进行分sheet。        if (fileType.equals(ReportFileTypeEnum.XLSX)) {            wb = new XSSFWorkbook();            int sheetSize = dataCount / 65000 + 1;            for (int i = 0; i < sheetSize; i++) {                wb.createSheet("Sheet" + i);            }            wb.createSheet();        } else if (fileType.equals(ReportFileTypeEnum.XLS)) {            wb = new HSSFWorkbook();            int sheetSize = dataCount / 65000 + 1;            for (int i = 0; i < sheetSize; i++) {                wb.createSheet("Sheet" + i);            }        } else {            throw new RuntimeException();        }        try {            fileOut = new FileOutputStream(outTemplate);            // 如果模板有两个sheet，则需要在每个sheet都做一个表头和表标题            int sumSheet = wb.getNumberOfSheets();            for (int i = 0; i < sumSheet; i++) {                Sheet sheet0 = wb.getSheetAt(i);                // 开始设置每列的宽度                Iterator<Entry<Integer, Integer>> iter = width.entrySet().iterator();                while (iter.hasNext()) {                    Entry<Integer, Integer> entry = iter.next();                    Integer key = entry.getKey();                    Integer val = entry.getValue();                    sheet0.setColumnWidth(key, WIDTH_BASE * val);                }                Row row0 = sheet0.createRow(0);                row0.setHeightInPoints(2 * row0.getHeightInPoints());                // 设置样式                CellStyle style = wb.createCellStyle();                style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中                style.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中                Font font = wb.createFont();                font.setFontHeightInPoints((short) titleHeight);                font.setFontName("Courier New");                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);                style.setFont(font);                // 设置表标题                CellUtil.createCell(row0, 0, excelTitle, style); // 第一个参数为哪行，第二个参数为当行的第几列，0起始                String ref = "A1:" + (char) (excelHeaders.length + 64) + "1"; // 将数字变成字母，使用了ascll                CellRangeAddress region = CellRangeAddress.valueOf(ref); // 合并                sheet0.addMergedRegion(region);                // 设置表头                Row row1 = sheet0.createRow(1);                row1.setHeightInPoints(2 * row1.getHeightInPoints());                for (int j = 0; j < excelHeaders.length; j++) {                    CellUtil.createCell(row1, j, excelHeaders[j].toString(), style);                }            }            wb.write(fileOut);        } catch (Exception e) {            e.printStackTrace();        } finally {            if (fileOut != null) {                try {                    fileOut.close();                } catch (IOException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        }    }    /**     * 宽度自适应     *      * @param wb     * @param columnAutoWidth     *            哪些列进行宽度自适应，如果表头的宽度大于数据宽度，不要补齐     */    @Deprecated    public static void fixWidth(Workbook wb, int[] columnAutoWidth) {        if (columnAutoWidth == null) {            return;        }        int sum = wb.getNumberOfSheets();        Sheet sheet0 = null;        for (int i = 0; i < sum; i++) {            sheet0 = wb.getSheetAt(i);            for (int j = 0; j < columnAutoWidth.length; j++) {                sheet0.autoSizeColumn(columnAutoWidth[j]);            }        }    }    /**     * 通过传入excel格式，返回不同excel的实例     *      * @param fileType     * @param fo     * @return     */    public static Workbook getWorkbookInstance(ReportFileTypeEnum fileType, FileInputStream fo) {        Workbook workbookResult = null;        switch (fileType) {        case XLSX:            XSSFWorkbook workbook1 = null;            try {                workbook1 = new XSSFWorkbook(fo);            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            workbookResult = new SXSSFWorkbook(workbook1);            break;        case XLS:            try {                workbookResult = new HSSFWorkbook(fo);            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            break;        default:            throw new RuntimeException();        }        return workbookResult;    }    /**     * 传入文件路径，以及设置从第几行第几列开始写入数据，再给数据，最终将该文件填充数据。如果想进行循环分页，则data变化，rowNo变化即可，columnNo在一般情况下不用变化     *      * @param outPutUrl     *            最终文件URL(文件必须存在，并且文件是模板文件的复制文件，因为操作为直接操作此URL文件，如果是模板文件的原地址，则模板文件就会变化)     * @param data     *            通过数据库查询到的page后获得的list     * @param rowNo     *            左上角的Y坐标，0开始 → x     * @param columnNo     *            左上角的X坐标，0开始↓ y     * @param dataHeight     *            数据的高度     * @param headers     *            excel数据的顺序，只使用到了key     */    private static <T> void file2FileWithDataXlsx(Workbook wb, List<T> data, int rowNo, int columnNo, int dataHeight,            Object... headers) {        int sheetIndex = rowNo / 100000;        if (rowNo > sheetIndex * 100000 && rowNo < (sheetIndex + 1) * 100000) {            rowNo = rowNo - sheetIndex * 100000;        }        file2FileWithData(wb, data, rowNo, columnNo, dataHeight, sheetIndex, headers);    }    private static <T> void file2FileWithData(Workbook wb, List<T> data, int rowNo, int columnNo, int dataHeight,            int sheetIndex,            Object... headers) {        Sheet sheet0 = wb.getSheetAt(sheetIndex);        CellStyle style = wb.createCellStyle();        Font font = wb.createFont();        font.setFontHeightInPoints((short) dataHeight);        font.setFontName("Courier New");        style.setFont(font);        style.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中        Iterator<T> iterator = data.iterator();        float rowHeight = 0;        DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        for (int i = rowNo; iterator.hasNext();) {            Model t = (Model) iterator.next();            rowHeight = sheet0.getRow(0).getHeightInPoints();            Row row = sheet0.getRow(i);            if (row == null) {                row = sheet0.createRow(i);            }            row.setHeightInPoints(rowHeight * 2 / 3);            i++;            int j = columnNo;            for (int j2 = 0; j2 < headers.length; j2++) {                Object finalrowStr = t.get(headers[j2].toString());                Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);                j++;                if (finalrowStr == null) {                    finalrowStr = "";                }                cell.setCellValue(finalrowStr.toString());                cell.setCellStyle(style);            }        }    }    private static <T> void file2FileWithDataXls(Workbook wb, List<T> data, int rowNo, int columnNo, int dataHeight,            Object... headers) {        int sheetIndex = rowNo / 65000;        if (rowNo > sheetIndex * 65000 && rowNo < (sheetIndex + 1) * 65000) {            rowNo = rowNo - sheetIndex * 65000;        }        file2FileWithData(wb, data, rowNo, columnNo, dataHeight, sheetIndex, headers);    }    /**     * 用于给Excel创建Sheet，使用模板文件而不是code生成模板文件才需要执行这个方法     *      * @param workbook     * @param dataCount     * @param bufferSize     */    public static <T> void createSheet(Workbook workbook, int dataCount) {        int sheetSum = dataCount / 65000;        for (int i = 0; i < sheetSum; i++) {            workbook.cloneSheet(0);        }    }}

简单的测试，由于使用的是JFinal里面的Model，因为模拟了数据源

package liwenguang.demo.poi;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;import liwenguang.demo.poi.util.ScheduleJob;import liwenguang.demo.poi.util.TemplateToXlsx;public class Example {    public static void main(String args[]) {        List<ScheduleJob> dataCode0 = new ArrayList<ScheduleJob>();        ScheduleJob job4 = new ScheduleJob();        job4.set("name", "天下");        job4.set("mobile", "13554481001");        job4.set("memberId", "900115336000003");        job4.set("openId", "oVDyijumNgKEbAEO-LJAbhs4utqY");        job4.set("subscribe", "取消关注");        job4.set("subscribeTime", "2015/9/14 12:11:57");        job4.set("time", "2015/12/2 16:25:04");        dataCode0.add(job4);        ScheduleJob job5 = new ScheduleJob();        job5.set("name", "天下无双");        job5.set("mobile", "13554481001");        job5.set("memberId", "900115336000003");        job5.set("openId", "oVDyijumNgKEbAEO-LJAbhs4utqY");        job5.set("subscribe", "取消关注");        job5.set("subscribeTime", "2015/9/14 12:11:57");        job5.set("time", "2015/12/2 16:25:04");        dataCode0.add(job5);        List<ScheduleJob> dataCode1 = new ArrayList<ScheduleJob>();        ScheduleJob job6 = new ScheduleJob();        job6.set("name", "天下无双第一");        job6.set("mobile", "13554481001");        job6.set("memberId", "900115336000003");        job6.set("openId", "oVDyijumNgKEbAEO-LJAbhs4utqY");        job6.set("subscribe", "取消关注");        job6.set("subscribeTime", "2015/9/14 12:11:57");        job6.set("time", "2015/12/2 16:25:04");        dataCode1.add(job6);        LinkedHashMap<String, String> headersCode = new LinkedHashMap<String, String>();        headersCode.put("name", "昵称");        headersCode.put("mobile", "手机号");        headersCode.put("memberId", "会员号");        headersCode.put("openId", "OpenId");        headersCode.put("subscribe", "关注状态");        headersCode.put("subscribeTime", "关注时间");        headersCode.put("time", "绑定时间");        TemplateToXlsx<ScheduleJob> xml = new TemplateToXlsx<ScheduleJob>();        String code2temlateUrl = "D:\\export\\code2template.xlsx";        String copytemplate2Url = "D:\\export\\copytemplate.xlsx";        // 两种获取模板的的方式，一种代码生成，一种直接从模板fileUrl复制。        xml.code2Tempalte(code2temlateUrl, "会员报表导出-营销模块", 12, true,         headersCode.values().toArray());        xml.copyTemplate(code2temlateUrl, copytemplate2Url);        // 两种写入数据的方式，一种一次性写入，一种分页，都是同一个方法。        int pageSize = 2;        for (int i = 0; i < pageSize; i++) {            if (i == 0) {                xml.file2FileWithData(code2temlateUrl, dataCode0, 2, 0, new int[] { 0, 1, 2, 3, 5, 6 },                        headersCode.keySet().toArray());            }            if (i == 1) {                xml.file2FileWithData(code2temlateUrl, dataCode1, 2 + i * dataCode0.size(), 0,                        new int[] { 0, 1, 2, 3, 5, 6 },                         headersCode.keySet().toArray());            }        }    }}

package liwenguang.demo.poi.util;import java.util.HashMap;import java.util.Map;import java.util.Set;public class Model {    private Map<String, Object> attrs = new HashMap<String, Object>();    public void set(String attr, Object value) {        attrs.put(attr, value);    }    public Object get(String attr) {        return attrs.get(attr);    }    public Set entrySet() {        return attrs.entrySet();    }}

package liwenguang.demo.poi.util;/** * @Description 定时任务的操作枚举 * @author cylion * @time 2017年5月31日 下午5:10:31 */public enum ReportFileTypeEnum {    XLSX(0, ".xlsx"),    XLS(1, ".xls");    int status;    String statusDesc;    private ReportFileTypeEnum(int status,String statusDesc){        this.status=status;        this.statusDesc=statusDesc;    }    public int getStatus(){        return status;    }    public String getStatusDesc(){        return statusDesc;    }    public static ReportFileTypeEnum getType(int status){        for (ReportFileTypeEnum excelFileTypeEnums : ReportFileTypeEnum.values()) {            if (excelFileTypeEnums.getStatus() == status) {                return excelFileTypeEnums;            }        }        return null;    }    public static String getStatusDesc(int status){        return getType(status).getStatusDesc();    }}

package liwenguang.demo.poi.util;public class ScheduleJob extends Model {}

package liwenguang.demo.poi;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.List;import org.apache.poi.ss.usermodel.Workbook;import liwenguang.demo.poi.util.ExcelUtil;import liwenguang.demo.poi.util.ReportFileTypeEnum;import liwenguang.demo.poi.util.ScheduleJob;public class Example {    private static final ReportFileTypeEnum X = ReportFileTypeEnum.XLSX;    public static void main(String args[]) {        List<ScheduleJob> dataCode0 = new ArrayList<ScheduleJob>();        for (int i = 0; i < 65000; i++) {            ScheduleJob job6 = new ScheduleJob();            job6.set("name", "天下无双第一");            job6.set("mobile", "13554481001");            job6.set("memberId", "900115336000003");            job6.set("openId", "oVDyijumNgKEbAEO-LJAbhs4utqY");            job6.set("subscribe", "取消关注");            job6.set("subscribeTime", "2015/9/14 12:11:57");            job6.set("time", "2015/12/2 16:25:04");            dataCode0.add(job6);        }        List<ScheduleJob> dataCode1 = new ArrayList<ScheduleJob>();        for (int i = 0; i < 65000; i++) {            ScheduleJob job6 = new ScheduleJob();            job6.set("name", "天下无双第一");            job6.set("mobile", "13554481001");            job6.set("memberId", "900115336000003");            job6.set("openId", "oVDyijumNgKEbAEO-LJAbhs4utqY");            job6.set("subscribe", "取消关注");            job6.set("subscribeTime", "2015/9/14 12:11:57");            job6.set("time", "2015/12/2 16:25:04");            dataCode1.add(job6);        }        LinkedHashMap<String, String> headersCode = new LinkedHashMap<String, String>();        headersCode.put("name", "昵称");        headersCode.put("mobile", "手机号");        headersCode.put("memberId", "会员号");        headersCode.put("openId", "OpenId");        headersCode.put("subscribe", "关注状态");        headersCode.put("subscribeTime", "关注时间");        headersCode.put("time", "绑定时间");        String code2temlateUrl = "D:\\export\\code2template" + X.getStatusDesc();        String copytemplate2Url = "D:\\export\\copytemplate" + X.getStatusDesc();        if (!new File("D:\\export").exists()) {            new File("D:\\export").mkdir();        }        HashMap<Integer, Integer> width = new HashMap<>();        width.put(0, 3);        width.put(1, 3);        width.put(2, 3);        width.put(3, 3);        width.put(4, 3);        width.put(5, 3);        width.put(6, 3);        // 两种获取模板的的方式，一种代码生成，一种直接从模板fileUrl复制。        ExcelUtil.code2Tempalte(code2temlateUrl, dataCode1.size(), "会员报表导出-营销模块", 12, X, width,                headersCode.values().toArray());        File file = new File(code2temlateUrl);        if (!file.exists() || !file.isFile()) {            System.out.println("文件不存在");            return;        }        ExcelUtil.copyTemplate(file, copytemplate2Url, X);        try {            Workbook wb = ExcelUtil.getWorkbookInstance(X, new FileInputStream(copytemplate2Url));            int rowNo = 2; // 起始操作的行            int rowSum = 0;            ExcelUtil.file2FileWithData(wb, X, dataCode0, rowNo, 0, 12,                    headersCode.keySet().toArray());            rowSum = 2 + dataCode0.size();            ExcelUtil.file2FileWithData(wb, X, dataCode1, rowSum, 0, 12,                    headersCode.keySet().toArray());            FileOutputStream out = null;            out = new FileOutputStream(copytemplate2Url);            wb.write(out);            out.close();        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}