This implementation demonstrates handling hierarchical data, such as product categories and subcategroies, using a relasional database. The primary category (e.g., 'Rifle') acts as the parent, and the secondary category (e.g., 'AK-47') is the child, which also contains a description.

Database Schema

DROP TABLE IF EXISTS `product_category`;
CREATE TABLE `product_category` (
  `id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Category ID',
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Category Name',
  `parent_id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT 'Parent Category ID',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Description',
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT 'Creation Time',
  `update_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT 'Update Time',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_parent_id`(`parent_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Product Categories' ROW_FORMAT = Compact;

Entity Class

package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.*;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="ProductCategory", description="Product Category Entity")
public class ProductCategory implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "Category ID")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private String id;

    @ApiModelProperty(value = "Category Name")
    private String name;

    @ApiModelProperty(value = "Parent Category ID")
    private String parentId;

    @ApiModelProperty(value = "Description")
    private String description;

    @ApiModelProperty(value = "Creation Time")
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;

    @ApiModelProperty(value = "Update Time")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

Excel Data Model

Create a seperate package for Excel-related classes.

package com.example.demo.entity.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CategoryExcelData {
    @ExcelProperty(index = 0)
    private String primaryCategoryName;

    @ExcelProperty(index = 1)
    private String secondaryCategoryName;

    @ExcelProperty(index = 2)
    private String description;
}

Service Interface

package com.example.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.entity.ProductCategory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;

public interface ProductCategoryService extends IService<ProductCategory> {
    void exportCategories(HttpServletResponse response);
    void importCategories(MultipartFile file, ProductCategoryService service);
}

Controller

package com.example.demo.controller;

import com.example.demo.service.ProductCategoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/api/categories")
public class ProductCategoryController {

    @Autowired
    private ProductCategoryService categoryService;

    @GetMapping("/export")
    public void exportToExcel(HttpServletResponse response) {
        categoryService.exportCategories(response);
    }

    @PostMapping("/import")
    public String importFromExcel(@RequestParam("file") MultipartFile file) {
        categoryService.importCategories(file, categoryService);
        return "Import successful";
    }
}

Service Implementation: Data Export

package com.example.demo.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.entity.ProductCategory;
import com.example.demo.mapper.ProductCategoryMapper;
import com.example.demo.service.ProductCategoryService;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@Service
public class ProductCategoryServiceImpl extends ServiceImpl<ProductCategoryMapper, ProductCategory> implements ProductCategoryService {

    @Override
    public void exportCategories(HttpServletResponse response) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String fileName = "Product_Categories";
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

        List<ProductCategory> categoryList = this.list();
        try {
            EasyExcel.write(response.getOutputStream(), ProductCategory.class)
                    .sheet("Categories")
                    .doWrite(categoryList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Excel Import Listener

Create a listaner to process Excel rows during import.

package com.example.demo.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.ProductCategory;
import com.example.demo.entity.excel.CategoryExcelData;
import com.example.demo.service.ProductCategoryService;

public class CategoryExcelListener extends AnalysisEventListener<CategoryExcelData> {

    private ProductCategoryService categoryService;

    public CategoryExcelListener() {}

    public CategoryExcelListener(ProductCategoryService categoryService) {
        this.categoryService = categoryService;
    }

    @Override
    public void invoke(CategoryExcelData data, AnalysisContext context) {
        if (data == null) {
            throw new RuntimeException("Excel row data is empty");
        }
        // Check and save primary category
        ProductCategory primaryCategory = findPrimaryCategory(data.getPrimaryCategoryName());
        if (primaryCategory == null) {
            primaryCategory = new ProductCategory();
            primaryCategory.setName(data.getPrimaryCategoryName());
            primaryCategory.setParentId("0");
            categoryService.save(primaryCategory);
        }
        // Save secondary category linked to the primary
        String parentId = primaryCategory.getId();
        ProductCategory secondaryCategory = findSecondaryCategory(data.getSecondaryCategoryName(), parentId, data.getDescription());
        if (secondaryCategory == null) {
            secondaryCategory = new ProductCategory();
            secondaryCategory.setParentId(parentId);
            secondaryCategory.setName(data.getSecondaryCategoryName());
            secondaryCategory.setDescription(data.getDescription());
            categoryService.save(secondaryCategory);
        }
    }

    private ProductCategory findPrimaryCategory(String name) {
        QueryWrapper<ProductCategory> wrapper = new QueryWrapper<>();
        wrapper.eq("name", name);
        wrapper.eq("parent_id", "0");
        return categoryService.getOne(wrapper);
    }

    private ProductCategory findSecondaryCategory(String name, String parentId, String description) {
        QueryWrapper<ProductCategory> wrapper = new QueryWrapper<>();
        wrapper.eq("name", name);
        wrapper.eq("parent_id", parentId);
        wrapper.eq("description", description);
        return categoryService.getOne(wrapper);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {}
}

Service Implementation: Data Import

// In ProductCategoryServiceImpl class
@Override
public void importCategories(MultipartFile file, ProductCategoryService service) {
    try {
        EasyExcel.read(file.getInputStream(), CategoryExcelData.class, new CategoryExcelListener(service))
                .sheet()
                .doRead();
    } catch (Exception e) {
        e.printStackTrace();
    }
}