导入导出Excel 功能

导出功能文档

需要依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.15</version>
        </dependency>

工具类

package com.knowledge.fc.edsion.common;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    public static Cell getOrCreateCell(Row r, int i) {
        Cell c = r.getCell(i);
        if (c == null) {
            c = r.createCell(i);
        }
        return c;
    }

    public static Cell setCell(Row r, int i, Double v) {
        Cell c = getOrCreateCell(r, i);
        if (v != null) {
            DecimalFormat df = new DecimalFormat("#.####");
            c.setCellType(CellType.NUMERIC);
            c.setCellValue(Double.valueOf(df.format(v)));
        }
        return c;
    }

    public static Cell setCell(Row r, int i, String v) {
        Cell c = getOrCreateCell(r, i);
        if (v != null) {
            c.setCellType(CellType.STRING);
            c.setCellValue(v);
        }
        return c;
    }

    public static Cell setCell(Row r, int i, Integer v) {
        Cell c = getOrCreateCell(r, i);
        if (v != null) {
            c.setCellType(CellType.NUMERIC);
            c.setCellValue(v);
        }
        return c;
    }

    public static Date parseDate(Cell cell) {

        if (null != cell) {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                try {
                    java.util.Date date = cell.getDateCellValue();
                    return new Date(date.getTime());
                } catch (Exception e) {

                }
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                try {
                    String sDate = cell.getStringCellValue();
                    return Date.valueOf(sDate.replace('/', '-'));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                return null;
            }
        }
        return null;

    }

    public static Double parseNumber(Cell cell) {

        if (null != cell) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    return null;
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA:
                    try {
                        return cell.getNumericCellValue();
                    } catch (Exception e) {
//                        e.printStackTrace();
                        return null;
                    }
                case HSSFCell.CELL_TYPE_STRING:
                    String v = cell.getStringCellValue();
                    try {
                        v = v.replaceAll(",", "").replaceAll("￥", "");
                        return Double.valueOf(v);
                    } catch (Exception e) {

                    }
            }
        }
        return null;
    }

    public static String parseString(Cell cell) {
        if (null != cell) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    return null;
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA:
                    try {
                        if (cell.getNumericCellValue() != Double.NaN) {
                            String val = "" + cell.getNumericCellValue();
                            val = new BigDecimal(val).stripTrailingZeros().toPlainString();
                            return val;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        return null;
                    }
                case HSSFCell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
            }
        }
        return null;
        //new ValidationException(cell.getRowIndex() + "行" + cell.getColumnIndex() + "列 数值类型解析失败 ，类型编码  " + cell.getCellType() + " 值 :" + cell.toString());
    }

    static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


    public interface Convertor {
        Object convert(String field, Object obj);

        boolean handle(String field, Object obj);
    }


    public static void setRow(Row row, int start, List vals) {
        for (int i = 0; i < vals.size(); ++i) {
            Object o = vals.get(i);
            if (null != o) {
                Cell c = row.getCell(i + start);
                if (c == null) {
                    c = row.createCell(i + start);
                }

                if (o instanceof String) {
                    c.setCellType(CellType.STRING);
                    c.setCellValue((String) o);
                } else if (o instanceof java.util.Date) {
                    c.setCellType(CellType.STRING);
                    c.setCellValue(sdf.format(o));
                } else if (o instanceof Integer) {
                    c.setCellType(CellType.NUMERIC);
                    c.setCellValue((Integer) o);
                } else if (o instanceof Double) {
                    c.setCellType(CellType.NUMERIC);
                    c.setCellValue((Double) o);
                } else {
                    c.setCellType(CellType.STRING);
                    c.setCellValue(o.toString());
                }
            }
        }
    }

    public static void setRow(Row row, List vals) {
        setRow(row, 0, vals);
    }

    public static void setRow(Row row, int start, Object bean, List<String> fields, Convertor convertor) {
        List vals = new ArrayList<>();
        for (String field : fields) {
            try {
                Method method = bean.getClass().getMethod("get" + field.substring(0, 1).toUpperCase() + field.substring(1));
                Object val = method.invoke(bean);
                if (null != convertor) {
                    if (convertor.handle(field, val)) {
                        val = convertor.convert(field, val);
                    }
                }
                vals.add(val);

            } catch (Exception e) {
                vals.add(null);
                e.printStackTrace();
            }
        }
        setRow(row, start, vals);
    }

    public static void setRow(Row row, Object bean, List<String> fields, Convertor convertor) {
        setRow(row, 0, bean, fields, convertor);
    }

    public static Row nextRow(Row row) {
        Sheet sheet = row.getSheet();
        Row r = sheet.getRow(row.getRowNum() + 1);
        if (r == null) {
            r = sheet.createRow(row.getRowNum() + 1);
        }
        return r;
    }

    public static void setRows(Row row, int start, List beans, List<String> fields, Convertor convertor) {
        for (Object bean : beans) {
            setRow(row, start, bean, fields, convertor);
            row = nextRow(row);
        }
    }

    public static void setRows(Row row, List beans, List<String> fields, Convertor convertor) {
        setRows(row, 0, beans, fields, convertor);
    }


    public static <T> T getBean(Row row, int start, Class cls, List<String> fields) throws IllegalAccessException, InstantiationException {
        if (null == row) {
            return null;
        }


        Object bean = cls.newInstance();

        for (int i = 0; i < fields.size(); ++i) {
            String field = fields.get(i);

            if (Util.isEmpty(field))
                continue;

            try {
                Class fdClass = cls.getDeclaredField(field).getType();
                Method setMethod = cls.getMethod("set" + field.substring(0, 1).toUpperCase() + field.substring(1), fdClass);
                Cell cell = row.getCell(i + start);

                if (fdClass.getName().equals(String.class.getName())) {
                    setMethod.invoke(bean, parseString(cell));
                }
                if (fdClass.getName().equals(java.util.Date.class.getName())) {
                    java.util.Date val = parseDate(cell);
                    if (null != val) {
                        setMethod.invoke(bean, val);
                    }
                }
                if (fdClass.getName().equals(java.sql.Date.class.getName())) {
                    java.util.Date val = parseDate(cell);
                    if (null != val) {
                        setMethod.invoke(bean, new java.sql.Date(val.getTime()));
                    }
                }
                if (fdClass.getName().equals(java.sql.Timestamp.class.getName())) {
                    java.util.Date val = parseDate(cell);
                    if (null != val) {
                        setMethod.invoke(bean, new Timestamp(val.getTime()));
                    }
                }
                if (fdClass.getName().equals(Double.class.getName())) {
                    setMethod.invoke(bean, parseNumber(cell));
                }
                if (fdClass.getName().equals(Integer.class.getName())) {
                    Double val = parseNumber(cell);
                    if (null != val) {
                        setMethod.invoke(bean, val.intValue());
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return (T) bean;
    }

    public static <T> T getBean(Row row, Class cls, List<String> fields) throws InstantiationException, IllegalAccessException {
        return getBean(row, 0, cls, fields);
    }

    public static <T> List<T> getBeans(Row row, int start, Class cls, List<String> fields) throws InstantiationException, IllegalAccessException {
        List<T> ret = new ArrayList<>();
        if (null != row) {
            Sheet sheet = row.getSheet();
            for (int i = row.getRowNum(); i <= sheet.getLastRowNum(); ++i) {
                ret.add(getBean(sheet.getRow(i), start, cls, fields));
            }
        }
        return ret;
    }

    public static <T> List<T> getBeans(Row row, Class cls, List<String> fields) throws InstantiationException, IllegalAccessException {
        return getBeans(row, 0, cls, fields);
    }

    /**
     * 返回Excel真实 行数
     *
     * @Author: shunqiao.wang
     * @CreateDate: 2021-01-07
     */
    private int getExcelRealRow(Sheet sheet) {
        boolean flag = false;
        for (int i = 1; i <= sheet.getLastRowNum(); ) {
            Row r = sheet.getRow(i);
            if (r == null) {
                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                continue;
            }
            flag = false;
            for (Cell c : r) {
                if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    flag = true;
                    break;
                }
            }
            if (flag) {
                i++;
                continue;
            } else {
                // 如果是空白行（即可能没有数据，但是有一定格式）
                if (i == sheet.getLastRowNum()) {// 如果到了最后一行，直接将那一行remove掉
                    sheet.removeRow(r);
                } else {// 如果还没到最后一行，则数据往上移一行
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                }
            }
        }
        return sheet.getLastRowNum();
    }
}

实际Demo

controller

/**
     * 导出 设备工时
     */
    @RequestMapping("/exportEquipmentNewsInfo")
    public ResponseEntity<ByteArrayResource> exportEquipmentNewsInfo(@SessionAttribute("user") User user,
                                                                 @RequestParam List<Integer> ids) throws IOException {
        ByteArrayOutputStream baos = exportSMMInfoService.exportEquipmentNewsInfo(user, ids);
        byte[] data = baos.toByteArray();
        ByteArrayResource resource = new ByteArrayResource(data);
        return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(
                        "机加设备信息表_" + new SimpleDateFormat("_yyyy年MM月dd日").format(new java.sql.Date(System.currentTimeMillis())) + ".xlsx", "UTF-8"))
                .contentType(MediaType.APPLICATION_OCTET_STREAM)
                .contentLength(data.length)
                .body(resource);
    }
    /**
     * 导入 设备工时
     */
    @RequestMapping("/importEquipmentNewsInfo")
    public Result<Object> importEquipmentNewsInfo(@SessionAttribute("user") User user,
                                              @RequestParam MultipartFile file) {
        try {
            exportSMMInfoService.importEquipmentNewsInfo(user, file.getOriginalFilename(), file.getInputStream());
        } catch (Exception exception) {
            exception.printStackTrace();
            return Result.error(500, exception.toString());
        }
        return Result.ok("导入成功");
    }

impl

 @Override
    public ByteArrayOutputStream exportEquipmentNewsInfo(User user, List<Integer> ids) throws IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        InputStream is = this.getClass().getResourceAsStream("/设备信息_基础信息导出模板.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        List<EquipmentInfo> equipmentInfoList = equipmentInfoRepository.findInfoByIdAndIsDeleted(ids, 0);
        Sheet sheet = workbook.getSheetAt(0);
        int num = 2;
        int count = 1;
        for (EquipmentInfo i : equipmentInfoList) {
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    0, count);
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    1, i.getEquipmentType() == null ? null : i.getEquipmentType());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    2, i.getEquipmentModel() == null ? null : i.getEquipmentModel());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    3, i.getEquipmentName() == null ? null : i.getEquipmentName());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    4, i.getQuotation() == null ? null : i.getQuotation());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    5, i.getToolCount() == null ? null : i.getToolCount());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    6, i.getToolPlant() == null ? null : i.getToolPlant());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    7, i.getControlSystem() == null ?  null : i.getControlSystem());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    8, i.getLocationPrecision() == null ? null : i.getLocationPrecision());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    9, i.getReLocationPrecision() == null ? null : i.getReLocationPrecision());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    10, i.getToolRoute() == null ?  null : i.getToolRoute());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    11, i.getWorkbenchSize() == null ? null : i.getWorkbenchSize() );
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    12, i.getPower() == null ? null : i.getPower());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    13, i.getRev() == null ?  null : i.getRev());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    14, i.getHiltModel() == null ? null : i.getHiltModel());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    15, i.getIsWater() == null ? null : i.getIsWater());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    16, i.getHiltCount() == null ?  null : i.getHiltCount());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    17, i.getWorkbenchBearing() == null ? null : i.getWorkbenchBearing());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    18, i.getManufacturer() == null ? null : i.getManufacturer());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    19, i.getCheckDate() == null ?  null : i.getCheckDate());
            ExcelUtil.setCell(sheet.getRow(num) == null ? sheet.createRow(num) : sheet.getRow(num),
                    20, i.getRemark() == null ? null : i.getRemark());
            num++;
            count++;
        }
        workbook.write(baos);
        return baos;
    }

    @Override
    public void importEquipmentNewsInfo(User user, String originalFilename, InputStream inputStream) throws IOException {
        // 获取整个文件
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 定义 第一个sheet页 对象变量
        XSSFSheet sheet1 = workbook.getSheetAt(0);
        // 循环读取 第一个sheet页 有效行数据
        for (int i = 2; i < getExcelRealRow(sheet1); i++) {
            // 动态 获取第i行数据
            Row zeroLevelRow = sheet1.getRow(i);
            EquipmentInfo equipmentInfo = new EquipmentInfo();
            equipmentInfo.setEquipmentType(ExcelUtil.parseString(zeroLevelRow.getCell(1)));
            equipmentInfo.setEquipmentModel(ExcelUtil.parseString(zeroLevelRow.getCell(2)));
            equipmentInfo.setEquipmentName(ExcelUtil.parseString(zeroLevelRow.getCell(3)));
            equipmentInfo.setQuotation(ExcelUtil.parseString(zeroLevelRow.getCell(4)));
            equipmentInfo.setToolCount(ExcelUtil.parseString(zeroLevelRow.getCell(5)));
            equipmentInfo.setToolPlant(ExcelUtil.parseString(zeroLevelRow.getCell(6)));
            equipmentInfo.setControlSystem(ExcelUtil.parseString(zeroLevelRow.getCell(7)));
            equipmentInfo.setLocationPrecision(ExcelUtil.parseString(zeroLevelRow.getCell(8)));
            equipmentInfo.setReLocationPrecision(ExcelUtil.parseString(zeroLevelRow.getCell(9)));
            equipmentInfo.setToolRoute(ExcelUtil.parseString(zeroLevelRow.getCell(10)));
            equipmentInfo.setWorkbenchSize(ExcelUtil.parseString(zeroLevelRow.getCell(11)));
            equipmentInfo.setPower(ExcelUtil.parseString(zeroLevelRow.getCell(12)));
            equipmentInfo.setRev(ExcelUtil.parseString(zeroLevelRow.getCell(13)));
            equipmentInfo.setHiltModel(ExcelUtil.parseString(zeroLevelRow.getCell(14)));
            equipmentInfo.setIsWater(ExcelUtil.parseString(zeroLevelRow.getCell(15)));
            equipmentInfo.setHiltCount(ExcelUtil.parseString(zeroLevelRow.getCell(16)));
            equipmentInfo.setWorkbenchBearing(ExcelUtil.parseString(zeroLevelRow.getCell(17)));
            equipmentInfo.setManufacturer(ExcelUtil.parseString(zeroLevelRow.getCell(18)));
            equipmentInfo.setCheckDate(ExcelUtil.parseString(zeroLevelRow.getCell(19)));
            equipmentInfo.setRemark(ExcelUtil.parseString(zeroLevelRow.getCell(20)));
            equipmentInfo.setCreateTime(Util.current());
            equipmentInfo.setCreateUser(user.getId());
            equipmentInfoRepository.saveAndFlush(equipmentInfo);
        }
    }