fj-dc/.svn/pristine/07/071081381eda0fac2bc07293cad9d683a5fe30d8.svn-base

package cn.com.goldenwater.dcproj.utils.impexcel;

import cn.com.goldenwater.dcproj.utils.ZipUtil;
import cn.com.goldenwater.dcproj.utils.impexcel.format.PblmFieldFormatExp;
import cn.com.goldenwater.dcproj.utils.impexcel.format.PblmFieldFormatImp;
import cn.com.goldenwater.id.util.UuidUtil;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

import static cn.com.goldenwater.dcproj.utils.expExcel.ExportAbstract.XLS;
import static cn.com.goldenwater.dcproj.utils.export.FieldFormat.formatCheckNull;
import static cn.com.goldenwater.dcproj.utils.impexcel.field.ExcelFieldLists.pblmFieldArrays;

/**
 * <p>
 *  通用的导出导入工具类
 * </p>
 *
 * @author liyz
 * @date 2019/7/19 20:19
 **/
public class ExpAndImpUtil {

    private static final String SHEET_NAME = "模板文件";

    /**
     * 功能：判断文件是否存在
     */
    public static File isExist(String originUrl) throws IOException {
        // 判断文件是否存在
        File file = new File(originUrl);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }
        return file;
    }
    /**
     * 下载模板
     * @param response HttpServletResponse
     * @param list 从数据库里获取到的数据集
     * @param filePath 模板文件的路径，带上扩展名，如：D:\DC\template\moban.xls
     * @param startRow 从第几行开始写入数据 key行所在的行号-1
     * @param sheetName sheet名称
     * @throws Exception 异常
     */
    public static void download(HttpServletResponse response, List<Map<String,Object>> list, String filePath,int startRow,String sheetName) throws Exception{

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        FileInputStream fis = null;
        if(StringUtils.isBlank(sheetName)){
            sheetName = SHEET_NAME;
        }
        try {
            // 获取模板
            fis = new FileInputStream(isExist(filePath));
            String fileExt = filePath.substring(filePath.lastIndexOf(".")).replace(".","");
            Workbook workBook = null;
            if(XLS.equals(fileExt)){
                workBook = new HSSFWorkbook(fis);
            }else {
                workBook = new XSSFWorkbook(fis);
            }

            workBook.setSheetName(0, sheetName);
            Sheet sheet = workBook.getSheetAt(0);

            for (int i = 0; i < list.size(); i++) {
                List<String> keys = new ArrayList<>();
                Row keyRow = sheet.getRow(startRow);
                for(int r=0;r<keyRow.getPhysicalNumberOfCells();r++){
                    if(!"".equals(keyRow.getCell(r).toString())){
                        keys.add(keyRow.getCell(r).toString());
                    }
                }
                Row creRow = sheet.createRow(startRow + 1 + i);
                Map<String,Object> map = list.get(i);
                for(int j = 0;j < keys.size(); j++){
                    Object val = map.get(keys.get(j));
                    creRow.createCell(j).setCellValue(val == null ? "" : val.toString());
                }
            }

            workBook.write(os);

            fis.close();
            os.flush();
            os.close();

            ExportExcel.downloadExcelFile(response, os,sheetName + filePath.substring(filePath.lastIndexOf(".")));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(fis != null){
                fis.close();
            }
            os.close();
        }

    }

    /**
     * 下载Excel的重载方法（简化常用）
     *
     * @param response HttpServletResponse
     * @param os 字节数据输出流
     * @param fileName 文件名称
     */
    public static void downloadExcelFile(HttpServletResponse response, ByteArrayOutputStream os, String fileName) {
        ExportExcel.downloadExcelFile(response,os,fileName);
    }

    /**
     * 下载任意文件的方法
     *
     * @param response HttpServletResponse
     * @param filePath "D:/excel.xls"
     * @param fileName "水库问题"
     * @throws Exception 异常
     */
    public static void downloadFile(HttpServletResponse response, String filePath, String fileName) throws Exception {

        File file = new File(filePath);
        String ext = filePath.substring(filePath.lastIndexOf("."));
        fileName = fileName + ext;
        if (file.exists()) {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

            InputStream input = new FileInputStream(file);

            OutputStream os = response.getOutputStream();
            int len = 0;
            byte[] buffer = new byte[4096];
            while ((len = input.read(buffer, 0, buffer.length)) != -1) {
                os.write(buffer, 0, len);
            }
            input.close();
            os.flush();
            os.close();

        }
    }

    /**
     * 获取Workbook
     * @param list 数据集
     * @param filePath 模板路径
     * @param startRow 开始写入行
     * @param sheetName Sheet名称
     * @return Workbook
     * @throws Exception 异常
     */
    public static Workbook getWorkbook(List<Map<String,Object>> list, String filePath,int startRow,String sheetName) throws Exception{

        if(StringUtils.isBlank(sheetName)){
            sheetName = SHEET_NAME;
        }
        FileInputStream fis = null;
        Workbook workBook = null;
        try {
            // 获取模板
            fis = new FileInputStream(isExist(filePath));
            String fileExt = filePath.substring(filePath.lastIndexOf(".")+1);

            if(XLS.equals(fileExt)){
                workBook = new HSSFWorkbook(fis);
            }else {
                workBook = new XSSFWorkbook(fis);
            }
            workBook.setSheetName(0, sheetName);
            Sheet sheet = workBook.getSheetAt(0);

            for (int i = 0; i < list.size(); i++) {
                List<String> keys = new ArrayList<>();
                Row keyRow = sheet.getRow(startRow);
                for(int r=0;r<keyRow.getPhysicalNumberOfCells();r++){
                    if(!"".equals(keyRow.getCell(r).toString())){
                        keys.add(keyRow.getCell(r).toString());
                    }
                }
                Row creRow = sheet.createRow(startRow + 1 + i);
                Map<String,Object> map = list.get(i);
                for(int j = 0;j < keys.size(); j++){
                    Object val = map.get(keys.get(j));
                    creRow.createCell(j).setCellValue(val == null ? "" : val.toString());
                }
            }
            fis.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(fis != null){
                fis.close();
            }
        }
        return workBook;
    }



    /**
     * 上传文件
     * @param file 文件流
     * @param folderPath 要存放的文件夹的路径
     * @return 生成的文件的路径
     * @throws IOException 异常
     */
    public static String upload(MultipartFile file,String folderPath) throws IOException {
        String fileName = UuidUtil.uuid();
        // .xls 格式的
        String ext = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        File pathFile = new File(folderPath);
        if(!pathFile.exists()){
            pathFile.mkdirs();
        }
        FileUtils.copyInputStreamToFile(file.getInputStream(), new File(folderPath, fileName + ext ));
        return folderPath + File.separator +  fileName + ext;
    }
    public static String uploadFileZipCommon(MultipartFile file,String folderPath) throws Exception{
        // 先存放到一个临时路径，再剪切或复制到图片路径下
        String filePath = ExpAndImpUtil.upload(file,folderPath);
        //解压到此文件夹
        ZipUtil.unzip(new File(filePath), folderPath);
        return filePath;
    }

    /**
     * 获取解析后的数据
     * @param filePath 文件存储路径
     * @param startRow 从第几行开始解析，注意把key那一行带上
     * @param clazz 格式化类
     * @return 解析后的数据
     * @throws IOException 异常
     */
    public static List<LinkedHashMap<String,Object>> parseData(String filePath, int startRow, Class<?> clazz) throws IOException {
        return ImportExcel.getParseData(filePath,startRow,0, clazz);
    }

    /**
     * 功能：把Map转成Bean (主要用于把从Excel解析出来的Map对象还原成实体类对象，方便入库)
     */
    public static Object convertMapToBean(Class<?> clazz, Map lhp) throws IllegalAccessException, InstantiationException {
        return ImportExcel.convertMapToBean(clazz,lhp);
    }

    /** 用反射简化赋值流程，通过对象和属性名称给Map赋值 */
    public static Map<String,Object> convertBeanToMap(Object obj, String[] names,Map map,Class<?> formatClazz)  {
        if(map == null){
            map = new LinkedHashMap<>();
        }
        if(obj == null){
            for (String name : names){
                map.put(name,"");
            }
            return map;
        }
        Method[] methods = obj.getClass().getMethods();
        for(Method method : methods){
            for (String name : names){
                if(("get"+name).toLowerCase().equals(method.getName().toLowerCase())){
                    try {
                        map.put(name,ImportExcel.formatValue(name,formatCheckNull(method.invoke(obj)),formatClazz));
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return map;
    }
    public static List<Map<String,Object>> convertBeanToList(Object list, String[] names, Class<?> clazz)  {
        List<Map<String,Object>> listMap = new ArrayList<>();
        for(Object obj : (List)list){
            Map<String,Object> dataMap = ExpAndImpUtil.convertBeanToMap(obj,pblmFieldArrays,null,clazz);
            listMap.add(dataMap);
        }
        return listMap;
    }

    /**
     * 获取展示到前台的数据，反格式化
     * @param parseData parseData
     * @param clazz 格式化类
     * @return showData
     * @throws IOException 异常
     */
    public static List<LinkedHashMap<String,Object>> showData(List<LinkedHashMap<String,Object>> parseData, Class<?> clazz) throws IOException {
        return ImportExcel.getShowData(parseData,clazz);
    }

    public static String getDateFormatPath(String basePath){
        return ImpUtil.getDateFormatPath(basePath);
    }

    /**
     * 导出EXCEL时动态创建一个下拉列表
     * @param groupNamesList 数据集
     * @param firstRow 下拉框从第几行开始，默认最终行结束
     * @param firstCol 下拉框从第几列开始
     * @return 输出流
     */
    public static ByteArrayOutputStream createListBox(String[] groupNamesList,int firstRow,int firstCol,Workbook workbook){
        return ExportExcel.createListBox(groupNamesList,firstRow,firstCol,workbook);
    }
    /**
     * 导出EXCEL时动态创建一个下拉列表
     * @param groupNamesList 数据集
     * @param firstRow 下拉框从第几行开始，默认最终行结束
     * @param firstCol 下拉框从第几列开始
     * @param fis 输入流
     * @param bos 输出流
     * @return 输出流
     */
    public static ByteArrayOutputStream createListBox(String[] groupNamesList,int firstRow,int firstCol,FileInputStream fis,ByteArrayOutputStream bos){
        return ExportExcel.createListBox(groupNamesList,firstRow,firstCol,fis,bos);
    }

    /**
     * 处理重复的主键，只取第一条，把后面重复的主键都置空
     * @param list 原始集合
     * @param id 要置空的主键
     * @return 处理后的集合
     */
    public static List<LinkedHashMap<String,Object>> handleRepeatPrimaryKey(List<LinkedHashMap<String,Object>> list,String id){
        List<LinkedHashMap<String,Object>> itemList = new ArrayList<>();
        for(LinkedHashMap<String,Object> lhm : list){
            if(itemList.size() == 0){
                itemList.add(lhm);
            }else {
                ListIterator<LinkedHashMap<String,Object>> listIterator = itemList.listIterator();
                int count = 0;
                while(listIterator.hasNext()){
                    LinkedHashMap<String,Object> linkedHashMap = listIterator.next();
                    if(StringUtils.isNotBlank(MapUtils.getString(linkedHashMap,id))){
                        if(MapUtils.getString(linkedHashMap,id).equals(MapUtils.getString(lhm,id))){
                            lhm.put(id,"");
                            count ++;
                        }
                    }
                }
                if(count == 0){
                    listIterator.add(lhm);
                }

            }
        }
        return list;
    }

    public static void main(String[] args) throws Exception {
        // 获取解析后的数据
        List<LinkedHashMap<String,Object>> parseData = parseData("D:\\DC\\impExcel\\2019\\7\\26\\fa9b5b0725fc400d8a6fdcab6427208a.xls",2,PblmFieldFormatImp.class);
        // 获取展示到前台的数据
        List<LinkedHashMap<String, Object>> showData = showData(parseData, PblmFieldFormatExp.class);
        System.out.println(showData);

        handleRepeatPrimaryKey(parseData,"pblmId");
        List<LinkedHashMap<String, Object>> list1 = parseData.stream().filter(x -> "".equals(x.get("pblmId").toString())).collect(Collectors.toList());
        List<LinkedHashMap<String, Object>> list2 = parseData.stream().filter(x -> !"".equals(x.get("pblmId").toString())).collect(Collectors.toList());
        System.out.println(list1);
        System.out.println(list2);





    }








}