java灵活配置处理excel导入导出

1.通过表头headers，属性字段exportFields 配置导入导出功能
设备导出

Controller层

//Controller
@GetMapping(value = "/system/exportEquipment", name = "导出excel")
public void exportEquipment(@RequestParam(value = "params", required = false) String params, HttpServletResponse response) throws UnsupportedEncodingException {
    Gson gson = new Gson();
    SfEqBasicInfoModel entity = gson.fromJson(params, SfEqBasicInfoModel.class);
    sfEqBasicInfoService.exportEquipment(entity, response);
}

 @PostMapping(value = "/system/importEquipment", name = "导入excel")
  public Object importUser(HttpServletRequest request) {
      MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;
      MultipartFile file = mulRequest.getFile("fileName");
      Map map = sfEqBasicInfoService.importEquipment(file);
      return JsonData.buildSuccess(map);
  }

service层

    static String[] headers = {"高校编号", "高校名称", "设备编号", "设备名称",
            "购置日期",
            "国别", "生产厂家", "联系人", "联系电话", "型号",
            "规格", "总价", "组织机构编码","组织机构", "设备分类", "安置地点",
            "是否涉密", "设备现状","功能范围", "技术指标"};
    static String[] exportFields = {"universityCode", "universityName", "eqId", "eqName",
            "buyDate",
            "countryName", "productFactory", "contact", "contactTelephone","modelNumber",
            "specification", "totalPrice","deptId","deptName", "typeName", "location",
            "isAuth", "presentSituationName", "rangeApplication", "technicalIndicators"};


@Override
    public void exportEquipment(SfEqBasicInfoModel model, HttpServletResponse response) {
        try {
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition","attachment;filename=" + new String("设备信息.xlsx".getBytes("gbk"), "iso8859-1"));
            //查询数据接口
            List<SfEqBasicInfo> dataList = sfEqBasicInfoMapper.selectRecordList(model);
            ExcelUtil.doExport(headers, exportFields, dataList, "设备信息", response.getOutputStream());
        } catch (Exception e) {
            logger.info(e.getMessage());
        }
    }


 @Override
    public Map importEquipment(MultipartFile file) {
        Map<String, Object> map = new HashMap<String, Object>();
        InputStream is = null;
        List<SfEqBasicInfo> eqList = new ArrayList<>();
        List<SfEqPerfectInfo> eqPerfectList = new ArrayList<>();
        try {
            is = file.getInputStream();
            String fileName = file.getOriginalFilename();
            Workbook workbook = null;
            if (fileName.endsWith("xlsx")) {
                //Excel 2007
                workbook = new XSSFWorkbook(is);
                //workbook = new SXSSFWorkbook(new XSSFWorkbook(is));
            } else if (fileName.endsWith("xls")) {
                //Excel 2003
                workbook = new HSSFWorkbook(is);
            }

            Map<String, String> headerMap = new HashMap<>();
            for (int i = 0; i < headers.length; i++) {
                headerMap.put(headers[i], exportFields[i]);
            }
            //解析excel
            ArrayList<Map<String, Object>> maps = ExcelUtil.readExcel(workbook, headerMap);

            Gson gson = GSONUtil.create();//支撑字符串转时间类型 LocalDateTime
            Type type = new TypeToken<ArrayList<SfEqBasicInfo>>() {
            }.getType();

            eqList = gson.fromJson(gson.toJson(maps), type);

            //方法一：使用流遍历操作
            for (SfEqBasicInfo info : eqList) {
                SfEqPerfectInfo sfEqPerfectInfo = new SfEqPerfectInfo();
                sfEqPerfectInfo.setEqId(info.getEqId());
                sfEqPerfectInfo.setUniversityCode(info.getUniversityCode());
                sfEqPerfectInfo.setTechnicalIndicators(info.getTechnicalIndicators());
                sfEqPerfectInfo.setRangeApplication(info.getRangeApplication());
                sfEqPerfectInfo.setJprofiles(info.getJprofiles());
                sfEqPerfectInfo.setJcontentinfo(info.getJcontentinfo());
                eqPerfectList.add(sfEqPerfectInfo);
            }
            List<List<SfEqBasicInfo>> eqListSub = ExcelUtil.getSubList(100, eqList);
            List<List<SfEqPerfectInfo>> eqPerfectListSub = ExcelUtil.getSubList(100, eqPerfectList);
            //清空临时表,插入临时表
            sfEqBasicInfoMapper.truncateTemp();
            sfEqPerfectInfoMapper.truncateTemp();
            eqListSub.forEach(
                    c -> {
                        sfEqBasicInfoMapper.insertList(c);
                    }
            );
            eqPerfectListSub.forEach(
                    c -> {
                        sfEqPerfectInfoMapper.insertList(c);
                    }
            );
            //更新正式表
            sfEqBasicInfoMapper.mergeList();
            sfEqPerfectInfoMapper.mergeList();
            map.put("code", 200);
            map.put("msg", "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            map.put("message", e.getMessage());
            map.put("code", 201);
            map.put("msg", "导入失败");
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return map;
        }

    }

ExcelUtil工具类

//
public class ExcelUtil {
/**
     * 将list拆分成指定数量的小list
     * 注: 使用的subList方式,返回的是list的内部类,不可做元素的删除,修改,添加操作
     *
     * @param length 数量
     * @param list   大list
     */
    public static  <T> List<List<T>> getSubList(int length, List<T> list) {
        int size = list.size();
        int temp = size / length + 1;
        boolean result = size % length == 0;
        List<List<T>> subList = new ArrayList<>();
        for (int i = 0; i < temp; i++) {
            if (i == temp - 1) {
                if (result) {
                    break;
                }
                subList.add(list.subList(length * i, size));
            } else {
                subList.add(list.subList(length * i, length * (i + 1)));
            }
        }
        return subList;
    }
}


    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行后将之前的数据刷新到硬盘

            }
        }
    }

public 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 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("LocalDateTime")) {
            //LocalDateTime 转成字符串 导出
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                String createTime = dateTimeFormatter.format((LocalDateTime)val);
                cell.setCellValue(createTime);
            }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());
                }
            }
        }
    }

DAO层 mapper.xml

<delete id="truncateTemp">
        truncate table sf_eq_basic_info_t
    </delete>
    <insert id="insertList" parameterType="java.util.List">

        insert ALL into sf_eq_basic_info_t(
        eq_ID, eq_Name,buy_Date,model_Number, specification, total_Price,
        product_Factory, country_Name,universityCode,universityName
        ,contact,contacttelephone,MODEL_NUMBER,DEPT_ID,DEPT_NAME,
        TYPE_NAME,location,PRESENT_SITUATION_NAME,RZ_STATUS
        )
        <foreach collection="list" item="info" index="index" separator="union all">
            select
            #{info.eqId},#{info.eqName},#{info.buyDate},#{info.modelNumber},#{info.specification},#{info.totalPrice},#{info.productFactory},#{info.countryName},#{info.universityCode},#{info.universityName}
            ,#{info.contact},#{info.contactTelephone},#{info.modelNumber},#{info.deptId},#{info.deptName},
            #{info.typeName},#{info.location},#{info.presentSituationName},'2'
            RZ_STATUS
            from dual
        </foreach>
    </insert>

    <update id="mergeList">
        merge into sf_eq_basic_info a
            using sf_eq_basic_info_t b on (a.eq_id = b.eq_id and a.universitycode = b.universitycode)
            when matched then
                update set a.eq_name = b.eq_name
                    ,a.buy_Date = b.buy_Date
                    ,a.model_number = b.model_number
                    ,a.specification = b.specification
                    ,a.total_price = b.total_price
                    ,a.product_factory = b.product_factory
                    ,a.country_name = b.country_name
                    ,a.universityname = b.universityname
                    ,a.contact = b.contact
                    ,a.contacttelephone = b.contacttelephone
                    ,a.dept_id = b.dept_id
                    ,a.dept_name = b.dept_name
                    ,a.type_name = b.type_name
                    ,a.location = b.location
                    ,a.present_situation_name = b.present_situation_name
                    ,a.RZ_STATUS = b.RZ_STATUS
            when not matched then
                insert (a.eq_id, a.eq_name,a.buy_Date, a.model_number, a.specification, a.total_price, a.product_factory,
                        a.country_name, a.universitycode, a.universityname, a.contact, a.contacttelephone, a.dept_id,a.dept_name,
                        a.type_name, a.location, a.present_situation_name, a.RZ_STATUS)
                    values ( b.eq_id, b.eq_name,b.buy_Date, b.model_number, b.specification, b.total_price, b.product_factory
                           , b.country_name, b.universitycode, b.universityname
                           , b.contact, b.contacttelephone, b.dept_id,b.dept_name, b.type_name, b.location
                           , b.present_situation_name, b.RZ_STATUS)
    </update>