Wechat search public number: science and technology cat, share programming, software, technology.
Easypoi function as the name easy, the main function is easy, so that a person who has not seen contact with POI can easily write Excel export,Excel template export,Excel import,Word template export, through simple annotations and template language (familiar expression grammar), complete the previous complex writing method
Introduction of depend on
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.41.</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2. 0</version>
</dependency>
Copy the codeSpring Boot configuration file application.properties
Spring Boot core configuration file
Server.port =8888Server.servlet. context-path=/easypoi # database driver Spring.datasource. Driver -class-name= com.mysql.cj.jdbc.driver # dbc.datasource. Url = JDBC :mysql://localhost:3306/easy_poi? useUnicode=true&characterEncoding=utf-8# database username spring. The datasource. The username = # root database user password spring. The datasource. Password =123456# the location of the mapping file mybatis - plus. Mapper - locations = classpath: com/springboot/dao/* dao.xml # Type alias mybatis-plus.type-aliases-package=com.springboot.entityCopy the codeSQL database
Database file, directly imported into the MySQL 5.7 version
-- MySQL dump 10.13 Distrib 5.7.29, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: easy_poi
-- ------------------------------------------------------
- Server version 5.7.29
/ *! 40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/ *! 40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/ *! 40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/ *! 40101 SET NAMES utf8 */;
/ *! 40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/ *! 40103 SET TIME_ZONE='+00:00' */;
/ *! 40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/ *! 40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/ *! 40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/ *! 40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
/ *! 40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`username` varchar(255) DEFAULT NULL COMMENT 'Username',
`email` varchar(255) DEFAULT NULL COMMENT 'email',
`phone` varchar(255) DEFAULT NULL COMMENT 'Mobile number',
`qq` varchar(255) DEFAULT NULL COMMENT 'qq number',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/ *! 40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/ *! 40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1.'wang Ming'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:22:48'.'the 2020-11-22 00:22:48'), (2.'Sun Hong'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:22:48'.'the 2020-11-22 00:22:48'), (3.'zhang fei'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:22:48'.'the 2020-11-22 00:22:48'), (4.'bill'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:22:48'.'the 2020-11-22 00:22:48'), (5.'wang Ming'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:50:04'.'the 2020-11-22 00:50:04'), (6.'Sun Hong'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:50:04'.'the 2020-11-22 00:50:04'), (7.'zhang fei'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:50:04'.'the 2020-11-22 00:50:04'), (8.'bill'.'[email protected]'.'123456'.'123456'.'the 2020-11-22 00:50:04'.'the 2020-11-22 00:50:04');
/ *! 40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/ *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/ *! 40101 SET SQL_MODE=@OLD_SQL_MODE */;
/ *! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/ *! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/ *! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/ *! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/ *! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/ *! 40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-11-22 0:50:16
Copy the codeDao
interface
@Mapper
@Repository
public interface UserDao extends BaseMapper<User> {
List<UserExportVO> select(@Param("startTime") Date startTime, @Param("endTime") Date endTime);
void insertList(@Param("userExportVO") List<UserExportVO> userExportVOS);
}
Copy the codemapper
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.UserDao">
<resultMap id="userVOMap" type="com.springboot.entity.export.UserExportVO">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="qq" column="qq"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</resultMap>
<select id="select" resultMap="userVOMap">
select id,username,email,phone,qq,create_time,update_time from user
<where>
<if test="startTime ! = null and endTime ! = null ">
create_time between #{startTime} and #{endTime}
</if>
</where>
</select>
<insert id="insertList">
insert into user(username,email,phone,qq) values
<foreach collection="userExportVO" item="item" separator=",">
(#{item.username},#{item.email},#{item.phone},#{item.qq})
</foreach>
</insert>
</mapper>
Copy the codeService
interface
public interface UserService {
void userExport(HttpServletResponse response, Date startTime, Date endTime);
void userImport(MultipartFile file);
}
Copy the codeImpl
@Service
public class UserServiceImpl extends ServiceImpl<UserDao.User> implements UserService {
@Autowired
private UserDao userDao;
@Override
public void userExport(HttpServletResponse response, Date startTime, Date endTime) {
List<UserExportVO> userExportVOS = userDao.select(startTime, endTime);
String title = "User Information Sheet";
if(startTime ! =null&& endTime ! =null) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String startTimeStr = format.format(startTime);
String endTimeStr = format.format(endTime);
title = startTimeStr + "To" + endTimeStr + "User Information Sheet";
}
Map<String, Object> oneSheet = ExcelUtil.createOneSheet(title, title, UserExportVO.class, userExportVOS);
List<Map<String, Object>> list = Lists.newArrayList();
list.add(oneSheet);
Workbook workbook = ExcelUtil.mutiSheet(list);
// Output files through the output stream
OutputStream os = null;
try {
response.setContentType("application/msexcel; charset=utf-8");
response.setHeader("Content-Disposition"."attachment; filename="
+ new String(title.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
response.setCharacterEncoding("UTF-8");
os = response.getOutputStream();
workbook.write(os);
} catch (IOException e) {
// Print exception
log.error("Export exception:", e);
} finally {
// Close the resource
if(os ! =null) {
try {
os.close();
} catch(IOException e) { e.printStackTrace(); }}}}@Override
public void userImport(MultipartFile file) {
List<UserExportVO> userExportVOS = Lists.newArrayList();
try {
ImportParams importParams = new ImportParams();
importParams.setHeadRows(2);
// importParams.setTitleRows(0);
userExportVOS = ExcelImportUtil.importExcel(file.getInputStream(), UserExportVO.class, importParams);
} catch(Exception e) { e.printStackTrace(); } userDao.insertList(userExportVOS); }}Copy the codeController
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response, @RequestParam(value = "startTime", required = false) Date startTime,
@RequestParam(value = "endTime", required = false) Date endTime) {
userService.userExport(response, startTime, endTime);
}
@GetMapping("/importExcel")
public void importExcel(@RequestParam("file") MultipartFile file) { userService.userImport(file); }}Copy the codeEntity class
User
@Data
public class User implements Serializable {
private static final long serialVersionUID = 1348665906085238496L;
private Long id;
private String username;
private String email;
private String phone;
private String qq;
private Date createTime;
private Date updateTime;
}
Copy the codeUserExportVO
An entity class used to map data when exporting
@Data
@ExcelTarget("userExportVO")
public class UserExportVO {
@excel (name = "id ")
private Long id;
@excel (name = "username ", isImportField = "true")
private String username;
@excel (name = "email ", isImportField = "true")
private String email;
@excel (name = "phone number ", isImportField = "true")
private String phone;
@excel (name = "qq number ", isImportField = "true")
private String qq;
@excel (name = "create time ", databaseFormat =" YYYY-MM-DD HH: MM :ss", format = "YYYY-MM-DD HH: MM :ss", width = 40)
private Date createTime;
@excel (name = "update time ", databaseFormat =" YYYY-MM-DD HH: MM :ss", format = "YYYY-MM-DD HH: MM :ss", width = 40)
private Date updateTime;
}
Copy the codeUtility class ExcelUtil
public class ExcelUtil {
/** * Create workbook, * fill Excel content with maplist * return workbook * * further use can write into streams,e.g. * FileOutputStream fos = new FileOutputStream(file); * workbook.write(fos); * /
public static Workbook mutiSheet(List<Map<String, Object>> mapListList) {
return ExcelExportUtil.exportExcel(mapListList, ExcelType.XSSF);
}
public static Map<String, Object> createOneSheet(ExportParams exportParams, Class
clazz, List
data) {
Map<String, Object> map = new HashMap<>();
map.put("title", exportParams);
map.put("entity", clazz);
map.put("data", data);
return map;
}
/** * Create a table and fill it * return map for workbook use */
public static Map<String, Object> createOneSheet(String sheetName, String title, Class
clazz, List
data) {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
returncreateOneSheet(exportParams, clazz, data); }}Copy the codetest
The database
The exported excel
This article source address: github.com/jonssonyan/…