fj-dc/.svn/pristine/13/1350ee4bbff7d781987921b55916f969a489eb60.svn-base

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

import cn.com.goldenwater.dcproj.annotation.NotExport;
import cn.com.goldenwater.dcproj.model.TacExprRcmm;
import cn.com.goldenwater.dcproj.model.TacWorkerB;
import cn.com.goldenwater.dcproj.utils.impexcel.ExportExcel;
import io.swagger.annotations.ApiModelProperty;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
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.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static cn.com.goldenwater.dcproj.utils.BeanUtil.transBean2Map;

/**
 * Created by jinshui on 2019/8/26.
 */
public class ExportUtil {
    private static final String XLS = "xls";

    private ExportUtil() {
    }

    public static final List<String> COMMONLIST_AND_OPEN_ANNOTATION = new ArrayList<String>() {{
        add("id");
    }};

    private static List<String> list = new ArrayList<>();
    private static List<String> statList = new ArrayList<>();

    static {
        list.add("code");
        list.add("nodeId");
        list.add("ptype");
        list.add("lgtd");
        list.add("lttd");
        list.add("gdX");
        list.add("gdY");
        list.add("objId");
        list.add("id");
        list.add("rgstrId");
        list.add("wintCode");
        list.add("pcX");
        list.add("pcY");
        list.add("safetyStat");
        list.add("measuresStat");
        list.add("waterStat");
        list.add("intInfoStat");
        list.add("apprInfoStat");
    }


    /**
     * 导出所选字段
     *
     * @param result
     * @param response
     * @param fileName
     * @param list
     */
    public static void exportExcelColumn(List<?> result, HttpServletResponse response, String fileName,
                                         List<String> list, Class cla) {
        if (list == null || list.size() == 0) {
            exportExcel(result, response, fileName);
            return;
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        result.forEach(worker -> mapList.add(transObjToMap(worker)));
        ExportAbstract export = new ExcelExport();
        if (StringUtils.isBlank(fileName)) {
            fileName = "结果列表";
        }
        export.setFileName(fileName);
        export.setExport_ps_export(true);
        export.setExport_ps_type(ExportAbstract.XLS);
        export.setExport_bzip(false);
        export.setTitle(fileName);
        ArrayList<Object> cols = new ArrayList<Object>();
        Field[] fields = cla.getDeclaredFields();
        for (int j = 0; j <= fields.length - 1; j++) {
            //设置可访问
            Field field = fields[j];
            if (!list.contains(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            if (field.getAnnotation(ApiModelProperty.class) != null) {
                cols.add(field.getName() + "[" + fileFormat(field.getAnnotation(ApiModelProperty.class).value()) + "]");
            }
        }
        if (cols.size() < 1) {
            cols.add("name[名称]");
            cols.add("code[编码]");
        }
        export.setCols(cols);
        export.setGroupable(false);
        // 设置视图指标
        export.setLevel(1);
        export.setLocksize(0);
        try {
            export.Export(response);
            export.ExportHeadForCustom(response);//导出表头
            export.ContinueExport(mapList);
            export.EndExport();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static Map<String, Object> transObjToMap(Object worker) {
        Map<String, Object> map = new HashedMap();
        Field[] fields = worker.getClass().getDeclaredFields();
        for (int j = 0; j <= fields.length - 1; j++) {
            //设置可访问
            Field field = fields[j];
            field.setAccessible(true);
            field.getName();
            try {
                if (field.getType() == String.class) {
                    map.put(field.getName(), String.valueOf(field.get(worker)));
                } else {
                    map.put(field.getName(), field.get(worker));
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    /**
     * 过滤字段
     *
     * @param result
     * @param response
     * @param fileName
     * @param list
     */
    public static void exportExcel(List<?> result, HttpServletResponse response, String fileName, List<String> list, Class cla) {
        if (list == null || list.size() == 0) {
            exportExcel(result, response, fileName);
            return;
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        result.forEach(worker -> mapList.add(transBean2Map(worker)));
        ExportAbstract export = new ExcelExport();
        if (StringUtils.isBlank(fileName)) {
            fileName = "结果列表";
        }
        export.setFileName(fileName);
        export.setExport_ps_export(true);
        export.setExport_ps_type(ExportAbstract.XLS);
        export.setExport_bzip(false);
        export.setTitle(fileName);
        ArrayList<Object> cols = new ArrayList<Object>();
        Field[] fields = cla.getDeclaredFields();
        Class clazz = cla.getSuperclass();
        if (clazz != null && clazz != Objects.class) {
            fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
        }
        for (int j = 0; j <= fields.length - 1; j++) {
            //设置可访问
            Field field = fields[j];
            if (list.contains(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            if (field.getAnnotation(ApiModelProperty.class) != null && field.getAnnotation(NotExport.class) == null) {
                cols.add(field.getName() + "[" + fileFormat(field.getAnnotation(ApiModelProperty.class).value()) + "]");
            }
        }
        if (cols.size() < 1) {
            cols.add("name[名称]");
            cols.add("code[编码]");
        }
        export.setCols(cols);
        export.setGroupable(false);
        // 设置视图指标
        export.setLevel(1);
        export.setLocksize(0);
        try {
            export.Export(response);
            // 导出表头
            export.ExportHeadForCustom(response);
            export.ContinueExport(mapList);
            export.EndExport();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 使用字段
     *
     * @param result
     * @param response
     * @param fileName
     * @param list
     */
    public static void exportExcel2(List<?> result, HttpServletResponse response, String fileName, List<String> list, Class cla) {
        if (list == null || list.size() == 0) {
            exportExcel(result, response, fileName);
            return;
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        result.forEach(worker -> mapList.add(transBean2Map(worker)));
        ExportAbstract export = new ExcelExport();
        if (StringUtils.isBlank(fileName)) {
            fileName = "结果列表";
        }
        export.setFileName(fileName);
        export.setExport_ps_export(true);
        export.setExport_ps_type(ExportAbstract.XLS);
        export.setExport_bzip(false);
        export.setTitle(fileName);
        ArrayList<Object> cols = new ArrayList<Object>();
        Field[] fields = cla.getDeclaredFields();
        Class clazz = cla.getSuperclass();
        if (clazz != null && clazz != Objects.class) {
            fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
        }
        for (String name : list) {
            for (Field field : fields) {
                if (name.equals(field.getName()) && field.getAnnotation(ApiModelProperty.class) != null) {
                    field.setAccessible(true);
                    cols.add(field.getName() + "[" + fileFormat(field.getAnnotation(ApiModelProperty.class).value()) + "]");
                    break;
                }
            }
        }
        if (cols.size() < 1) {
            cols.add("name[名称]");
            cols.add("code[编码]");
        }
        export.setCols(cols);
        export.setGroupable(false);
        // 设置视图指标
        export.setLevel(1);
        export.setLocksize(0);
        try {
            export.Export(response);
            // 导出表头
            export.ExportHeadForCustom(response);
            export.ContinueExport(mapList);
            export.EndExport();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static String fileFormat(String value) {
        if (isContainNum(value)) {
            return value;
        }
        //统一字符处理
        value = value.replace("(", "（");
        if (value.contains(" ")) {
            value = value.substring(0, value.indexOf(" "));
        }
        if (value.contains("（")) {
            value = value.substring(0, value.indexOf("（"));
        }
        if (value.startsWith("*")) {
            value = value.substring(1);
        }
        return value;
    }

    private static boolean isContainNum(String value) {
        if (value.contains("m3")) {
            return true;
        }
        if (value.contains("km2")) {
            return true;
        }
        return false;
    }


    public static void exportExcel(List<?> result, HttpServletResponse response, String fileName) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        ExportAbstract export = new ExcelExport();
        if (StringUtils.isBlank(fileName)) {
            fileName = "结果列表";
        }
        export.setFileName(fileName);
        export.setExport_ps_export(true);
        export.setExport_ps_type(ExportAbstract.XLS);
        export.setExport_bzip(false);
        export.setTitle(fileName);
        ArrayList<Object> cols = new ArrayList<Object>();
        boolean flag = true;
        for (Object o : result) {
            mapList.add(transBean2Map(o));
            if (flag) {
                Field[] fields = o.getClass().getDeclaredFields();
                for (int j = 0; j <= fields.length - 1; j++) {
                    //设置可访问
                    Field field = fields[j];
                    if (list.contains(field.getName())) {
                        continue;
                    }
                    field.setAccessible(true);
                    if (field.getAnnotation(ApiModelProperty.class) != null) {
                        cols.add(field.getName() + "[" + field.getAnnotation(ApiModelProperty.class).value() + "]");
                    }
                }
                flag = false;
            }
        }
        if (cols.size() < 1) {
            cols.add("name[名称]");
            cols.add("code[编码]");
            cols.add("location[地址]");
            cols.add("adCode[行政区划编码]");
        }
        export.setCols(cols);
        export.setGroupable(false);
        // 设置视图指标
        export.setLevel(1);
        export.setLocksize(0);
        try {
            export.Export(response);
            //导出表头
            export.ExportHeadForCustom(response);
            export.ContinueExport(mapList);
            export.EndExport();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportExcel(String isExport, List<?> result, HttpServletResponse response) {
        if ("1".equals(isExport)) {
            exportExcel(result, response, "");
        }
    }

    /**
     * 返回某个类需要导出的字段列表
     *
     * @param cla
     * @param list
     */
    public static List<Map<String, Object>> getExoportColumns(Class cla, List<String> list) {
        List<Map<String, Object>> cols = new ArrayList<>();
        Field[] fields = cla.getDeclaredFields();
        for (int j = 0; j <= fields.length - 1; j++) {
            //设置可访问
            Field field = fields[j];
            if (list.contains(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            if (field.getAnnotation(ApiModelProperty.class) != null) {
                Map<String, Object> map = new HashMap<>();
                map.put("code", field.getName());
                String value = field.getAnnotation(ApiModelProperty.class).value().trim();
                //统一字符处理
                value = value.replace("(", "（");
                if (value.contains(" ")) {
                    value = value.substring(0, value.indexOf(" "));
                }
                if (value.contains("（")) {
                    value = value.substring(0, value.indexOf("（"));
                }
                if (value.startsWith("*")) {
                    value = value.substring(1);
                    map.put("selectFlag", true);
                }
                map.put("name", value);

                cols.add(map);
            }
        }
        return cols;
    }

    /**
     * 根据名称
     *
     * @param value
     * @param name
     * @param b
     */
    public static void setClassFiledValue(Object value, String name, Object b, boolean isFormat) {
        if (name == null) {
            return;
        }
        try {
            Field[] fields = b.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                if (field.getAnnotation(ApiModelProperty.class) != null) {
                    String nameValue = field.getAnnotation(ApiModelProperty.class).value();
                    if (isFormat) {
                        nameValue = fileFormat(nameValue);
                    }
                    if (name.equals(nameValue) && StringUtils.isNotBlank(String.valueOf(value))) {
                        field.set(b, value);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static File isExist(String originUrl) throws IOException {
        // 判断文件是否存在
        File file = new File(originUrl);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }
        return file;
    }

    /**
     * 根据字段名称下载Excel
     *
     * @param response
     * @param list
     * @param filePath
     */
    public static void download(HttpServletResponse response, List<Map<String, Object>> list, String filePath) throws IOException {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        FileInputStream 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);
        }
        Sheet sheet = workBook.getSheetAt(0);
        Row keyRow = sheet.getRow(1);
        List<String> keys = new ArrayList<>();
        for (int r = 0; r < keyRow.getPhysicalNumberOfCells(); r++) {
            if (!"".equals(keyRow.getCell(r).toString())) {
                keys.add(keyRow.getCell(r).toString());
            }
        }
        Row row = null;
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 2);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < keys.size(); j++) {
                Object val = map.get(keys.get(j));
                if (val == null) {
                    continue;
                }
                if (val.getClass() == Double.class) {
                    row.createCell(j).setCellValue((double) val);
                } else if (val.getClass() == Long.class) {
                    row.createCell(j).setCellValue((long) val);
                } else {
                    row.createCell(j).setCellValue(val == null ? "" : val.toString());
                }
            }
        }
        workBook.write(os);
        fis.close();
        os.flush();
        os.close();
        ExportExcel.downloadExcelFile(response, os, "人员列表" + filePath.substring(filePath.lastIndexOf(".")));
        if (fis != null) {
            fis.close();
        }

    }

    public static Map<String, Object> objToMap(Object obj, boolean isFormat) {
        if (obj == null) {
            return null;
        }
        Map<String, Object> map = new HashedMap();
        Field[] fields = obj.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            if (field.getAnnotation(ApiModelProperty.class) != null) {
                String nameValue = field.getAnnotation(ApiModelProperty.class).value();
                if (isFormat) {
                    nameValue = fileFormat(nameValue);
                }
                try {
                    map.put(nameValue, field.get(obj));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return map;
    }

    public static List<Row> getRowListByFile(MultipartFile file) {
        List<Row> rowList = new ArrayList<>();
        Workbook wb = null;
        try (InputStream fis = file.getInputStream()) {
            if (Objects.requireNonNull(file.getOriginalFilename()).endsWith(XLS)) {
                wb = new HSSFWorkbook(fis);
            } else {
                wb = new XSSFWorkbook(fis);
            }
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            int lastRowNum = sheet.getPhysicalNumberOfRows();
            for (int i = sheet.getFirstRowNum(); i < lastRowNum; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    rowList.add(row);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowList;
    }

    /**
     * @param rowList 行集合
     * @param i       开始行，一般为名称行数的下表，没有code隐藏行时
     * @return
     */
    public static List<Map<String, Object>> rowToMap(List<Row> rowList, int i) {
        if (i == 0) {
            i = 1;
        }
        List<String> keys = new ArrayList<>();
        Row keyRow = rowList.get(i);
        for (int r = 0; r < keyRow.getPhysicalNumberOfCells(); r++) {
            if (!"".equals(keyRow.getCell(r).toString())) {
                keys.add(keyRow.getCell(r).toString());
            }
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        Row row = null;
        for (int j = i + 1; j < rowList.size(); j++) {
            row = rowList.get(j);
            Map<String, Object> map = new HashedMap();
            for (int k = 0; k < keys.size(); k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    if (cell.getCellType() == CellType.NUMERIC) {
                        map.put(keys.get(k), cell.getNumericCellValue());
                    } else if (cell.getCellType() == CellType.STRING) {
                        map.put(keys.get(k), cell.getStringCellValue());
                    }
                }
            }
            mapList.add(map);
        }
        return mapList;
    }

    public static List<TacWorkerB> mapToObj(List<Map<String, Object>> mapList, Class cla, boolean isFormat) {
        if (mapList == null || mapList.size() == 0) {
            return null;
        }
        List<TacWorkerB> list = new ArrayList<>();
        Field[] fields = cla.getDeclaredFields();
        for (Map<String, Object> map : mapList) {
            TacWorkerB workerB = new TacWorkerB();
            for (Field field : fields) {
                field.setAccessible(true);
                if (field.getAnnotation(ApiModelProperty.class) != null) {
                    String nameValue = field.getAnnotation(ApiModelProperty.class).value();
                    if (isFormat) {
                        nameValue = fileFormat(nameValue);
                    }
                    if (map.containsKey(nameValue)) {
                        try {
                            Object object = map.get(nameValue);
                            if (object == null) {
                                continue;
                            }
                            if (field.getType() == Double.class) {
                                field.set(workerB, Double.valueOf(String.valueOf(object)));
                            }
                            if (field.getType() == String.class) {
                                if (object.getClass() == Double.class) {
                                    if (80000000.0 - (double) object < 0) {
                                        object = (long) ((double) object);
                                        object = String.valueOf(object);
                                    }
                                    field.set(workerB, object);
                                } else {
                                    field.set(workerB, String.valueOf(object));
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
            list.add(workerB);
        }
        return list;
    }

    public static List<Object> mapToObj(List<Map<String, Object>> mapList, Class cla) {
        if (mapList == null || mapList.size() == 0) {
            return null;
        }
        List<Object> list = new ArrayList<>();
        Field[] fields = cla.getDeclaredFields();
        for (Map<String, Object> map : mapList) {
            Object obj = null;
            try {
                obj = cla.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    if (field.getAnnotation(ApiModelProperty.class) != null) {
                        String nameValue = field.getAnnotation(ApiModelProperty.class).value();
                        if (map.containsKey(nameValue)) {
                            try {
                                Object object = map.get(nameValue);
                                if (object == null) {
                                    continue;
                                }
                                if (field.getType() == Double.class) {
                                    field.set(obj, Double.valueOf(String.valueOf(object)));
                                }
                                if (field.getType() == String.class) {
                                    field.set(obj, String.valueOf(object));
                                }
                                if (field.getType() == Date.class) {
                                    SimpleDateFormat format = new SimpleDateFormat("yyyy/mm/dd");
                                    try {
                                        field.set(obj, format.parse(String.valueOf(object)));
                                    } catch (ParseException e) {
                                        e.printStackTrace();
                                    }
                                }
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                list.add(obj);
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        }
        return list;
    }

    public static void exportExcel(Map<String, List<TacExprRcmm>> map, HttpServletResponse response, String fileName, List<String> columns) throws IOException {
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        Workbook workbook = new XSSFWorkbook();
        if (map != null) {
            for (String sheetName : map.keySet()) {
                Sheet sheet = workbook.createSheet(sheetName);
                createExcelContent(sheet, map.get(sheetName), columns);
            }
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    private static void createExcelContent(Sheet sheet, List<?> tacExprRcmms, List<String> columns) {
        if (tacExprRcmms == null || tacExprRcmms.size() == 0) {
            return;
        }
        boolean flag = false;
        Row titleRow = null;
        Map<Integer, String> columnMap = new HashedMap();
        for (int i = 0; i < tacExprRcmms.size(); i++) {
            Map<String, Object> map = transObjToMap(tacExprRcmms.get(i));
            if (!flag) {
                titleRow = sheet.createRow(1);
                createTitleRow(titleRow, tacExprRcmms.get(i), columnMap, columns);
            }
            if (titleRow == null) {
                continue;
            }
            Row row = sheet.createRow(i + 2);
            row.createCell(0).setCellValue(i + 1);
            for (int j = 1; j < titleRow.getPhysicalNumberOfCells(); j++) {
                row.createCell(j).setCellValue(map.get(columnMap.get(j)) == null ? "" : String.valueOf(map.get(columnMap.get(j))));
            }
        }
    }

    private static void createTitleRow(Row titleRow, Object obj, Map<Integer, String> columnMap, List<String> columns) {
        Field[] fields = obj.getClass().getDeclaredFields();
        titleRow.createCell(0).setCellValue("序号");
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            if (columns.contains(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            if (field.getAnnotation(ApiModelProperty.class) != null) {
                String nameValue = fileFormat(field.getAnnotation(ApiModelProperty.class).value());
                titleRow.createCell(i + 1).setCellValue(nameValue);
                columnMap.put(i + 1, field.getName());
            }
        }
    }
}