hutool 解析 Excel

 

 

package com.yq.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.sax.Excel07SaxReader;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.yq.exception.BizException;
import com.yq.exception.ErrorMessagesEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author: wensm
 * @date: 2021/5/7 0007
 * @description: TODO
 **/
@Slf4j
public class ExcelUtils {

    private static List<List<Object>> lineList = new ArrayList<>();

    /**
     * excel 导出工具类
     *
     * @param response
     * @param fileName    文件名
     * @param projects    对象集合
     * @param columnNames 导出的excel中的列名
     * @param keys        对应的是对象中的字段名字
     * @throws IOException
     */
//    public static void export(HttpServletResponse response, String fileName, ArrayList<Map<String, Object>> projects, String[] columnNames, String[] keys) throws IOException {
    public static void export(HttpServletResponse response, String fileName, List<?> projects, String[] keys, String[] columnNames) throws IOException {
//        Map<String, Object> row1 = new LinkedHashMap<>();
//        row1.put("姓名", "张三");
//        row1.put("年龄", 23);
//        row1.put("成绩", 88.32);
//        row1.put("是否合格", true);
//        row1.put("考试日期", DateUtil.date());
//
//        Map<String, Object> row2 = new LinkedHashMap<>();
//        row2.put("姓名", "李四");
//        row2.put("年龄", 33);
//        row2.put("成绩", 59.50);
//        row2.put("是否合格", false);
//        row2.put("考试日期", DateUtil.date());
//
//        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);

        ExcelWriter bigWriter = ExcelUtil.getBigWriter();
//        ExcelWriter bigWriter = ExcelUtil.getWriter(true);

        for (int i = 0; i < keys.length; i++) {
            bigWriter.addHeaderAlias(keys[i], columnNames[i]);
            bigWriter.setColumnWidth(i, 20);
        }

        // 一次性写出内容，使用默认样式，强制输出标题
//          bigWriter.write(rows, true);
        bigWriter.write(projects, true);

        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
//        String name = StringUtils.toUtf8String("申请学院");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        bigWriter.flush(out, true);
        // 关闭writer，释放内存
        bigWriter.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }


    /**
     * excel导入工具类
     *
     * @param file       文件
     * @return 返回数据集合
     * @throws BizException
     * @throws IOException
     */
    public static List<Map<String, Object>> leading(MultipartFile file) throws BizException, IOException {
        String fileName = file.getOriginalFilename();
        // 上传文件为空
        if (StringUtils.isEmpty(fileName)) {
            throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR);
        }
        //上传文件大小为1000条数据
        if (file.getSize() > 1024 * 1024 * 10) {
            log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize());
            throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR);
        }
        // 上传文件名格式不正确
        if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
            throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR);
        }
//        log.info("========columNames========{}",columnNames);
//        //读取数据
//        ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler());
//
////        Excel07SaxReader reader = new Excel07SaxReader(createRowHandler());
////        reader.read(file.getInputStream(),1);
//
//
//        log.info("========lineList========{}",lineList.toString());
//        //去除excel中的第一行数据
//        lineList.remove(0);
//
//        //将数据封装到list<Map>中
//        List<Map<String, Object>> dataList = new ArrayList<>();
//        for (int i = 0; i < lineList.size(); i++) {
//            if (null != lineList.get(i)) {
//                Map<String, Object> hashMap = new HashMap<>();
//                for (int j = 0; j < columNames.length; j++) {
//                    Object property = lineList.get(i).get(j);
//                    hashMap.put(columNames[j], property);
//                }
//                dataList.add(hashMap);
//            } else {
//                break;
//            }
//        }



//        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
////        reader.addHeaderAlias("学号", "sno");
////        reader.addHeaderAlias("姓名", "name");
////        reader.addHeaderAlias("年龄", "age");
////        reader.addHeaderAlias("性别", "gender");
////        reader.addHeaderAlias("籍贯", "nativePlace");
////        reader.addHeaderAlias("入学时间", "enrollmentTime");
//
//        for (int i = 0; i < columNames.length; i++) {
//            reader.addHeaderAlias(columNames[i], keys[i]);
//        }
//        List dataList = new ArrayList();
//        if(reader.getRowCount()>0) dataList = reader.read(1);
//        dataList = reader.readAll();

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0);
        List<Map<String,Object>> dataList = reader.readAll();

        return dataList;
    }

    /**
     * excel导入工具类
     *
     * @param file       文件
     * @return 返回数据集合
     * @throws BizException
     * @throws IOException
     */
    public static List<Map<String, Object>> leading(MultipartFile file,Integer headerRowIndex, Integer startRowIndex) throws BizException, IOException {
        String fileName = file.getOriginalFilename();
        // 上传文件为空
        if (StringUtils.isEmpty(fileName)) {
            throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR);
        }
        //上传文件大小为1000条数据
        if (file.getSize() > 1024 * 1024 * 10) {
            log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize());
            throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR);
        }
        // 上传文件名格式不正确
        if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
            throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR);
        }
//        log.info("========columNames========{}",columnNames);
//        //读取数据
//        ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler());
//
////        Excel07SaxReader reader = new Excel07SaxReader(createRowHandler());
////        reader.read(file.getInputStream(),1);
//
//
//        log.info("========lineList========{}",lineList.toString());
//        //去除excel中的第一行数据
//        lineList.remove(0);
//
//        //将数据封装到list<Map>中
//        List<Map<String, Object>> dataList = new ArrayList<>();
//        for (int i = 0; i < lineList.size(); i++) {
//            if (null != lineList.get(i)) {
//                Map<String, Object> hashMap = new HashMap<>();
//                for (int j = 0; j < columNames.length; j++) {
//                    Object property = lineList.get(i).get(j);
//                    hashMap.put(columNames[j], property);
//                }
//                dataList.add(hashMap);
//            } else {
//                break;
//            }
//        }



//        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
////        reader.addHeaderAlias("学号", "sno");
////        reader.addHeaderAlias("姓名", "name");
////        reader.addHeaderAlias("年龄", "age");
////        reader.addHeaderAlias("性别", "gender");
////        reader.addHeaderAlias("籍贯", "nativePlace");
////        reader.addHeaderAlias("入学时间", "enrollmentTime");
//
//        for (int i = 0; i < columNames.length; i++) {
//            reader.addHeaderAlias(columNames[i], keys[i]);
//        }
//        List dataList = new ArrayList();
//        if(reader.getRowCount()>0) dataList = reader.read(1);
//        dataList = reader.readAll();

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0);
//        List<Map<String,Object>> dataList = reader.readAll();
        List<Map<String,Object>> dataList = reader.read(headerRowIndex,startRowIndex,reader.getRowCount());
        return dataList;
    }

    /**
     * excel导入工具类
     *
     * @param file       文件
     * @return 返回数据集合
     * @throws BizException
     * @throws IOException
     */
    public static List<Map<String, Object>> leadingSheets(MultipartFile file,Integer headerRowIndex, Integer startRowIndex) throws BizException, IOException {
        String fileName = file.getOriginalFilename();
        // 上传文件为空
        if (StringUtils.isEmpty(fileName)) {
            throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR);
        }
        //上传文件大小为1000条数据
        if (file.getSize() > 1024 * 1024 * 10) {
            log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize());
            throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR);
        }
        // 上传文件名格式不正确
        if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
            throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR);
        }
//        log.info("========columNames========{}",columnNames);
//        //读取数据
//        ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler());
//
////        Excel07SaxReader reader = new Excel07SaxReader(createRowHandler());
////        reader.read(file.getInputStream(),1);
//
//
//        log.info("========lineList========{}",lineList.toString());
//        //去除excel中的第一行数据
//        lineList.remove(0);
//
//        //将数据封装到list<Map>中
//        List<Map<String, Object>> dataList = new ArrayList<>();
//        for (int i = 0; i < lineList.size(); i++) {
//            if (null != lineList.get(i)) {
//                Map<String, Object> hashMap = new HashMap<>();
//                for (int j = 0; j < columNames.length; j++) {
//                    Object property = lineList.get(i).get(j);
//                    hashMap.put(columNames[j], property);
//                }
//                dataList.add(hashMap);
//            } else {
//                break;
//            }
//        }



//        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
////        reader.addHeaderAlias("学号", "sno");
////        reader.addHeaderAlias("姓名", "name");
////        reader.addHeaderAlias("年龄", "age");
////        reader.addHeaderAlias("性别", "gender");
////        reader.addHeaderAlias("籍贯", "nativePlace");
////        reader.addHeaderAlias("入学时间", "enrollmentTime");
//
//        for (int i = 0; i < columNames.length; i++) {
//            reader.addHeaderAlias(columNames[i], keys[i]);
//        }
//        List dataList = new ArrayList();
//        if(reader.getRowCount()>0) dataList = reader.read(1);
//        dataList = reader.readAll();
        ExcelReader reader1 =  ExcelUtil.getReader(file.getInputStream());
//        ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0);
        int sheetCount = reader1.getSheetCount();
        log.info("================sheetCount========={}",sheetCount);
        List<Map<String,Object>> dataList = new ArrayList<>();
        for(int i=0;i<sheetCount;i++){
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),i);
            dataList.addAll(reader.read(headerRowIndex,startRowIndex,reader.getRowCount()));
        }
//        List<Map<String,Object>> dataList = reader.readAll();
//        dataList = reader.read(headerRowIndex,startRowIndex,reader.getRowCount());
        return dataList;
    }

    /**
     * 通过实现handle方法编写我们要对每行数据的操作方式
     */
    private static RowHandler createRowHandler() {
        //清空一下集合中的数据
        lineList.removeAll(lineList);
//        log.info("====remove====lineList========{}",lineList.toString());
        return new RowHandler() {
            @Override
            public void handle(int i, long l, List<Object> list) {
//                log.info("====add====list========{}",list.toString());
                //将读取到的每一行数据放入到list集合中
                JSONArray jsonObject = new JSONArray(list);
                lineList.add(jsonObject.toList(Object.class));
//                log.info("====add====lineList========{}",lineList.toString());
            }
//            @Override
//            public void handle(int sheetIndex, int rowIndex, List rowlist) {
//                //将读取到的每一行数据放入到list集合中
//                JSONArray jsonObject = new JSONArray(rowlist);
//                lineList.add(jsonObject.toList(Object.class));
//            }
        };
    }
}