一、引入依赖
<dependency>
<groupId>com.sargeraswang.util</groupId>
<artifactId>excel-util</artifactId>
<version>1.2.1</version>
</dependency>
二、数据库中数据
1.创建表
CREATE TABLE `t_user` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mobile` varchar(45) NOT NULL,
`mail` varchar(50) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2.导入数据
三、编写实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TUser implements Serializable {
@ExcelCell(index = 0,defaultValue = "用户ID")
private Integer userId;
@ExcelCell(index = 1,defaultValue = "用户名")
private String username;
@ExcelCell(index = 2,defaultValue = "性别")
private int sex;
@ExcelCell(index = 3,defaultValue = "电话号")
private String mobile;
@ExcelCell(index = 4,defaultValue = "邮箱")
private String mail;
@ExcelCell(index = 5,defaultValue = "创建时间")
private String createTime;
}
四、利用反射拿到TUser的属性
public List<Object[]> getFields(){
List<Object[]> fields = new ArrayList<Object[]>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(TUser.class.getDeclaredFields()));
for(Field field:tempFields){
if(field.isAnnotationPresent(ExcelCell.class)){
ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
if(excelCell != null){
field.setAccessible(true);
fields.add(new Object[]{field,excelCell});
}
}
}
return fields;
}
五、编写查询所有tuser代码
1.映射配置文件
<mapper namespace="com.hzz.mydemo.dao.TUserDao">
<insert id="insertTuser" parameterType="com.hzz.mydemo.entities.TUser" useGeneratedKeys="true" keyProperty="userId">
insert into t_user(username,sex,mobile,mail,create_time) values(#{username},#{sex},#{mobile},#{mail},#{createTime})
</insert>
<!-- <select id="queryAll" resultType="com.hzz.mydemo.entities.TUser">-->
<!-- select * from Blog where id = #{id}-->
<!-- </select>-->
<select id="queryAllUser" resultType="com.hzz.mydemo.entities.TUser">
select user_id,username,sex,mobile,mail,create_time from t_user
</select>
</mapper>
2.Dao层、service层和serviceImpl层
@Mapper
public interface TUserDao {
public List<TUser> queryAllUser();
public int insertTuser(TUser tuser);
}
public interface TUserService {
public List<TUser> queryAllUser();
public int insertUser(TUser tUser);
}
@Service
public class TUserServiceImpl implements TUserService {
@Autowired
private TUserDao tUserDao;
@Override
public List<TUser> queryAllUser() {
return tUserDao.queryAllUser();
}
@Override
public int insertUser(TUser tUser) {
return tUserDao.insertTuser(tUser);
}
}
六、导出数据代码
ExcelSheet<TUser> sheet = new ExcelSheet<>();
sheet.setSheetName("tuser");
List<TUser> tUsers = tUserService.queryAllUser();
sheet.setDataset(tUsers);
List<Object[]> fields = getFields();
Map<String, String> map1 = new LinkedHashMap<>();
if(CollectionUtils.isNotEmpty(fields)){
fields.stream().forEach(objects -> {
Field field = (Field) objects[0];
ExcelCell excelCells = (ExcelCell) objects[1];
map1.put(field.getName(), excelCells.defaultValue());
});
}
sheet.setHeaders(map1);
File f= new File("test2.xls");
OutputStream out = new FileOutputStream(f);
List<ExcelSheet<TUser>> sheetList = new ArrayList<>();
sheetList.add(sheet);
ExcelUtil.exportExcel(sheetList,out);
七、导出的Excel