Java ExcelReadUtil

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.16</version>
</dependency>

public class Client {
    public static void main(String[] args) {
        String path = "D:\\xxx.xls";
        if (ExcelReadUtil.check(path)) {
//            Map<String, Object> result = ExcelReadUtil.readExcel(path);
//            Map<String, Object> result = ExcelReadUtil.readExcel(new FileInputStream(path));
            List<Map<String, Object>> sheetMapList = ExcelReadUtil.readExcel(path, true);
//            List<Map<String, Object>> sheetMapList = ExcelReadUtil.readExcel(new FileInputStream(path), true);
            for (Map<String, Object> sheetMap : sheetMapList) {
                List<String> titles = (List<String>) sheetMap.get(ExcelReadUtil.TITLE);
                List<List<String>> content = (List<List<String>>) sheetMap.get(ExcelReadUtil.CONTENT);
                System.out.println(titles);
                System.out.println(content);
                System.out.println(sheetMap);
            }
        }
    }
}

 

package com.chenqx.demo.demo_util.excelutil;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author chenqx1
 * @description 支持2003、2007、线程安全
 * @since 2017/11/9
 */
public final class ExcelReadUtil {
    public static final String TITLE = "title";
    public static final String CONTENT = "content";
    private static final ThreadLocal<NumberFormat> NUMERIC_FORMAT = new ThreadLocal<NumberFormat>() {
        @Override
        public NumberFormat initialValue() {
            return new DecimalFormat("###################.###########");
        }
    };
    private static final ThreadLocal<DateFormat> DATE_FORMAT = new ThreadLocal<DateFormat>() {
        @Override
        public DateFormat initialValue() {
            return new SimpleDateFormat("yyyy/MM/dd");
        }
    };

    private ExcelReadUtil() {
    }

    /**
     * 判断导入文件为excel（暂时以文件后缀判断）
     *
     * @param path
     * @return
     */
    public static boolean check(String path) {
        return path.endsWith(".xls") || path.endsWith(".xlsx");
    }

    /**
     * 使用：
     * Map<String, Object> result = ExcelReadUtil.readExcel(path);
     * List<String> titles = (List<String>) result.get(ExcelReadUtil.TITLE);
     * List<List<String>> content = (List<List<String>>) result.get(ExcelReadUtil.CONTENT);
     *
     * @param path
     * @return
     */
    public static Map<String, Object> readExcel(String path) {
        return readExcel(path, false).get(0);
    }

    public static Map<String, Object> readExcel(InputStream is) {
        return readExcel(is, false).get(0);
    }

    /**
     * 使用：
     * List<Map<String, Object>> sheetMapList = ExcelReadUtil.readExcel(path);
     * sheetMap = sheetMapList.get(0);
     * List<String> titles = (List<String>) sheetMap.get(ExcelReadUtil.TITLE);
     * List<List<String>> content = (List<List<String>>) sheetMap.get(ExcelReadUtil.CONTENT);
     *
     * @param path
     * @param isReadAllSheet
     * @return
     */
    public static List<Map<String, Object>> readExcel(String path, boolean isReadAllSheet) {
        List<Map<String, Object>> resultMaplList;

        try (InputStream is = new FileInputStream(path)) {
            resultMaplList = readExcel(is, isReadAllSheet);
        } catch (IOException e) {
            throw new RuntimeException("文件没找到！");
        }

        return resultMaplList;
    }

    public static List<Map<String, Object>> readExcel(InputStream is, boolean isReadAllSheet) {
        List<Map<String, Object>> resultMapList;

        try (Workbook wb = WorkbookFactory.create(is)) {
            resultMapList = readWorkbook(wb, isReadAllSheet);

            wb.close();
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException("读取Excel流失败！");
        }

        return resultMapList;
    }

    public static List<Map<String, Object>> readWorkbook(Workbook wb, boolean isReadAllSheet) {
        List<Map<String, Object>> resultMapList = new ArrayList<>();

        Iterator<Sheet> sheetIterator = wb.sheetIterator();
        while (sheetIterator.hasNext()) {
            Map<String, Object> resultMap = new HashMap<>();

            Sheet sheet = sheetIterator.next();
            resultMap.put(TITLE, readSheetTitles(sheet));
            resultMap.put(CONTENT, readSheetContent(sheet));

            resultMapList.add(resultMap);

            if (!isReadAllSheet) {
                break;
            }
        }

        return resultMapList;
    }

    public static List<String> readSheetTitles(Sheet sheet) {
        List<String> resultList = new ArrayList<>();

        Iterator<Row> rowIter = sheet.rowIterator();
        if (rowIter.hasNext()) {
            Iterator<Cell> cellIter = rowIter.next().iterator();
            while (cellIter.hasNext()) {
                resultList.add(getCellValue(cellIter.next()));
            }
        }

        return resultList;
    }

    public static List<List<String>> readSheetContent(Sheet sheet) {
        List<List<String>> resultList = new ArrayList<>();

        Row row;
        Cell cell;
        int colCount = 0;
        Iterator<Row> rowIter = sheet.rowIterator();
        if (rowIter.hasNext()) { // 第一行为标题
            row = rowIter.next();
            colCount = row.getPhysicalNumberOfCells();
        }

        while (rowIter.hasNext()) {
            List<String> cellValues = new ArrayList<>();
            row = rowIter.next();

            for (int cellNum = 0; cellNum < colCount; cellNum++) {
                cell = row.getCell(cellNum);
                cellValues.add(getCellValue(cell));
            }

            resultList.add(cellValues);
        }

        return resultList;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellTypeEnum();
        if (CellType.FORMULA.equals(cellType)) {
            cellType = cell.getCachedFormulaResultTypeEnum();
        }

        return doGetCellValue(cell, cellType);
    }

    private static String doGetCellValue(Cell cell, CellType cellType) {
        String cellValue;

        switch (cellType) {
            case NUMERIC:
                cellValue = String.valueOf(NUMERIC_FORMAT.get().format(cell.getNumericCellValue()));
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = String.valueOf(DATE_FORMAT.get().format(cell.getDateCellValue()));
                }
                break;
            case STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            default:
                cellValue = "";
        }

        return cellValue;
    }
}

 

转载于:https://my.oschina.net/u/1583957/blog/1595162