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：本人较懒，不喜欢多解释～🤪
网友评论