欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-17 19:06:46
java实现easyExcel下载数据库数据到本地一、easyExcel是什么EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。二、maven依赖 mysql mysql-connecto...

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>

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

三、实体类

	@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("导出成功!");
    }
}

九、实现效果

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

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