springboot的excle文件导入

excle文件导入

文件导入导出几乎每个项目都用到，最近在网上找了点资料整理一下：
1、第一步引入依赖：

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.10.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>

第二步：准备工作，建好相关实体类（根据自己的需求创建）：

**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.dto
 * @date:2020/8/3
 */
public class ResultEntity {

	private String state;

	private String message;

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}
}

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

/**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.dto
 * @date:2020/7/27
 */
@ApiModel
public class User {

	@ApiModelProperty(value = "用户id")
	private Integer id;
	@ApiModelProperty(value = "用户名")
	private String usename;
	@ApiModelProperty(value = "用户地址")
	private String address;
	@ApiModelProperty(value = "年龄")
	private Integer age;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return usename;
	}

	public void setUsername(String username) {
		this.usename = username;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}
}

第三步：创建controller层代码实现

import com.example.demo.dto.ResultEntity;
import com.example.demo.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.controller
 * @date:2020/8/3
 */
@RestController
@Api(tags = "文件导入导出相关接口")
@RequestMapping("/export")
public class ExportController {

	@Autowired
	private UserService userService;

	@PostMapping("/")
	@ApiOperation("文件导入")
	@ApiImplicitParams({
		@ApiImplicitParam(name = "MultipartFile", value = "file"),
		@ApiImplicitParam(name = "HttpServletRequest", value = "request")
	}
	)
	public ResultEntity InputExcel(@RequestParam("file") MultipartFile file){
		ResultEntity result=new ResultEntity();
		try {
			//校验文件是否存在
			if (file == null) {
				result.setMessage("请选择文件");
			}
			//获取文件流
			InputStream inputStream = file.getInputStream();
			List<Map<String,String>> data = parseExcle(inputStream);
			String message = userService.saveUser(data);
			result.setMessage(message);
		}catch (Exception e){
			e.getMessage();
			result.setMessage("导入失败");
		}
		return result;
	}

	//解析文件input流
	public List<Map<String,String>>parseExcle(InputStream inputStream)throws Exception{
		List<Map<String,String>>data=new ArrayList<>();
		//获取excel的工作簿
		HSSFWorkbook workbook=new HSSFWorkbook(inputStream);
        //获取excel工作簿第一个工作表
		HSSFSheet hssfSheet = workbook.getSheetAt(0);
        //获取excel的第一行序号
		int firstRowNum = hssfSheet.getFirstRowNum();
		//获取excle的最后一行序号
		int lastRowNum = hssfSheet.getLastRowNum();
        //获取表头行
		HSSFRow titleRow = hssfSheet.getRow(firstRowNum);
		for (int i=firstRowNum+1;i<lastRowNum+1;i++){
			HSSFRow row = hssfSheet.getRow(i);//获取第i行数据
			Map map = getExcleRowData(row,titleRow);//获取第i行数据
			data.add(map);
		}

		return data;
	}

    //获取excle中数据内容
	public Map<String,String>getExcleRowData(HSSFRow row,HSSFRow titleRow){
		Map<String,String> map=new HashMap<>();
       //获取第一列序号
		short firstCellNum = row.getFirstCellNum();
		//获取最后一列序号
		short lastCellNum = row.getLastCellNum();
		for (int i=firstCellNum;i<lastCellNum;i++){
            //获取标题
			String title = titleRow.getCell(i).getStringCellValue();
			//获取当前单元格
			HSSFCell cell = row.getCell(i);
			//当单元格是数字格式时，需要把它的cell type转成String，否则会出错
			if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			}
			//获取当前单元格的值
			String value=cell.getStringCellValue();
            map.put(title,value);
		}

		return map;
	}

service层：

import com.example.demo.dto.User;
import java.util.List;
import java.util.Map;

/**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.service
 * @date:2020/7/27
 */

public interface UserService {

	User searchById(Integer userId);

	public String saveUser(List<Map<String,String>> data);
}

import com.example.demo.dao.UserDao;
import com.example.demo.dto.User;
import com.example.demo.service.UserService;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.service.impl
 * @date:2020/7/27
 */
@Service
public class UserServiceImpl implements UserService {

	@Autowired
	private UserDao usrDao;

	@Override
	public User searchById(Integer userId) {
		try {
			User user = usrDao.searchById();
			return user;
		}catch (Exception e){

			e.getMessage();
		}

		return new User();
	}
	private static   String message="导入成功";
	@Override
	public String saveUser(List<Map<String,String>> data) {

			List<User> userList = new ArrayList<>();
			data.forEach(userMap -> {
				User user = new User();
				user.setUsername(userMap.get("usename"));
				user.setAddress(userMap.get("address"));
				user.setAge(Integer.parseInt(userMap.get("age")));
				userList.add(user);
			});
			userList.forEach(user -> {

				try {
				usrDao.saveUser(user);
				}catch (Exception e){
					e.getMessage();
					message="导入失败";
				}
			});


      return message;

	}
}

dao；

/**
 * @author: create by kyc
 * @version: v1.0
 * @description: com.example.demo.dao
 * @date:2020/7/27
 */

@Repository
public interface UserDao {

	User searchById()throws Exception;

	void saveUser(User user)throws Exception;
}

mapper文件

<?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.example.demo.dao.UserDao">

  <resultMap type="com.example.demo.dto.User" id="userMap">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="usename" jdbcType="VARCHAR" property="usename" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="age" jdbcType="INTEGER" property="age" />
  </resultMap>

  <select id="searchById" resultMap="userMap">
        select * from user
    </select>

  <insert id="saveUser"  parameterType="User" >
        insert into user
         (
                usename,
                address,
                age
            )
        values
            (
                #{usename},
                #{address},
                #{age}
            )

    </insert>

</mapper>

这样就大功告成了，小伙伴可以自己测试一下