环境说明:spring+springmvc+springJdbc 数据库为,mysql8.0
1、表字段类型:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`roleId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`company_name` varchar(255) DEFAULT NULL,
`company_code` varchar(255) DEFAULT NULL,
`region_code` varchar(255) DEFAULT NULL,
`first_cp_code` varchar(255) DEFAULT NULL,
`second_cp_code` varchar(255) DEFAULT NULL,
`first_industry_code` varchar(255) DEFAULT NULL,
PRIMARY KEY (`roleId`)
) E
2、xls文件数据(fileTest1.xls):
roleId | username | password | company_name | company_code | region_code | first_cp_code | second_cp_code | first_industry_code | |
101 | 张三 | 123456 | 华智 | 1492 | test1 | test2 | test3 | test4 | |
102 | 张三 | 123456 | 华智 | 1492 | test1 | test2 | test3 | test4 | |
103 | 张三 | 123456 | 华智 | 1492 | test1 | test2 | test3 | test4 | |
104 | 张三 | 123456 | 华智 | 1492 | test1 | test2 | test3 | test4 | |
105 | 张三 | 123456 | 华智 | 1492 | test1 | test2 | test3 | test4 | |
3、项目结构图:
3.1配置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>com.spring.test</groupId>
<artifactId>fileTest</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<spring.version>5.1.5.RELEASE</spring.version>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!--读取excel文件所需要的包-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-dao -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-dao</artifactId>
<version>2.0.8</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
</dependencies>
</project>
3.2配置DataSourceConfig.java类:
package fileTest.config;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
public class DataSourceConfig {
@Bean
public DataSource createDataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/filetest1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false");
dataSource.setUsername("root");
dataSource.setPassword("dsb520");
return dataSource;
}
@Bean
public JdbcOperations createJdbcTemplate(DataSource dataSource){
return new JdbcTemplate(dataSource);
}
}
3.3配置UserWebAppInitializer.java类
package fileTest.config;
import javax.servlet.MultipartConfigElement;
import javax.servlet.ServletRegistration.Dynamic;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;
public class UserWebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer{
@Override
protected Class<?>[] getRootConfigClasses() {
return new Class<?>[] {RootConfig.class};
}
@Override
protected Class<?>[] getServletConfigClasses() {
return new Class<?>[] {WebConfig.class};
}
@Override
protected String[] getServletMappings() {
return new String[] {"/"};
}
@Override
protected void customizeRegistration(Dynamic registration) {
registration.setMultipartConfig(
// 必须在tomcat下新建此目录
new MultipartConfigElement("/tmp/uploads", 2097152, 4194304, 0));
}
}
3.3配置WebConfig.java类:
package fileTest.config;
import java.io.IOException;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.support.StandardServletMultipartResolver;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
@Configuration
@EnableWebMvc
@ComponentScan("fileTest")
public class WebConfig implements WebMvcConfigurer {
@Bean
public ViewResolver viewResolver() {
InternalResourceViewResolver resolver = new InternalResourceViewResolver();
resolver.setPrefix("/WEB-INF/jsp/");
resolver.setSuffix(".jsp");
resolver.setExposeContextBeansAsAttributes(true);
return resolver;
}
@Override
public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
configurer.enable();
}
@Bean
public MultipartResolver multipartResolver() throws IOException {
return new StandardServletMultipartResolver();
}
}
3.4配置RootConfig.java类:
package fileTest.config;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.ComponentScan.Filter;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.FilterType;
import org.springframework.context.annotation.Import;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
@Configuration
@Import(DataSourceConfig.class)
@ComponentScan(basePackages={"fileTest"},
excludeFilters={
@Filter(type=FilterType.ANNOTATION, value=EnableWebMvc.class)
})
public class RootConfig {
}
3.5配置User.java类:
package fileTest.entity;
import java.io.Serializable;
public class User implements Serializable{
private static final long serialVersionUID = 1L;
private String roleId;
private String username;
private String password;
private String company_name;
private String company_code;
private String region_code;
private String first_cp_code;
private String second_cp_code;
private String first_industry_code;
public User() {
}
public User(String roleId, String username, String password, String company_name, String company_code,
String region_code, String first_cp_code, String second_cp_code, String first_industry_code) {
this.roleId = roleId;
this.username = username;
this.password = password;
this.company_name = company_name;
this.company_code = company_code;
this.region_code = region_code;
this.first_cp_code = first_cp_code;
this.second_cp_code = second_cp_code;
this.first_industry_code = first_industry_code;
}
public String getRoleId() {
return roleId;
}
public void setRoleId(String roleId) {
this.roleId = roleId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getCompany_name() {
return company_name;
}
public void setCompany_name(String company_name) {
this.company_name = company_name;
}
public String getCompany_code() {
return company_code;
}
public void setCompany_code(String company_code) {
this.company_code = company_code;
}
public String getRegion_code() {
return region_code;
}
public void setRegion_code(String region_code) {
this.region_code = region_code;
}
public String getFirst_cp_code() {
return first_cp_code;
}
public void setFirst_cp_code(String first_cp_code) {
this.first_cp_code = first_cp_code;
}
public String getSecond_cp_code() {
return second_cp_code;
}
public void setSecond_cp_code(String second_cp_code) {
this.second_cp_code = second_cp_code;
}
public String getFirst_industry_code() {
return first_industry_code;
}
public void setFirst_industry_code(String first_industry_code) {
this.first_industry_code = first_industry_code;
}
}
3.6配置IUser.java类:
package fileTest.dao;
import org.springframework.stereotype.Repository;
import fileTest.entity.User;
@Repository
public interface IUser {
int saveUser(User user);
}
3.7配置UserImpl.java类:
package fileTest.dao.impl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.stereotype.Repository;
import fileTest.dao.IUser;
import fileTest.entity.User;
@Repository
public class UserImpl implements IUser{
private JdbcOperations jdbcTemplate;
@Autowired
public UserImpl(JdbcOperations jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public int saveUser(User user) {
int row = 0;
String insertQuery = "insert into user(roleId,username,password,company_name,company_code,region_code,first_cp_code,second_cp_code,first_industry_code) values(?,?,?,?,?,?,?,?,?)";
row = jdbcTemplate.update(insertQuery,
user.getRoleId(), user.getUsername(), user.getPassword(),
user.getCompany_name(), user.getCompany_code() , user.getRegion_code(),
user.getFirst_cp_code(), user.getSecond_cp_code(), user.getFirst_industry_code());
return row;
}
}
3.8配置UserController.java类:
package fileTest.controller;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
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.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import fileTest.dao.impl.UserImpl;
import fileTest.entity.User;
@Controller
@RequestMapping("/")
public class UserController {
@Autowired
private UserImpl userImpl;
//文件所在位置
private static final String URL = "E:\\";
//坐标位置
private static String COORDINATE = "A2";
public UserImpl getUserImpl() {
return userImpl;
}
public void setUserImpl(UserImpl userImpl) {
this.userImpl = userImpl;
}
@RequestMapping("/index")
public String index() {
return "index";
}
@RequestMapping(value="/saveUser", method = RequestMethod.POST)
public String saveUser(@RequestParam("file") MultipartFile file) throws EncryptedDocumentException, InvalidFormatException, IOException {
List<User> list = readFile(URL + file.getOriginalFilename());
System.out.println("共有: " + list.size() + "条数据");
int row = 0;
for (User user : list) {
row += userImpl.saveUser(user);
}
System.out.println("成功插入:" + row + "条数据");
return "index";
}
private List<User> readFile(String fileName) throws EncryptedDocumentException, InvalidFormatException, IOException{
List<User> list = new ArrayList<User>();
Workbook workbook = WorkbookFactory.create(new File(fileName));
System.out.println("sheets" + workbook.getNumberOfSheets());
//读取xlsx的第一页
Sheet sheet = workbook.getSheetAt(0);
COORDINATE = COORDINATE.toUpperCase(); // 把坐标字母转为大写
String str_x = COORDINATE.replaceAll("[^A-Z]", ""); // 获取字母(横坐标)
String str_y = COORDINATE.replaceAll("[^0-9]", ""); // 获取数字(纵坐标)
int x = letterToNumber(str_x); // 将x列字母坐标转为数字横坐标
int y = Integer.parseInt(str_y); // 得到行 纵坐标
// for (int i=1;i<=sheet.getLastRowNum();i++) {//从第二行开始
for (int i=y-1;i<=sheet.getLastRowNum();i++) {//从第y行开始 下标从0开始,所以要减1
Row row=sheet.getRow(i);//得到当前行数
String []str=new String[row.getLastCellNum()];
// for (int j=0;j<row.getLastCellNum();j++) { //从第一列开始
for (int j=x-1;j<row.getLastCellNum();j++) { //从第x列开始 下标从0开始,所以要减1
Cell cell=row.getCell(j);//得到单元格
cell.setCellType(CellType.STRING); //把单元格设置为string类型
str[j]=cell.getStringCellValue().trim();
System.out.print(str[j]+" ");
}
System.out.println();
//把xlsx中的数据封装的user对象中
User user=new User();
user.setRoleId(str[0]);
user.setUsername(str[1]);
user.setPassword(str[2]);
user.setCompany_name(str[3]);
user.setCompany_code(str[4]);
user.setRegion_code(str[5]);
user.setFirst_cp_code(str[6]);
user.setSecond_cp_code(str[7]);
user.setFirst_industry_code(str[8]);
list.add(user); //把user对象插入到list中
}
return list;
}
/**
* 将字母坐标转为数字坐标
* @param letter 字母坐标
* @return
*/
private int letterToNumber(String letter) {
int length = letter.length();
int num = 0;
int number = 0;
for(int i = 0; i < length; i++) {
char ch = letter.charAt(length - i - 1);
num = (int)(ch - 'A' + 1) ;
num *= Math.pow(26, i);
number += num;
}
return number;
}
}
3.9配置index.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="saveUser" method="post" enctype="multipart/form-data">
<input type="file" id="file" name="file" />
<button type="submit" id="btn">提交</button>
</form>
</html>
3.10配置web.xml:
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd" >
<web-app>
<display-name>Archetype Created Web Application</display-name>
</web-app>
4、启动tomcat:访问localhost:8080/fileTest/index