Java 读取Excel工具类

最近项目需要 写了一个读取Excel的工具 可以获取到行列的信息 其后就任意处理数据了
而且还可以生成Excel

例子：

    // 垃圾桶分组
    @Test
    public void group(){
        ExcelReadResult excelReadResult = RWexcel.Instance().readExcel("D:\\奥园位置统计.xls", true);
        System.out.println(excelReadResult.tableBody);
    }

下面输出

[[1, 7, , 95, , 39.996305, 116.386228], [2, 24, , 35, , 39.996214, 116.38624], [3, 13, , 20, , 39.996172, 116.386099], [4, 6, , 14, , 39.996101, 116.385975], [5, 8, , 15, , 39.996094, 116.385756], [6, 16, , 76, , 39.996321, 116.385793], [7, 19, , 78, , 39.995936, 116.385757], [8, 33, , 69, , 39.995713, 116.385795], [9, 23, , 2, , 39.995744, 116.386048], [10, 31, , 25, , 39.99575, 116.386459], [11, 12, , 81, , 39.995423, 116.386573], [12, 88, , 50, , 39.995396, 116.386084], [13, 93, , 11, , 39.995415, 116.385801], [14, 56, , 75, , 39.994635, 116.385896], [15, 96, , 5, , 39.994647, 116.386249], [16, 10, , 27, , 39.994652, 116.386576], [17, 61, , 3, , 39.99445, 116.386635], [18, 4, , 72, , 39.99434, 116.386669], [19, 1, , 73, , 39.994168, 116.386701], [20, 46, , 22, , 39.994095, 116.386201], [21, 51, , 53, , 39.99412, 116.385904], [22, 68, , 91, , 39.99445, 116.386234], [23, 29, , 9, , 39.994438, 116.385924], [24, 36, , 80, , 39.993986, 116.385876], [25, 37, , 45, , 39.993729, 116.38588], [26, 34, , 49, , 39.993692, 116.386129], [27, 28, , 62, , 39.993687, 116.386312], [28, 17, , 47, , 39.993504, 116.386363], [29, 54, , 59, , 39.993473, 116.385903], [30, 74, , 65, , 39.992882, 116.386213], [31, 52, , 77, , 39.992896, 116.386584], [32, 18, , 79, , 39.992659, 116.3866], [33, 48, , 58, , 39.992612, 116.386402], [34, 85, , 89, , 39.992493, 116.386431], [35, 39, , 44, , 39.992539, 116.386225], [36, 82, , 94, , 39.992651, 116.386285], [37, 26, , 64, , 39.992648, 116.385904], [38, 57, , 83, , 39.992517, 116.385839], [39, 92, , 97, , 39.992296, 116.385955], [40, 40, , 71, , 39.992302, 116.38638], [41, 66, , 86, , 39.992317, 116.386655], [42, 42, , 63, , 39.992043, 116.386723]]

代码

/**
 * @author 李鑫
 * @version 1.0
 * @date 2019/7/5 13:31
 * @desc
 */
public class RWexcel {
    /**
     * @param filePath  文件路径
     * @param hasHead   是否有表头
     * @return
     */
    public static  RWexcel rWexcel;

    // 单例模式
    public static RWexcel Instance(){
        if (rWexcel == null){
            return  new RWexcel();
        }else {
            return rWexcel;
        }
    }

    // 读取excel 返回
    public ExcelReadResult readExcel(String filePath,boolean hasHead){

        ExcelReadResult readResult = new ExcelReadResult();
        InputStream inputStream ;

        ExcelReader reader;
        try {
            inputStream =new BufferedInputStream(new FileInputStream(filePath));
        } catch (FileNotFoundException e) {
            readResult.isSuccess=true;
            readResult.fialReason = "未找到文件";
            return readResult;
        }


        char c = filePath.charAt(filePath.length()-1);
        // 根据不同的文件后缀来判断是03版之前还是之后
        if (c=='s'){
            reader = EasyExcelFactory.getReader(inputStream, new ExcelListener(readResult, hasHead));
        }else if(c=='x'){
            reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, new ExcelListener(readResult, hasHead));
        }else {
            readResult.isSuccess=true;
            readResult.fialReason = "文件格式不匹配";
            return readResult;
        }
        reader.read();
        return readResult;
    }

    // 通过输入流来解析
    public ExcelReadResult readExcelByStream(InputStream is,String suffix, boolean hasHead){
        InputStream stream = new BufferedInputStream(is);
        ExcelReadResult readResult = new ExcelReadResult();
        ExcelReader reader;
        char c = suffix.charAt(suffix.length()-1);
        // 根据不同的文件后缀来判断是03版之前还是之后
        if (c=='s'){
            reader = EasyExcelFactory.getReader(stream, new ExcelListener(readResult, hasHead));
        }else if(c=='x'){
            reader = new ExcelReader(stream, ExcelTypeEnum.XLSX, new ExcelListener(readResult, hasHead));
        }else {
            readResult.isSuccess=true;
            readResult.fialReason = "文件格式不匹配";
            return readResult;
        }
        reader.read();
        return readResult;
    }

    // 生成Excel表格

    /**
     * @param head    要创建的excel的头信息
     * @param body    要创建的excel的内部信息
     * @param name    要创建的excel的名字
     * @param parentFilePath 创建的文件放在那个文件加下
     * @param env 选择工作的环境 Linux 是 ’L‘ windows 是 ’W‘
     * @return        excel的完整路径   Windows 默认在C盘excel文件夹下
     */
    public String writeExcel(ArrayList<String> head , List<List<String>> body,  String name , String parentFilePath, char env){
        FileOutputStream stream ;
        String filepath;
        if (env=='L'){
            filepath = parentFilePath+name+".xlsx";
        }else {
            filepath = "F:\\"+name+".xlsx";
        }
        try {
            File file = new File(filepath);
            file.setReadable(true,false);
            file.setWritable(true,false);
            file.setExecutable(true,false);
            if (file.exists()){
                file.delete();
            }else {
                //
            }
            //C:\AppCode\graduateDesign-master
            stream = new FileOutputStream(filepath);
        } catch (FileNotFoundException e) {
            return null;
        }
        ExcelWriter writer = new ExcelWriter(stream, ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1);
        sheet.setSheetName(name);
        List<List<String>> headdata = new ArrayList<List<String>>();
        for (String string : head){
            List<String> item = new ArrayList<String>();
            item.add(string);
            headdata.add(item);
        }
        sheet.setHead(headdata);
        writer.write0(body,sheet);
        writer.finish();
        String prefUrl = "/excel/"+name+".xlsx";
        //http://39.105.219.190:8082/%E6%B7%BB%E5%8A%A0%E6%83%85%E5%86%B5.xlsx
        //http://39.105.219.190:8082/%E6%B7%BB%E5%8A%A0%E6%83%85%E5%86%B5.xlsx
        return prefUrl;
    }

    static class ExcelListener extends AnalysisEventListener {

        List<List<String>> excelBody = new ArrayList<>();

        ArrayList<String> excelHead = new ArrayList<>();

        private  boolean hasHead;

        private  int  RowNumber = 0;
        int headSize = 0;

        ExcelReadResult excelReadResult = null;

        public ExcelListener(ExcelReadResult readResult ,boolean hashead) {
            this.hasHead = hashead;
            excelReadResult = readResult;
        }

        @Override
        // 开始读取
        public void invoke(Object o, AnalysisContext analysisContext) {
            // 如果有表头 那么第一行读的是表头的信息
            if (hasHead&&analysisContext.getCurrentRowNum()==0){
                excelHead = (ArrayList<String>) o;
                headSize = excelHead.size();
            }else {
                ArrayList<String> rowBody = (ArrayList<String>) o;
                int j = headSize - rowBody.size();
                for (int i = 0; i < j ; i++) {
                    rowBody.add("");
                }
                excelBody.add(rowBody);
            }
        }
        // 读取完毕回调
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            excelReadResult.isSuccess = true;
            excelReadResult.tableBody=excelBody;
            excelReadResult.tableHead = excelHead;
        }
    }


}

/**
 * @author 李鑫
 * @version 1.0
 * @date 2019/7/5 11:11
 * @desc
 */
public class ExcelReadResult {
    public boolean isSuccess;
    public String fialReason;
    public ArrayList<String> tableHead;
    public List<List<String>> tableBody;

    public ExcelReadResult() {
        this.tableHead = new ArrayList<>();
        this.tableBody = new ArrayList<>();
    }
}