excle读取

package com.zrar.luwak.manager.util; import com.alibaba.excel.ExcelReader; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.support.ExcelTypeEnum; import com.zrar.luwak.manager.vo.ImportColumnVO; import com.zrar.easyweb.util.StringUtil; import net.sf.json.JSONObject; import org.springframework.util.StringUtils;

import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.*;

/**

• Created by lkf on 2019/8/26 */ public class EasyExcelUtil { public static void main(String[] args) {

   //读取文件
   long act = System.currentTimeMillis();
   String filepath = "E:\\test.xlsx";
   File finalXlsxFile = new File(filepath);
   //获取列数
   int num = FileUtils.getNumberOfRows(finalXlsxFile);
   String content = "{\"1\":\"name\",\"2\":\"age\",\"3\":\"birthplace\"}";
   //  Map<String, List<String>> sheetContent = read(filepath, content, num);
   //      System.out.println("一共" + sheetContent.size() + "有效数据");
   //List<String> list = getExeclData(filepath,num);
   long end = System.currentTimeMillis();
   System.out.println("耗时间=======:" + (end - act) + "毫秒");
  
   //向execl中写数据
  

// String filepath = "E:\test3.xlsx"; // Sheet sheet = new Sheet(1,0); // sheet.setSheetName("sheet"); // List<List> header = new ArrayList<>(); // header.add(Lists.newArrayList("编号")); // header.add(Lists.newArrayList("姓名")); // header.add(Lists.newArrayList("年龄")); // sheet.setHead(header); // // List<List> data = new ArrayList<>(); // data.add(Lists.newArrayList("1001L","张三","19")); // data.add(Lists.newArrayList("1002L","李四","20")); // try { // OutputStream out = new FileOutputStream(new File(filepath)); // ExcelWriter writer = EasyExcelFactory.getWriter(out,ExcelTypeEnum.XLSX,true); // writer.write1(data,sheet); // writer.finish(); // } catch (FileNotFoundException e) { // e.printStackTrace(); // }

}

/**
 * 读取execl文件
 * @param filepath
 * @param result
 * @param num
 * @return
 */
public static Map<String, List<String>> read(String filepath, String result, int num) {
    Map<String, List<String>> map = new HashMap<>();
    InputStream inputStream = null;
    if (StringUtil.isNull(result)) {
        return null;
    }
    JSONObject obj = JSONObject.fromObject(result);
    String[] strs = new String[num];

    Set<String> set = obj.keySet();
    for (String s : set) {
        strs[Integer.parseInt(s) - 1] = (String) obj.get(s);
    }
    for (int i = 0; i < strs.length; i++) {
        if (StringUtil.isNotNull(strs[i])) {
            map.put(strs[i], new ArrayList<String>());
        }
    }
    try {
        inputStream = new FileInputStream(filepath);
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null,
                new AnalysisEventListener<List<String>>() {
                    @Override
                    public void invoke(List<String> object, AnalysisContext context) {

                        StringBuffer stringBuffer = new StringBuffer();
                        if (object != null && !StringUtils.isEmpty(object.get(0))) {
                            for (int j = 0; j < strs.length; j++) {
                                if (StringUtil.isNotNull(strs[j])) {

                                    List<String> list = map.get(strs[j]);
                                    String content = object.get(j);
                                    if (StringUtil.isNotNull(content)) {
                                        list.add(content);
                                    } else {
                                        list.add("");
                                    }

                                }
                            }
                        }

                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {

                    }
                });
        excelReader.read();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (null != inputStream) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        new File(filepath).delete();
    }
    return map;

}

/**
 * 读取execl文件
 * @param filepath
 * @param num
 * @return
 */
public static List<Map<String,String>> getExeclData(String filepath, int num, ImportColumnVO vo) {
    InputStream inputStream = null;
    List<Map<String,String>> list = new ArrayList<>();
    Map<Integer,String> map = vo==null ? new HashMap<>() : vo.getMap();
    map.put(0,"CONTENT");
    try {
        inputStream = new FileInputStream(filepath);
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null,
                new AnalysisEventListener<List<String>>() {

                    @Override
                    public void invoke(List<String> object, AnalysisContext context) {
                        if(object.size()>0){
                            Map<String,String> data = new HashMap<>();
                            for(int i=0;i<object.size();i++){
                                String str = object.get(i);
                                if(StringUtil.isNotNull(str) && StringUtil.isNotNull(map.get(i))){
                                    data.put(map.get(i),str);
                                }
                            }
                            list.add(data);
                        }
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {

                    }
                });
        excelReader.read();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (null != inputStream) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        new File(filepath).delete();
    }
    return list;

}

}