在springboot中整合POI实现Excel表的导入导出

在springboot中整合POI实现Excel表的导入导出

前端代码

index.jsp

<%@ page language=“java” contentType=“text/html; charset=UTF-8” pageEncoding=“UTF-8” %>
<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>

<%@include file="common.jsp" %>

    <thead class="Table cell">
        <td>ID</td>
        <td>用户名</td>
        <td>密码</td>
    </thead>

    <tbody>
        <c:forEach var="user" items="${user}">
            <tr class="success">
                <td>${user.uid}</td>
                <td>${user.username}</td>
                <td>${user.password}</td>
            </tr>
        </c:forEach>>
    </tbody>

</table>
<a href="/export"><button type="button" class="btn btn-primary">导出</button></a>

<form class="form-horizontal" id="form_table" action="/import" enctype="multipart/form-data" method="post">
    <br/>
    <br/>
    <button type="submit" class="btn btn-primary">导入</button>
    <input class="form-input" type="file" name="filename"></input>
</form>

common.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var ="poiLuqz" value="${pageContext.request.contextPath}"/>
<link rel="stylesheet" href="${poiLuqz}/static/css/bootstrap.css"/>
<link rel="stylesheet" href="${poiLuqz}/static/bootstrapTable/bootstrap.min.css"/>
<link rel="stylesheet" href="${poiLuqz}/static/css/bootstrap-theme.css"/>
<link rel="stylesheet" href="${poiLuqz}/static/bootstrapTable/bootstrap-table.min.css"/>
<script src="${poiLuqz}/static/js/npm.js"></script>
<script src="${poiLuqz}/static/js/jquery.min.js"></script>
<script src="${poiLuqz}/static/js/jquery-1.8.2.js"></script>
<script src="${poiLuqz}/static/bootstrapTable/bootstrap-table.min.js"></script>
<script src="${poiLuqz}/static/bootstrapTable/bootstrap-table-zh-CN.min.js"></script>

异常处理类

package com.poi.testpoi.common;

public class MyException extends RuntimeException {

    private static final long serialVersionUID = 1L;

    /**
     * 错误编码
     */
    private String errorCode;

    /**
     * 消息是否为属性文件中的Key
     */
    private boolean propertiesKey = true;

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     */
    public MyException(String message)
    {
        super(message);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message)
    {
        this(errorCode, message, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message, Throwable cause)
    {
        this(errorCode, message, cause, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     * @param propertiesKey
     *            消息是否为属性文件中的Key
     */
    public MyException(String errorCode, String message, boolean propertiesKey)
    {
        super(message);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public MyException(String errorCode, String message, Throwable cause, boolean propertiesKey)
    {
        super(message, cause);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     * @param cause
     *            根异常类（可以存入任何异常）
     */
    public MyException(String message, Throwable cause)
    {
        super(message, cause);
    }

    public String getErrorCode()
    {
        return errorCode;
    }

    public void setErrorCode(String errorCode)
    {
        this.errorCode = errorCode;
    }

    public boolean isPropertiesKey()
    {
        return propertiesKey;
    }

    public void setPropertiesKey(boolean propertiesKey)
    {
        this.propertiesKey = propertiesKey;
    }

}

comtroller

package com.poi.testpoi.controller;

import com.poi.testpoi.pojo.User;
import com.poi.testpoi.service.UserService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
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 javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

@Controller
public class IndexController {

	@Autowired
	private UserService userService;


	@RequestMapping("/index")
	public String showUser(Model model) {
		List<User> users = userService.selectUsers();
		model.addAttribute("user", users);
		return "index";
	}


	@RequestMapping(value = "/export")
	@ResponseBody
	public void export(HttpServletResponse response) throws IOException {
		List<User> users = userService.selectUsers();

		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet = wb.createSheet("获取excel测试表格");

		HSSFRow row = null;

		row = sheet.createRow(0);//创建第一个单元格
		row.setHeight((short) (26.25 * 20));
		row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值

		/*为标题设计空间
		* firstRow从第1行开始
		* lastRow从第0行结束
		*
		*从第1个单元格开始
		* 从第3个单元格结束
		*/
		CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 2);
		sheet.addMergedRegion(rowRegion);

		/*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
		sheet.addMergedRegion(columnRegion);*/


		/*
		* 动态获取数据库列 sql语句 select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='user' and table_schema='test'
		* 第一个table_name 表名字
		* 第二个table_name 数据库名称
		* */
		row = sheet.createRow(1);
		row.setHeight((short) (22.50 * 20));//设置行高
		row.createCell(0).setCellValue("用户Id");//为第一个单元格设值
		row.createCell(1).setCellValue("用户名");//为第二个单元格设值
		row.createCell(2).setCellValue("用户密码");//为第三个单元格设值

		for (int i = 0; i < users.size(); i++) {
			row = sheet.createRow(i + 2);
			User user = users.get(i);
			row.createCell(0).setCellValue(user.getUid());
			row.createCell(1).setCellValue(user.getUsername());
			row.createCell(2).setCellValue(user.getPassword());
		}
		sheet.setDefaultRowHeight((short) (16.5 * 20));
		//列宽自适应
		for (int i = 0; i <= 13; i++) {
			sheet.autoSizeColumn(i);
		}

		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		OutputStream os = response.getOutputStream();
		//告诉浏览器这个文件的名字和类型，attachment：作为附件下载；inline：直接打开
		response.setHeader("Content-disposition", "attachment;filename=user.xls");//默认Excel名称
		wb.write(os);
		os.flush();
		os.close();


	}


	@RequestMapping(value = "/import")
	public String exImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session) {

		boolean a = false;

		String fileName = file.getOriginalFilename();

		try {
			a = userService.batchImport(fileName, file);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "redirect:index";
	}

	/**
	 * 获取样式
	 *
	 * @param hssfWorkbook
	 * @param styleNum
	 * @return
	 */
	public HSSFCellStyle getStyle(HSSFWorkbook hssfWorkbook, Integer styleNum) {
		HSSFCellStyle style = hssfWorkbook.createCellStyle();
		style.setBorderRight(BorderStyle.THIN);//右边框
		style.setBorderBottom(BorderStyle.THIN);//下边框

		HSSFFont font = hssfWorkbook.createFont();
		font.setFontName("微软雅黑");//设置字体为微软雅黑

		HSSFPalette palette = hssfWorkbook.getCustomPalette();//拿到palette颜色板,可以根据需要设置颜色
		switch (styleNum) {
			case (0): {//HorizontalAlignment
				style.setAlignment(HorizontalAlignment.CENTER_SELECTION);//跨列居中
				font.setBold(true);//粗体
				font.setFontHeightInPoints((short) 14);//字体大小
				style.setFont(font);
				palette.setColorAtIndex(HSSFColor.BLUE.index, (byte) 184, (byte) 204, (byte) 228);//替换颜色板中的颜色
				style.setFillForegroundColor(HSSFColor.BLUE.index);
				style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			}
			break;
			case (1): {
				font.setBold(true);//粗体
				font.setFontHeightInPoints((short) 11);//字体大小
				style.setFont(font);
			}
			break;
			case (2): {
				font.setFontHeightInPoints((short) 10);
				style.setFont(font);
			}
			break;
			case (3): {
				style.setFont(font);

				palette.setColorAtIndex(HSSFColor.GREEN.index, (byte) 0, (byte) 32, (byte) 96);//替换颜色板中的颜色
				style.setFillForegroundColor(HSSFColor.GREEN.index);
				style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			}
			break;
		}

		return style;
	}


}

Mapper

package com.poi.testpoi.mapper;

import com.poi.testpoi.pojo.User;

import java.util.List;

public interface UserMapper {


	List<User> selectUsers();

	void updateUserByName(User user);

	void addUser(User user);

	int selectByName(String username);


}

pojo

package com.poi.testpoi.pojo;

public class User {

	private Integer uid;

	private String username;

	private String password;


	public Integer getUid() {
		return uid;
	}

	public void setUid(Integer uid) {
		this.uid = uid;
	}

	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;
	}


}

service

package com.poi.testpoi.service;

import com.poi.testpoi.pojo.User;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

public interface UserService {

	List<User> selectUsers();


	boolean batchImport(String fileName, MultipartFile file) throws Exception;
}

package com.poi.testpoi.service.Impl;

import com.poi.testpoi.common.MyException;
import com.poi.testpoi.mapper.UserMapper;
import com.poi.testpoi.pojo.User;
import com.poi.testpoi.service.UserService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {


	@Autowired
	private UserMapper userMapper;


	@Override
	public List<User> selectUsers() {
		return userMapper.selectUsers();
	}

	//readOnly=true表明所注解的方法或类只是读取数据。

	//readOnly=false表明所注解的方法或类是增加，删除，修改数据。
	@Transactional(readOnly = false,rollbackFor = Exception.class)
	@Override
	public boolean batchImport(String fileName, MultipartFile file) throws Exception {
		boolean notNull = false;
		List<User> userList = new ArrayList<>();
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new MyException("上传文件格式不正确");
		}
		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		InputStream is = file.getInputStream();
		Workbook wb = null;
		if (isExcel2003) {
			wb = new HSSFWorkbook(is);
		} else {
			wb = new XSSFWorkbook(is);
		}
		Sheet sheet = wb.getSheetAt(0);
		if(sheet!=null){
			notNull = true;
		}
		User user;
		for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
			Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
			if (row == null){
				continue;
			}

			//sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException

			user = new User();

			if( row.getCell(0).getCellType() !=1){//循环时，得到每一行的单元格进行判断
				throw new MyException("导入失败(第"+(r+1)+"行,用户名请设为文本格式)");
			}

			String username = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值




			if(username == null || username.isEmpty()){//判断是否为空
				throw new MyException("导入失败(第"+(r+1)+"行,用户名未填写)");
			}


			row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
			String password = row.getCell(1).getStringCellValue();


			if(password==null || password.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,密码未填写)");
			}


			//完整的循环一次 就组成了一个对象
			user.setUsername(username);
			user.setPassword(password);
			userList.add(user);
		}
		for (User userResord : userList) {
			String name = userResord.getUsername();
			int cnt = userMapper.selectByName(name);
			if (cnt == 0) {
				userMapper.addUser(userResord);
				System.out.println(" 插入 "+userResord);
			} else {
				userMapper.updateUserByName(userResord);
				System.out.println(" 更新 "+userResord);
			}
		}
		return notNull;
	}

}

文件后缀校验工具类

package com.poi.testpoi.util;

public class ExcelImportUtils {
    
    
    // @描述：是否是2003的excel，返回true是2003 
    public static boolean isExcel2003(String filePath)  {  
        return filePath.matches("^.+\\.(?i)(xls)$");  
    }  
   
    //@描述：是否是2007的excel，返回true是2007 
    public static boolean isExcel2007(String filePath)  {  
        return filePath.matches("^.+\\.(?i)(xlsx)$");  
    }  
    
  /**
   * 验证EXCEL文件
   * @param filePath
   * @return
   */
  public static boolean validateExcel(String filePath){
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){  
            return false;  
        }  
        return true;
  }
    
 
}

配置文件

spring.mvc.view.prefix=/WEB-INF/view/

spring.mvc.view.suffix=.jsp

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jtdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

##加載mapper配置文件
mybatis.mapper-locations=classpath:mapper/*.xml

启动类

package com.poi.testpoi;

import com.poi.testpoi.mapper.UserMapper;
import com.poi.testpoi.pojo.User;
import com.poi.testpoi.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
@MapperScan(value = {"com.poi.testpoi.mapper"})
public class TestpoiApplicationTests {


	@Autowired
	private UserMapper userMapper;

	@Autowired
	private UserService userService;

	@Test
	public void contextLoads() {
		List<User> users = userMapper.selectUsers();
		System.out.println("_________________>" + users);
	}

	@Test
	public void contextLoad_() {
		List<User> users = userService.selectUsers();
		System.out.println("_________________>" + users);
	}


	@Test
	public void ecule() throws Exception {
	}
}

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.poi.testpoi.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.poi.testpoi.pojo.User">
        <id column="uid" property="uid" jdbcType="INTEGER"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="column_List">
        uid,username,password
    </sql>

    <select id="selectUsers" resultMap="BaseResultMap">
        SELECT
        <include refid="column_List"/>
        FROM user
    </select>

    <update id="updateUserByName" parameterType="com.poi.testpoi.pojo.User">
      update user set password=#{password} where username = #{username}
    </update>

    <insert id="addUser" parameterType="com.poi.testpoi.pojo.User">
        INSERT INTO USER (username,password) VALUES (#{username},#{password})
    </insert>

    <select id="selectByName" resultType="Integer">
        SELECT count(*) FROM user WHERE username=#{username}
    </select>


</mapper>

以上代码来着：lu5211019

总结

Excel文件的导出：
1.去数据库查询用户信息
（不采用前端传参，防止用户更改前端数据）
2.创建工作簿，设计第一行表标题
3.设计表头字段
4.遍历数据库查到的对象，填入每一行
（注意：从第三行开始导入数据）
5.response.setContentType设置文件格式，编码
6.response.setHeader设置附件下载及文件名
7.创建流
8.将工作簿写入流
9.关闭流

Excel文件的导入：
1.接受前端传来的文件对象MultipartFile
2.为此方法添加事务
readOnly=true表明所注解的方法或类只是读取数据。
readOnly=false表明所注解的方法或类是增加，删除，修改数据
3.判断文件格式，判断是03版本的还是07版本的excel，通过判断结果创建不同的工作簿对象
4.创建表1对象
5.如果该表不为空，则从第三行开始，创建行对象，如果行对象不为空，这判断单元格的格式和是否为空
6.符合条件的就存入pojo对象，最后存入list对象集合
7.拿到list的对象集合后，遍历每个对象，去数据库查name是否存在，如果不存在着插入，如果存在则更新，成功返回true,控制层重定向主页，查询导入的数据。

补充

POI官网：https://poi.apache.org/
图片截图来自“狂神说”