解析excel工具

import com.alibaba.fastjson.JSON;
import com.dookay.coral.common.core.utils.lang.DateUtils;
import com.dookay.coral.common.core.utils.lang.EnumUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.core.io.Resource;

public class NewExcelUtils {
private static Logger LG = LoggerFactory.getLogger(NewExcelUtils.class);
private static SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);

public NewExcelUtils() {
}

public static <T> void exportExcel(HttpServletResponse response, List<T> data, String name) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(name) + ".xlsx");
    if (data.size() > 0) {
        XSSFWorkbook workbook = createWorkbook(data);
        workbook.write(response.getOutputStream());
        workbook.close();
    }

}

private static <T> XSSFWorkbook createWorkbook(List<T> data) throws SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    createHeader(sheet, data.get(0));
    createBody(sheet, data, 1);
    return workbook;
}

private static <T> void createBody(XSSFSheet sheet, List<T> data, Integer rowIndex) throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    if (!data.isEmpty()) {
        for(Iterator var3 = data.iterator(); var3.hasNext(); rowIndex = rowIndex + 1) {
            T dataRow = var3.next();
            Row sheetRow = sheet.createRow(rowIndex);
            Field[] fields = dataRow.getClass().getDeclaredFields();
            int cellIndex = 0;
            Field[] var8 = fields;
            int var9 = fields.length;

            for(int var10 = 0; var10 < var9; ++var10) {
                Field field = var8[var10];
                if (field.isAnnotationPresent(ExcelColumn.class)) {
                    field.setAccessible(true);
                    Object cellValue = field.get(dataRow);
                    Cell sheetCell = sheetRow.createCell(cellIndex);
                    if (null != cellValue) {
                        ExcelColumn export = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
                        String cellStr = cellValue.toString();
                        if (!export.format().equals("")) {
                            cellStr = String.format(export.format(), cellValue);
                        } else if (cellValue.getClass().equals(Date.class)) {
                            cellStr = sdf.format(cellValue);
                        } else if (export.type().isEnum()) {
                            cellStr = (String)EnumUtils.getDescriptionByValue(cellValue, export.type());
                        } else if (cellValue.getClass().equals(Boolean.class)) {
                            cellStr = (Boolean)cellValue ? "是" : "否";
                        } else if (cellValue.getClass().equals(Double.class)) {
                            sheetCell.setCellValue(Double.valueOf(cellStr));
                        } else if (cellValue.getClass().equals(Integer.class)) {
                            sheetCell.setCellValue((double)Integer.valueOf(cellStr));
                        } else if (export.json()) {
                            cellStr = JSON.toJSONString(export.type().cast(cellValue));
                        }

                        if (!cellValue.getClass().equals(Double.class) && !cellValue.getClass().equals(Integer.class)) {
                            sheetCell.setCellValue(cellStr);
                        }
                    }

                    ++cellIndex;
                }
            }
        }
    }

}

public static <T> List<T> importStreamExcel(InputStream inputStream, Class dataClass) throws IOException {
    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception var4) {
        workbook = new XSSFWorkbook(inputStream);
    }

    return workbook(workbook, dataClass);
}

public static <T> List<T> workbook(Workbook workbook, Class dataClass) {
    Sheet sheet = workbook.getSheetAt(0);
    Row sheetHeader = sheet.getRow(0);
    List<String> headerList = new ArrayList();

    for(int i = 0; i < sheetHeader.getLastCellNum(); ++i) {
        String header = sheetHeader.getCell(i).getStringCellValue();
        if (StringUtils.isNotBlank(header)) {
            headerList.add(org.springframework.util.StringUtils.trimAllWhitespace(header));
        }
    }

    Field[] fields = dataClass.getDeclaredFields();
    Map<String, Field> headerFieldMap = new HashMap();
    Map<String, ExcelColumn> headerExcelColumnMap = new HashedMap();
    Field[] var8 = fields;
    int j = fields.length;

    for(int var10 = 0; var10 < j; ++var10) {
        Field field = var8[var10];
        if (field.isAnnotationPresent(ExcelColumn.class)) {
            ExcelColumn excelColumn = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
            headerFieldMap.put(excelColumn.name(), field);
            headerExcelColumnMap.put(excelColumn.name(), excelColumn);
        }
    }

    List<T> dataList = new LinkedList();

    for(j = 1; j <= sheet.getLastRowNum(); ++j) {
        Row row = sheet.getRow(j);
        if (!isRowEmpty(row) && row != null) {
            try {
                T data = dataClass.newInstance();

                for(int i = 0; i < row.getLastCellNum(); ++i) {
                    Cell cell = row.getCell(i);
                    String header = (String)headerList.get(i);
                    Field field = (Field)headerFieldMap.get(header);
                    ExcelColumn excelColumn = (ExcelColumn)headerExcelColumnMap.get(header);
                    if (excelColumn != null) {
                        try {
                            setDataValue(data, excelColumn, field, cell);
                        } catch (Exception var18) {
                            var18.printStackTrace();
                            field.set(data, (Object)null);
                        }
                    }
                }

                dataList.add(data);
            } catch (InstantiationException var19) {
                var19.printStackTrace();
            } catch (IllegalAccessException var20) {
                var20.printStackTrace();
            }
        }
    }

    return dataList;
}

public static <T> List<T> importExcel(File file, Class dataClass) throws IOException {
    if (file == null) {
        return null;
    } else {
        XSSFWorkbook workbook = null;

        try {
            workbook = new XSSFWorkbook(file);
        } catch (Exception var4) {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        }

        return workbook(workbook, dataClass);
    }
}

private static <T> void createHeader(XSSFSheet sheet, T dataRow) {
    Row header = sheet.createRow(0);
    Field[] fields = dataRow.getClass().getDeclaredFields();
    int cellIndex = 0;
    Field[] var5 = fields;
    int var6 = fields.length;

    for(int var7 = 0; var7 < var6; ++var7) {
        Field field = var5[var7];
        if (field.isAnnotationPresent(ExcelColumn.class)) {
            ExcelColumn export = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
            String title = export.name();
            Cell headerCell = header.createCell(cellIndex);
            headerCell.setCellValue(title);
            ++cellIndex;
        }
    }

}

public static boolean isRowEmpty(Row row) {
    for(int c = row.getFirstCellNum(); c < row.getLastCellNum(); ++c) {
        Cell cell = row.getCell(c);
        if (cell != null && cell.getCellType() != 3) {
            return false;
        }
    }

    return true;
}

private static void setDataValue(Object data, ExcelColumn excelColumn, Field field, Cell cell) throws ParseException, IllegalAccessException {
    field.setAccessible(true);
    if (cell == null) {
        field.set(data, (Object)null);
    } else if (field.getType() == Date.class) {
        if (cell.getCellType() == 0) {
            field.set(data, cell.getDateCellValue());
        } else if (StringUtils.isEmpty(excelColumn.format())) {
            field.set(data, DateUtils.parseDate(cell.getStringCellValue()));
        } else {
            field.set(data, DateUtils.parseDate(cell.getStringCellValue(), new String[]{excelColumn.format()}));
        }
    } else {
        Object cellValue;
        if (excelColumn.type().isEnum()) {
            cellValue = cell.getCellType() == 0 ? cell.getNumericCellValue() : cell.getStringCellValue();
            Integer enumValue = (Integer)EnumUtils.getValueByDescription(cellValue, excelColumn.type());
            field.set(data, enumValue);
        } else if (field.getType() == Integer.class) {
            if (cell.getCellType() == 0) {
                field.set(data, (int)cell.getNumericCellValue());
            } else {
                field.set(data, Integer.parseInt(cell.getStringCellValue()));
            }
        } else if (field.getType() == Long.class) {
            if (cell.getCellType() == 0) {
                field.set(data, (long)cell.getNumericCellValue());
            } else {
                field.set(data, Long.parseLong(cell.getStringCellValue()));
            }
        } else if (field.getType() == Double.class) {
            if (cell.getCellType() == 0) {
                field.set(data, cell.getNumericCellValue());
            } else {
                field.set(data, Double.parseDouble(cell.getStringCellValue()));
            }
        } else if (field.getType() == String.class) {
            cellValue = cell.getCellType() == 0 ? cell.getNumericCellValue() : cell.getStringCellValue();
            field.set(data, cellValue);
        }
    }

}

public static <T> void tempImport(ApplicationContext applicationContext, HttpServletResponse response, List<T> data, String name, String path, Integer rowIndex) throws Exception {
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(name) + ".xlsx");
    Resource resource = applicationContext.getResource(path);
    InputStream inputStream = resource.getInputStream();

    try {
        if (data.size() > 0) {
            XSSFWorkbook workbook = createTempWorkbook(data, inputStream, rowIndex);
            workbook.write(response.getOutputStream());
            workbook.close();
        }
    } catch (Exception var9) {
        var9.printStackTrace();
    }

}

private static <T> XSSFWorkbook createTempWorkbook(List<T> data, InputStream inputStream, Integer rowIndex) throws Exception, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    InputStream is = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    workbook = new XSSFWorkbook(inputStream);
    sheet = workbook.getSheetAt(0);
    createBody(sheet, data, rowIndex);
    return workbook;
}

}