com.gitee.cliveyuan.tools.data.ExcelHelper Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of java-tools Show documentation
Some commonly used methods in java
There is a newer version: 4.0.6
package com.gitee.cliveyuan.tools.data;

import com.gitee.cliveyuan.tools.Assert;
import com.gitee.cliveyuan.tools.CollectionTools;
import com.gitee.cliveyuan.tools.StringTools;
import com.gitee.cliveyuan.tools.enums.ExcelType;
import com.gitee.cliveyuan.tools.exception.ExcelException;
import com.google.common.collect.Maps;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;

/**
 * Excel 帮助类
 *
 * @author clive
 * Created on 2018/07/25
 * @since 1.0
 */
public class ExcelHelper extends TableAbstract {

    private static final Logger logger = LoggerFactory.getLogger(ExcelHelper.class);

    /**
     * 生成EXCEL
     *
     * @param headers  抬头 (要与数据中的属性顺序和数量对应)
     * @param data     数据
     * @param filePath 导出的文件路径
     * @throws ExcelException
     */
    public File generate(String[] headers, Collection data, String filePath) throws ExcelException {
        return generate(headers, data, filePath, ExcelType.XLSX);
    }

    /**
     * 生成EXCEL
     *
     * @param headers   抬头 (要与数据中的属性顺序和数量对应)
     * @param data      数据
     * @param filePath  导出的文件路径
     * @param excelType 导出文件格式
     * @return 导出的文件
     */
    public File generate(String[] headers, Collection data, String filePath, ExcelType excelType) {
        ExcelGenerateParam param = ExcelGenerateParam.builder()
                .filePath(filePath)
                .excelType(excelType)
                .build()
                .addSheetContent("Sheet1", headers, data);
        return TableUtils.generate(param);
    }



    /**
     * 解析EXCEL
     *
     * @param excelPath    excel绝对路径 (支持xlsx和xls)
     * @param skipFirstRow 是否跳过首行
     * @param clazz        类
     */
    public List parse(String excelPath, boolean skipFirstRow, Class clazz) throws ExcelException {
        int skipRowNo = 0;
        if (skipFirstRow) skipRowNo = 1;
        return parse(excelPath, skipRowNo, clazz);
    }

    /**
     * 解析EXCEL
     * (只解析第一个sheet)
     *
     * @param excelPath excel绝对路径 (支持xlsx和xls)
     * @param skipRowNo 跳过前n行
     * @param clazz     类
     */
    public List parse(String excelPath, int skipRowNo, Class clazz) throws ExcelException {

        try {
            Map> excelContent = ExcelTools.readExcel(excelPath);
            Optional first = excelContent.keySet().stream().findFirst();
            Assert.isTrue(first.isPresent(), "data is empty");
            List data = excelContent.get(first.get());
            return dataToObject(skipRowNo, data, clazz);
        } catch (IllegalArgumentException | ExcelException e) {
            throw e;
        } catch (Exception e) {
            logger.error("parse", e);
            throw ExcelException.failToParseExcel();
        }
    }

    /**
     * 解析EXCEL
     * (解析所有sheet)
     * map key 为sheetName, value为数据
     *
     * @param excelPath
     * @param clazz
     * @throws ExcelException
     */
    public Map> parse(String excelPath, Class clazz) throws ExcelException {
        Map> result = Maps.newHashMap();
        try {
            Map> excelContent = ExcelTools.readExcel(excelPath);
            excelContent.forEach((sheetName, data) ->
                result.put(sheetName, dataToObject(0, data, clazz))
            );
        } catch (IllegalArgumentException | ExcelException e) {
            throw e;
        } catch (Exception e) {
            logger.error("parse", e);
            throw ExcelException.failToParseExcel();
        }
        return result;
    }





}