SpringBoot+Mybatis+Vue ElementUI+POI 实现Excel数据的导入导出
一、页面效果：
二、主要功能：
1、CRUD的操作
2、批量删除
3、将Excel导入到数据库
4、将数据表导出到Excel中
三、前端代码：
1、页面代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href="../js/index.css">
<script src="../js/vue.js" type="text/javascript"></script>
<script src="../js/elementUI.js" type="text/javascript"></script>
<script src="../js/axios.js" type="text/javascript"></script>
</head>
<body>
<div id="app" style="margin-left: 25px;margin-top: 15px;">
<el-form :model="formData" :rules="rules" ref="formData" label-width="100px">
<el-row>
<el-col :span="6">
<el-form-item label="图书名称" prop="bookName">
<el-input v-model="formData.bookName"></el-input>
</el-form-item>
</el-col>
<el-col :span="6">
<el-form-item label="图书作者" prop="bookAuthor">
<el-input v-model="formData.bookAuthor"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="6">
<el-form-item label="发行日期" prop="bookDate">
<el-input type="date" v-model="formData.bookDate"></el-input>
</el-form-item>
</el-col>
<el-col :span="6">
<el-form-item label="图书价格" prop="bookPrice">
<el-input v-model="formData.bookPrice" ></el-input>
</el-form-item>
</el-col>
</el-row>
<el-col>
<el-form-item>
<el-button type="primary" icon="el-icon-plus" size="mini" @click="addBook('formData')">增加</el-button>
<el-button type="danger" icon="el-icon-delete" size="mini" @click="batchDelete">批量删除</el-button>
<el-button icon="el-icon-search" size="mini" @click="searchBook">查询</el-button>
<el-button type="success" icon="el-icon-right" size="mini" @click="importBook">导入Excel</el-button>
<el-button type="warning" icon="el-icon-back" size="mini" @click="exportBook">导出Excel</el-button>
</el-form-item>
</el-col>
</el-form>
<!--表格-->
<el-table :data="tableData" style="width: 1050px;" ref="multipleTable" @selection-change="handleSelectionChange" id="out-table">
<el-table-column min-width='140' type="selection"></el-table-column>
<el-table-column prop="bookId" label="图书编号" width="150"> </el-table-column>
<el-table-column prop="bookName" label="图书名称" width="180"> </el-table-column>
<el-table-column prop="bookAuthor" label="图书作者" width="150"> </el-table-column>
<el-table-column prop="bookDate" label="发行日期" width="150"> </el-table-column>
<el-table-column prop="bookPrice" label="图书价格" width="150"> </el-table-column>
<el-table-column label="操作" width="210" align="center">
<template slot-scope="scope">
<el-button type="danger" icon="el-icon-delete" size="mini" @click="Delete(scope.row.bookId)">删除</el-button>
<el-button type="success" icon="el-icon-edit" size="mini" @click="Update(scope.row)">修改</el-button>
</template>
</el-table-column>
</el-table>
<!--分页组件-->
<el-pagination class="tabListPage"
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="currentPage"
:page-sizes="pageSizes"
:page-size="pageCount"
layout="total, sizes, prev, pager, next, jumper"
:total="totalCount"
>
</el-pagination>
<!--修改对话框-->
<el-dialog customClass="customWidth"
:modal="true"
:visible.sync="dialogVisible"
width="25%"
:title="Title"
>
<!--"修改"对话框中的表单-->
<div style="margin-right: 20px;">
<el-form :model="updateData" :rules="rules" ref="updateData" label-width="100px">
<el-row>
<el-col>
<el-form-item label="图书编号" prop="update_bookId">
<el-input v-model="updateData.update_bookId" disabled></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col>
<el-form-item label="图书名称" prop="update_bookName">
<el-input v-model="updateData.update_bookName"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col>
<el-form-item label="图书作者" prop="update_bookAuthor">
<el-input v-model="updateData.update_bookAuthor"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col>
<el-form-item label="发行日期" prop="update_bookDate">
<el-input type="date" v-model="updateData.update_bookDate"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col>
<el-form-item label="图书价格" prop="update_bookPrice">
<el-input v-model="updateData.update_bookPrice"></el-input>
</el-form-item>
</el-col>
</el-row>
</el-form>
</div>
<!-- 底部按钮 -->
<div slot="footer" class="dialog-footer">
<el-button @click="dialogVisible = false">取 消</el-button>
<el-button type="primary" @click="handlerAddOk">确 定</el-button>
</div>
</el-dialog>
<!--导入Excel对话框-->
<el-dialog customClass="customWidth"
:modal="true"
:visible.sync="ImportdialogVisible"
width="29%"
:title="ImportTitle"
>
<el-form :model="form">
<el-form-item>
<el-upload class="upload-file"
drag
ref="uploadExcel"
action="http://localhost:8080/elementui/import"
:limit=limitNum
:auto-upload="false"
accept=".xlsx"
:before-upload="beforeUploadFile"
:on-change="fileChange"
:on-exceed="exceedFile"
:on-success="handleSuccess"
:on-error="handleError"
:file-list="fileList"
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击选择Excel文件</em></div>
<!-- <div slot="tip" class="el-upload-list__item-name">{{fileName}}</div> -->
</el-upload>
</el-form-item>
<el-form-item>
<div style="margin-left: 120px;">
<el-button @click="ImportdialogVisible = false" size="small">取 消</el-button>
<el-button size="small" type="primary" @click="submitUpload()">立即导入</el-button>
</div>
</el-form-item>
</el-form>
</el-dialog>
</div>
2、JS代码：
<script>
new Vue({
el:'#app',
data:{
tableData:[], //表格绑定的数据
multipleSelection:[], //多选时绑定的数据
currentPage:1,// 默认显示第几页
totalCount:1,// 总条数，根据接口获取数据长度(注意：这里不能为空)
pageSizes:[5,10,15,20,25,30],// 个数选择器（可修改）
pageCount:5,// 默认每页显示的条数（可修改）
formData:{ //"新增"表单数据的初始化
bookName:'',
bookAuthor:'',
bookDate:'',
bookPrice:''
},
updateData:{ //"修改"对话框中表单数据的初始化
update_bookName:'',
update_bookAuthor:'',
update_bookDate:'',
update_bookPrice:''
},
dialogVisible:false, //"修改"对话框是否可见
Title:"修改记录", //"修改"对话框的标题
/* --- 以下导入Excel文件对话框绑定的数据 --- */
ImportdialogVisible:false, //"导入"对话框是否可见
ImportTitle:'导入Excel', //"导入"对话框的标题
limitNum: 1,//导入文件的个数
form: { //"导入"表单绑定file，并初始化
file: ''
},
fileList: [],//"导入"文件列表
rules:{ //表单验证
bookName:[{ required:true,message:'名称不能为空',trigger:'blur'}],
bookAuthor:[{required:true,message:'作者不能为空',trigger:'blur'}],
bookDate:[{required:true,message:'发行日期不能为空',trigger:'blue'}],
bookPrice:[{required:true,message:'图书价格不能为空',trigger:'blue'}]
}
},
methods:{
getData(){ //初始化获取后台数据的函数
let self = this;
axios({
url:"http://localhost:8080/elementui/books",
method:"get",
params:{
"CurrentPage":self.currentPage,
"PageSize":self.pageCount
}
}).then(response=>{
console.log(response.data);
self.tableData = response.data.pageList;
self.totalCount = response.data.recordTotal;
console.log(self.tableData)
})
},
handleSizeChange(val) { //每页显示的记录数(下拉列表)发生改变时绑定的函数
console.log(`每页 ${val} 条`);
let self = this;
axios({
url:"http://localhost:8080/elementui/books",
methods:"get",
params:{
"CurrentPage":self.currentPage,
"PageSize":val
}
}).then(response=>{
self.tableData = response.data.pageList;
})
},
handleCurrentChange(val) { //"当前页"发生改变时绑定的函数
console.log(`当前页: ${val}`);
let self = this;
axios({
url:"http://localhost:8080/elementui/books",
methods:"get",
params:{
"CurrentPage":val,
"PageSize":self.pageCount
}
}).then(response=>{
self.tableData = response.data.pageList;
})
},
Delete(val){ //"删除"按钮绑定的函数
let self = this;
console.info("ID:"+val);
self.$confirm("确定删除吗？","操作提示",{
confirmButtonText:"确定",
cancelButtonText:"取消",
type:"warning"
}).then(()=>{ //当用户点击"确定"时，向后台发送删除请求
axios({
url:"http://localhost:8080/elementui/delete",
methods:"get",
params:{
"bookId":val
}
}).then(response=>{
console.info(response.data)
if(response.data==true){
self.getData();
self.$message({
type:"success",
message:"操作成功",
offset:100,
center:true,
})
}else{
self.$message({
type:"error",
message:"操作失败",
offset:100,
center:true,
})
}
})
}).catch(()=>{
})
},
Update(row){ //点击"修改"按钮弹出修改对话框
let self = this
self.dialogVisible = true;
self.updateData.update_bookId = row.bookId;
self.updateData.update_bookName = row.bookName;
self.updateData.update_bookAuthor = row.bookAuthor;
self.updateData.update_bookDate = row.bookDate;
self.updateData.update_bookPrice = row.bookPrice;
},
handlerAddOk(){ //修改对话框上的"确定"按钮
let self = this;
axios({
url:"http://localhost:8080/elementui/update",
method:"get",
params:{
"bookId":self.updateData.update_bookId,
"bookName":self.updateData.update_bookName,
"bookAuthor":self.updateData.update_bookAuthor,
"bookDate":self.updateData.update_bookDate,
"bookPrice":self.updateData.update_bookPrice
}
}).then(response=>{
if(response.data == true){
self.getData();
self.$message({
type:"success",
message:"操作成功",
offset:100,
center:true,
})
}else{
self.$message({
type:"error",
message:"操作失败",
offset:100,
center:true,
})
}
self.dialogVisible = false;
})
},
addBook(formName){ //"增加"按钮绑定的函数
let self = this;
self.$refs[formName].validate((valid)=>{
if(valid){
axios({
url:"http://localhost:8080/elementui/insert",
method:"get",
params:{
bookName:self.formData.bookName,
bookAuthor:self.formData.bookAuthor,
bookDate:self.formData.bookDate,
bookPrice:self.formData.bookPrice
}
}).then(response=>{
if(response.data==true){
self.getData();
self.$message({
type:"success",
message:"操作成功",
offset:100,
center:true,
});
self.$refs[formName].resetFields();
}else{
self.$message({
type:"error",
message:"操作失败",
offset:100,
center:true,
})
}
})
}
})
},
batchDelete(){ //"批量删除"按钮绑定的函数
let self = this;
let sid = '';
if(self.multipleSelection.length!=0){
self.$confirm("确定删除吗？","操作提示",{
confirmButtonText:"确定",
cancelButtonText:"取消",
type:"warning"
}).then(()=>{ //当用户点击"确定"时，获取用户选择的行的id，并将id拼接成字符串提交给后台
for(let i=0;i<self.multipleSelection.length;i++){
sid = sid+self.multipleSelection[i].bookId;
sid = sid+",";
}
sid = sid.substring(0,sid.length-1);
console.log(sid);
axios({
url:"http://localhost:8080/elementui/batchDelete",
method:"get",
params:{
ids:sid
}
}).then(response=>{
console.info(response.data)
if(response.data==true){
self.getData();
self.$message({
type:"success",
message:"操作成功",
offset:100,
center:true,
})
}else{
self.$message({
type:"error",
message:"操作失败",
offset:100,
center:true,
})
}
})
}).catch(()=>{
})
}else{
self.$message({
type:"error",
message:"请选择要删除的数据",
offset:100,
center:true,
})
}
},
searchBook(){ //"查询"按钮绑定的函数
},
importBook(){ //弹出导入对话框
let self = this;
self.ImportdialogVisible = true;
},
// 文件超出个数限制时的钩子
exceedFile(files, fileList) {
this.$notify.warning({
title: '警告',
message: `只能选择 ${this.limitNum} 个文件，当前共选择了 ${files.length + fileList.length} 个`
});
},
// 文件状态改变时的钩子
fileChange(file, fileList) {
console.log('change')
console.log(file)
this.form.file = file.raw
console.log(this.form.file)
console.log(fileList)
},
// 上传文件之前的钩子, 参数为上传的文件,若返回 false 或者返回 Promise 且被 reject，则停止上传
beforeUploadFile(file) {
console.log('before upload')
console.log(file)
// let extension = file.name.substring(file.name.lastIndexOf('.')+1); //获取上传文件的扩展名
console.log("文件扩展名为:"+extension);
let size = file.size / 1024 / 1024; //设置上传文件的大小
const isXLSX = file.name.split(".")[1] === 'xlsx'; //获取上传文件的扩展名
if(!isXLSX){
this.$notify.warning({
title: '警告',
message: `只能上传Excel2017（即后缀是.xlsx）的文件`
});
}
if(size > 10) {
this.$notify.warning({
title: '警告',
message: `文件大小不得超过10M`
});
}
},
// 文件上传成功时的钩子
handleSuccess(res, file, fileList) {
let self = this;
this.$notify.success({
title: '成功',
message: `文件导入成功`
});
this.getData();
self.ImportdialogVisible = false;
},
// 文件上传失败时的钩子
handleError(err, file, fileList) {
this.$notify.error({
title: '错误',
message: `文件导入失败`
});
},
submitUpload(){
let self = this;
if(self.form.file == ''){
this.$notify.error({
title: '错误',
message: `请选择要导入的文件`
});
return;
}
self.$refs.uploadExcel.submit();
},
exportBook(){
//这里不能用ajax请求，ajax请求无法弹出下载保存对话框
location.href="http://localhost:8080/elementui/export";
},
handleSelectionChange(rows){ //用户在表格上选择行时绑定的函数
let self = this;
self.multipleSelection = rows;
}
},
created:function(){
this.getData()
}
})
</script>
二、后台代码
1、目录结构：
2、依赖jar包：
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!--上传-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
3、配置文件：
server:
port: 8080
servlet:
context-path: /elementui
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mvc?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
maxActive: 20
initialSize: 5
minIdle: 3
maxWait: 10000
mybatis:
type-aliases-package: com.vue.elementui.entity
4、实体类：
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable {
private Integer bookId;
private String bookName;
private String bookAuthor;
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
private Date bookDate;
private Double bookPrice;
}
5、SQL语句生成器类：
public class BookSQLProvider {
public String selectSQL(){ //查询所有
return new SQL() {
{
SELECT("*");
FROM("ssm_book");
}
}.toString();
}
public String findByIdSQL(Integer id){ //按Id查询
return new SQL(){
{
SELECT("*");
FROM("ssm_book");
WHERE("book_id=#{id}");
}
}.toString();
}
public String insertSQL(Book book){ //插入
return new SQL(){
{
INSERT_INTO("ssm_book");
VALUES("book_name","#{bookName}");
VALUES("book_author","#{bookAuthor}");
VALUES("book_date","#{bookDate}");
VALUES("book_price","#{bookPrice}");
}
}.toString();
}
public String deleteSQL(Integer id){ //删除
return new SQL(){
{
DELETE_FROM("ssm_book");
WHERE("book_id=#{id}");
}
}.toString();
}
public String updateSQL(Book book){ //更新
return new SQL(){
{
UPDATE("ssm_book");
SET("book_name=#{bookName}");
SET("book_author=#{bookAuthor}");
SET("book_date=#{bookDate}");
SET("book_price=#{bookPrice}");
WHERE("book_id=#{bookId}");
}
}.toString();
}
public String pageListSQL(Integer start,Integer count){ //分页查询
return new SQL(){
{
SELECT("*");
FROM("ssm_book");
LIMIT("#{start},#{count}");
}
}.toString();
}
}
6、代理(Mapper):
@Mapper
@Repository
public interface IBookMapper {
@SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "selectSQL")
@Results(id = "bookMap",value = {
@Result(id = true,property = "bookId",column = "book_id"),
@Result(property = "bookName",column = "book_name"),
@Result(property = "bookAuthor",column = "book_author"),
@Result(property = "bookDate",column = "book_date"),
@Result(property = "bookPrice",column = "book_price")
})
public List<Book> findAll();
@SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "findByIdSQL")
@ResultMap(value = "bookMap")
public Book findById(Integer id);
@SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "pageListSQL")
@ResultMap(value = "bookMap")
public List<Book> findPageBook(Integer start,Integer count);
@InsertProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method ="insertSQL" )
public int insert(Book book);
@DeleteProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method ="deleteSQL")
public int delete(Integer id);
@UpdateProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "updateSQL")
public int update(Book book);
}
7、服务层接口：
public interface IBookService {
public int addBook(Book book); //插入
public int removeBook(Integer id);//删除
public int modifyBook(Book book);//更新
public List<Book> getBooks();//查询所有
public Book findBookById(Integer id);//按Id查询
public List<Book> getPageBook(Integer start,Integer count);//分页查询
public boolean batchImport(String fileName, MultipartFile file) throws Exception;//导入Excel
public HSSFWorkbook exportToExcel(List<Book> books);//导出到Excel
}
8、服务层实现类：
@Service
@Transactional
public class BookService implements IBookService {
@Autowired
private IBookMapper bookMapper;
@Override
public int addBook(Book book) {
return bookMapper.insert(book);
}
@Override
public int removeBook(Integer id) {
return bookMapper.delete(id);
}
@Override
public int modifyBook(Book book) {
return bookMapper.update(book);
}
@Override
public List<Book> getBooks() {
return bookMapper.findAll();
}
@Override
public Book findBookById(Integer id) {
return bookMapper.findById(id);
}
@Override
public List<Book> getPageBook(Integer start, Integer count) {
return bookMapper.findPageBook(start,count);
}
@Override
public boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notnull = false;
List<Book> bookList = new ArrayList<>();
if(!fileName.matches("^.+\\.(?i)(xlsx)$")){ //
throw new Exception("上传文件格式不正确");
}
boolean isExcel2007 = true;
if(fileName.matches("^.+\\.(?i)(xls)$")){
isExcel2007 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2007) {
wb = new HSSFWorkbook(is);
}else{
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet != null){
notnull = true;
}
Book book = null;
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
book = new Book();
row.getCell(0).setCellType(CellType.STRING);//将每一行第一个单元格设置为字符串类型
String bId =row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
if(bId == null || bId.isEmpty()){
throw new Exception("导入失败(第\"+(r+1)+\"行，图书编号未填写)");
}
Integer bookId = Integer.parseInt(bId);
String bookName = row.getCell(1).getStringCellValue();//得到每一行的第二个单元格的值
if(bookName == null || bookName.isEmpty()){
throw new Exception("导入失败(第\"+(r+1)+\"行，图书名称未填写)");
}
String bookAuthor = row.getCell(2).getStringCellValue();//得到每一行的第二个单元格的值
if(bookAuthor == null || bookAuthor.isEmpty()){
throw new Exception("导入失败(第\"+(r+1)+\"行，图书作者未填写)");
}
row.getCell(3).setCellType(CellType.STRING);
Date bookDate =DateConvert.StringToDate(row.getCell(3).getStringCellValue());//得到每一行的第三个单元格的值(日期型)
if(bookDate == null){
throw new Exception("导入失败(第\"+(r+1)+\"行，图书发行日期未填写)");
}
row.getCell(4).setCellType(CellType.STRING);//将每一行第四个单元格设置为字符串类型
String bPrice =row.getCell(4).getStringCellValue();//得到每一行的第四个单元格的值(日期型)
if(bPrice == null || bPrice.isEmpty()){
throw new Exception("导入失败(第\"+(r+1)+\"行，图书价格未填写)");
}
Double bookPrice = Double.parseDouble(bPrice);
//完整的循环一次 就组成了一个对象
book.setBookId(bookId);
book.setBookName(bookName);
book.setBookAuthor(bookAuthor);
book.setBookDate(bookDate);
book.setBookPrice(bookPrice);
bookList.add(book);
}
for(Book bookResord : bookList){
int id = bookResord.getBookId();
Book b1 = bookMapper.findById(id);
if(b1 == null){
bookMapper.insert(bookResord);
System.out.println("==>插入："+bookResord);
}else{
bookMapper.update(bookResord);
System.out.println("==>更新："+bookResord);
}
}
return notnull;
}
@Override
public HSSFWorkbook exportToExcel(List<Book> books) { //将集合中的数据存储到Execl工作簿中
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("book_info");
HSSFRow row = null;
row = sheet.createRow(0);//创建第一行
row.setHeight((short) 800);// 设置行高
HSSFCell c00 = row.createCell(0); //创建第一个单元格
c00.setCellValue("图书列表");//设置单元格内容
//设置标题样式
c00.setCellStyle(ExcelImportUtils.createTitleCellStyle(wb));
//合并单元格(firstRow:起始行,lastRow:结束行,firstCol:起始列,lastCol:结束列)
CellRangeAddress rowRegion = new CellRangeAddress(0,0,0,4);
sheet.addMergedRegion(rowRegion);
//创建表头行，并设置样式
row = sheet.createRow(1); //创建第二行
row.setHeight((short)500);//设置行高
String[] row_head = {"图书编号","图书名称","图书作者","发行时间","图书单价"};
for(int i=0;i<row_head.length;i++){ //创建表头
HSSFCell tempCell = row.createCell(i);
tempCell.setCellValue(row_head[i]); //设置单元格内容
//设置表头样式
tempCell.setCellStyle(ExcelImportUtils.createHeadCellStyle(wb));
}
//定义表格内容(每行数据)
//集合(books)中有多少个元素就生成多少行
for(int i=0;i<books.size();i++) {
row = sheet.createRow(i + 2);
Book book = books.get(i);
for (int j = 0; j < 5; j++) { //每行有5列
HSSFCell tempCell = row.createCell(j); //设置单元格内容
//设置内容样式
tempCell.setCellStyle(ExcelImportUtils.createContentCellStyle(wb));
if (j == 0) {
tempCell.setCellValue(book.getBookId());
} else if (j == 1) {
tempCell.setCellValue(book.getBookName());
} else if (j == 2) {
tempCell.setCellValue(book.getBookAuthor());
} else if (j == 3) {
tempCell.setCellValue(DateConvert.DateToString(book.getBookDate()));
} else if (j == 4) {
tempCell.setCellValue(book.getBookPrice());
}
}
}
// sheet.setDefaultRowHeight((short)(16.5*20)); 设置默认行高
//列宽自适应
for(int i=0;i<5;i++){
sheet.autoSizeColumn(i);
}
return wb;
}
}
9、工具类：
(1)日期转换：
public class DateConvert {
public static Date StringToDate(String str){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
date = sdf.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
public static String DateToString(Date date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(date);
}
}
(2)分页类：
public class Pagination<T> {
public Integer currentPage; //当前页
public Integer pageSize; //每页显示的记录数
public Integer recordTotal;//记录总数
public Integer pageCount;//总页数
public List<T> pageList;//分页数据
public Pagination() {
this.currentPage = 1;
this.pageSize = 5;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getRecordTotal() {
return recordTotal;
}
public void setRecordTotal(Integer recordTotal) {
this.recordTotal = recordTotal;
if(this.recordTotal % this.pageSize ==0){
this.pageCount = this.recordTotal/this.pageSize;
}else{
this.pageCount = this.recordTotal/this.pageSize + 1;
}
}
public List<T> getPageList() {
return pageList;
}
public void setPageList(List<T> pageList) {
this.pageList = pageList;
}
}
(3)Excel导入导出工具类：
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;
}else{
return true;
}
}
/**
* 创建标题样式
* @param wb
* @return
*/
public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//背景颜色 cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setBold(true); //字体加粗
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
* @param wb
* @return
*/
public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);// 设置自动换行
//背景颜色 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setBold(true); //字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建内容样式
* @param wb
* @return
*/
public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
// 生成12号字体
HSSFFont font = wb.createFont();
font.setColor((short)8);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
return cellStyle;
}
}
10、控制器类：
@CrossOrigin
@RestController
public class BookController {
@Autowired
private IBookService bookService;
private Pagination<Book> pagination = new Pagination<>();
//private Integer records = bookService.getBooks().size();
@GetMapping("/books")
public String getBooks(String CurrentPage,String PageSize){
pagination.setRecordTotal(bookService.getBooks().size());
int current_page = Integer.parseInt(CurrentPage);
int page_size = Integer.parseInt(PageSize);
pagination.setCurrentPage(current_page);
pagination.setPageSize(page_size);
int start = pagination.getCurrentPage()*pagination.getPageSize()-pagination.getPageSize();
pagination.setPageList(bookService.getPageBook(start,pagination.getPageSize()));
ObjectMapper objectMapper = new ObjectMapper();
String pagebooks = null;
try {
pagebooks = objectMapper.writeValueAsString(pagination);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return pagebooks;
}
@GetMapping("/insert")
public boolean insertBook(HttpServletRequest request){
String name = request.getParameter("bookName");
String author = request.getParameter("bookAuthor");
Date date = DateConvert.StringToDate(request.getParameter("bookDate"));
Double price = Double.parseDouble(request.getParameter("bookPrice"));
Book book = new Book(null,name,author,date,price);
int flag = bookService.addBook(book);
return flag>0;
}
@GetMapping("/delete")
public boolean deleteBook(Integer bookId){
int flag = bookService.removeBook(bookId);
return flag>0;
}
@GetMapping("/batchDelete")
public boolean batchDeleteBook(String ids){
String[] str = ids.split(",");
int flag = 0;
for(int i=0;i<str.length;i++){
int id = Integer.parseInt(str[i]);
flag = bookService.removeBook(id);
}
return flag>0;
}
@GetMapping("/update")
public boolean updateBook(HttpServletRequest request){
Integer id = Integer.parseInt(request.getParameter("bookId"));
String name = request.getParameter("bookName");
String author = request.getParameter("bookAuthor");
Date date = DateConvert.StringToDate(request.getParameter("bookDate"));
Double price = Double.parseDouble(request.getParameter("bookPrice"));
Book book = new Book(id,name,author,date,price);
int flag = bookService.modifyBook(book);
return flag>0;
}
@PostMapping("/import")
public boolean exImport(@RequestParam("file") MultipartFile file){
boolean a = false;
String fileName = file.getOriginalFilename();
System.out.println(fileName);
try {
a = bookService.batchImport(fileName,file);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(a);
return a;
}
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<Book> books = bookService.getBooks();
HSSFWorkbook wb = bookService.exportToExcel(books);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=book.xlsx"); //默认Excel名称
wb.write(os);
os.flush();
os.close();
}
}
