java实现easyExcel下载数据库数据到本地

java实现easyExcel下载数据库数据到本地

一、easyExcel是什么

	EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

二、maven依赖

 <!--        mysql驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!--        连接池-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.16</version>
    </dependency>
    <!--  easyExcel   -->
     <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.7</version>
    </dependency>
    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper</artifactId>
        <version>4.1.5</version>
    </dependency>

三、实体类

	@Data
	@AllArgsConstructor
	@NoArgsConstructor
	public class China {
	    @ExcelProperty(index = 0,value = "城市id")
	    private Integer id;
	    @ExcelProperty(index = 1,value = "城市名称")
	    private String name;
	    @ExcelProperty(index = 2,value = "城市代码")
	    private Integer pid;
	}

四、application.yml文件

server:
  port: 80

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://你的mysql数据库地址:3306/china?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: 用户名
    password: 密码
mybatis:
  mapper-locations: classpath:com/mapper/*Mapper.xml
  type-aliases-package: com.entity

五、mapper层ChinaDao

public interface ChinaDao extends Mapper<China> {

}

六、1ChinaService

public interface ChinaService {
    public Integer chinaCount();

    public List<China> selectByCityPage(Integer no);
}

六、2业务逻辑ChinaServiceImpl

@Service
@Transactional
public class ChinaServiceImpl implements ChinaService {
@Resource
private ChinaDao chinaDao;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public Integer chinaCount() {
        return chinaDao.selectCount(new China());
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<China> selectByCityPage(Integer no) {
        Integer offSet = (no - 1) * 1000 + 1;
        RowBounds rowBounds = new RowBounds(offSet, 1000);
        return chinaDao.selectByRowBounds(new China(), rowBounds);
    }
}

七、测试SpringBootTest

@org.springframework.boot.test.context.SpringBootTest(classes = SpringBootMain.class)
@RunWith(SpringRunner.class)
public class SpringBootTest {

}

八、Test

public class Test extends SpringBootTest {
    @Resource
    private ChinaDao chinaDao;

    @Resource
    private ChinaService chinaService;

    @org.junit.Test
    public void test() {
        China china = new China();
        int i = chinaDao.selectCount(china);
        System.out.println(i);
    }

    @org.junit.Test
    public void Test2() {
        // 导出路径
        String path = "C:\\Users\\MI\\Desktop\\city.xls";
        // 数据总条数
        Integer count = chinaService.chinaCount();
        // 导出地址  页头格式
        ExcelWriter excelWriter = EasyExcel.write(path, China.class).build();
        // 判断当前分多少页
        Integer pageSize = count % 1000 == 0 ? count / 1000 : count / 1000 + 1;
        for (Integer page = 1; page <= pageSize; page++) {
            // 数据分页返回  1000页
            List<China> selectByCityPage = chinaService.selectByCityPage(page);
            WriteSheet test = EasyExcel.writerSheet("第【" + page + "】批次").build();
            // 写入
            excelWriter.write(selectByCityPage, test);
        }
        // 关闭
        excelWriter.finish();
        System.out.println("导出成功！");
    }
}

九、实现效果