Java 导出Excel

一、引入依赖

<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