簡(jiǎn)單呀打、快速的導(dǎo)入導(dǎo)出Excel
一肪虎、安裝依賴
推薦使用最新版本鉴分，可通過文章末尾官方文檔鏈接跳轉(zhuǎn)查看
<dependency>
<groupId>cn.gjing</groupId>
<artifactId>tools-excel</artifactId>
<version>2021.12.3</version>
</dependency>
二哮幢、Excel導(dǎo)出
1、單表頭
定義Excel映射實(shí)體, @Data是lombok的注解
/**
* @author Gjing
**/
@Data
@Excel("單級(jí)表頭")
public class SingleHead {
@ExcelField("姓名")
private String userName;
@ExcelField(value = "年齡", format = "0")
private Integer userAge;
@ExcelField("性別")
private Gender gender;
@ExcelField("愛好")
private String favorite;
}
/**
* @author Gjing
**/
@RestController
public class UserController {
@GetMapping("/test_export")
@ApiOperation("導(dǎo)出一級(jí)表頭")
public void testExport(HttpServletResponse response) {
//指定映射的實(shí)體為剛剛定義的
ExcelFactory.createWriter(SingleHead.class, response)
.write(null)
.flush();
}
}
single
2志珍、多級(jí)表頭
數(shù)組中的每個(gè)值代表著一級(jí)表頭
/**
* @author Gjing
**/
@Data
@Excel("多級(jí)表頭")
public class MultiHead {
@ExcelField({"用戶名","用戶名"})
private String userName;
@ExcelField({"年齡","年齡"})
private Integer age;
@ExcelField({"形態(tài)","身高"})
private BigDecimal height;
@ExcelField({"形態(tài)","體重"})
private BigDecimal weight;
}
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("多級(jí)表頭")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(MultiHead.class, response)
//需要在write前激活多級(jí)表頭橙垢，否則不會(huì)自動(dòng)合并
.multiHead(true)
.write(null)
.flush();
}
}
multi
3、帶大標(biāo)題
大標(biāo)題的起始行是你要插入的sheet中最后一條數(shù)據(jù)的下一行碴裙，如果sheet中沒有數(shù)據(jù)钢悲，就是第一行。你可以配置大標(biāo)題占用的行數(shù)和起始單元格下標(biāo)(默認(rèn)第一個(gè)單元格)和結(jié)束單元格下標(biāo)(默認(rèn)跟隨表頭的數(shù)量)
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test")
@ApiOperation("含大標(biāo)題")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(SingleHead.class, response)
//大標(biāo)題占用兩行
.writeTitle(new BigTitle("我是大標(biāo)題"))
.write(null)
.flush();
}
}
title
4舔株、下拉框
單元格增加下拉框
- 注解方式
/**
* @author Gjing
**/
@Data
@Excel("下拉框?qū)С?)
public class SingleHead {
@ExcelField("性別")
@ExcelDropdownBox(combobox = {"男", "女"})
private Gender gender;
@ExcelField("愛好")
private String favorite;
}
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("帶下拉框")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(SingleHead.class, response)
//需要在write前激活校驗(yàn)
.valid(true)
.write(null)
.flush();
}
}
- 通過方法設(shè)置普通下拉框的選項(xiàng)
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("帶下拉框")
public void testExport(HttpServletResponse response) {
Map<String, String[]> genderMap = new HashMap<>(8);
//key為實(shí)體類的字段名莺琳，使用方法進(jìn)行設(shè)置時(shí)，實(shí)體字段的@ExcelDropdownBox注解不在需要指定combobox
//如果指定了也會(huì)去覆蓋注解中的值
genderMap.put("gender", new String[]{"男", "女"});
ExcelFactory.createWriter(SingleHead.class, response)
.valid(true)
.write(null, genderMap)
.flush();
}
}
下拉框
5载慈、時(shí)間校驗(yàn)
導(dǎo)出時(shí)給列表頭下方的單元格增加時(shí)間校驗(yàn)
/**
* @author Gjing
**/
@Data
@Excel
public class SingleHead {
@ExcelField("姓名")
private String userName;
@ExcelField(value = "年齡", format = "0")
private Integer userAge;
@ExcelField(value = "生日", format = "yyyy-MM-dd")
@ExcelDateValid(expr1 = "2000-01-01", operatorType = LESS_OR_EQUAL, errorContent = "出生日期不能超過2000年")
private Date birthday;
}
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("帶時(shí)間校驗(yàn)")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(SingleHead.class, response)
//需要在write前激活校驗(yàn)
.valid(true)
.write(null)
.flush();
}
}
時(shí)間校驗(yàn)
6惭等、數(shù)字、文本校驗(yàn)
導(dǎo)出時(shí)給列表頭下方的單元格增加數(shù)值校驗(yàn)办铡〈亲觯可以對(duì)數(shù)字的大小，文本的長(zhǎng)度進(jìn)行校驗(yàn)
/**
* @author Gjing
**/
@Data
@Excel
public class SingleHead {
@ExcelField("姓名")
//對(duì)輸入的名稱字?jǐn)?shù)進(jìn)行校驗(yàn)寡具，字?jǐn)?shù)限制小于4
@ExcelNumericValid(validType = TEXT_LENGTH,operatorType = LESS_THAN,expr1 = "4",errorContent = "姓名字?jǐn)?shù)小于4")
private String userName;
}
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("帶數(shù)值校驗(yàn)")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(SingleHead .class, response)
//需要在write前激活校驗(yàn)
.valid(true)
.write(null)
.flush();
}
}
數(shù)值校驗(yàn)
7秤茅、重復(fù)校驗(yàn)
導(dǎo)出時(shí)給表頭下方增加數(shù)據(jù)重復(fù)校驗(yàn)
/**
* @author Gjing
**/
@Data
@Excel
public class SingleHead {
@ExcelField("身份證號(hào)")
@ExcelRepeatValid
private String idCard;
}
/**
* @author Gjing
**/
@RestController
public class TestController {
@GetMapping("/test_export")
@ApiOperation("帶數(shù)值校驗(yàn)")
public void testExport(HttpServletResponse response) {
ExcelFactory.createWriter(SingleHead .class, response)
//需要在write前激活校驗(yàn)
.valid(true)
.write(null)
.flush();
}
}
數(shù)據(jù)重復(fù)
8、數(shù)據(jù)轉(zhuǎn)換
導(dǎo)出時(shí)對(duì)數(shù)據(jù)進(jìn)行加工或者添加默認(rèn)值童叠，支持注解方式和接口方式
- 注解方式
/**
* @author Gjing
**/
@Data
@Excel
public class SingleHead {
@ExcelField("姓名")
private String userName;
@ExcelField(value = "年齡", format = "0")
//對(duì)每個(gè)人的年齡乘以10
@ExcelDataConvert(expr1 = "#userAge * 10")
private Integer userAge;
}
- 接口方式
/**
* @author Gjing
**/
public class MyDataConvert implements DataConvert<SingleHead> {
@Override
public Object toEntityAttribute(Object o, Field field) {
return null;
}
@Override
public Object toExcelAttribute(SingleHead singleHead, Object value, Field field) {
return (int) value * 10;
}
}
實(shí)現(xiàn)接口后需要在你需要轉(zhuǎn)換的字段上指定轉(zhuǎn)換器
/**
* @author Gjing
**/
@Data
@Excel
public class SingleHead {
@ExcelField("姓名")
private String userName;
@ExcelField(value = "年齡", format = "0", convert = MyDataConvert.class)
private Integer userAge;
}
導(dǎo)出方法調(diào)用最后一定要使用flush()方法進(jìn)行數(shù)據(jù)刷新到Excel文件中
三框喳、導(dǎo)入
導(dǎo)入的實(shí)體類皆采用導(dǎo)出的實(shí)體類
1、單表頭
/**
* @author Gjing
**/
@RestController
public class TestController {
@Resource
private UserService userService;
@PostMapping("/user_import")
@ApiOperation("導(dǎo)入單表頭")
public void userImport(MultipartFile file) throws IOException {
ExcelFactory.createReader(file, SingleHead.class)
//在read()方法前通過訂閱方法增加一個(gè)結(jié)果監(jiān)聽器厦坛，該監(jiān)聽器會(huì)在每一次read()結(jié)束之后觸發(fā)
.subscribe(e -> this.userService.saveUsers(e))
.read()
.finish();
}
}
2五垮、多級(jí)表頭
前文有提到多級(jí)表頭時(shí)，最后一級(jí)為實(shí)際表頭杜秸，所以要在導(dǎo)入時(shí)指定實(shí)際表頭開始下標(biāo)放仗，由于導(dǎo)出的模板映射實(shí)體設(shè)置兩級(jí)表頭，因此這里的實(shí)際表頭為下標(biāo)為1(Excel行和列下標(biāo)都是默認(rèn)0開始的)
/**
* @author Gjing
**/
@RestController
public class TestController {
@Resource
private UserService userService;
@PostMapping("/user_import")
@ApiOperation("導(dǎo)入單表頭")
public void userImport(MultipartFile file) throws IOException {
ExcelFactory.createReader(file, SingleHead.class)
//在read()方法前通過訂閱方法增加一個(gè)結(jié)果監(jiān)聽器撬碟，該監(jiān)聽器會(huì)在每一次read()結(jié)束之后觸發(fā)
//如果Excel中數(shù)據(jù)量太大诞挨，不建議使用結(jié)果監(jiān)聽器莉撇，會(huì)造成生成了過多的映射實(shí)體對(duì)象造成內(nèi)存溢出
.subscribe(e -> this.userService.saveUsers(e))
.read(1)
.finish();
}
}
在導(dǎo)入調(diào)用結(jié)束后，一定要在最后調(diào)用finish()方法對(duì)流進(jìn)行關(guān)閉
Demo地址：excel-demo
注解參數(shù)說明與更多用法可查看：官方文檔
