SpringBoot导入导出数据实现方法详解

今天给大家带来的是一个 SpringBoot导入导出数据

首先我们先创建项目注意：创建SpringBoot项目时一定要联网不然会报错

项目创建好后我们首先对 application.yml 进行编译

server:

port: 8081

# mysql

spring:

datasource:

driver-class-name: com.mysql.cj.jdbc.Driver

url: jdbc:mysql://127.0.0.1:3306/dvd?characterEncoding=utf-8&&severTimezone=utc

username: root

password: root

thymeleaf:

mode: HTML5

cache: false

suffix: .html

prefix: classpath:/

mybatis:

mapperLocations: classpath:mapper/**/*.xml

configuration:

map-underscore-to-camel-case: true

pagehelper:

helper-dialect: mysql

offset-as-page-num: true

params: count=countSql

reasonable: true

row-bounds-with-count: true

support-methods-arguments: true

注意：在：后一定要空格，这是他的语法，不空格就会运行报错

接下来我们进行对项目的构建创建好如下几个包可根据自己实际需要创建其他的工具包之类的

mapper：用于存放dao层接口

pojo：用于存放实体类

service：用于存放service层接口，以及service层实现类

controller：用于存放controller控制层

接下来我们开始编写代码

首先是实体类

 
package com.bdqn.springbootexcel.pojo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
@Data
public class ExcelData implements Serializable{
    //文件名称
    private String fileName;
    //表头数据
    private String[] head;
    //数据
    private List<String[]> data;
}

然后是service层

 
package com.bdqn.springbootexcel.service;
import com.bdqn.springbootexcel.pojo.User;
import org.apache.ibatis.annotations.Select;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
public interface ExcelService {
    Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize);
    Boolean importExcel(String fileName);
    List<User> find();
}
package com.bdqn.springbootexcel.service;
import com.bdqn.springbootexcel.mapper.UserMapper;
import com.bdqn.springbootexcel.pojo.ExcelData;
import com.bdqn.springbootexcel.pojo.User;
import com.bdqn.springbootexcel.util.ExcelUtil;
import lombok.extern.slfj.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
@Slfj
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private UserMapper userMapper;
    @Override
    public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
        log.info("导出数据开始。。。。。。");
        //查询数据并赋值给ExcelData
        List<User> userList = userMapper.find();
        List<String[]> list = new ArrayList<String[]>();
        for (User user : userList) {
            String[] arrs = new String[userList.size()];
            arrs[] = String.valueOf(user.getId());
            arrs[] = String.valueOf(user.getName());
            arrs[] = String.valueOf(user.getAge());
            arrs[] = String.valueOf(user.getSex());
            list.add(arrs);
        }
        //表头赋值
        String[] head = {"序列", "名字", "年龄", "性别"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        //实现导出
        try {
            ExcelUtil.exportExcel(response, data);
            log.info("导出数据结束。。。。。。");
            return true;
        } catch (Exception e) {
            log.info("导出数据失败。。。。。。");
            return false;
        }
    }
    @Override
    public Boolean importExcel(String fileName) {
        log.info("导入数据开始。。。。。。");
        try {
            List<Object[]> list = ExcelUtil.importExcel(fileName);
            System.out.println(list.toString());
            for (int i =; i < list.size(); i++) {
                User user = new User();
                user.setName((String) list.get(i)[]);
                user.setAge((String) list.get(i)[]);
                user.setSex((String) list.get(i)[]);
                userMapper.add(user);
            }
            log.info("导入数据结束。。。。。。");
            return true;
        } catch (Exception e) {
            log.info("导入数据失败。。。。。。");
            e.printStackTrace();
        }
        return false;
    }
    @Override
    public List<User> find() {
        return userMapper.find();
    }
}

工具类

 
package com.bdqn.springbootexcel.util;
import com.bdqn.springbootexcel.pojo.ExcelData;
import com.bdqn.springbootexcel.pojo.User;
import lombok.extern.slfj.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import static org.apache.poi.ss.usermodel.CellType.*;
@Slfj
public class ExcelUtil {
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("导出解析开始，fileName:{}",data.getFileName());
        try {
            //实例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个Excel表单，参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //设置表头
            setTitle(workbook, sheet, data.getHead());
            //设置单元格并赋值
            setData(sheet, data.getData());
            //设置浏览器下载
            setBrowser(response, workbook, data.getFileName());
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow();
            //设置列宽，setColumnWidth的第二个参数要乘以，这个参数的单位是1/256个字符宽度
            for (int i =; i <= str.length; i++) {
                sheet.setColumnWidth(i, * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //创建表头名称
            HSSFCell cell;
            for (int j =; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }
    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try{
            int rowNum =;
            for (int i =; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j =; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("表格赋值成功！");
        }catch (Exception e){
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("设置浏览器下载成功！");
        } catch (Exception e) {
            log.info("设置浏览器下载失败！");
            e.printStackTrace();
        }
    }
    public static List<Object[]> importExcel(String fileName) {
        log.info("导入解析开始，fileName:{}",fileName);
        try {
            List<Object[]> list = new ArrayList<>();
            InputStream inputStream = new FileInputStream(fileName);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt();
            //获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i =; i < rows; i++) {
                //过滤表头行
                if (i ==) {
                    continue;
                }
                //获取当前行的数据
                Row row = sheet.getRow(i);
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index =;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        objects[index] = (int) cell.getNumericCellValue();
                    }
                    if (cell.getCellType().equals(STRING)) {
                        objects[index] = cell.getStringCellValue();
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                    }
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                    }
                    index++;
                }
                list.add(objects);
            }
            log.info("导入文件解析成功！");
            return list;
        }catch (Exception e){
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }
    //测试导入
    public static void main(String[] args) {
        try {
            String fileName = "G:/test.xlsx";
            List<Object[]> list = importExcel(fileName);
            for (int i =; i < list.size(); i++) {
                User user = new User();
                user.setName((String) list.get(i)[]);
                user.setAge((String) list.get(i)[]);
                user.setSex((String) list.get(i)[]);
                System.out.println(user.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

最后是controller层

 
package com.bdqn.springbootexcel.controller;
import com.bdqn.springbootexcel.pojo.User;
import com.bdqn.springbootexcel.service.ExcelService;
import lombok.extern.slfj.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
@Slfj
@RestController
public class ExcelController {
    @Autowired
    private ExcelService excelService;
    @GetMapping("/export")
    public String exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
        fileName = "test.xlsx";
        if (fileName == null || "".equals(fileName)) {
            return "文件名不能为空！";
        } else {
            if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {
                Boolean isOk = excelService.exportExcel(response, fileName,, 10);
                if (isOk) {
                    return "导出成功！";
                } else {
                    return "导出失败！";
                }
            }
            return "文件格式有误！";
        }
    }
    @GetMapping("/import")
    public String importExcel(String fileName) {
        fileName = "G:/test.xlsx";
        if (fileName == null && "".equals(fileName)) {
            return "文件名不能为空！";
        } else {
            if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {
                Boolean isOk = excelService.importExcel(fileName);
                if (isOk) {
                    return "导入成功！";
                } else {
                    return "导入失败！";
                }
            }
            return "文件格式错误！";
        }
    }
    //饼状图的数据查询
    //@ResponseBody
    @RequestMapping("/pojos_bing")
    public List<User> gotoIndex() {
        List<User> pojos = excelService.find();
        return pojos;
    }
}

到现在为止我们的后端代码就已经完全搞定了，前端页面如下

写了一个简单前端用于测试

index.html

 
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<html lang="en">
<head>
    <meta charset="UTF-">
    <title>Title</title>
</head>
<body>
    <div align="center">
        <a th:href="@{'/export'}" rel="external nofollow" >导出</a>
        <a th:href="@{'/import'}" rel="external nofollow" >导入</a>
    </div>
</body>
</html>

当我们点击导出按钮时浏览器会自动下载

当我们点击导入按钮时会往数据库中添加表格数据

	package com.bdqn.springbootexcel.pojo;
	import lombok.Data;
	import java.io.Serializable;
	import java.util.List;
	@Data
	public class ExcelData implements Serializable{
	//文件名称
	private String fileName;
	//表头数据
	private String[] head;
	//数据
	private List<String[]> data;
	}

	package com.bdqn.springbootexcel.service;
	import com.bdqn.springbootexcel.pojo.User;
	import org.apache.ibatis.annotations.Select;
	import javax.servlet.http.HttpServletResponse;
	import java.util.List;
	public interface ExcelService {
	Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize);
	Boolean importExcel(String fileName);
	List<User> find();
	}
	package com.bdqn.springbootexcel.service;
	import com.bdqn.springbootexcel.mapper.UserMapper;
	import com.bdqn.springbootexcel.pojo.ExcelData;
	import com.bdqn.springbootexcel.pojo.User;
	import com.bdqn.springbootexcel.util.ExcelUtil;
	import lombok.extern.slfj.Slf4j;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.stereotype.Service;
	import javax.servlet.http.HttpServletResponse;
	import java.util.ArrayList;
	import java.util.List;
	@Slfj
	@Service
	public class ExcelServiceImpl implements ExcelService {
	@Autowired
	private UserMapper userMapper;
	@Override
	public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
	log.info("导出数据开始。。。。。。");
	//查询数据并赋值给ExcelData
	List<User> userList = userMapper.find();
	List<String[]> list = new ArrayList<String[]>();
	for (User user : userList) {
	String[] arrs = new String[userList.size()];
	arrs[] = String.valueOf(user.getId());
	arrs[] = String.valueOf(user.getName());
	arrs[] = String.valueOf(user.getAge());
	arrs[] = String.valueOf(user.getSex());
	list.add(arrs);
	}
	//表头赋值
	String[] head = {"序列", "名字", "年龄", "性别"};
	ExcelData data = new ExcelData();
	data.setHead(head);
	data.setData(list);
	data.setFileName(fileName);
	//实现导出
	try {
	ExcelUtil.exportExcel(response, data);
	log.info("导出数据结束。。。。。。");
	return true;
	} catch (Exception e) {
	log.info("导出数据失败。。。。。。");
	return false;
	}
	}
	@Override
	public Boolean importExcel(String fileName) {
	log.info("导入数据开始。。。。。。");
	try {
	List<Object[]> list = ExcelUtil.importExcel(fileName);
	System.out.println(list.toString());
	for (int i =; i < list.size(); i++) {
	User user = new User();
	user.setName((String) list.get(i)[]);
	user.setAge((String) list.get(i)[]);
	user.setSex((String) list.get(i)[]);
	userMapper.add(user);
	}
	log.info("导入数据结束。。。。。。");
	return true;
	} catch (Exception e) {
	log.info("导入数据失败。。。。。。");
	e.printStackTrace();
	}
	return false;
	}
	@Override
	public List<User> find() {
	return userMapper.find();
	}
	}

	package com.bdqn.springbootexcel.util;
	import com.bdqn.springbootexcel.pojo.ExcelData;
	import com.bdqn.springbootexcel.pojo.User;
	import lombok.extern.slfj.Slf4j;
	import org.apache.poi.hssf.usermodel.*;
	import org.apache.poi.ss.usermodel.*;
	import javax.servlet.http.HttpServletResponse;
	import java.io.BufferedOutputStream;
	import java.io.FileInputStream;
	import java.io.InputStream;
	import java.io.OutputStream;
	import java.util.ArrayList;
	import java.util.List;
	import static org.apache.poi.ss.usermodel.CellType.*;
	@Slfj
	public class ExcelUtil {
	public static void exportExcel(HttpServletResponse response, ExcelData data) {
	log.info("导出解析开始，fileName:{}",data.getFileName());
	try {
	//实例化HSSFWorkbook
	HSSFWorkbook workbook = new HSSFWorkbook();
	//创建一个Excel表单，参数为sheet的名字
	HSSFSheet sheet = workbook.createSheet("sheet");
	//设置表头
	setTitle(workbook, sheet, data.getHead());
	//设置单元格并赋值
	setData(sheet, data.getData());
	//设置浏览器下载
	setBrowser(response, workbook, data.getFileName());
	log.info("导出解析成功!");
	} catch (Exception e) {
	log.info("导出解析失败!");
	e.printStackTrace();
	}
	}
	private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
	try {
	HSSFRow row = sheet.createRow();
	//设置列宽，setColumnWidth的第二个参数要乘以，这个参数的单位是1/256个字符宽度
	for (int i =; i <= str.length; i++) {
	sheet.setColumnWidth(i, * 256);
	}
	//设置为居中加粗,格式化时间格式
	HSSFCellStyle style = workbook.createCellStyle();
	HSSFFont font = workbook.createFont();
	font.setBold(true);
	style.setFont(font);
	style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
	//创建表头名称
	HSSFCell cell;
	for (int j =; j < str.length; j++) {
	cell = row.createCell(j);
	cell.setCellValue(str[j]);
	cell.setCellStyle(style);
	}
	} catch (Exception e) {
	log.info("导出时设置表头失败！");
	e.printStackTrace();
	}
	}
	private static void setData(HSSFSheet sheet, List<String[]> data) {
	try{
	int rowNum =;
	for (int i =; i < data.size(); i++) {
	HSSFRow row = sheet.createRow(rowNum);
	for (int j =; j < data.get(i).length; j++) {
	row.createCell(j).setCellValue(data.get(i)[j]);
	}
	rowNum++;
	}
	log.info("表格赋值成功！");
	}catch (Exception e){
	log.info("表格赋值失败！");
	e.printStackTrace();
	}
	}
	private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
	try {
	//清空response
	response.reset();
	//设置response的Header
	response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
	OutputStream os = new BufferedOutputStream(response.getOutputStream());
	response.setContentType("application/vnd.ms-excel;charset=gb");
	//将excel写入到输出流中
	workbook.write(os);
	os.flush();
	os.close();
	log.info("设置浏览器下载成功！");
	} catch (Exception e) {
	log.info("设置浏览器下载失败！");
	e.printStackTrace();
	}
	}
	public static List<Object[]> importExcel(String fileName) {
	log.info("导入解析开始，fileName:{}",fileName);
	try {
	List<Object[]> list = new ArrayList<>();
	InputStream inputStream = new FileInputStream(fileName);
	Workbook workbook = WorkbookFactory.create(inputStream);
	Sheet sheet = workbook.getSheetAt();
	//获取sheet的行数
	int rows = sheet.getPhysicalNumberOfRows();
	for (int i =; i < rows; i++) {
	//过滤表头行
	if (i ==) {
	continue;
	}
	//获取当前行的数据
	Row row = sheet.getRow(i);
	Object[] objects = new Object[row.getPhysicalNumberOfCells()];
	int index =;
	for (Cell cell : row) {
	if (cell.getCellType().equals(NUMERIC)) {
	objects[index] = (int) cell.getNumericCellValue();
	}
	if (cell.getCellType().equals(STRING)) {
	objects[index] = cell.getStringCellValue();
	}
	if (cell.getCellType().equals(BOOLEAN)) {
	objects[index] = cell.getBooleanCellValue();
	}
	if (cell.getCellType().equals(ERROR)) {
	objects[index] = cell.getErrorCellValue();
	}
	index++;
	}
	list.add(objects);
	}
	log.info("导入文件解析成功！");
	return list;
	}catch (Exception e){
	log.info("导入文件解析失败！");
	e.printStackTrace();
	}
	return null;
	}
	//测试导入
	public static void main(String[] args) {
	try {
	String fileName = "G:/test.xlsx";
	List<Object[]> list = importExcel(fileName);
	for (int i =; i < list.size(); i++) {
	User user = new User();
	user.setName((String) list.get(i)[]);
	user.setAge((String) list.get(i)[]);
	user.setSex((String) list.get(i)[]);
	System.out.println(user.toString());
	}
	} catch (Exception e) {
	e.printStackTrace();
	}
	}
	}

	package com.bdqn.springbootexcel.controller;
	import com.bdqn.springbootexcel.pojo.User;
	import com.bdqn.springbootexcel.service.ExcelService;
	import lombok.extern.slfj.Slf4j;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.web.bind.annotation.GetMapping;
	import org.springframework.web.bind.annotation.RequestMapping;
	import org.springframework.web.bind.annotation.ResponseBody;
	import org.springframework.web.bind.annotation.RestController;
	import javax.servlet.http.HttpServletResponse;
	import java.util.List;
	@Slfj
	@RestController
	public class ExcelController {
	@Autowired
	private ExcelService excelService;
	@GetMapping("/export")
	public String exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
	fileName = "test.xlsx";
	if (fileName == null \|\| "".equals(fileName)) {
	return "文件名不能为空！";
	} else {
	if (fileName.endsWith("xls") \|\| fileName.endsWith("xlsx")) {
	Boolean isOk = excelService.exportExcel(response, fileName,, 10);
	if (isOk) {
	return "导出成功！";
	} else {
	return "导出失败！";
	}
	}
	return "文件格式有误！";
	}
	}
	@GetMapping("/import")
	public String importExcel(String fileName) {
	fileName = "G:/test.xlsx";
	if (fileName == null && "".equals(fileName)) {
	return "文件名不能为空！";
	} else {
	if (fileName.endsWith("xls") \|\| fileName.endsWith("xlsx")) {
	Boolean isOk = excelService.importExcel(fileName);
	if (isOk) {
	return "导入成功！";
	} else {
	return "导入失败！";
	}
	}
	return "文件格式错误！";
	}
	}
	//饼状图的数据查询
	//@ResponseBody
	@RequestMapping("/pojos_bing")
	public List<User> gotoIndex() {
	List<User> pojos = excelService.find();
	return pojos;
	}
	}

	<!DOCTYPE html>
	<html lang="en" xmlns:th="http://www.thymeleaf.org">
	<html lang="en">
	<head>
	<meta charset="UTF-">
	<title>Title</title>
	</head>
	<body>
	<div align="center">
	<a th:href="@{'/export'}" rel="external nofollow" >导出</a>
	<a th:href="@{'/import'}" rel="external nofollow" >导入</a>
	</div>
	</body>
	</html>