• Home
• io.gitee.loulan_yxq
• owner-excel
• 1.4.2.2
• source code
• ExcelTool.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

io.gitee.loulan_yxq.owner.poi.excel.ExcelTool Maven / Gradle / Ivy

package io.gitee.loulan_yxq.owner.poi.excel;

import io.gitee.loulan_yxq.owner.core.enums.FileMagic;
import io.gitee.loulan_yxq.owner.core.io.FileTool;
import io.gitee.loulan_yxq.owner.core.tool.ObjectTool;
import io.gitee.loulan_yxq.owner.poi.excel.entity.ExcelExportConfiguration;
import io.gitee.loulan_yxq.owner.poi.excel.entity.ExcelImportConfiguration;
import io.gitee.loulan_yxq.owner.poi.excel.entity.ExcelImportData;
import io.gitee.loulan_yxq.owner.poi.excel.enums.ExcelTypeEnum;
import io.gitee.loulan_yxq.owner.poi.excel.exception.ExcelException;
import io.gitee.loulan_yxq.owner.poi.excel.tool.*;
import org.dom4j.DocumentException;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/*********************************************************
 ** 表格操作得工具类
 ** 


 ** Date: Created in 2022/2/7  19:49
 ** @author loulan
 ** @version 0.0.0
 *********************************************************/
public class ExcelTool {

    /**
     * 读取excel数据
     *
     * @param excelInputstream         excel的输入流
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream excelInputstream) throws IOException {
        return read(excelInputstream, 0,null);
    }

    /**
     * 读取excel数据
     *
     * @param excelInputstream         excel的输入流
     * @param sheetIndex               工作簿索引
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream excelInputstream, Integer sheetIndex) throws IOException {
        return read(excelInputstream, sheetIndex,null);
    }

    /**
     * 读取excel数据
     *
     * @param excelInputstream         excel的输入流
     * @param sheetName                工作簿名称
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream excelInputstream, String sheetName) throws IOException {
        return read(excelInputstream, sheetName, null);
    }

    /**
     * 读取excel数据
     *
     * @param xmlConfigInputStream excel导入配置文件输入流
     * @param excelInputstream     excel的输入流
     * @param sheetName            工作簿名称
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream xmlConfigInputStream, InputStream excelInputstream, String sheetName) throws IOException, DocumentException {
        if (ObjectTool.isNull(excelInputstream)) {
            throw new NullPointerException("excel输入流不能为空");
        }

        if (ObjectTool.isNull(xmlConfigInputStream)) {
            throw new NullPointerException("导入配置文件输入流不能为空");
        }

        ExcelImportConfiguration excelImportConfiguration = ExcelXmlParseTool.parseExcelIMportXml(xmlConfigInputStream);
        return read(excelInputstream, sheetName, excelImportConfiguration);
    }

    /**
     * 读取excel数据
     *
     * @param xmlConfigInputStream excel导入配置文件输入流
     * @param excelInputstream     excel的输入流
     * @param sheetIndex           工作簿索引
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream xmlConfigInputStream, InputStream excelInputstream, Integer sheetIndex) throws IOException, DocumentException {
        if (ObjectTool.isNull(excelInputstream)) {
            throw new NullPointerException("excel输入流不能为空");
        }

        if (ObjectTool.isNull(xmlConfigInputStream)) {
            throw new NullPointerException("导入配置文件输入流不能为空");
        }

        ExcelImportConfiguration excelImportConfiguration = ExcelXmlParseTool.parseExcelIMportXml(xmlConfigInputStream);
        return read(excelInputstream, sheetIndex, excelImportConfiguration);
    }

    /**
     * 读取excel数据
     *
     * @param excelInputstream         excel的输入流
     * @param sheetIndex               工作簿索引
     * @param excelImportConfiguration excel导入配置
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream excelInputstream, Integer sheetIndex, ExcelImportConfiguration excelImportConfiguration) throws IOException {
        if (ObjectTool.isNull(excelInputstream)) {
            throw new NullPointerException("输入流不能为空");
        }

        InputStream inputStream = FileMagic.prepareToCheckMagic(excelInputstream);
        FileMagic fileMagic = FileMagic.valueOf(inputStream);

        ExcelReader excelReader = null;
        switch (fileMagic) {
            case OLE2:
                excelReader = ExcelHReader.create();
                break;
            case OOXML:
                excelReader = ExcelXReader.create();
                break;
            default:
                throw new IOException("文件类型错误");
        }
        return excelReader.read(inputStream, sheetIndex, excelImportConfiguration);
    }

    /**
     * 读取excel数据
     *
     * @param excelInputstream         excel的输入流
     * @param sheetName                工作簿名称
     * @param excelImportConfiguration excel导入配置
     * @return excel导入数据
     * @author :loulan
     */
    public static ExcelImportData read(InputStream excelInputstream, String sheetName, ExcelImportConfiguration excelImportConfiguration) throws IOException {
        if (ObjectTool.isNull(excelInputstream)) {
            throw new NullPointerException("输入流不能为空");
        }

        InputStream inputStream = FileMagic.prepareToCheckMagic(excelInputstream);
        FileMagic fileMagic = FileMagic.valueOf(inputStream);

        ExcelReader excelReader = null;
        switch (fileMagic) {
            case OLE2:
                excelReader = ExcelHReader.create();
                break;
            case OOXML:
                excelReader = ExcelXReader.create();
                break;
            default:
                throw new IOException("文件类型错误");
        }
        return excelReader.read(inputStream, sheetName, excelImportConfiguration);
    }

    /**
     * 导出excel (表头为对象的属性名称)
     * 默认导出xls
     *
     * @param os        输出流
     * @param data      要写数据的list集合
     * @param sheetName 工作簿得名称
     * @author :loulan
     */
    public static void export(OutputStream os, String sheetName, List data) {
        export(os, sheetName, data, ExcelTypeEnum.XLS);
    }

    /**
     * 导出excel (表头为对象的属性名称)
     *
     * @param os        输出流
     * @param data      要写数据的list集合
     * @param sheetName 工作簿得名称
     * @param excelType excel表的类型，xls或者xlsx
     * @author :loulan
     */
    public static void export(OutputStream os, String sheetName, List data, ExcelTypeEnum excelType) {
        export(os, null, sheetName, data, excelType);
    }

    /**
     * 导出excel(可配置表头)
     * 默认导出xls
     *
     * @param os          输出流
     * @param data        要写数据的list集合
     * @param headerAlias 表头对应的别名，这个是对应list数据中对象的属性对应得名称，比如指定一个map数据为，key='uername',value='用户名'
     * @param sheetName   工作簿得名称
     * @author :loulan
     */
    public static void export(OutputStream os, Map headerAlias, String sheetName, List data) {
        export(os, headerAlias, sheetName, data, ExcelTypeEnum.XLS);
    }

    /**
     * 导出excel(可配置表头)
     *
     * @param os          输出流
     * @param data        要写数据的list集合
     * @param headerAlias 表头对应的别名，这个是对应list数据中对象的属性对应得名称，比如指定一个map数据为，key='uername',value='用户名'
     * @param sheetName   工作簿得名称
     * @param excelType   excel表的类型，xls或者xlsx
     * @author :loulan
     */
    public static void export(OutputStream os, Map headerAlias, String sheetName, List data, ExcelTypeEnum excelType) {
        if (ObjectTool.isNull(excelType)) {
            // 设置导出表的默认类型
            excelType = ExcelTypeEnum.XLS;
        }

        ExcelWriter writer = null;
        if (ExcelTypeEnum.XLS.getCode().equals(excelType.getCode())) {
            writer = ExcelHWriter.create();
        } else if (ExcelTypeEnum.XLSX.getCode().equals(excelType.getCode())) {
            writer = ExcelXWriter.create();
        } else {
            throw new ExcelException("the excelType is error!");
        }
        writer.write(os, headerAlias, sheetName, data);
        writer.close();
    }

    /**
     * 导出excel(可配置,默认导出xls)
     *
     * @param exportConfiguration excel导出配置
     * @author :loulan
     */
    public static void export(ExcelExportConfiguration exportConfiguration) {
        export(exportConfiguration, ExcelTypeEnum.XLS);
    }

    /**
     * 导出excel(可配置)
     *
     * @param exportConfiguration excel导出配置
     * @param excelType           excel表的类型，xls或者xlsx
     * @author :loulan
     */
    public static void export(ExcelExportConfiguration exportConfiguration, ExcelTypeEnum excelType) {
        if (ObjectTool.isNull(excelType)) {
            // 设置导出表的默认类型
            excelType = ExcelTypeEnum.XLS;
        }

        ExcelWriter writer = null;
        if (ExcelTypeEnum.XLS.getCode().equals(excelType.getCode())) {
            writer = ExcelHWriter.create();
        } else if (ExcelTypeEnum.XLSX.getCode().equals(excelType.getCode())) {
            writer = ExcelXWriter.create();
        } else {
            throw new ExcelException("the excelType is error!");
        }
        writer.write(exportConfiguration);
        writer.close();
    }
}