「工具」Excel工具类

pom.xml依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

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

完整代码

/**
 * @version 1.0.0
 * @ClassName ExcelUtil.java
 * @Description excel工具类
 */
public final class ExcelUtil {

    private ExcelUtil() {
    }

    /**
     * 2003以下版本的excel
     */
    public static final String EXCEL_2003L = ".xls";
    /**
     * 2007以上版本的excel
     */
    public static final String EXCEL_2007U = ".xlsx";

    public static final String DATA_GENERAL_FORMAT = "General";

    public static final ThreadLocal<DecimalFormat> DECIMAL_FORMAT_LOCAL = ThreadLocal.withInitial(() -> new DecimalFormat("0"));

    public static final ThreadLocal<DecimalFormat> DECIMAL_FORMAT_LOCAL2 = ThreadLocal.withInitial(() -> new DecimalFormat("0.00"));

    /**
     * 获取IO流中的数据,组装成List<Object[]>对象
     *
     * @param sheet    第几张工作簿(以0开始)
     * @param startRow 工作簿中第行开始(以0开始)
     * @return
     * @throws Exception
     */
    public static List<Object[]> getExcelData(Sheet sheet, int startRow) throws Exception {
        List<Object[]> list = new ArrayList();
        //遍历当前sheet中的所有行
        List<Object> tampList = null;
        Row row = null;
        Cell cell = null;
        for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //遍历所有的列
            tampList = new ArrayList();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                tampList.add(getCellValue(cell));
            }
            list.add(tampList.toArray());
        }
        return list;
    }

    /**
     * 获取指定某一行的数据
     *
     * @param sheet
     * @param rowIndex 工作簿中第行开始(以0开始)
     * @return
     */
    public static List<Object> getRowData(Sheet sheet, int rowIndex) {
        List<Object> list = new ArrayList();
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return list;
        }
        Cell cell = null;
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            list.add(getCellValue(cell));
        }
        return list;
    }

    /**
     * 获取excel数据, 每一行数据就是一个Map, key为字段, value为数据值
     *
     * @return
     */
    public static List<Map<String, Object>> getExcelDataMapList(Sheet sheet, int startDataRowIndex, int headerRowIndex) throws Exception {
        //获取列头
        List<Object> headerCols = getRowData(sheet, headerRowIndex);
        //开始解析实际数据
        Row row = null;
        Cell cell = null;
        List<Map<String, Object>> list = new ArrayList();
        //遍历当前sheet中的所有行
        for (int i = startDataRowIndex; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String, Object> rowMap = new HashMap(16);
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                rowMap.put(String.valueOf(headerCols.get(j)), getCellValue(cell));
            }
            if (rowMap.isEmpty()) {
                continue;
            }
            list.add(rowMap);
        }
        return list;
    }

    /**
     * 描述:根据文件后缀,自适应上传文件的版本
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(File file) throws Exception {
        return getWorkbook(new FileInputStream(file), file.getName());
    }

    /**
     * 描述:根据文件后缀,自适应上传文件的版本
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(MultipartFile file) throws Exception {
        return getWorkbook(file.getInputStream(), file.getOriginalFilename());
    }

    /**
     * 获取sheet
     *
     * @param workbook
     * @param index    第几张工作簿(以0开始)
     * @return
     */
    public static Sheet getExcelSheet(Workbook workbook, int index) {
        Sheet sheet = workbook.getSheetAt(index);
        if (sheet == null) {
            throw new BizException("Excel工作薄为空!", CodeMsg.FILE_ANALYSIS_ERROR.getCode());
        }
        return sheet;
    }

    /**
     * 获取Sheet的最大行数
     *
     * @return
     */
    public static int getSheetMaxRowNum(Sheet sheet) {
        return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
    }

    public static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (EXCEL_2003L.equals(fileType)) {
            //2003以下
            wb = new HSSFWorkbook(in);
        } else if (EXCEL_2007U.equals(fileType)) {
            //2007以上
            wb = new XSSFWorkbook(in);
        } else {
            throw new BizException(CodeMsg.FILE_ANALYSIS_ERROR);
        }
        if (null == wb) {
            throw new BizException("Excel为空!", CodeMsg.FILE_ANALYSIS_ERROR.getCode());
        }
        return wb;
    }

    /**
     * 描述:对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        //用String接收所有返回的值
        String value = null;
        switch (cell.getCellType()) {
            case STRING:
                //String类型的数据
                value = cell.getStringCellValue();
                break;

            case NUMERIC:
                //数值类型(取值用cell.getNumericCellValue() 或cell.getDateCellValue())
                if (DATA_GENERAL_FORMAT.equals(cell.getCellStyle().getDataFormatString())) {
                    value = DECIMAL_FORMAT_LOCAL.get().format(cell.getNumericCellValue());
                } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = DateTimeUtil.DATE_TIME_LOCAL.get().format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    value = DECIMAL_FORMAT_LOCAL2.get().format(cell.getNumericCellValue());
                }
                break;

            case BOOLEAN:
                //Boolean类型
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                //表达式类型
                value = String.valueOf(cell.getCellFormula());
                break;

            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                value = "";
                break;
        }
        if (StringUtils.isBlank(value)) {
            value = "";
        }
        if (cell == null) {
            return "";
        }
        return value;
    }
}

 

上一篇:在线教育项目-day13【课程列表前端】


下一篇:14.8 实践项目Excel 到 CSV 的转换程序