SpringBoot实现数据库的读取导出到Excel表格（数据库查询Mybatis实现）

最新推荐文章于 2024-04-09 21:46:20 发布

LANG-ITTECHNOGY

最新推荐文章于 2024-04-09 21:46:20 发布

阅读量1.5k

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/weixin_43818651/article/details/103598245

版权

java 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Controller层

package com.southgis.imap.maintain.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.southgis.imap.maintain.service.sendMessage;

@RestController
public class ExcelController{

	@Autowired
	private sendMessage sendmessage;
	
	@RequestMapping("/Excel")
	public Object excelFind(HttpServletResponse response) {
		 XSSFWorkbook wb =this.sendmessage.show();
         String fileName = "Goods报表.xlsx";
         OutputStream outputStream =null;
         try {
             fileName = URLEncoder.encode(fileName,"UTF-8");
             //设置ContentType请求信息格式
             response.setContentType("application/vnd.ms-excel");
             response.setHeader("Content-disposition", "attachment;filename=" + fileName);
             outputStream = response.getOutputStream();
             wb.write(outputStream);
             outputStream.flush();
             outputStream.close();
         } catch (UnsupportedEncodingException e) {
             e.printStackTrace();
         } catch (IOException e) {
             e.printStackTrace();
         }
         return "success";
	}
}

Service层
（1）接口（sendMessage.java）

package com.southgis.imap.maintain.service;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public interface sendMessage {
	
	XSSFWorkbook show();
}

（2）实现类（SendMessageImpl.java）

package com.southgis.imap.maintain.service.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.southgis.imap.maintain.entity.SendMessage;
import com.southgis.imap.maintain.mapper.SendMessageMapper;
import com.southgis.imap.maintain.service.sendMessage;

@Service
@Transactional
public class SendMessageImpl implements sendMessage {

	@Autowired
	private SendMessageMapper sendMessageMapper;

	@Override
	public XSSFWorkbook show() {
		// 查出数据库数据（这里用Mybatis实现）
		List<SendMessage> list = sendMessageMapper.findAll();// 查出数据库数据
		XSSFWorkbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet("Goods");// 创建一张表
		Row titleRow = sheet.createRow(0);// 创建第一行，起始为0
		titleRow.createCell(0).setCellValue("smid");// 第一列
		titleRow.createCell(1).setCellValue("company");
		titleRow.createCell(2).setCellValue("org");
		titleRow.createCell(3).setCellValue("dest");
		titleRow.createCell(4).setCellValue("senddata");
		int cell = 1;
		for (SendMessage goods : list) {
			Row row = sheet.createRow(cell);// 从第二行开始保存数据
			row.createCell(0).setCellValue(goods.getSmid());
			row.createCell(1).setCellValue(goods.getCompany());// 将数据库的数据遍历出来
			row.createCell(2).setCellValue(goods.getOrg());
			row.createCell(3).setCellValue(goods.getDest());
			row.createCell(4).setCellValue(goods.getSenddata());
			cell++;
		}
		return wb;
	}
}

数据库数据读取（Mybatis实现）
（1）接口（SendMessageMapper.java）

package com.southgis.imap.maintain.mapper;

import java.util.Date;
import java.util.List;

import com.southgis.imap.maintain.entity.SendMessage;

public interface SendMessageMapper {	
	//查询数据库所有字段
	List<SendMessage> findAll();
}

（2）Mybatis（SendMessageMapper.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.southgis.imap.maintain.mapper.SendMessageMapper">
	<select id="findAll" resultType="SendMessage">
		select * from SendMessage
	</select>
</mapper>

对应实体类（SendMessage.java）

package com.southgis.imap.maintain.entity;


import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import org.hibernate.annotations.GenericGenerator;
import org.springframework.format.annotation.DateTimeFormat;

import com.fasterxml.jackson.annotation.JsonFormat;

import lombok.Data;
@Data
@Entity(name="sendmessage")
public class SendMessage {
	
	//主键
	@Id
	@GeneratedValue(generator="id")
	@GenericGenerator(name="id",strategy="native")
	private Integer id;
	
	private String smid;
	
	//所属公司
	private String company;
	
	private String org;
	//是否发送成功
	private String dest;
	
	private String senddata;
	
	//创建时间
	@DateTimeFormat(pattern="yyyy-MM-dd")
	@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
	private Date insertDate;
	
	//是否发送成功?success or fail
	private Integer status;
	
	private String msgGroup;
	
	private String reportStatus;
	
	private String errorCode;
	
	@DateTimeFormat(pattern="yyyy-MM-dd")
	private Date receiveDate;
	
	@DateTimeFormat(pattern="yyyy-MM-dd")
	private Date sendDate;
}

主类（Application.java）

package com.southgis.imap.maintain;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.southgis.imap.maintain.mapper")
public class Application {
	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
		System.out.println("(♥◠‿◠)ﾉﾞ  SouthProject启动成功   ლ(´ڡ`ლ)ﾞ  \\n");
	}
}

相应pom依赖（pom.xml）

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>southtechnogy</groupId>
	<artifactId>imap-southtechnogy2</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.0.RELEASE</version>
	</parent>


	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<fork>true</fork>
					<addResources>true</addResources>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<configuration>
					<testFailureIgnore>true</testFailureIgnore>
					<skip>false</skip>
				</configuration>
			</plugin>

			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>

			<!-- 拷贝一份配置文件到 jar包 外面 -->
			<plugin>
				<artifactId>maven-resources-plugin</artifactId>
				<executions>
					<execution>
						<id>copy-yml</id>
						<phase>validate</phase>
						<goals>
							<goal>copy-resources</goal>
						</goals>
						<configuration>
							<outputDirectory>${project.build.directory}</outputDirectory>
							<resources>
								<resource>
									<directory>src/main/resources</directory>
									<include>application.yml</include>
								</resource>
							</resources>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>


	<dependencies>
	
	 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
	<dependency>
	        <groupId>com.alibaba</groupId>
	        <artifactId>fastjson</artifactId>
	        <version>1.2.7</version>
	    </dependency>
	    <dependency> 
	        <groupId>net.sourceforge.jexcelapi</groupId>
	        <artifactId>jxl</artifactId>
	        <version>2.6.10</version>
	    </dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<version>2.0.2.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>5.0.5.RELEASE</version>
			<scope>compile</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<!-- 重新部署 -->
		<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> 
			<optional>true</optional> </dependency> -->

		<!-- Spring Boot缓存支持启动器 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-cache</artifactId>
		</dependency>

		<dependency>
			<groupId>net.sf.ehcache</groupId>
			<artifactId>ehcache</artifactId>
		</dependency>

		<!-- springBoot的启动器 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<!--druid数据库连接池 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.9</version>
		</dependency>

		<!-- 数据库连接 驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
		</dependency>

		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>
		
		<!-- apache 常用工具 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
		</dependency>

		<!-- lombok 简化代码工具 -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<scope>provided</scope>
		</dependency>

		<!-- fastJSON -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.32</version>
		</dependency>

		<!-- 方便的 java 的工具类 -->
		<dependency>
			<groupId>com.google.guava</groupId>
			<artifactId>guava</artifactId>
		</dependency>

		<!-- AOP -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>

			</dependency> -->
		<!-- WEB -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<exclusions>
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-tomcat</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>

		<!-- h2数据库测试 -->
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> 
			<version>3.11</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> 
			<artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency> 
			<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> 
			<version>3.11</version> </dependency> -->
		<!-- https://mvnrepository.com/artifact/org.json/json <dependency> <groupId>org.apache.commons</groupId> 
			<artifactId>commons-io</artifactId> <version>1.3.2</version> </dependency> -->
		<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> 
			<optional>true</optional> </dependency> -->
	</dependencies>

	<!-- 管理所有子工程 spring cloud 系列依赖的版本 -->
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.cloud</groupId>
				<artifactId>spring-cloud-dependencies</artifactId>
				<version>Finchley.M9</version> 
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>

	<repositories>
		<repository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/libs-milestone</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
	</repositories>
	<distributionManagement>
		<repository>
			<id>releases</id>
			<name>infra realease</name>
			<url>http://172.16.50.180:8085/nexus/content/repositories/releases/</url>
		</repository>
		<snapshotRepository>
			<id>snapshots</id>
			<name>infra snapshotRepository</name>
			<url>http://112.94.224.249:8085/nexus/content/repositories/snapshots/</url>
		</snapshotRepository>
	</distributionManagement>
</project>

相关配置文件（application.yml）

spring:
  datasource:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/build?useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 112233
      initialize: true
  init-db: true
mybatis:
  type-aliases-package: com.southgis.imap.maintain.entity
  configuration:
    map-underscore-to-camel-case: true