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 True\False

     */

    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 True\False

     */

    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();

    }

}

 

如有疑问，欢迎留言讨论！