java使用poi导出工具类

╰半橙微兮° 2023-01-20 03:58 4阅读 0赞

package com.buba.util;
    
    import com.buba.annotation.ExcelCell;
    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.FileNotFoundException;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    /**
     * Excel工具类
     */
    public class MyExcel<T> {
    
        private Workbook workbook;
        private Sheet sheet;
        private Row row;
        private Cell cell;
    
        private T t;
        private List<T> tList;
        private List<Field> fieldList;
        private OutputStream outputStream;
    
        /**
         * 导出Excel
         *
         * @param excelName 导出页名字
         * @param dataList  导出的数据
         * @param c         要导出的对象类
         * @param response  下载需要的响应对象
         * @throws Exception
         */
        public void exportExcel(String excelName, List<T> dataList, Class<T> c, HttpServletResponse response) throws Exception {
            workbook = new XSSFWorkbook();
            sheet = workbook.createSheet(excelName);
            fieldList = filterField(c.getDeclaredFields());
            Row firstRow = sheet.createRow(0);
            int cellLength = fieldList.size();
            for (int j = 0; j < cellLength; j++) {
                cell = firstRow.createCell(j);
                cell.setCellValue(fieldList.get(j).getAnnotation(ExcelCell.class).value());
            }
    
            for (int i = 0; i < dataList.size(); i++) {
                row = sheet.createRow(i + 1);
                T t = dataList.get(i);
                for (int j = 0; j < cellLength; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(getGetMethod(t, fieldList.get(j).getName()).toString());
                }
            }
    
            outputStream = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + c.getSimpleName() + ".xlsx");
            response.setContentType("application/msexcel");
            workbook.write(outputStream);
            outputStream.close();
        }
    
        private int index = 0;
        private int count = 0;
    
        /**
         * 导入功能（导入的excel单元格要求设置为文本格式）
         *
         * @param excel
         * @param c
         * @return
         * @throws IOException
         * @throws IllegalAccessException
         * @throws NoSuchMethodException
         * @throws InvocationTargetException
         * @throws InstantiationException
         */
        public List<T> importExcel(MultipartFile excel, Class<T> c) throws IOException, IllegalAccessException, NoSuchMethodException, InvocationTargetException, InstantiationException {
            String excelName = excel.getOriginalFilename();
            workbook = null;
            if (excelName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(excel.getInputStream());
            } else if (excelName.endsWith("xls")) {
                workbook = new HSSFWorkbook(excel.getInputStream());
            } else {
                throw new FileNotFoundException("文件类型错误");
            }
            tList = new ArrayList<>();
            fieldList = filterField(c.getDeclaredFields());
    
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                sheet = sheetIterator.next();
                if (sheet.getLastRowNum() == -1) return tList;
                Iterator<Row> rowIterator = sheet.rowIterator();
                rowIterator.next();// 排除第一行
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    if (row.getLastCellNum() == -1) return tList;
                    Iterator<Cell> cellIterator = row.cellIterator();
                    t = c.getConstructor().newInstance();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        fieldList.get(index).setAccessible(true);
                        MyExcelCellType type = fieldList.get(index).getAnnotation(ExcelCell.class).type();
                        if (cell.getCellType() == CellType.STRING) {
                            String stringCellValue = cell.getStringCellValue();
                            if ("".equals(stringCellValue.trim()))
                                count++;
                            if (MyExcelCellType.INTEGER == type) {
                                fieldList.get(index++).set(t, Integer.valueOf(stringCellValue));
                            } else if (MyExcelCellType.DOUBLE == type) {
                                fieldList.get(index++).set(t, Double.valueOf(stringCellValue));
                            } else if (MyExcelCellType.BIG_DECIMAL == type) {
                                fieldList.get(index++).set(t, new BigDecimal(stringCellValue));
                            } else {
                                fieldList.get(index++).set(t, stringCellValue);
                            }
                        } else {
                            count++;
                            index++;
                        }
                    }
                    if (index != count)
                        tList.add(t);
                    index = 0;
                    count = 0;
                }
            }
            return tList;
        }
    
        /**
         * 根据属性，获取get方法
         *
         * @param obj  对象
         * @param name 属性名
         * @return
         * @throws Exception
         */
        private Object getGetMethod(Object obj, String name) throws Exception {
            Method[] m = obj.getClass().getMethods();
            for (int i = 0; i < m.length; i++) {
                if (("get" + name).equalsIgnoreCase(m[i].getName())) {
                    return m[i].invoke(obj);
                }
            }
            return null;
        }
    
        /**
         * 字段过滤
         *
         * @param fields
         * @return
         */
        private List<Field> filterField(Field[] fields) {
            fieldList = new ArrayList<>();
            for (Field field : fields)
                if (field.isAnnotationPresent(ExcelCell.class))
                    fieldList.add(field);
            return fieldList;
        }
    }

自定义注解类

package com.buba.annotation;
    
    import com.buba.util.MyExcelCellType;
    
    import java.lang.annotation.*;
    
    /**
     * Excel单元格注解
     */
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface ExcelCell {
    
        String value() default "";
    
        // 用户描述该字段的类型
        MyExcelCellType type() default MyExcelCellType.STRING;
    }

自定义枚举类

package com.buba.util;
    
    public enum MyExcelCellType {
        INTEGER, DOUBLE, BIG_DECIMAL, STRING;
    }