Excel导入导出工具类（完整版）

川长思鸟来 2022-03-10 03:44 682阅读 0赞

*  **ExcelUtil工具类**

package com.gdz.excel.util;
    
    
    import java.io.*;
    import java.lang.reflect.Field;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * Excel导入导出
     * @Author: guandezhi
     * @Date: 2019/3/9 9:47
     */
    public class ExcelUtil {
    
        /**
         * 导出多个sheet的excel
         * @param name
         * @param mapList
         * @param response
         * @param <T>
         */
        public static <T> void exportMultisheetExcel(String name, List<Map> mapList, HttpServletResponse response) {
            BufferedOutputStream bos = null;
            try {
                String fileName = name + ".xlsx";
                bos = getBufferedOutputStream(fileName, response);
                doExport(mapList, bos);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (bos != null) {
                        bos.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 从excel中读内容
         * @param filePath
         * @param sheetIndex
         * @return
         */
        public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) {
            List<Map<String, String>> dataList = new ArrayList<>();
            Workbook wb = ExcelUtil.createWorkBook(filePath);
            if (wb != null) {
                Sheet sheet = wb.getSheetAt(sheetIndex);
                int maxRownum = sheet.getPhysicalNumberOfRows();
                Row firstRow = sheet.getRow(0);
                int maxColnum = firstRow.getPhysicalNumberOfCells();
                String columns[] = new String[maxColnum];
                for (int i = 0; i < maxRownum; i++) {
                    Map<String, String> map = null;
                    if (i > 0) {
                        map = new LinkedHashMap<>();
                        firstRow = sheet.getRow(i);
                    }
                    if (firstRow != null) {
                        String cellData = null;
                        for (int j = 0; j < maxColnum; j++) {
                            cellData = (String) ExcelUtil.getCellFormatValue(firstRow.getCell(j));
                            if (i == 0) {
                                columns[j] = cellData;
                            } else {
                                map.put(columns[j], cellData);
                            }
                        }
                    } else {
                        break;
                    }
                    if (i > 0) {
                        dataList.add(map);
                    }
                }
            }
            return dataList;
        }
    
        private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response) throws Exception {
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            return new BufferedOutputStream(response.getOutputStream());
        }
    
        private static <T> void doExport(List<Map> mapList, OutputStream outputStream) {
            int maxBuff = 100;
            // 创建excel工作文本，100表示默认允许保存在内存中的行数
            SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
            try {
                for (int i = 0; i < mapList.size(); i++) {
                    Map map = mapList.get(i);
                    String[] headers = (String[]) map.get("headers");
                    Collection<T> dataList = (Collection<T>) map.get("dataList");
                    String fileName = (String) map.get("fileName");
                    createSheet(wb, null, headers, dataList, fileName, maxBuff);
                }
    
                if (outputStream != null) {
                    wb.write(outputStream);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    
        }
    
        private static <T> void createSheet(SXSSFWorkbook wb, String[] exportFields, String[] headers, Collection<T> dataList, String fileName, int maxBuff) throws NoSuchFieldException, IllegalAccessException, IOException {
    
            Sheet sh = wb.createSheet(fileName);
    
            CellStyle style = wb.createCellStyle();
            CellStyle style2 = wb.createCellStyle();
            //创建表头
            Font font = wb.createFont();
            font.setFontName("微软雅黑");
            font.setFontHeightInPoints((short) 11);//设置字体大小
            style.setFont(font);//选择需要用到的字体格式
    
            style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景色
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    
            style2.setFont(font);//选择需要用到的字体格式
    
            style2.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景色
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平向下居中
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    
            Row headerRow = sh.createRow(0); //表头
    
            int headerSize = headers.length;
            for (int cellnum = 0; cellnum < headerSize; cellnum++) {
                Cell cell = headerRow.createCell(cellnum);
                cell.setCellStyle(style);
                sh.setColumnWidth(cellnum, 4000);
                cell.setCellValue(headers[cellnum]);
            }
    
            int rownum = 0;
            Iterator<T> iterator = dataList.iterator();
            while (iterator.hasNext()) {
                T data = iterator.next();
                Row row = sh.createRow(rownum + 1);
    
                Field[] fields = getExportFields(data.getClass(), exportFields);
                for (int cellnum = 0; cellnum < headerSize; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    cell.setCellStyle(style2);
                    Field field = fields[cellnum];
    
                    setData(field, data, field.getName(), cell);
                }
                rownum = sh.getLastRowNum();
                // 大数据量时将之前的数据保存到硬盘
                if (rownum % maxBuff == 0) {
                    ((SXSSFSheet) sh).flushRows(maxBuff); // 超过100行后将之前的数据刷新到硬盘
    
                }
            }
        }
    
    
        private static <T> void doExport(String[] headers, String[] exportFields, Collection<T> dataList,
                                         String fileName, OutputStream outputStream) {
    
            int maxBuff = 100;
            // 创建excel工作文本，100表示默认允许保存在内存中的行数
            SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
            try {
                createSheet(wb, exportFields, headers, dataList, fileName, maxBuff);
                if (outputStream != null) {
                    wb.write(outputStream);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    
        /**
         * 获取单条数据的属性
         *
         * @param object
         * @param property
         * @param <T>
         * @return
         * @throws NoSuchFieldException
         * @throws IllegalAccessException
         */
        private static <T> Field getDataField(T object, String property) throws NoSuchFieldException, IllegalAccessException {
            Field dataField;
            if (property.contains(".")) {
                String p = property.substring(0, property.indexOf("."));
                dataField = object.getClass().getDeclaredField(p);
                return dataField;
            } else {
                dataField = object.getClass().getDeclaredField(property);
            }
            return dataField;
        }
    
        private static Field[] getExportFields(Class<?> targetClass, String[] exportFieldNames) {
            Field[] fields = null;
            if (exportFieldNames == null || exportFieldNames.length < 1) {
                fields = targetClass.getDeclaredFields();
            } else {
                fields = new Field[exportFieldNames.length];
                for (int i = 0; i < exportFieldNames.length; i++) {
                    try {
                        fields[i] = targetClass.getDeclaredField(exportFieldNames[i]);
                    } catch (Exception e) {
                        try {
                            fields[i] = targetClass.getSuperclass().getDeclaredField(exportFieldNames[i]);
                        } catch (Exception e1) {
                            throw new IllegalArgumentException("无法获取导出字段", e);
                        }
    
                    }
                }
            }
            return fields;
        }
    
        /**
         * 根据属性设置对应的属性值
         *
         * @param dataField 属性
         * @param object    数据对象
         * @param property  表头的属性映射
         * @param cell      单元格
         * @param <T>
         * @return
         * @throws IllegalAccessException
         * @throws NoSuchFieldException
         */
        private static <T> void setData(Field dataField, T object, String property, Cell cell)
                throws IllegalAccessException, NoSuchFieldException {
            dataField.setAccessible(true); //允许访问private属性
            Object val = dataField.get(object); //获取属性值
            Sheet sh = cell.getSheet(); //获取excel工作区
            CellStyle style = cell.getCellStyle(); //获取单元格样式
            int cellnum = cell.getColumnIndex();
            if (val != null) {
                if (dataField.getType().toString().endsWith("String")) {
                    cell.setCellValue((String) val);
                } else if (dataField.getType().toString().endsWith("Integer") || dataField.getType().toString().endsWith("int")) {
                    cell.setCellValue((Integer) val);
                } else if (dataField.getType().toString().endsWith("Long") || dataField.getType().toString().endsWith("long")) {
                    cell.setCellValue(val.toString());
                } else if (dataField.getType().toString().endsWith("Double") || dataField.getType().toString().endsWith("double")) {
                    cell.setCellValue((Double) val);
                } else if (dataField.getType().toString().endsWith("Date")) {
                    DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell.setCellValue(format.format((Date) val));
                } else if (dataField.getType().toString().endsWith("List")) {
                    List list1 = (List) val;
                    int size = list1.size();
                    for (int i = 0; i < size; i++) {
                        //加1是因为要去掉点号
                        int start = property.indexOf(dataField.getName()) + dataField.getName().length() + 1;
                        String tempProperty = property.substring(start, property.length());
                        Field field = getDataField(list1.get(i), tempProperty);
                        Cell tempCell = cell;
                        if (i > 0) {
                            int rowNum = cell.getRowIndex() + i;
                            Row row = sh.getRow(rowNum);
                            if (row == null) {//另起一行
                                row = sh.createRow(rowNum);
                                //合并之前的空白单元格（在这里需要在header中按照顺序把list类型的字段放到最后，方便显示和合并单元格）
                                for (int j = 0; j < cell.getColumnIndex(); j++) {
                                    sh.addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + size - 1, j, j));
                                    Cell c = row.createCell(j);
                                    c.setCellStyle(style);
                                }
                            }
                            tempCell = row.createCell(cellnum);
                            tempCell.setCellStyle(style);
                        }
                        //递归传参到单元格并获取偏移量（这里获取到的偏移量都是第二层后list的偏移量）
                        setData(field, list1.get(i), tempProperty, tempCell);
                    }
                } else {
                    if (property.contains(".")) {
                        String p = property.substring(property.indexOf(".") + 1, property.length());
                        Field field = getDataField(val, p);
                        setData(field, val, p, cell);
                    } else {
                        cell.setCellValue(val.toString());
                    }
                }
            }
        }
    
    
        private static Workbook createWorkBook(String filePath) {
            Workbook wb = null;
            if (filePath == null) {
                return null;
            }
            String extString = filePath.substring(filePath.lastIndexOf("."));
            InputStream is = null;
            try {
                is = new FileInputStream(filePath);
                if (".xls".equals(extString)) {
                    return wb = new HSSFWorkbook(is);
                } else if (".xlsx".equals(extString)) {
                    return wb = new XSSFWorkbook(is);
                } else {
                    return wb;
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return wb;
        }
    
        /**
         * 将字段转为相应的格式
         * @param cell
         * @return
         */
        private static Object getCellFormatValue(Cell cell) {
            Object cellValue = null;
            if (cell != null) {
                //判断cell类型
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        break;
                    }
                    case Cell.CELL_TYPE_FORMULA: {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            cellValue = cell.getDateCellValue();转换为日期格式YYYY-mm-dd
                        } else {
                            cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING: {
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    }
                    default:
                        cellValue = "";
                }
            } else {
                cellValue = "";
            }
            return cellValue;
        }
    
    }

*  **测试一下**

/**
     * @Author: guandezhi
     * @Date: 2019/3/9 11:18
     */
    @Slf4j
    @RestController
    @RequestMapping("/excel")
    public class ExcelController {
    
        @RequestMapping(value = "/exportExcel")
        public String exportExcel(HttpServletResponse response) throws Exception {
            String[] headers = {"姓名", "性别", "年龄", "学校", "班级"};
            String fileName = "学生表";
            List<Student> studentList = new ArrayList<>();
            Student student = new Student();
            student.setStudentName("guandezhi");
            student.setGrade("三年二班");
            student.setAge(20);
            student.setSchool("XX大学");
            student.setSex("男");
            studentList.add(student);
    
            Map<String, Object> studentMap = new HashMap();
            studentMap.put("headers", headers);
            studentMap.put("dataList", studentList);
            studentMap.put("fileName", fileName);
    
            List<Map> mapList = new ArrayList();
            mapList.add(studentMap);
            ExcelUtil.exportMultisheetExcel(fileName, mapList, response);
            return "success";
        }
    
        @RequestMapping(value = "/readExcel")
        public String readExcel() throws Exception {
            String filePath = "F:\\student.xls";
            List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0);
            log.info("mapList:" + mapList);
            return "success";
        }
    
    }

其中的student类

/**
     * @Author: guandezhi
     * @Date: 2019/3/7 9:53
     */
    @Data
    public class Student {
    
        private String studentName;
    
        private String sex;
    
        private Integer age;
    
        private String school;
    
        private String grade;
    }

*  导出的excel数据

![20190309134353994.png][]

*  读取到的excel数据

![20190309134522954.png][]

**总结：**

*  **该excelUtil可以导出多个sheet的excel**

[20190309134353994.png]: /images/20220310/1d538d49e74649459efc19a4784ddbf7.png
[20190309134522954.png]: /images/20220310/6eae03eec09b4f989856a974f01b1a94.png