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

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

一、easyExcel是什么

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

二、maven依赖

mysql

mysql-connector-java

5.1.47

com.alibaba

druid

1.1.16

com.alibaba

easyexcel

2.1.7

tk.mybatis

mapper

4.1.5

三、实体类

@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 {

}

六、1ChinaService

public interface ChinaService {

public Integer chinaCount();

public List 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 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 selectByCityPage = chinaService.selectByCityPage(page);

WriteSheet test = EasyExcel.writerSheet("第【" + page + "】批次").build();

// 写入

excelWriter.write(selectByCityPage, test);

}

// 关闭

excelWriter.finish();

System.out.println("导出成功！");

}

}

九、实现效果

本文地址：https://blog.csdn.net/qq_39074952/article/details/110000437

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！