pom文件依赖:
<!-- poi 相关 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<!-- mysql 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--mybatis plus-->
<dependency>
<!--包米豆-->
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!-- mybatis -->
<!--<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>-->
<!-- 连接池 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
实体类:
package com.bean.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.List;
import lombok.Data;
/**
* 人员信息表Me
* @TableName a_personnel_info
*/
@TableName(value ="a_personnel_info")
@Data
public class APersonnelInfo implements Serializable {
/**
*
*/
@TableId(type = IdType.AUTO)
private Long id;
/**
* 姓名
*/
private String name;
/**
* 性别
*/
private String gender;
/**
* 年龄
*/
private Integer age;
/**
* 地址
*/
private String address;
/**
* 手机号
*/
private String phone;
/**
* 唯一编码
*/
private String uuidnum;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
public APersonnelInfo() {
}
public APersonnelInfo(Long id, String name, String gender, Integer age, String address, String phone, String uuidnum) {
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
this.address = address;
this.phone = phone;
this.uuidnum = uuidnum;
}
}
mapper:
package com.bean.mapper;
import com.bean.domain.APersonnelInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* @author y1851
* @description 针对表【a_personnel_info(人员信息表Me)】的数据库操作Mapper
* @createDate 2024-03-11 19:35:17
* @Entity com.bean.domain.APersonnelInfo
*/
@Mapper
public interface APersonnelInfoMapper extends BaseMapper<APersonnelInfo> {
List<APersonnelInfo> selectAPersonnelInfoListThree();
}
mapper.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bean.mapper.APersonnelInfoMapper">
<resultMap id="BaseResultMap" type="com.bean.domain.APersonnelInfo">
<id property="id" column="id" jdbcType="BIGINT"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="gender" column="gender" jdbcType="VARCHAR"/>
<result property="age" column="age" jdbcType="INTEGER"/>
<result property="address" column="address" jdbcType="VARCHAR"/>
<result property="phone" column="phone" jdbcType="VARCHAR"/>
<result property="uuidnum" column="uuidNum" jdbcType="VARCHAR"/>
</resultMap>
<sql id="Base_Column_List">
id,name,gender,
age,address,phone,
uuidNum
</sql>
<select id="selectAPersonnelInfoListThree" resultType="com.bean.domain.APersonnelInfo">
SELECT * FROM a_personnel_info
</select>
</mapper>
application.yml:
server:
port: 1003
---
spring:
application:
name: ExcelService-service
---
spring:
redis:
host: localhost
port: 6379
datasource:
url: jdbc:mysql://localhost:3306/blockchain?serverTimezone=Asia/Shanghai&useTimezone=true
driver-class-name: com.mysql.jdbc.Driver
username: root
password: root
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
测试类数据:
package com.bean;
import com.bean.domain.APersonnelInfo;
import com.bean.mapper.APersonnelInfoMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
/**
* Unit test for simple App.
*/
@SpringBootTest(classes = AppExcelService.class)
@RunWith(SpringRunner.class)
public class AppTest {
@Autowired
APersonnelInfoMapper aPersonnelInfoMapper;
@Test
public void test1() throws IOException {
// 创建工作簿对象
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 创建工作表对象
SXSSFSheet sheet = workbook.createSheet("Sheet1");
// 写入表头
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("id");
header.createCell(1).setCellValue("姓名");
header.createCell(2).setCellValue("性别");
header.createCell(3).setCellValue("年龄");
header.createCell(4).setCellValue("地址");
header.createCell(5).setCellValue("手机号");
header.createCell(6).setCellValue("唯一编码");
List<APersonnelInfo> aPersonnelInfos = aPersonnelInfoMapper.selectAPersonnelInfoListThree();
for (int i = 0; i < aPersonnelInfos.size(); i++) {
Row row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(aPersonnelInfos.get(i).getId());
row.createCell(1).setCellValue(aPersonnelInfos.get(i).getName());
row.createCell(2).setCellValue(aPersonnelInfos.get(i).getGender());
row.createCell(3).setCellValue(aPersonnelInfos.get(i).getAge());
row.createCell(4).setCellValue(aPersonnelInfos.get(i).getAddress());
row.createCell(5).setCellValue(aPersonnelInfos.get(i).getPhone());
row.createCell(6).setCellValue(aPersonnelInfos.get(i).getUuidnum());
}
// 写入到文件中
FileOutputStream outputStream = new FileOutputStream("test.xlsx");
workbook.write(outputStream);
outputStream.close();
// 关闭工作簿对象
workbook.close();
}
}
成果: