poi 操作 excel 工具类

获取数据集合(map形式),获取title,excel合并



package utils.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * @Author hzy
 * @Description excel 工具
 * 编写基于 poi 版本 3.9
 */
public class ExcelUtil {


    public static final String WORKBOOK_TYPE_XLS = ".xls";
    public static final String WORKBOOK_TYPE_XLSX = ".xlsx";

    /**
     * 1、HSSF支持.xls为后缀的二进制格式，并提供了流解析模式的HSSFListener相关API以及基于内存模型的HSSFWorkbook相关API。
     * 2、XSSF支持.xlsx为后缀的OpenXML格式。因为是底层文件是XML所以可以使用SAX解析，POI提供了XSSFReader用来获取压缩包中的各个XML文件相应的输入流；
     * 另外提供了基于DOM解析模式的XSSFWorkbook相关API。
     * 3、POI3.8后提供了SXSSF API，它是基于XSSF构建的低内存占用版本(使用滑动窗口机制来实现低内存访问)。
     * 但是需要注意的是SXSSFWorkbook默认使用内联字符串而不是共享字符串表(SharedStringsTable)，这样可以让保存在内存中的数据尽可能更少
     * (SharedStringsTable需要常驻内存)，所以如果是自己写SAX解析要注意兼容性。
     */

    /**
     * initWorkbook
     *
     * @param obj workbook 文件或者路径
     * @return Workbook
     * @throws IOException
     */
    public static Workbook initWorkbook(Object obj) throws IOException {
        if (obj == null) return null;
        Workbook workbook = null;
        if (obj instanceof String) {
            File file = new File((String) obj);
            if (!file.exists()) return null;
            System.out.println("init " + obj);
            InputStream fileInputStream = new FileInputStream(file);
            if (file.getName().endsWith(WORKBOOK_TYPE_XLS)) {
                workbook = new HSSFWorkbook(fileInputStream);
            } else if (file.getName().endsWith(WORKBOOK_TYPE_XLSX)) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else {
                throw new RuntimeException("un know workbook type...");
            }
        } else if (obj instanceof Workbook) {
            workbook = (Workbook) obj;
        } else {
            throw new RuntimeException("un expect param type...");
        }
        return workbook;
    }


    /**
     * get cell value
     *
     * @param cell org.apache.poi.ss.usermodel.Cell
     * @return object
     */
    private static Object getCellValue(Cell cell) {
        return cell == null ? null : getCellValue(cell, cell.getCellType());
    }

    private static Object getCellValue(Cell cell, int cellType) {
        if (cell == null) return null;
        Object cellValue = null;
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellValue = getCellValue(cell, cell.getCachedFormulaResultType());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_ERROR:
                cellValue = cell.getErrorCellValue();
                break;
        }
        return cellValue;
    }


    /**
     * 获取 title
     *
     * @param workbook    workbook 文件 或者路径
     * @param titleRowNum title row num
     * @return
     * @throws IOException
     */
    public static List<Map<Integer, Object>> getTitle(Object workbook, int titleRowNum) throws IOException {
        return getDataMap(workbook, 0, titleRowNum, 1, Integer.class);
    }

    public static List<Map<String, Object>> getDataMap(Object workbook, int titleRowNum) throws IOException {
        return getDataMap(workbook, titleRowNum, Integer.MAX_VALUE, Integer.MAX_VALUE, String.class);
    }

    /**
     * 读取 excel 数据
     *
     * @param workbookObj   路径, Workbook
     * @param titleRowNum   title 位于第几行,之前的数据将会跳过,后面根据字段信息把数据放到map里面
     * @param readRow       限制读取行数
     * @param readSheet     读取几个 sheet 页
     * @param resultKeyType 返回key类型 {Integer.class, String.class}Integer excel row 下标  String 第 skipRow 行的值
     * @param <K>
     * @return data List<Map<String, Object>>
     * @throws IOException exception
     */
    public static <K> List<Map<K, Object>> getDataMap(Object workbookObj, int titleRowNum
            , int readRow, int readSheet, Class<K> resultKeyType) throws IOException {
        if (null == workbookObj) return null;
        long beginTime = System.currentTimeMillis();
        Workbook workbook = initWorkbook(workbookObj);
        List<Map<K, Object>> dataList = new ArrayList<>();
        Map<Integer, String> propNames = new HashMap<>();
        for (int i = 0; (i < workbook.getNumberOfSheets() && i < readSheet); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator<Row> rowIterator = sheet.rowIterator();
            int rowIdx = 1;
            while (rowIterator.hasNext()) {
                if (rowIdx > readRow) break;
                Map<K, Object> dataMap = new HashMap<>();
                Row row = rowIterator.next();
                //cell不能用cellIterator的方式  不能跳过空行，否则会导致数据无法对齐
                for (Integer cellIdx = 0; cellIdx < row.getLastCellNum(); cellIdx++) {
                    if (rowIdx < titleRowNum) continue;
                    if (resultKeyType == String.class) {
                        if (rowIdx == titleRowNum) {
                            Object cellValue = getCellValue(row.getCell(cellIdx));
                            propNames.put(cellIdx, cellValue == null ? null : String.valueOf(cellValue));
                        } else {
                            K key = propNames == null ? (K) cellIdx : (K) propNames.get(cellIdx);
                            dataMap.put(key, getCellValue(row.getCell(cellIdx)));
                        }
                    } else {
                        //default Integer
                        dataMap.put((K) cellIdx, getCellValue(row.getCell(cellIdx)));
                    }
                }
                if (rowIdx > titleRowNum) dataList.add(dataMap);
                rowIdx++;
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println("读取" + workbook + "  耗时 | " + (endTime - beginTime) + "ms");
        return dataList;
    }


    /**
     * 创建一个 excel 文件
     *
     * @param workbookType workbook 类型
     * @param titleRow     excel title 首行数据
     * @param dataList     List<Map<title, value> 数据集合
     * @param <T>          key 类型, 最好 String
     * @return
     */
    public static <T> Workbook createWorkbook(String workbookType
            , Map<Integer, T> titleRow, List<Map<T, Object>> dataList) {
        long begin = System.currentTimeMillis();
        Workbook workbook;
        if (workbookType.equals(WORKBOOK_TYPE_XLS)) {
            workbook = new HSSFWorkbook();
        } else { //.xlsx
            workbook = new XSSFWorkbook();
        }
        Sheet sheet1 = workbook.createSheet("sheet1");
        int row_num = 0;
        if (titleRow != null) {
            Row row = sheet1.createRow(row_num);
            for (int i = 0; i < titleRow.size(); i++) {
                createCell(row, i, titleRow.get(i));
            }
            row_num++;
        }
        if (null != dataList && !dataList.isEmpty()) {
            for (Map<T, Object> data : dataList) {
                Row row = sheet1.createRow(row_num);
                for (int i = 0; i < titleRow.size(); i++) {
                    T key = titleRow.get(i);
                    createCell(row, i, data.get(key));
                }
                row_num++;
            }

        }
        long end = System.currentTimeMillis();
        System.out.println("创建 workbook 耗时 | " + (end - begin) + "ms");
        return workbook;
    }


    //创建 cell
    private static Cell createCell(Row row, int idx, Object value) {
        Cell cell = row.createCell(idx);
        if (isBlank(value)) {
            cell.setCellType(Cell.CELL_TYPE_BLANK);
            cell.setCellValue("");
        } else if (value instanceof Number) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(toDouble(value));
        } else if (value instanceof String) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(value));
        } else if (value instanceof Boolean) {
            cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(value));
        }
        return cell;
    }

    //是否为空
    private static boolean isBlank(Object obj) {
        if (null == obj) return true;
        if (obj instanceof String) {
            if (obj.equals("") || ((String) obj).trim().equals("")) {
                return true;
            }
        }
        return false;
    }

    //to double
    private static Double toDouble(Object obj) {
        if (obj instanceof Number) {
            return ((Number) obj).doubleValue();
        } else if (obj instanceof String) {
            return Double.parseDouble((String) obj);
        }
        return (Double) obj;
    }

    /**
     * 写出 excel 文件
     *
     * @param workbook workbook
     * @param path     输出路径
     * @throws IOException
     */
    public static void write(Workbook workbook, String path) throws IOException {
        long begin = System.currentTimeMillis();
        OutputStream outputStream = new FileOutputStream(path);
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println("写出 " + path + " 耗时 | " + (end - begin) + "ms");
    }


    /**
     * 合并 excel
     *
     * @param titleRow  titleRow
     * @param workbooks workbooks
     * @throws IOException
     * @return
     */
    public static Workbook mergeExcel(int titleRow, Object... workbooks) throws IOException {
        long begin = System.currentTimeMillis();
        if (null == workbooks || workbooks.length == 1) {
            return null;
        }
        List<Map<String, Object>> catchListMap = new ArrayList<>();
        Map<Integer, String> title = null;
        for (int i = 0; i < workbooks.length; i++) {
            Object workbook = null;
            if (i == 0) {
                workbook = initWorkbook(workbooks[i]);
                Map<Integer, Object> t1 = getTitle(workbook, titleRow).get(titleRow - 1);
                title = new HashMap<>();
                for (Integer integer : t1.keySet()) {
                    title.put(integer, String.valueOf(t1.get(integer)));
                }
            } else {
                workbook = workbooks[i];
            }
            List<Map<String, Object>> dataMap = getDataMap(workbook, titleRow, Integer.MAX_VALUE, Integer.MAX_VALUE, String.class);
            catchListMap.addAll(dataMap);
            dataMap.clear();
        }
        long end = System.currentTimeMillis();
        System.out.println("合并耗时 | " + (end - begin) + "ms");
        return createWorkbook(WORKBOOK_TYPE_XLS, title, catchListMap);
    }


}