源码地址
介绍
July Excel一款开源的Excel操作工具，支持Excel的导入与导出，后期支持Excel深度定制操作。
使用方式
第一步： 在pom.xml中引入July Doc依赖：
<dependency>
<groupId>com.github.zengxueqi-yu</groupId>
<artifactId>july-excel</artifactId>
<version>1.0.1-RELEASE</version>
</dependency>
第二步： 创建用来接受excel数据的VO类：
package com.company.mission.vo.user;
import com.july.excel.entity.ExcelField;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* 用户信息
* @author zengxueqi
* @program mission
* @since 2020-07-14 11:56
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class UserExcelVo implements Serializable {
private static final long serialVersionUID = 2836364506833118111L;
@ExcelField("用户名")
private String userName;
@ExcelField("性别")
private String sex;
@ExcelField("手机号")
private String mobile;
@ExcelField("公司名称")
private String companyName;
@ExcelField("入职时间")
private String createTime;
@ExcelField("头像")
private String avatar;
}
第三步： 控制器使用Excel工具类实现Excel操作：
/**
* excel测试控制器
* @author missye
* @program july-bpm
* @since 2020-05-07 16:57
**/
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
/**
* 导出excel数据
* @param response
* @return void
* @author missye
* @since 2020/5/9
*/
@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
ExcelData excelData = ExcelData.builder()
//需要导出的excel数据
.excelData(exportExcel())
//sheet名称
.sheetName("测试数据")
//文件名称
.fileName("测试数据")
//字体大小
.fontSize(12)
//列宽
.cellWidth(25)
//大标题
.labelName("教师请假信息数据")
.build();
long startTime = System.currentTimeMillis();
com.july.excel.excel.ExcelFactory.exportExcelData(excelData, TeacherLeaveRecordExcel.class, response);
log.info("===> time:" + (System.currentTimeMillis() - startTime) + " ms!");
}
/**
* 模拟需要导出的excel数据
* @param
* @return java.util.List<com.july.bpm.vo.node.EduTeacherLeaveRecordExcel1>
* @author missye
* @since 2020/5/9
*/
public List<TeacherLeaveRecordExcel> exportExcel() {
List<TeacherLeaveRecordExcel> list = new ArrayList<>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 0; i < 10; i++) {
TeacherLeaveRecordExcel excel = TeacherLeaveRecordExcel.builder()
.schoolName("鹭岛小学" + i)
.teacherName("曾雪琪" + i)
.activeTime(simpleDateFormat.format(new Date()))
.img("#34;)
.build();
list.add(excel);
}
return list;
}
/**
* 导入excel数据
* @param file
* @return void
* @author missye
* @since 2020/5/9
*/
@GetMapping("/importExcel")
public void importExcel(MultipartFile file) throws Exception {
ExcelReadData excelTitleRowNum = ExcelReadData.builder()
.sheetNum(0)
.rowNum(1)
.build();
ExcelReadData excelReadData = ExcelReadData.builder()
.sheetNum(0)
.rowNum(2)
.build();
ExcelData excelData = ExcelData.builder()
//导入测试excel这个sheet
.sheetName("测试数据")
//时间格式化
.expectDateFormatStr("yyyy-MM-dd")
//sheet标题所在行数(读取sheet下标为0，行数为1的标题)
.excelTitleRowNum(Arrays.asList(excelTitleRowNum))
//sheet解析数据开始行数(从sheet下标为0，行数为2开始解析数据)
.excelReadDataList(Arrays.asList(excelReadData))
.build();
List<TeacherLeaveRecordExcel> excel1s = ExcelFactory.importExcelData(file, TeacherLeaveRecordExcel.class, excelData);
log.info("excel data===>" + JSON.toJSONString(excel1s));
}
}
1.导出的excel数据，如下图：
2.导入excel解析的数据
{
"code": 0,
"msg": "业务处理成功",
"content": [
{
"userName": "曾雪琪0",
"sex": "女0",
"mobile": "147082222220",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪1",
"sex": "女1",
"mobile": "147082222221",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪2",
"sex": "女2",
"mobile": "147082222222",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪3",
"sex": "女3",
"mobile": "147082222223",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪4",
"sex": "女4",
"mobile": "147082222224",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪5",
"sex": "女5",
"mobile": "147082222225",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪6",
"sex": "女6",
"mobile": "147082222226",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪7",
"sex": "女7",
"mobile": "147082222227",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪8",
"sex": "女8",
"mobile": "147082222228",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
},
{
"userName": "曾雪琪9",
"sex": "女9",
"mobile": "147082222229",
"companyName": "重庆丽笙网络科技有限公司",
"createTime": "2020-07-14",
"avatar": " "
}
]
}
因为图片没有做上传操作，所以avatar为空。
以上就是Java Excel的使用说明，只需要三步即可实现Excel的导入与导出！
