SpringBoot实现Excel表格数据读取并将数据添加到相应数据库（Mybatis实现）

最新推荐文章于 2024-08-05 16:32:19 发布

LANG-ITTECHNOGY

最新推荐文章于 2024-08-05 16:32:19 发布

阅读量2.7k

点赞数 3

分类专栏： java

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

版权

java 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Controller层


package com.xue.controller;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.xue.service.UserService;


@Controller
public class UserController {
	
	@Autowired
	private UserService userService;
	
	/**
	 * 页面
	 */
	@RequestMapping("/index")
	public String index(){
		return "index";
	}
	
	/**
	 * 导入excel
	 */
	@RequestMapping("/import")
	@ResponseBody
	public String excelImport(@RequestParam(value="filename")MultipartFile file,HttpSession session){
		int result = 0;
		try {
			result = userService.addUser(file);
		} catch (Exception e) {
			e.printStackTrace();
		}	
		if(result > 0){
			return "excel文件数据导入成功！";
		}else{
			return "excel文件数据导入成功！";
		}	
	}
}

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


package com.xue.service; 
import org.springframework.web.multipart.MultipartFile;

public interface UserService {

	/*传入文件内容*/
	public int addUser(MultipartFile file) throws Exception;

}

(2)实现类（UserServiceImpl.java）

package com.xue.service.Impl;
 
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.xue.dao.UserMapper;
import com.xue.entity.SendMessage;
import com.xue.service.UserService;

@Service
public class UserServiceImpl implements UserService {
	
	@Autowired
	private UserMapper userMapper;
 
	@Override
	public int addUser(MultipartFile file) throws Exception{
		
		int result = 0;
		//存放excel表中所有SendMessage细腻
		List<SendMessage> userList = new ArrayList<>();
		/**
		 * 
		 * 判断文件版本
		 */
		String fileName = file.getOriginalFilename();
		String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
		
		InputStream ins = file.getInputStream();
		Workbook wb = null;
		if(suffix.equals("xlsx")){
			wb = new XSSFWorkbook(ins);
		}else{
			wb = new HSSFWorkbook(ins);
		}
		/**
		 * 获取excel表单
		 */
		Sheet sheet = wb.getSheetAt(0);
		
		
		/**
		 * line = 1:从表的第二行开始获取记录
		 * 
		 */
		if(null != sheet){
			
			for(int line = 1; line <= sheet.getLastRowNum();line++){
				
				SendMessage sendmessage = new SendMessage();
				
				Row row = sheet.getRow(line);
				
				if(null == row){
					continue;
				}
				
				//将从Excel表中读取的内容转成String格式（如果Excel表格中纯数字，不执行一下操作，会报错，建议一下操作全部执行。）
				row.getCell(0).setCellType(CellType.STRING);
				row.getCell(1).setCellType(CellType.STRING);
				row.getCell(2).setCellType(CellType.STRING);
				row.getCell(3).setCellType(CellType.STRING);
				row.getCell(4).setCellType(CellType.STRING);
				row.getCell(5).setCellType(CellType.STRING);
				row.getCell(6).setCellType(CellType.STRING);
				row.getCell(7).setCellType(CellType.STRING);
				row.getCell(8).setCellType(CellType.STRING);
				row.getCell(9).setCellType(CellType.STRING);
				row.getCell(10).setCellType(CellType.STRING);
				row.getCell(11).setCellType(CellType.STRING);
				
				/**
				 * 获取第一个单元格的内容
				 */
				String smid = row.getCell(0).getStringCellValue();
				/**
				 * 获取第二个单元格的内容
				 */
				String company = row.getCell(1).getStringCellValue();
				String org = row.getCell(2).getStringCellValue();
				String dest = row.getCell(3).getStringCellValue();
				String senddata = row.getCell(4).getStringCellValue();
				
				//Date类型
				String insert_date = row.getCell(5).getStringCellValue();
				Date date = null;
				DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				date = format.parse(insert_date);
				
				//Integer类型
				Integer i = null;
				String status = row.getCell(6).getStringCellValue();
				if(status.equals("") || status.equals(null)) {
					i = null;
				}else {
					i = Integer.parseInt(status);
				}
				
				String msg_group = row.getCell(7).getStringCellValue();
				String report_status = row.getCell(8).getStringCellValue();
				String error_code = row.getCell(9).getStringCellValue();
				
				String receive_date = row.getCell(10).getStringCellValue();
				Date date2 = null;
				DateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
				date2 = format2.parse(receive_date);
						
				String send_date = row.getCell(11).getStringCellValue();
				Date date3 = null;
				DateFormat format3 = new SimpleDateFormat("yyyy-MM-dd");
				date3 = format3.parse(receive_date);
				
				sendmessage.setSmid(smid);
				sendmessage.setCompany(company);
				sendmessage.setOrg(org);;
				sendmessage.setDest(dest);
				sendmessage.setSenddata(senddata);
				sendmessage.setInsertDate(date);
				sendmessage.setStatus(i);
				sendmessage.setMsgGroup(msg_group);
				sendmessage.setReportStatus(report_status);
				sendmessage.setErrorCode(error_code);
				sendmessage.setReceiveDate(date2);
				sendmessage.setSendDate(date3);
				userList.add(sendmessage);
	
			}
			for(SendMessage sendmessage:userList){
				result = userMapper.addUser(sendmessage);
			}
		} 
		return result;
	}
}

数据库数据添加（Mybatis实现）
（1）接口（UserMapper.java）

package com.xue.dao;

import com.xue.entity.SendMessage;

public interface UserMapper {
	
	 int addUser(SendMessage sendmessage);;
}

（2）Mybatis（UserMapper.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.xue.dao.UserMapper">

	 <insert id="addUser" parameterType="SendMessage" >
	      insert into sg_sendsms
	      	(smid,company,org,dest,senddata,insert_date,status,msg_group,report_status,error_code,receive_date,send_date) 
	      values
	      	(#{smid},#{company},#{org},#{dest},#{senddata},
	      	#{insertDate},#{status},#{msgGroup},#{reportStatus},#{errorCode},#{receiveDate},#{sendDate})
	  </insert>

</mapper>

实体类（SendMessage.java）

package com.xue.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="sg_sendsms")
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;
}

主类（SpringbootImportExcelApplication.java）

package com.xue;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
@MapperScan("com.xue.dao")
public class SpringbootImportExcelApplication { 
	public static void main(String[] args) {
		SpringApplication.run(SpringbootImportExcelApplication.class, args);
	}

}

前端页面代码（index.html）

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8"/>
<title>Insert title here</title>
<script src="js/jquery-3.4.1.min.js"></script>
</head>
<body>
	<h1>导入EXCEL</h1>
	<br/>
    <form>
    	<button type="button" class="btn-primary">导入</button>
        <input class="form-input" type="file" name="filename"></input>
        <a id="result"></a>
    </form>
    
    <script>
    	$(".btn-primary").click(function(){
    		debugger;
    		var fileobj = $(".form-input")[0].files[0];
    		var form = new FormData();
            form.append("filename", fileobj);
    		$.ajax({
    			type:'post',
    			url:'/import',
    			data:form,
    			processData: false,
                contentType: false, 
    			success:function(data){
    				console.log(data);
    				$("#result").text("添加成功")
    			}
    		})
    	})
    </script>
</body>
</html>

相关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>Import-Excel</groupId>
	<artifactId>Import-Excel</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>


		<!-- 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>

		<!-- apollo 客户端 <dependency> <groupId>com.ctrip.framework.apollo</groupId> 
			<artifactId>apollo-client</artifactId> <version>1.0.0</version> </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>

		<!-- 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>
	</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