ExcelUtil.java

package com.zhongzhi.common.utils;

import com.zhongzhi.common.exception.HttpException;
import com.zhongzhi.vo.ExcelFieldVO;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Component
public class ExcelUtil {

    private final static String EXCEL2003 = "xls";

    private final static String EXCEL2007 = "xlsx";

    public static <T> List<T> readExcel(Class<T> cls, MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new HttpException(10800);
        }

        Workbook workbook = null;
        List<T> list = new ArrayList<>();
        try {
            InputStream stream = file.getInputStream();
            if (fileName.endsWith(EXCEL2003)) {
                workbook = new HSSFWorkbook(stream);
            } else if (fileName.endsWith(EXCEL2007)) {
                workbook = new XSSFWorkbook(stream);
            }

            if (workbook != null) {
                Map<String, List<Field>> classMap = new HashMap<>();
                List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
                fields.forEach(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        String value = annotation.value();
                        if (StringUtils.isBlank(value)) {
                            return;
                        }

                        if (!classMap.containsKey(value)) {
                            classMap.put(value, new ArrayList<>());
                        }
                        field.setAccessible(true);
                        classMap.get(value).add(field);
                    }
                });

                boolean title = true;
                Map<Integer, List<Field>> reflectionMap = new HashMap<>();
                Sheet sheet = workbook.getSheetAt(0);
                for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (title) {
                        for (int j = 0; j <= row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {
                                String cellValue = cell.getStringCellValue();
                                if (StringUtils.isNotBlank(cellValue)) {
                                    if (classMap.containsKey(cellValue)) {
                                        reflectionMap.put(j, classMap.get(cellValue));
                                    } else {
                                        throw new HttpException(10802);
                                    }
                                }
                            }
                        }
                        title = false;
                    } else {
                        if (row == null) {
                            continue;
                        }

                        boolean isBlank = true;
                        T t = cls.newInstance();
                        for (int j = 0; j <= row.getLastCellNum(); j++) {
                            if (reflectionMap.containsKey(j)) {
                                List<Field> fieldList = reflectionMap.get(j);
                                for (Field field : fieldList) {
                                    Cell cell = row.getCell(j);
                                    Class<?> type = field.getType();
                                    Object cellValue = getCellValue(cell, type);
                                    if (cellValue != null) {
                                        isBlank = false;
                                    }
                                    handleField(t, cellValue, field);
                                }
                            }
                        }
                        if (!isBlank) {
                            list.add(t);
                        }
                    }
                }
            }
        } catch (InstantiationException | IOException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static <T> void writeExcel(String title, List<ExcelFieldVO> fields, List<Map> list) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        AtomicInteger ai = new AtomicInteger();
        {
            sheet.addMergedRegion(new CellRangeAddress(0,0, 0, 6));
            Row t = sheet.createRow(ai.getAndIncrement());
            AtomicInteger tat = new AtomicInteger();
            Cell tcl = t.createCell(tat.getAndIncrement());
            CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font f = wb.createFont();
            f.setFontHeightInPoints((short) 13);
            f.setBoldweight(Font.BOLDWEIGHT_NORMAL);
            style.setFont(f);
            tcl.setCellStyle(style);
            tcl.setCellValue(title);

            Row row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger at = new AtomicInteger();
            fields.forEach(field -> {
                Cell cell = row.createCell(at.getAndIncrement());
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(field.getName());
            });

            if (list != null) {
                for (Map map : list) {
                    Row r = sheet.createRow(ai.getAndIncrement());
                    AtomicInteger a = new AtomicInteger();
                    fields.forEach(field -> {
                        Object value = map.get(field.getField());
                        Cell cell = r.createCell(a.getAndIncrement());
                        if (value != null) {
                            cell.setCellValue(value.toString());
                        }
                    });
                }
            }
            sheet.autoSizeColumn(1);
            String fileName = String.valueOf(new Date().getTime());
            buildExcelDocument(fileName + "." + EXCEL2007, wb, response);
        }
    }

    public static <T> void writeExcel(List<ExcelFieldVO> fields, List<Map> list) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        AtomicInteger ai = new AtomicInteger();
        {
            Row row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger at = new AtomicInteger();
            fields.forEach(field -> {
                Cell cell = row.createCell(at.getAndIncrement());
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(field.getName());
            });

            if (list != null) {
                list.forEach(map -> {
                    Row r = sheet.createRow(ai.getAndIncrement());
                    AtomicInteger a = new AtomicInteger();
                    fields.forEach(field -> {
                        Object value = map.get(field.getField());
                        Cell cell = r.createCell(a.getAndIncrement());
                        if (value != null) {
                            cell.setCellValue(value.toString());
                        }
                    });
                });
                for (int i = 0; i < list.size(); i++) {
                    sheet.autoSizeColumn(i);
                }
            }
            String fileName = String.valueOf(new Date().getTime());
            buildExcelDocument(fileName + "." + EXCEL2007, wb, response);
        }
    }

    public static List<ExcelFieldVO> getField(Class cls) {
        List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
        List<ExcelFieldVO> list = new ArrayList<>();
        fields.forEach(field -> {
            ExcelFieldVO excelFieldVO = new ExcelFieldVO();
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {

                String value = annotation.value();
                if (StringUtils.isBlank(value)) {
                    return;
                }

                excelFieldVO.setField(field.getName());
                excelFieldVO.setName(value);
                field.setAccessible(true);
                list.add(excelFieldVO);
            }
        });
        return list;
    }

    private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Object getCellValue(Cell cell, Class<?> type) {
        if (cell == null) {
            return null;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                if ("h:mm".equals(cell.getCellStyle().getDataFormatString()) || "hh:mm".equals(cell.getCellStyle().getDataFormatString())) {
                    return DateFormatUtil.format(cell.getDateCellValue(), DateFormatUtil.FMT_sdf_Hm);
                }
                return cell.getDateCellValue();
            } else {
                if (type != null) {
                    if (type == String.class) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        return cell.getStringCellValue();
                    } else {
                        return cell.getNumericCellValue();
                    }
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    return cell.getStringCellValue();
                }

            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else {
            return cell;
        }
    }

    private static <T> void handleField(T t, Object value, Field field) {
        try {
            Class<?> type = field.getType();
            if (type == null || type == void.class || value == null) {
                return;
            }

            if (type == Object.class) {
                field.set(t, value);
            } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
                if (type == BigDecimal.class) {
                    field.set(t, new BigDecimal(value.toString()));
                } else {
                    field.set(t, value);
                }
            } else if (type == Boolean.class) {
                field.set(t, BooleanUtils.toBoolean(value.toString()));
            } else if (type == Date.class || type == LocalTime.class || type == LocalDateTime.class) {
                if (type == Date.class) {
                    field.set(t, value);
                } else if (type == LocalTime.class) {
                    if (value != null && value != "") {
                        field.set(t, DateFormatUtil.dateToLocalTime(DateFormatUtil.parse(value.toString(), DateFormatUtil.FMT_sdf_yMd)));
                    }
                } else if (type == LocalDateTime.class) {
                    if (value != null && value != "") {
                        field.set(t, DateFormatUtil.dateToLocalDateTime(DateFormatUtil.parse(value.toString(), DateFormatUtil.FMT_sdf_yMd)));
                    }
                }
            } else if (type == String.class) {
                field.set(t, value.toString());
            } else {
                Constructor<?> constructor = type.getConstructor(String.class);
                field.set(t, constructor.newInstance(value));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new HttpException(10801);
        }
    }
}