SpringBoot导出数据为Excel

Excel导入请参考：SpringBoot读取Excel并存入数据库

• 引入依赖
  这里我只是贴出了关键性的依赖～

        <!-- https://mvnrepository.com/artifact/com.jcraft/jsch -->
        <dependency>
            <groupId>com.jcraft</groupId>
            <artifactId>jsch</artifactId>
            <version>0.1.55</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>

• MyBatisPlusConfig

@Configuration
public class MyBatisPlusConfig {
    /**
     * mybatis-plus分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        return paginationInterceptor;
    }
}

• Swagger2

@Configuration
@EnableSwagger2
public class Swagger2 {

    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.booot.excel.controller"))
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                //设置文档的标题
                .title("APi")
                // 设置文档的描述
                .description("接口测试")
                // 设置文档的版本信息-> 1.0.0 Version information
                .version("1.0")
                // 设置文档的License信息->1.3 License information
                .termsOfServiceUrl("www.756316064@qq.com")
                .build();
    }
}

• ProfessionController

/**
 * (Profession)表控制层
 *
 * @author makejava
 * @since 2020-04-20 20:28:55
 */
@RestController
@Api("专业")
@RequestMapping("pro")
public class ProfessionController {
    /**
     * 服务对象
     */
    @Resource
    private ProfessionService professionService;

    /**
     * @Description: 导出
     * @params: [response, pageNo, pageSize]
     * @return: void
     * @Date: 2020/3/5 10:11 AM
     */
    @ApiOperation(value = "excel导出")
    @GetMapping("/export")
    public void exportSign(HttpServletResponse response, @RequestParam("page") Integer page,
                           @RequestParam("limit") Integer limit ) {
        WritableWorkbook workbook = null;
        try {
            //导出的文件名
            String fileName = "专业信息-" + System.currentTimeMillis() + ".xls";
            //从数据库中查询出来的数据
            Page<Profession> professionPage = new Page<>(page,limit);
            Page<Profession> professionIPage = this.professionService.page(professionPage);
            List<Profession> professions = professionIPage.getRecords();

            response.reset();
            // vnd.ms-excel
            response.setContentType("application/x-xls");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook = Workbook.createWorkbook(response.getOutputStream());
            //创建sheet
            WritableSheet profession = workbook.createSheet("专业信息", 0);
            profession.setColumnView(0, 20);
            profession.setColumnView(1, 20);
            profession.setColumnView(2, 20);

            profession.setRowView(0, 450, false);

            //设置标题
            WritableCellFormat titleCellFormat = setTitleCellFormat(true);
            profession.addCell(new Label(0, 0, "专业名称", titleCellFormat));
            profession.addCell(new Label(1, 0, "学院", titleCellFormat));
            profession.addCell(new Label(2, 0, "学校", titleCellFormat));

            //存放数据
            WritableCellFormat contentCellFormat = setContentCellFormat();
            for (int i = 0; i < professions.size(); i++) {
                profession.addCell(new Label(0, i + 1, professions.get(i).getProfessionName(), contentCellFormat));
                profession.addCell(new Label(1, i + 1, professions.get(i).getApartment(), contentCellFormat));
                profession.addCell(new Label(2, i + 1, professions.get(i).getSchool(), contentCellFormat));
            }
            response.flushBuffer();
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //如果判断workbook是否为空，不为空->关闭链接
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 设置导出表格的标题栏单元格样式
     * @param hasBorder
     * @return
     * @throws WriteException
     */
    private WritableCellFormat setTitleCellFormat(boolean hasBorder) throws WriteException {
        WritableFont titleFont = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                Colour.BLACK);

        WritableCellFormat titleCellFormat = new WritableCellFormat();
        titleCellFormat.setFont(titleFont);
        titleCellFormat.setAlignment(jxl.format.Alignment.CENTRE);
        titleCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        if (hasBorder) {
            titleCellFormat.setBorder(Border.BOTTOM, BorderLineStyle.DOUBLE, Colour.BLACK);
        }
        return titleCellFormat;
    }

    /**
     * 设置导出表格的内容单元格样式
     * @return
     * @throws WriteException
     */
    private WritableCellFormat setContentCellFormat() throws WriteException {
        WritableCellFormat contentCellFormat = new WritableCellFormat();
        contentCellFormat.setFont(new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false));
        contentCellFormat.setAlignment(jxl.format.Alignment.CENTRE);
        contentCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        contentCellFormat.setWrap(true);
        return contentCellFormat;
    }

}

• ProfessionDao

@Mapper
public interface ProfessionDao extends BaseMapper<Profession> {
}

• Profession

@Data
@TableName("profession")
public class Profession implements Serializable {
    private static final long serialVersionUID = 217915017239376333L;

    @TableId(value = "profession_id",type = IdType.AUTO)
    private Integer professionId;

    @TableField("profession_name")
    private String professionName;

    @TableField("apartment")
    private String apartment;

    @TableField("school")
    private String school;

    @TableField("create_time")
    private Date createTime;
}

• ProfessionService

public interface ProfessionService extends IService<Profession> {

}

@Service("professionService")
public class ProfessionServiceImpl extends ServiceImpl<ProfessionDao,Profession> implements ProfessionService {
    @Resource
    private ProfessionDao professionDao;
}

• ExcelApplication

@SpringBootApplication
@MapperScan("com.booot.excel.dao")
@EnableSwagger2
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class, args);
    }

}

• ProfessionDao.xml

<?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="com.booot.excel.dao.ProfessionDao">

    <resultMap type="com.booot.excel.entity.Profession" id="ProfessionMap">
        <result property="professionId" column="profession_id" jdbcType="INTEGER"/>
        <result property="professionName" column="profession_name" jdbcType="VARCHAR"/>
        <result property="apartment" column="apartment" jdbcType="VARCHAR"/>
        <result property="school" column="school" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>
</mapper>

• application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/appdemo?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 100MB
mybatis-plus:
  mapper-locations: classpath:/mapper/*Dao.xml
  typeAliasesPackage: com.booot.excel.entity
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazyLoadingEnabled: true
    multipleResultSetsEnabled: true

测试

• 浏览器输入：http://localhost:8080/pro/export?page=1&limit=10
  文件就会下载到你的浏览器中～

PS：本人较懒，不喜欢多解释～🤪