一. 添加依赖
<!-- 导入和导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
二. 导出数据
1. 生成Excel文件(示例)
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreatExcel {
public static void main(String[] args) {
//1. 创建HSSFWorkbook对象,对应【一个excel文件】
HSSFWorkbook wb = new HSSFWorkbook();
//2. 创建HSSFSheet对象,对应【文件】中的【一页】
HSSFSheet sheet = wb.createSheet("书");
//3. 创建HSSFRow对象,对应【页】中的【一行】
HSSFRow row = sheet.createRow(0); //行号,从0开始,依次增加
//4. 创建HSSFCell对象,对应【行】的【一列】
HSSFCell cell = row.createCell(0); //列号,从0开始,依次增加
cell.setCellValue("书名");
cell = row.createCell(1);
cell.setCellValue("价格");
//创建HSSFCellStyle对象,用来修饰表格的样式(可选)
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //居中
//添加10行的数据
for(int i = 1; i <= 10; i++){
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue("Java从入门到精通" + i);
cell = row.createCell(1);
cell.setCellStyle(style); //让价格这一列居中(可选)
cell.setCellValue(56 + i);
}
try {
//生成excel文件
FileOutputStream fos = new FileOutputStream("D:\\book.xls"); //在D盘下生成一个book的excel文件
wb.write(fos);
//关闭资源
fos.close();
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2. 将数据库中的数据导出到一个Excel文件下载到用户端
package com.stu.springboot.controller;
import com.stu.springboot.pojo.Book;
import com.stu.springboot.service.BookService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
@Controller
public class ExportAndImportController {
@Autowired
private BookService bookService;
@RequestMapping("/exportPage")
public String exportPage(){
return "file/export";
}
@RequestMapping("/export")
public void exportAllBook(HttpServletResponse response){
//查询数据库中的所有书
List<Book> bookList = bookService.list();
//创建Excel文件,把bookList写入到excel文件中
//1. 创建HSSFWorkbook对象,对应【一个excel文件】
HSSFWorkbook wb = new HSSFWorkbook();
//2. 创建HSSFSheet对象,对应【文件】中的【一页】
HSSFSheet sheet = wb.createSheet("书");
//3. 创建HSSFRow对象,对应【页】中的【一行】
HSSFRow row = sheet.createRow(0); //行号,从0开始,依次增加
//4. 创建HSSFCell对象,对应【行】的【一列】
HSSFCell cell = row.createCell(0); //列号,从0开始,依次增加
cell.setCellValue("id");
cell = row.createCell(1); //列号,从0开始,依次增加
cell.setCellValue("书名");
cell = row.createCell(2);
cell.setCellValue("价格");
//遍历bookList,创建行
if(bookList != null && bookList.size() > 0){
Book book = null;
for(int i = 0; i < bookList.size(); i++){
book = bookList.get(i);
//每遍历出一个Book,就生成一行,行号从1开始
row = sheet.createRow(i+1);
//为每一行创建3列,每一列的数据从Book中获取
cell = row.createCell(0);
cell.setCellValue(book.getId());
cell = row.createCell(1);
cell.setCellValue(book.getBookName());
cell = row.createCell(2);
cell.setCellValue(book.getPrice());
}
}
//生成excel文件
/**
try {
FileOutputStream fos = new FileOutputStream("D:\\Java\\SpringBootProject\\springboot\\src\\main\\resources\\static\\export\\book.xls"); //生成一个book的excel文件
wb.write(fos);
fos.close();
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
//把生成的excel文件下载到客户端
try {
//创建输入流
InputStream is = new FileInputStream("D:\\Java\\SpringBootProject\\springboot\\src\\main\\resources\\static\\export\\book.xls");
//创建字节数组,获取当前文件中所有的字节数
byte[] bytes = new byte[is.available()];
//将流读到字节数组中
is.read(bytes);
//设置响应头信息,Content-Disposition响应头表示收到的数据怎么处理(固定),attachment表示下载使用(固定),filename指定下载的文件名(下载时会在客户端显示该名字)
response.addHeader("Content-Disposition", "attachment;filename=book.xls");
//创建输出流
OutputStream out = response.getOutputStream();
out.write(bytes);
is.close();
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
**/
//将上面两个过程合并,即:生成excel文件并把生成的excel文件下载到客户端
try {
response.addHeader("Content-Disposition", "attachment;filename=book.xls");
OutputStream out = response.getOutputStream();
wb.write(out);
wb.close();
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
<!DOCTYPE html>
<!--suppress ThymeleafVariablesResolveInspection -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>导出数据</title>
<script type="text/javascript" src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script> <!--引入jquery -->
<script type="text/javascript">
$(function (){
$("#exportBook").click(function (){
window.location.href="[[@{/export}]]";
});
});
</script>
</head>
<body>
<button type="button" id="exportBook">导出数据</button>
</body>
</html>
三. 导入数据
1. 解析Excel文件(示例)
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 java.io.FileInputStream;
import java.io.InputStream;
public class ParseExcel {
public static void main(String[] args) {
//根据excel文件生成HSSFWorkbook对象,封装了excel文件的所有信息
InputStream is = null;
HSSFWorkbook wb = null;
try {
is = new FileInputStream("D:\\book.xls");
wb = new HSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
}
//根据wb获取HSSFSheet对象,封装了一页的所有信息
HSSFSheet sheet = wb.getSheetAt(0); //页的下标,下标从0开始,依次增加
HSSFRow row = null;
HSSFCell cell = null;
//根据sheet获取HSSFRow对象,封装了一行的所有信息
for(int i = 0; i <= sheet.getLastRowNum(); i++){ //sheet.getLastRowNum()获取最后一行的下标
row = sheet.getRow(i); //行的下标,下标从0开始,依次增加
//根据列获取HSSFCell对象,封装了一列的所有信息
for(int j = 0; j < row.getLastCellNum(); j++){ //row.getLastCellNum()获取最后一列的下标+1
cell = row.getCell(j); //列的下标,下标从0开始,依次增加
//获取列中的数据
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
System.out.print(cell.getStringCellValue() + " ");
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
System.out.print(cell.getNumericCellValue() + " ");
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
System.out.print(cell.getBooleanCellValue() + " ");
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
System.out.print(cell.getCellFormula() + " ");
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
System.out.print("" + " ");
}
}
System.out.println();
}
}
}
2. 将用户导入的Excel文件解析并保存到数据库中
package com.stu.springboot.controller;
import com.stu.springboot.common.R;
import com.stu.springboot.pojo.Book;
import com.stu.springboot.service.BookService;
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.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
@Controller
public class ImportController {
@Autowired
private BookService bookService;
@RequestMapping("/importPage")
public String importPage(){
return "file/import";
}
@RequestMapping("/importBook")
@ResponseBody
public R importBook(MultipartFile file){
//【获取上传的Excel文件】
//获取上传的文件的文件名
String fileName = file.getOriginalFilename();
//处理文件重名问题(当上传的文件同名,新上传的文件会将原文件覆盖),因此将UUID作为文件名,来解决该问题
String suffixName = fileName.substring(fileName.lastIndexOf(".")); //获取上传文件的后缀名
fileName = UUID.randomUUID().toString() + suffixName; //将UUID和后缀名拼接后的结果作为文件名
//设置保存文件的目录(将上传的文件放在服务器的该目录下)
String filePath = "D:\\upload";
//判断服务器中是否存在文件保存的目录,如果不存在,则创建目录
File fileDir = new File(filePath);
if(!fileDir.exists()){
fileDir.mkdir();
}
String path = filePath + File.separator + fileName; //File.separator表示路径的分隔符
try {
file.transferTo(new File(path)); //将文件保存到path目录下,
} catch (IOException e) {
e.printStackTrace();
}
//【解析Excel文件】
//根据excel文件生成HSSFWorkbook对象,封装了excel文件的所有信息
InputStream is = null;
HSSFWorkbook wb = null;
try {
is = new FileInputStream(path);
wb = new HSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
}
//根据wb获取HSSFSheet对象,封装了一页的所有信息
HSSFSheet sheet = wb.getSheetAt(0); //页的下标,下标从0开始,依次增加
HSSFRow row = null;
HSSFCell cell = null;
Book book = null;
List<Book> bookList = new ArrayList<>();
//根据sheet获取HSSFRow对象,封装了一行的所有信息
for(int i = 1; i <= sheet.getLastRowNum(); i++){ //sheet.getLastRowNum()获取最后一行的下标
row = sheet.getRow(i); //行的下标,下标从1开始,依次增加
book = new Book();
//根据列获取HSSFCell对象,封装了一列的所有信息
for(int j = 0; j < row.getLastCellNum(); j++){ //row.getLastCellNum()获取最后一列的下标+1
cell = row.getCell(j); //列的下标,下标从0开始,依次增加
//获取列中的数据
String cellValue = "";
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
cellValue = cell.getStringCellValue();
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
cellValue = cell.getNumericCellValue() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
cellValue = cell.getBooleanCellValue() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
cellValue = cell.getCellFormula() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
cellValue = "";
}
if(j == 0){
book.setId((int)Double.parseDouble(cellValue));
}else if(j == 1){
book.setBookName(cellValue);
}else if(j == 2){
book.setPrice((int)Double.parseDouble(cellValue));
}
}
//每一行中所有列都封装完成后,把book保存到list中
bookList.add(book);
}
//将所有数据保存到数据库中
boolean saveResult = bookService.saveBatch(bookList);
if(saveResult){
return R.success("成功");
}
return R.error("导入失败");
}
}
以上代码需要将上传的文件先写到磁盘上,然后从磁盘中读出文件并解析;可以将两个过程合并,直接获取上传的文件进行解析,如下:
package com.stu.springboot.controller;
import com.stu.springboot.common.R;
import com.stu.springboot.pojo.Book;
import com.stu.springboot.service.BookService;
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.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Controller
public class ImportController {
@Autowired
private BookService bookService;
@RequestMapping("/importPage")
public String importPage(){
return "file/import";
}
@RequestMapping("/importBook")
@ResponseBody
public R importBook(MultipartFile file){
//根据excel文件生成HSSFWorkbook对象,封装了excel文件的所有信息
InputStream is = null;
HSSFWorkbook wb = null;
try {
is = file.getInputStream(); //getInputStream()代替了上面保存文件然后再读取的过程
wb = new HSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
}
//根据wb获取HSSFSheet对象,封装了一页的所有信息
HSSFSheet sheet = wb.getSheetAt(0); //页的下标,下标从0开始,依次增加
HSSFRow row = null;
HSSFCell cell = null;
Book book = null;
List<Book> bookList = new ArrayList<>();
//根据sheet获取HSSFRow对象,封装了一行的所有信息
for(int i = 1; i <= sheet.getLastRowNum(); i++){ //sheet.getLastRowNum()获取最后一行的下标
row = sheet.getRow(i); //行的下标,下标从1开始,依次增加
book = new Book();
//根据列获取HSSFCell对象,封装了一列的所有信息
for(int j = 0; j < row.getLastCellNum(); j++){ //row.getLastCellNum()获取最后一列的下标+1
cell = row.getCell(j); //列的下标,下标从0开始,依次增加
//获取列中的数据
String cellValue = "";
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
cellValue = cell.getStringCellValue();
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
cellValue = cell.getNumericCellValue() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
cellValue = cell.getBooleanCellValue() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
cellValue = cell.getCellFormula() + "";
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
cellValue = "";
}
//将数据封装到book对象中
if(j == 0){
book.setId((int)Double.parseDouble(cellValue));
}else if(j == 1){
book.setBookName(cellValue);
}else if(j == 2){
book.setPrice((int)Double.parseDouble(cellValue));
}
}
//每一行中所有列都封装完成后,把book保存到list中
bookList.add(book);
}
//将所有数据保存到数据库中
boolean saveResult = bookService.saveBatch(bookList);
if(saveResult){
return R.success("成功");
}
return R.error("导入失败");
}
}
将导入的数据分页显示
<!DOCTYPE html>
<!--suppress ThymeleafVariablesResolveInspection -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>导入数据</title>
<script type="text/javascript" src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script> <!--引入jquery -->
<!--引入BootStrap框架-->
<link rel="stylesheet" type="text/css" href="../../static/jquery/bootstrap_3.3.0/css/bootstrap.min.css">
<script type="text/javascript" src="../../static/jquery/bootstrap_3.3.0/js/bootstrap.min.js"></script>
<!--引入BootStrap框架的分页插件-->
<link rel="stylesheet" type="text/css" href="../../static/jquery/bs_pagination-master/css/jquery.bs_pagination.min.css">
<script type="text/javascript" src="../../static/jquery/bs_pagination-master/js/jquery.bs_pagination.min.js"></script>
<script type="text/javascript" src="../../static/jquery/bs_pagination-master/localization/en.js"></script>
<script type="text/javascript">
$(function (){
//当图书管理页面加载完成后,默认显示第1页,每页显示5条(即查询所有数据的第1页的数据,每页显示5条)
queryBookForPage(1, 5);
//给导入按钮添加单击事件
$("#importBtn").click(function (){
var fileName = $("#file").val(); //获取文件名
var suffix = fileName.substr(fileName.lastIndexOf(".")+1).toLowerCase(); //拿到文件后缀名并转为小写
if(suffix != "xls"){
alert("仅支持xls文件");
return;
}
//获取文件本身
var bookFile = $("#file")[0].files[0];
if(bookFile.size > 5 * 1024 * 1024){
alert("文件大小不能超过5MB");
return;
}
//FormData是ajax提供的接口,可以模拟键值对向后端提交参数
var formData = new FormData();
formData.append("file", bookFile);
//发送请求
$.ajax({
url:'[[@{/importBook}]]',
data:formData,
processData:false, //设置ajax向后台提交参数之前,是否把参数统一转化成字符串,默认是true
contentType:false, //设置ajax向后台提交参数之前,是否把参数统一按Urlencoded编码,默认是true
type:'post',
dataType:'json',
success:function (data){
if(data.code=="1"){
alert("导入成功");
//刷新分页显示列表
queryBookForPage($("#page").bs_pagination('getOption', 'currentPage'), $("#page").bs_pagination('getOption', 'rowsPerPage'));
}else{
alert(data.message);
}
}
});
});
});
//分页显示
function queryBookForPage(pageNo, pageSize) {
//发送请求
$.ajax({
url: '[[@{/manage/book}]]',
data: {
pageNo: pageNo,
pageSize: pageSize
},
type: 'post',
dataType: 'json',
success: function (data) { //data是后端的R对象,R.objectData是后端的Page对象
//显示书的列表,将后端传回来的数据变成html写在相应的表格里
var htmlStr = "";
$.each(data.objectData.records, function (index, obj) {
htmlStr += "<tr>";
htmlStr += "<td><input type=\"checkbox\" value=\"" + obj.id + "\"/></td>";
htmlStr += "<td>" + obj.bookName + "</td>";
htmlStr += "<td>" + obj.price + "</td>";
htmlStr += "</tr>";
});
$("#bookTableRow").html(htmlStr);
//【 切换页面时,刷新页面显示的内容 】
//计算总页数
var totalPages = 1;
if (data.objectData.total % pageSize == 0) {
totalPages = data.objectData.total / pageSize;
} else {
totalPages = parseInt(data.objectData.total / pageSize) + 1;
}
//调用BootStrap框架的分页插件,显示翻页信息
$("#page").bs_pagination({
currentPage: pageNo, //当前页号
rowsPerPage: pageSize, //每页显示的数据个数
totalRows: data.objectData.total, //数据的总条数
totalPages: totalPages, //总页数,必须赋值
visiblePageLinks: 5, //分页条中显示的页面卡片个数
showGoToPage: true, //是否显示 跳转 到某个页面, 默认是true
showRowsPerPage: true, //是否显示 每页显示条数, 默认是true
showRowsInfo: false, //是否显示 记录的信息, 默认是true
//用户每次切换页号,都会自动出发本函数, 返回切换页号之后的pageNo和pageSize
onChangePage: function (event, pageObj) {
queryBookForPage(pageObj.currentPage, pageObj.rowsPerPage);
}
});
}
});
}
</script>
</head>
<body>
<input type="file" id="file"/> <br>
<button type="button" id="importBtn">导入</button>
<!-- 分页显示具体内容 -->
<div>
<table>
<thead>
<tr>
<td><input type="checkbox" id="checkAll"/></td>
<td>书名</td>
<td>价格</td>
</tr>
</thead>
<tbody id="bookTableRow">
<tr>
<td><input type="checkbox"/></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</div>
<br>
<!-- 分页条 -->
<div id="page"></div>
</body>
</html>