点击导出按钮弹出导出选择框
写一个导出按钮
<button class="layui-btn layui-btn-sm" lay-event="importExce" data-method="offset" data-type="auto" class="layui-btn layui-btn-normal">导入Excel</button>
写一个弹出框
<!--导出的上传框-->
<div class="layui-form-item" style="display: none;" id="importDiv">
<div class="layui-upload-drag" id="importExcel">
<i class="layui-icon"></i>
<p>点击上传,或将Excel文件拖拽到此处</p>
<div class="layui-hide" id="importExcelDiv">
<input type="hidden" name="importExcel"/>
</div>
</div>
</div>
//头工具栏事件
table.on('toolbar(aaa)', function(obj){
$(':focus').blur();
var data = obj.data;
//新增
if(obj.event === 'addPhone'){
layer.open({
type: 1,
title:"新增手机",
content: $("#addForm"),
area:['50%','70%'],
success:function (layero,index) {
laydate.render({
elem:"#producedDate",
type:"datetime",
format:"yyyy-MM-dd HH:mm:ss",
trigger: 'click'
})
//新增
selectBrand("/phone/queryBrand.do","brandId1",null);
//新增
selectarea("/phone/queryArea.do","areaId1",null);
//上传图片
layui.$('#uploadDemoView').removeClass('layui-hide').find('img').attr('src', '');
$('#imgpath').val();
}
,btn:["提交","取消"]
,yes:function (index,layero) {
$.ajax({
url:"/phone/addPhone.do",
type:"post",
data:$("#addForm").serialize(),
dataType:"text",
success:function (result) {
if(result){
layer.msg("添加成功");
layer.close(index);
// 重新加载表格数据
tableIns.reload();
}else{
layer.msg("新增失败");
}
},
error:function () {
layer.msg("新增异常");
}
})
}
})
//导出
}else if(obj.event === 'exportExcel'){
// 获取条件查询的form表单
var seachForm = document.getElementById("seachForm");
// 给form表单设置提交路径
seachForm.action = "<%=request.getContextPath()%>/excelController/exportExcel2.do";
// 提交form表单
seachForm.submit();
}else if(obj.event === 'importExce'){
//导入方法
importExce();
}
});
接收发起请求:
//导入Excel
var imports;
function importExce() {
imports = layer.open({
type:1,
title:"导入Excel",
content: $("#importDiv"),
area:['auto'],
anim : 4,
btnAlign : 'c',
})
}
//上传Excel
upload.render({
elem: '#importExcel',
url: '/excelController/importExcel.do',
accept : 'file',
exts : 'xlsx',
done: function(res){
if(res){
layer.msg('导入成功');
layer.close(imports);
tableIns.reload();
}else{
layer.msg('导入失败');
layer.close(imports);
}
}
});
后端控制器
package com.jq.controller;
import com.jq.model.Area;
import com.jq.model.Brand;
import com.jq.model.Phone;
import com.jq.model.vo.PhoneVo;
import com.jq.service.PhoneService;
import com.jq.util.ExcelUtil;
import lombok.AllArgsConstructor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
@Controller
@RequestMapping("excelController")
public class ExcelController {
@Autowired
private PhoneService phoneService;
// 导入Excel
@RequestMapping("importExcel")
@ResponseBody //importExcel是弹出框name值
public Map<String, Object> importExcel(@RequestParam("file") MultipartFile importExcel){
Map<String, Object> map = new HashMap<>();
try {
//1.将文件封装成工作薄
XSSFWorkbook workbook = new XSSFWorkbook(importExcel.getInputStream());
//2.获取工作表的数量
int numberOfSheets = workbook.getNumberOfSheets();
//3.循环所有的工作表,根据工作表下标获取对应的sheet
for (int i = 0; i < numberOfSheets; i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
// 4.获取当前工作表中数据的开始位置
int firstRowNum = sheet.getFirstRowNum();
// 5.获取当前工作表中数据的结束位置
int lastRowNum = sheet.getLastRowNum();
// 6.将文件中的数据封装为一个list保存到数据库中
List<Phone> list = new ArrayList<Phone>();
for (int j = firstRowNum+1; j <= lastRowNum; j++) {
// 获取sheet中的每一行
XSSFRow row = sheet.getRow(j);
// 获取行中单元格的值
Phone phone = getExcelDate(row);
//导入
phoneService.addPhone(phone);
}
}
map.put("state", true);
} catch (IOException e) {
e.printStackTrace();
map.put("state", false);
}
return map;
}
// 获取行中单元格的值并赋值
private Phone getExcelDate(XSSFRow row) {
String name = row.getCell(1).getStringCellValue();
double price = row.getCell(2).getNumericCellValue();
double screen = row.getCell(3).getNumericCellValue();
String isup = row.getCell(4).getStringCellValue();
String os = row.getCell(5).getStringCellValue();
String config = row.getCell(6).getStringCellValue();
Date producedDate = row.getCell(7).getDateCellValue();
Date createDate = row.getCell(8).getDateCellValue();
Date updateDate = row.getCell(9).getDateCellValue();
//地区
String area = row.getCell(10).getStringCellValue();
String brand = row.getCell(11).getStringCellValue();
//图片
String img = row.getCell(12).getStringCellValue();
//给对象赋值
Phone phone = new Phone();
phone.setName(name);
phone.setPrice(price);
phone.setScreen(screen);
//单选
phone.setIsup(isup.equals("是")?1:isup.equals("否")?2:null);
//单选
phone.setOs(os.equals("Android")?1:os.equals("IOS")?2:os.equals("Windows")?3:null);
//多选
phone.setConfig(config.replace("双卡双待", "1").replace("语音控制", "2").replace("指纹解锁", "3").replace("远程控制", "4"));
phone.setProducedDate(producedDate);
phone.setCreateDate(createDate);
phone.setUpdateDate(updateDate);
//地区
phone.setAreaId(area.equals("山东")?1:area.equals("河南")?2:area.equals("北京")?3:null);
//品牌
phone.setBrandId(brand.equals("苹果")?1:brand.equals("oppo")?2:brand.equals("小米")?3:null);
//图片
phone.setImg(img);
return phone;
}
}
效果: