fj-dc/.svn/pristine/bd/bd8ae48c5a65d1dbaff10499fea16c374fba4950.svn-base

package cn.com.goldenwater.dcproj.utils;

import cn.com.goldenwater.dcproj.annotation.ExcelImport;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

public class ImportUtil<T> {

    /**
     * 解析导入文件 MultipartFile -> List<T>
     * @param f 导入文件
     * @return List<T>
     * @throws IOException
     */
    public List fileToList(MultipartFile f, Class c) throws Exception {
        Map<String, Integer> titleMap = new HashMap<>(); // k：表头中文  v：第几列
        Map<String, Integer> fieldMap = new HashMap<>(); // k：字段名    v：第几列
        List<T> result = new ArrayList<>();

        Workbook wb = new HSSFWorkbook(f.getInputStream());
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0); // 表头行
        for (int i = 0; i < 99; i++) {
            Cell cell = row.getCell(i);
            if(cell == null) { break; }
            String cellValue = cell.getStringCellValue().trim();
            if(cellValue == null || "".equals(cellValue)) { break; }
            titleMap.put(row.getCell(i).getStringCellValue(), i);
        }

        List<Field> fields = new ArrayList<>();
        Class superclass = c.getSuperclass();
        if (superclass != null) {
            List<Field> superFields = Arrays.asList(superclass.getDeclaredFields());
            List<Field> thisFields = Arrays.asList(c.getDeclaredFields());
            fields.addAll(superFields);
            fields.addAll(thisFields);
        } else {
            fields = Arrays.asList(c.getDeclaredFields());
        }
        for (int i = 0; i < fields.size(); i++) {
            ExcelImport annotation1 = fields.get(i).getAnnotation(ExcelImport.class);
            if(annotation1 != null && annotation1.isImport()) {
                Integer columNum = titleMap.get(annotation1.titleName());
                if(columNum != null) {
                    fieldMap.put(fields.get(i).getName(), columNum);
                }
            }
        }

        for (int i = 1; i < 9999; i++) {
            T t = (T)c.newInstance();
            before(t);
            Row r = sheet.getRow(i);
            if(r == null || r.getCell(0) == null) { break; }
            double cell0DoubleValue = 0;
            String cell0StringValue = null;
            try {cell0DoubleValue = r.getCell(0).getNumericCellValue();} catch(Exception e) {}
            try {cell0StringValue = r.getCell(0).getStringCellValue();} catch(Exception e) {}
            if(cell0DoubleValue == 0 && (cell0StringValue == null || "".equals(cell0StringValue))) { break; }
            Set<Map.Entry<String, Integer>> entries = fieldMap.entrySet();
            for (Map.Entry<String, Integer> entry : entries) {
                Cell cell = r.getCell(entry.getValue());
                if(cell == null) { continue; }
                Field field;
                try {
                    try {
                        field = c.getDeclaredField(entry.getKey());
                    } catch (NoSuchFieldException e) {
                        field = c.getSuperclass().getDeclaredField(entry.getKey());
                    }
                    if (field != null) {
                        field.setAccessible(true);
                        Class<?> type = field.getType();
                        if (type == String.class) {
                            String cellValue = cell.getStringCellValue();
                            ExcelImport annotation = field.getAnnotation(ExcelImport.class);
                            if(annotation != null) {
                                int subStringLength = annotation.subString();
                                if(subStringLength != -1 && cellValue != null) {
                                    cellValue = cellValue.substring(0, subStringLength);
                                }
                            }
                            field.set(t, cellValue);
                        } else if (type == Integer.class ) {
                            field.set(t, (int)cell.getNumericCellValue());
                        } else if (type == Double.class){
                            field.set(t, cell.getNumericCellValue());
                        } else if (type == Long.class){
                            field.set(t, (long)cell.getNumericCellValue());
                        } else if (type == BigDecimal.class){
                            field.set(t, BigDecimal.valueOf(cell.getNumericCellValue()));
                        } else if (type == Float.class){
                            field.set(t, (float)cell.getNumericCellValue());
                        } else if (type == Short.class){
                            field.set(t, (short)cell.getNumericCellValue());
                        } else if (type == Date.class){
                            field.set(t, cell.getDateCellValue());
                        } else if (type == Boolean.class) {
                            String v = cell.getStringCellValue();
                            if (Arrays.asList("是","1","true").contains(v)) {
                                field.set(t, true);
                            } else {
                                field.set(t, false);
                            }
                        }
                    }
                } catch (Exception e) {}
            }
            after(t);
            result.add(t);
        }
        return result;
    }

    public void before(T t){};
    public void after(T t){};

    /**
     * 导出excel
     * @param list
     * @param c
     * @return
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public Workbook export(List<T> list, Class c) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<Field> declaredFields = new ArrayList<>();
        Class superclass = c.getSuperclass();
        if (superclass != null) {
            List<Field> superFields = Arrays.asList(superclass.getDeclaredFields());
            List<Field> thisFields = Arrays.asList(c.getDeclaredFields());
            declaredFields.addAll(superFields);
            declaredFields.addAll(thisFields);
        } else {
            declaredFields = Arrays.asList(c.getDeclaredFields());
        }
        List<Field> sortedList = declaredFields.stream()
                .filter(a -> {
                    return a.getAnnotation(ExcelImport.class) != null;
                })
                .filter(b -> {
                    return b.getAnnotation(ExcelImport.class).isExport();
                })
                .sorted(Comparator.comparing(a -> a.getAnnotation(ExcelImport.class).order()))
                .collect(Collectors.toList());

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < sortedList.size(); i++) {
            titleRow.createCell(i).setCellValue(sortedList.get(i).getAnnotation(ExcelImport.class).titleName());
            sheet.setColumnWidth(i, sortedList.get(i).getAnnotation(ExcelImport.class).width() * 256);
        }

        DataFormat dataFormat = workbook.createDataFormat();
        CellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));

        for (int i = 0; i < list.size(); i++) {
            Row rowi = sheet.createRow(i + 1);
            for (int j = 0; j < sortedList.size(); j++) {
                Cell cell = rowi.createCell(j);
                Field field = sortedList.get(j);
                String key = field.getName();
                String methordName = "get" + key.substring(0, 1).toUpperCase() + key.substring(1);
                Method m = c.getMethod(methordName, null);
                if (field.getType() == String.class) {
                    String cellValue = (String)m.invoke(list.get(i), null);
                    if (cellValue != null) { cell.setCellValue(cellValue); }
                } else if (field.getType() == Integer.class) {
                    Integer cellValue = (Integer)m.invoke(list.get(i), null);
                    if (cellValue != null) { cell.setCellValue(cellValue); }
                } else if (field.getType() == Double.class) {
                    Double cellValue = (Double)m.invoke(list.get(i), null);
                    if (cellValue != null) { cell.setCellValue(cellValue); }
                } else if (field.getType() == Date.class){
                    Date cellValue = (Date)m.invoke(list.get(i), null);
                    cell.setCellStyle(dateCellStyle);
                    if (cellValue != null) { cell.setCellValue(cellValue); }
                } else if (field.getType() == Long.class) {
                    Long cellValue = (Long)m.invoke(list.get(i), null);
                    if (cellValue != null) { cell.setCellValue(cellValue); }
                }
            }
        }
        return workbook;
    }

    /**
     * Workbook放入response
     * @param wb
     * @param response
     */
    public void buildResponse(Workbook wb, String fileName, HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        wb.write(response.getOutputStream());
    }

}