可以导入Excel到任意表中的通用接口

2023-12-30 18:29:16

导出的参数为，传入一个tableName，传入参数，然后根据tableName查找到表，根据参数，查找特定的数据，将数据放在Excel表里返回

##Controller层


import com.rsic.iomp.assistant.domain.params.TableNameDTO;
import com.rsic.iomp.assistant.service.FileService;
import com.rsic.iomp.common.domain.ResponseBean;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @author csj
 * @version 1.0
 * @date 2020/1/6 9:37
 * @description:d
 */
@RestController
@RequestMapping("/excel")
public class FileController {

     @Autowired
     private FileService fileService;

    @ApiImplicitParam(name = "map", value = "MAP", required = true, dataType = "Map")
    @ApiOperation(value="导出excel", notes="参数：{\"tableName\":\"xxx\",\"参数名称\":\"参数值\".....(参数为多个)}")
    @GetMapping(value = "/export", produces = "application/json;charset=UTF-8")
    public ResponseBean exportExcel(HttpServletResponse response,@RequestParam Map map) throws Exception {
        fileService.exportExcel(response,map);
        return null;
    }
    @ApiImplicitParam(name = "map", value = "MAP", required = true, dataType = "Map")
    @ApiOperation(value="导出excel模板", notes="参数：{\"tableName\":\"xxx\"}")
    @GetMapping(value = "/exportTemplate", produces = "application/json;charset=UTF-8")
    public ResponseBean exportExcelTemplate(HttpServletResponse response,@RequestParam Map map) throws Exception {
        fileService.exportExcelTemplate(response,map);
        return null;
    }

    @ApiOperation(value="导入excel", notes="导入excel")
    @PostMapping(value = "/import", produces = "application/json;charset=UTF-8")
    public ResponseBean importExcel(@RequestParam("file") MultipartFile file, TableNameDTO tableNameDTO) throws Exception {
        fileService.importExcel(file,tableNameDTO);
        return null;
    }

    @GetMapping(value = "/table")
    public ResponseBean table() throws Exception {
        List tableList = Arrays.asList("d_city","cb_part_type","d_dept");
        return new ResponseBean(200,"请求成功！",tableList);

    }


}

Service层

import com.alibaba.excel.metadata.BaseRowModel;
import com.rsic.iomp.assistant.domain.*;
import com.rsic.iomp.assistant.domain.params.TableNameDTO;
import com.rsic.iomp.assistant.service.*;
import com.rsic.iomp.assistant.service.FileService;
import com.rsic.iomp.assistant.utils.excelexport.testinfo.ExportInfo;
import com.rsic.iomp.assistant.utils.excelexport.utils.ExcelUtil;
import com.rsic.iomp.common.domain.ResponseBean;
import com.rsic.iomp.common.utils.JSONUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author csj
 * @version 1.0
 * @date 2020/1/6 9:46
 * @description:
 */
@Service
public class FileServiceImpl implements FileService {


    @Autowired
    private DCityService dCityService;
    @Autowired
    private DDeptclassifyService dDeptclassifyService;
    @Autowired
    private DDevBaseinfoService dDevBaseinfoService;
    @Autowired
    private DDevTypeService dDevTypeService;
    @Autowired
    private DevResumeService devResumeService;
    @Autowired
    private DevSoftwareVersionService devSoftwareVersionService;
    @Autowired
    private DFaultclassifyService dFaultclassifyService;
    @Autowired
    private DKnowledgeclassifyService dKnowledgeclassifyService;
    @Autowired
    private DLineService dLineService;
    @Autowired
    private DMajorsService dMajorsService;
    @Autowired
    private DMaterialclassifyService dMaterialclassifyService;
    @Autowired
    private DStationclassifyService dStationclassifyService;
    @Autowired
    private DSubsysService dSubsysService;
    @Autowired
    private KnowledgerecordsService knowledgerecordsService;
    @Autowired
    private SupplierTableService supplierTableService;

    @Override
    public ResponseBean exportExcel(HttpServletResponse response,Map map) throws Exception {
        if(map == null){
            return new ResponseBean(500,"请选择需要导出的数据表！",null);
        }else if(map.get("tableName") == null){
            return new ResponseBean(500,"请选择需要导出的数据表！",null);
        }

        String tableName = map.get("tableName").toString();
        Object clazz;
        List list;
        switch (tableName){
            case "d_city":clazz = JSONUtils.map2DTO(map,DCityExample.class);list = dCityService.list((DCityExample) clazz).getRows();break;
            case "d_dept":clazz = JSONUtils.map2DTO(map, DDeptExample.class);list = dDeptclassifyService.list((DDeptExample) clazz).getRows();break;
            case "d_dev_type":clazz = JSONUtils.map2DTO(map, DDevTypeExample.class);list = dDevTypeService.list((DDevTypeExample) clazz).getRows();break;
            case "d_device":clazz = JSONUtils.map2DTO(map, DevBaseinfoExample.class);list = dDevBaseinfoService.list((DDeviceExample) clazz).getRows();break;
            case "dev_resume":clazz = JSONUtils.map2DTO(map, DevResumeExample.class);list = devResumeService.list((DevResumeExample) clazz).getRows();break;
            case "dev_software_version":clazz = JSONUtils.map2DTO(map,DevSoftwareVersionExample.class);list = devSoftwareVersionService.list((DevSoftwareVersionExample) clazz).getRows();break;
            case "d_fault_type":clazz = JSONUtils.map2DTO(map,DFaultTypeExample.class);list = dFaultclassifyService.list((DFaultTypeExample) clazz).getRows();break;
            case "d_knowledge_type":clazz = JSONUtils.map2DTO(map,DKnowledgeTypeExample.class);list = dKnowledgeclassifyService.list((DKnowledgeTypeExample) clazz).getRows();break;
            case "d_line":clazz = JSONUtils.map2DTO(map,DLineExample.class);list = dLineService.list((DLineExample) clazz).getRows();break;
            case "d_majors":clazz = JSONUtils.map2DTO(map,DMajorsExample.class);list = dMajorsService.list((DMajorsExample) clazz).getRows();break;
            case "d_material_type":clazz = JSONUtils.map2DTO(map,DMaterialTypeExample.class);list = dMaterialclassifyService.list((DMaterialTypeExample) clazz).getRows();break;
            case "d_station":clazz = JSONUtils.map2DTO(map,DStationExample.class);list = dStationclassifyService.list((DStationExample) clazz).getRows();break;
            case "d_subsys":clazz = JSONUtils.map2DTO(map,DSubsysExample.class);list = dSubsysService.list((DSubsysExample) clazz).getRows();break;
            case "knowledgerecords":clazz = JSONUtils.map2DTO(map,KnowledgerecordsExample.class);list = knowledgerecordsService.list((KnowledgerecordsExample) clazz).getRows();break;
            case "supplier_table":clazz = JSONUtils.map2DTO(map,SupplierTableExample.class);list = supplierTableService.list((SupplierTableExample) clazz).getRows();break;
            default:return new ResponseBean(500,"请输入正确的数据表",null);
        }
        String fileName = "一个 Excel 文件";
        String sheetName = "第一个 sheet";
        ExcelUtil.writeExcel(response, list, fileName, sheetName,  (BaseRowModel)clazz.getClass().newInstance());
        return null;
    }

    @Override
    public ResponseBean exportExcelTemplate(HttpServletResponse response, Map map) throws Exception {
        if(map == null){
            return new ResponseBean(500,"请选择需要导出的数据表！",null);
        }else if(map.get("tableName") == null){
            return new ResponseBean(500,"请选择需要导出的数据表！",null);
        }

        String tableName = map.get("tableName").toString();
        Object clazz;
        switch (tableName){
            case "d_city":clazz = JSONUtils.map2DTO(map,DCityExample.class);break;
            case "d_dept":clazz = JSONUtils.map2DTO(map, DDeptExample.class);break;
            case "d_dev_type":clazz = JSONUtils.map2DTO(map, DDevTypeExample.class);break;
            case "dev_baseinfo":clazz = JSONUtils.map2DTO(map, DevBaseinfoExample.class);break;
            case "dev_resume":clazz = JSONUtils.map2DTO(map, DevResumeExample.class);break;
            case "dev_software_version":clazz = JSONUtils.map2DTO(map,DevSoftwareVersionExample.class);break;
            case "d_fault_type":clazz = JSONUtils.map2DTO(map,DFaultTypeExample.class);break;
            case "d_knowledge_type":clazz = JSONUtils.map2DTO(map,DKnowledgeTypeExample.class);break;
            case "d_line":clazz = JSONUtils.map2DTO(map,DLineExample.class);break;
            case "d_majors":clazz = JSONUtils.map2DTO(map,DMajorsExample.class);break;
            case "d_material_type":clazz = JSONUtils.map2DTO(map,DMaterialTypeExample.class);break;
            case "d_station":clazz = JSONUtils.map2DTO(map,DStationExample.class);break;
            case "d_subsys":clazz = JSONUtils.map2DTO(map,DSubsysExample.class);break;
            case "knowledgerecords":clazz = JSONUtils.map2DTO(map,KnowledgerecordsExample.class);break;
            case "supplier_table":clazz = JSONUtils.map2DTO(map,SupplierTableExample.class);break;
            default:return new ResponseBean(500,"请输入正确的数据表",null);
        }

        String fileName = tableName;
        String sheetName = "第一个 sheet";
        ExcelUtil.writeExcel(response, null, fileName, sheetName,  (BaseRowModel)clazz.getClass().newInstance());
        return null;
    }

    @Override
    public ResponseBean importExcel(MultipartFile file, TableNameDTO tableNameDTO) throws Exception {
        String tableName = tableNameDTO.getTableName();
        List<Object> objectList;
        switch (tableName){
            case "d_city": objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dCityService.save((DCity)o);
                }            break;
            case "d_dept":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dDeptclassifyService.save((DDept) o);
                }            break;
            case "d_dev_type":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dDevTypeService.save((DDevType) o);
                }            break;
            case "dev_baseinfo":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dDevBaseinfoService.save((DDevice) o);
                }            break;
            case "dev_resume":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    devResumeService.save((DevResume) o);
                }            break;
            case "dev_software_version":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    devSoftwareVersionService.save((DevSoftwareVersion) o);
                }            break;
            case "d_fault_type":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dFaultclassifyService.save((DFaultType) o);
                }            break;
            case "d_knowledge_type":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dKnowledgeclassifyService.save((DKnowledgeType) o);
                }            break;
            case "d_line":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dLineService.save((DLine) o);
                }            break;
            case "d_majors":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dMajorsService.save((DMajors) o);
                }            break;
            case "d_material_type":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dMaterialclassifyService.save((DMaterialType) o);
                }            break;
            case "d_station":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dStationclassifyService.save((DStation) o);
                }            break;
            case "d_subsys":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    dSubsysService.save((DSubsys) o);
                }            break;
            case "knowledgerecords":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    knowledgerecordsService.save((Knowledgerecords) o);
                }            break;
            case "supplier_table":objectList = ExcelUtil.readExcel(file,new DCity());
                for (Object o : objectList) {
                    supplierTableService.save((SupplierTable) o);
                }            break;
            default:return new ResponseBean(500,"请输入正确的数据表",null);
        }
        return null;
    }

}

map转化为DTO的工具类

public static <T> T map2DTO(Map<String, Object> map, Class<T> clz) {
		T bean = null;
			try {
				bean = clz.newInstance();
				BeanUtils.populate(bean,map);
		}catch (Exception e){

			}
	 return bean;
	}

实体类的代码为

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DCityExample extends DCity{
    protected String orderByClause;

    protected boolean distinct;

    protected List<Criteria> oredCriteria;

    private Integer limit;

    private Long offset;
	
	......
}

//这里的get set方法要显示生成出来，只用@Data注解的话，会出问题

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.experimental.Accessors;
import org.springframework.boot.autoconfigure.AutoConfigurationPackage;

import java.io.Serializable;
import java.util.Date;

/**
 * d_city
 * @author 
 */
@Data
@Accessors(chain = true)
public class DCity extends BaseRowModel implements Serializable {

    private Integer id;

    /**
     * 城市名
     */
    @ExcelProperty(value = "城市",index = 0)
    private String cityName;

    /**
     * 城市ID,用户输入
     */
    @ExcelProperty(value = "城市ID",index = 1)
    private Integer cityId;

    /**
     * 创建时间
     */
    @ExcelProperty(value = "创建时间",index = 2)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createdTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public Integer getCityId() {
        return cityId;
    }

    public void setCityId(Integer cityId) {
        this.cityId = cityId;
    }

    public Date getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(Date createdTime) {
        this.createdTime = createdTime;
    }

    public Date getUpdatedTime() {
        return updatedTime;
    }

    public void setUpdatedTime(Date updatedTime) {
        this.updatedTime = updatedTime;
    }

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date updatedTime;

    private static final long serialVersionUID = 1L;


}

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.CellStyle;

/**
 * Excel基础模型
 * @author jipengfei
 */
public class BaseRowModel {

    /**
     * 每列样式
     */
    private Map<Integer,CellStyle> cellStyleMap = new HashMap<Integer,CellStyle>();

    public void addStyle(Integer row, CellStyle cellStyle){
        cellStyleMap.put(row,cellStyle);
    }

    public CellStyle getStyle(Integer row){
        return cellStyleMap.get(row);
    }

    public Map<Integer, CellStyle> getCellStyleMap() {
        return cellStyleMap;
    }

    public void setCellStyleMap(Map<Integer, CellStyle> cellStyleMap) {
        this.cellStyleMap = cellStyleMap;
    }
}

何不就现在发布了1 篇原创文章 · 获赞 0 · 访问量 30 私信关注

码农公寓

Service层

map转化为DTO的工具类

实体类的代码为

相关文章