Java的POI的封装与应用

Java对Excel表格的导出一直是对我有种可怕噩梦的东西，每次对要建立行与列，并一个一个放值，我是从心底拒绝的。
处于项目需求，需要导出表格，于是找到网上一版很好的开发，《不想用POI？几行代码完成Excel导出导入》原文链接： https://juejin.im/post/5c3b683ee51d4551d14175ee
但是、我们项目不支持如此进行开发，
================================分割线=================================
因为需要引入xml文件进行开发，于是，只能自己动手封装一个满足需求的类：
一、需求：
查询统计的数据 List，进行数据导出，并且表头有合并单元格的需求。
直接上图：以下是部分结果（由于全部截取失真）
二、设计思路：
表头：：把表头与数据插入表格对象进行分开。表头部分，每一行做为一个list，使用String[]进行存储。每次分析表头，进行解析，生成表头。
表体：：需要插入的数据使用list中存入 map对象，其中的对象字段名称与表头部分相同，就可以进行数据的插入。
代码如下：
/** * Created by 闲一 on 2019/2/20.
 */
package com.test;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
 
public class POItest2 {
     
    @Test
    public void test1() throws IOException {
        List<String[]> list = new ArrayList<>();
        String[] arr = {"部门", "云速"};
        String[] arr1 = {"总计", "10000000"};
        String[] arr2 = {"单位", "元"};
        list.add(arr);
        list.add(arr1);
        list.add(arr2);
        XSSFWorkbook workbook = new XSSFWorkbook();
        workbook = workbookList(workbook, "come", "", list);
 
        String[] arra0 = {"name", "名称", "1", "1"};
        String[] arra1 = {"Person", "人类", "1", "2"};
        String[] arrb1 = {"Amions", "动物", "1", "1"};
        List<String[]> lista = new ArrayList<>();
        List<List<String[]>> titles = new ArrayList<>();
        lista.add(arra0);
        lista.add(arra1);
        lista.add(arrb1);
        titles.add(lista);
        String[] arr11 = {"", "", "2", "1"};
        List<String[]> list1 = new ArrayList<>();
        list1.add(arr11);
        String[] arr21 = {"sex", "性别", "1", "1"};
        list1.add(arr21);
        String[] arr31 = {"kaka", "咳咳", "1", "1"};
        list1.add(arr31);
        String[] arr41 = {"kaka", "小米", "1", "1"};
        list1.add(arr41);
        titles.add(list1);
 
        workbook = workbookinList(workbook, "come", "", titles, new ArrayList<>());
        workbook = workbookinList(workbook, "come", "", titles, new ArrayList<>());
 
        String path = "d:/data/2019-02-19-04.csv";
        wirteOutWorkbook(workbook, path);
    }
 
    /**
     * 将数据写入指定Excel对象中
     *
     * @param workbook  Excel对象
     * @param sheetName sheet名
     * @param style     Excel类型
     * @param titles    标题串 {{['person','人类',1,2]},
     *                  {['name','名称',1,1], ['sex','性别',1,1]}}   【 字段名称，标题，所占行数，所占列数】
     * @param values    内容集
     * @return TrueFalse
     */
    public XSSFWorkbook workbookinList(XSSFWorkbook workbook, String sheetName, String style, List<List<String[]>> titles, List<Map<String, Object>> values) {
        // 生成一个表格
        Sheet sheet;
        sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            if (null == sheetName || "".equals(sheetName)) {
                sheet = workbook.createSheet(); // sheetName 为空则使用默认值
            } else {
                sheet = workbook.createSheet(sheetName);
            }
        }
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 第一行生成账单标题
        Row row = null;
        int rowNumCount = 0 == sheet.getLastRowNum() ? 0 : sheet.getLastRowNum() + 2;//获得总行数
        // 存储标题在Excel文件中的序号
        Map<String, Integer> titleOrder = new HashMap<>();
        for (int j = 0; j < titles.size(); j++) {
            List<String[]> list = titles.get(j);
            row = sheet.createRow(rowNumCount + j);
            int curCol = 0; //当前列数名称
            for (int i = 0; i < list.size(); i++) {
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(curCol);
                String[] title = list.get(i);
                cell.setCellValue(title[1]); // 标题值
                // 需要记录每次的列位置
                int rowNum = Integer.valueOf(title[2]) - 1; // 需要占用的行数
                int colNum = Integer.valueOf(title[3]) - 1; // 需要占用的列数
                if (0 != rowNum || 0 != colNum) {
                    int curRow = rowNumCount + j;
                    CellRangeAddress region = new CellRangeAddress(curRow - rowNum, curRow, curCol, curCol + colNum);
                    sheet.addMergedRegion(region);
                    curCol += Integer.valueOf(title[3]);
                } else {
                    curCol++;
                }
                // 最后一行包含所有列
                if (j == titles.size() - 1) {
                    titleOrder.put(title[0], i);
                }
            }
        }
        /*
         * 写入正文
         */
        Iterator<Map<String, Object>> iterator = values.iterator();
        int index = rowNumCount + 1; // 行号
        while (iterator.hasNext()) {
            index++; // 出去标题行，从第一行开始写
            row = sheet.createRow(index);
            Map<String, Object> value = iterator.next();
            for (Map.Entry<String, Object> map : value.entrySet()) {
                // 获取列名
                String title = map.getKey();
                // 根据列名获取序号
                Integer i = titleOrder.get(title);
                if (i == null) {
                    continue;
                }
                // 在指定序号处创建cell
                Cell cell = row.createCell(i);
                // 获取列的值
                Object object = map.getValue();
                // 判断object的类型
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                if (object instanceof Double) {
                    cell.setCellValue((Double) object);
                } else if (object instanceof Date) {
                    String time = simpleDateFormat.format((Date) object);
                    cell.setCellValue(time);
                } else if (object instanceof Calendar) {
                    Calendar calendar = (Calendar) object;
                    String time = simpleDateFormat.format(calendar.getTime());
                    cell.setCellValue(time);
                } else if (object instanceof Boolean) {
                    cell.setCellValue((Boolean) object);
                } else {
                    if (object == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(object.toString());
                    }
                }
            }
        }
        return workbook;
    }
 
    /**
     * 将数据写入Excel文件中
     *
     * @param workbook  Excel对象
     * @param sheetName sheet名
     * @param style     Excel类型
     * @param dataList  标题串+内容集 {{'部门','201930'},{'总计','10000万'}}
     * @return TrueFalse
     */
    public XSSFWorkbook workbookList(XSSFWorkbook workbook, String sheetName, String style, List<String[]> dataList) {
        // 生成一个表格
        Sheet sheet;
        sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            if (null == sheetName || "".equals(sheetName)) {
                sheet = workbook.createSheet(); // sheetName 为空则使用默认值
            } else {
                sheet = workbook.createSheet(sheetName);
            }
        }
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 第一行生成账单标题
        Row row = null;
        int rowNumCount = 0 == sheet.getLastRowNum() ? 0 : sheet.getLastRowNum() + 2;//获得总行数
        // 存储标题在Excel文件中的序号
        for (int i = 0; i < dataList.size(); i++) {
            row = sheet.createRow(rowNumCount + i);
            row.createCell(0).setCellValue(dataList.get(i)[0]);
            row.createCell(1).setCellValue(dataList.get(i)[1]);
        }
        return workbook;
    }
 
    /**
     * 文件写出
     *
     * @param workbook
     * @param path
     * @throws IOException
     */
    public void wirteOutWorkbook(XSSFWorkbook workbook, String path) throws IOException {
        String dirPath = path.substring(0, path.lastIndexOf('.'));
        File dirFile = new File(dirPath);
        if (!dirFile.exists() && !dirFile.isDirectory()) {
            dirFile.mkdirs();
        }
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        OutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }
}
如有疑问，欢迎留言讨论！