springboot+mybatis excel导出

╰半夏微凉° 2023-08-17 15:32 107阅读 0赞

//controller page 
    
    import cn.asiic.labor.core.utils.Servlets;
    import cn.asiic.labor.table.entity.ProjMain;
    import cn.asiic.labor.table.entity.ProjMainInt;
    import cn.asiic.labor.table.service.ProjMainService;
    import cn.asiic.sdk.excel.ExcelWriter;
    import com.aspose.cells.SaveFormat;
    import com.aspose.cells.Workbook;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.UnsupportedEncodingException;
    import java.time.LocalDate;
    import java.util.List;
        @GetMapping("/excel")
        public void exprotExcel(HttpServletRequest request, HttpServletResponse response,ProjMainInt projMainInt) throws Exception {
            List<ProjMain> perms = projMainService.query(projMainInt);
            Workbook workbook = ExcelWriter.wirteData(perms, ProjMain.class);
            Servlets.setFileDownloadHeader(request, response, "物料主数据" + LocalDate.now() + ".xls");
            workbook.save(response.getOutputStream(), SaveFormat.EXCEL_97_TO_2003);
        }
    
    //service page 
    import cn.asiic.labor.core.utils.PageUtils;
    import cn.asiic.labor.table.entity.ProjMain;
    import cn.asiic.labor.table.entity.ProjMainInt;
    import cn.asiic.labor.table.repository.ProjMainRepository;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.util.List;
        @Transactional(readOnly = true)
        public List<ProjMain> query(ProjMainInt projMainInt){
            setAllSelectValue(projMainInt);
            List<ProjMain> list=projMainRepository.getProjMain(projMainInt);
            return list;
        }
    
    //repository page
    package cn.asiic.labor.table.repository;
    
    import cn.asiic.labor.table.entity.ProjMain;
    import cn.asiic.labor.table.entity.ProjMainInt;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    @Mapper
    public interface ProjMainRepository {
      List<ProjMain> getProjMain(ProjMainInt projMainInt);
    }
    
    
    //
    package cn.asiic.labor.table.entity;
    
    import cn.asiic.sdk.excel.annotation.ExcelField;
    import lombok.Data;
    
    @Data
    public class ProjMain {
        @ExcelField(title = "序号")
        private int id;
        @ExcelField(title = "项目编码")
        private String projcode;
        @ExcelField(title = "项目名称")
        private String projname;
        @ExcelField(title = "年度")
        private String projyear;
        private String protypecode;
        @ExcelField(title = "项目类别")
        private String protypename;
        @ExcelField(title = "作业内容")
        private String projworkcontent;
        @ExcelField(title = "定员人数")
        private String projpersoncount;
        @ExcelField(title = "项目总数")
        private String projmoney;
        private String proorgcode;
        @ExcelField(title = "用工单位")
        private String proorgname;
        private String prostatuecode;
        @ExcelField(title = "项目状态")
        private String prostatuename;
    
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getProjcode() {
            return projcode;
        }
    
        public void setProjcode(String projcode) {
            this.projcode = projcode;
        }
    
        public String getProjname() {
            return projname;
        }
    
        public void setProjname(String projname) {
            this.projname = projname;
        }
    
        public String getProjyear() {
            return projyear;
        }
    
        public void setProjyear(String projyear) {
            this.projyear = projyear;
        }
    
        public String getProtypecode() {
            return protypecode;
        }
    
        public void setProtypecode(String protypecode) {
            this.protypecode = protypecode;
        }
    
        public String getProtypename() {
            return protypename;
        }
    
        public void setProtypename(String protypename) {
            this.protypename = protypename;
        }
    
        public String getProjworkcontent() {
            return projworkcontent;
        }
    
        public void setProjworkcontent(String projworkcontent) {
            this.projworkcontent = projworkcontent;
        }
    
        public String getProjpersoncount() {
            return projpersoncount;
        }
    
        public void setProjpersoncount(String projpersoncount) {
            this.projpersoncount = projpersoncount;
        }
    
        public String getProjmoney() {
            return projmoney;
        }
    
        public void setProjmoney(String projmoney) {
            this.projmoney = projmoney;
        }
    
        public String getProorgcode() {
            return proorgcode;
        }
    
        public void setProorgcode(String proorgcode) {
            this.proorgcode = proorgcode;
        }
    
        public String getProorgname() {
            return proorgname;
        }
    
        public void setProorgname(String proorgname) {
            this.proorgname = proorgname;
        }
    
        public String getProstatuecode() {
            return prostatuecode;
        }
    
        public void setProstatuecode(String prostatuecode) {
            this.prostatuecode = prostatuecode;
        }
    
        public String getProstatuename() {
            return prostatuename;
        }
    
        public void setProstatuename(String prostatuename) {
            this.prostatuename = prostatuename;
        }
    }

// mybatis
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="cn.asiic.labor.table.repository.ProjMainRepository">
        <select id="getProjMain" resultType="cn.asiic.labor.table.entity.ProjMain">
            select p.ID as id,
            p.PROJ_CODE as projcode,
            p.PROJ_NAME as projname,
            p.PROJ_YEAR as projyear,
            p.PROJ_TYPE_CODE as protypecode,
            p.PROJ_TYPE_NAME as protypename,
            p.PROJ_WORK_CONTENT as projworkcontent,
            p.PROJ_PERSON_COUNT as projpersoncount,
            p.PROJ_MONEY as projmoney,
            p.PROJ_ORG_CODE as proorgcode,
            PROJ_ORG_NAME as proorgname,
            p.PROJ_STATUS_CODE as prostatuecode,
            p.PROJ_STATUS_NAME as prostatuename
            from proj_main as p
            where p.PROJ_YEAR like concat('%',#{year},'%')
            and p.PROJ_ORG_CODE like concat('%',#{userorg},'%')
            and p.PROJ_TYPE_CODE like concat('%',#{proclass},'%')
            and p.PROJ_STATUS_CODE like concat('%',#{prostatue},'%')
            and p.PROJ_CODE like concat('%',#{projcode},'%')
            and p.PROJ_NAME like concat('%', #{projname},'%')
        </select>
    </mapper>