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
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!