2、前端页面定义layui按钮，弹出导入页面org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 org.apache.poi poi-ooxml-schemas 3.17 org.apache.xmlbeans xmlbeans 2.4.0 org.apache.commons commons-collections4 4.1
//定义按钮 //弹出层位置
导入excel数据
4、后端controller
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
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 com.jincheng.bean.Unit;
import com.jincheng.common.ReadDataByExcel;
import com.jincheng.service.UnitService;
import com.jincheng.util.JsonResult;
import com.jincheng.util.UtilTool;
import lombok.extern.slf4j.Slf4j;
@Controller
@Slf4j
public class UploadController {
@Autowired
private UnitService unitService;
//弹出页面
@RequestMapping("/uploadHtml")
public String uploadHtml(String mode,Model model) {
return "upload/upload";
}
//批量数据添加
@Transactional
@ResponseBody
@PostMapping(value = "upload")
public JsonResult upload(@RequestParam MultipartFile file) {
System.out.println(file.getOriginalFilename());
log.info("Excel批量上传信息");
try {
InputStream inputStream = file.getInputStream();
List> list = ReadDataByExcel.readExcel(inputStream);//自定义读取excel
List unitsList = new ArrayList<>();
for (Map map : list) {
Set keySet = map.keySet();
Unit unit = new Unit();
for (String string : keySet) {
String stt = (String) map.get(string);
String str = stt.trim();
if (string.equals("单位")) {
unit.setUnit(str);
}else if (string.equals("地区")) {
unit.setRegin(str);
}else if (string.equals("地域")) {
unit.setReach(str);
}else if (string.equals("情况说明")) {
unit.setRemark(str);
}
}
unit.setRegin(UtilTool.getRegin(unit.getRegin()));
unit.setReach(UtilTool.getReach(unit.getRegin(), unit.getReach()));
unitsList.add(unit);
}
//批量插入
boolean i = unitService.saveBatch(unitsList);
if (i) {
log.info("数据导入成功");
return JsonResult.success("数据导入成功");
} else {
log.error("数据导入失败!");
return JsonResult.error(-1,"数据导入失败!");
}
} catch (Exception e) {
log.error("数据导入出现异常：{}", e.getMessage());
return JsonResult.error(-1,"数据导入失败，请检查模板数据格式!");
}
}
}
5、自定义读取excel 里面数据 （poi）
import lombok.extern.slf4j.Slf4j;
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.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.linkedHashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class ReadDataByExcel {
public static List> readExcel(InputStream inputStream) {
List> list = new ArrayList<>();
try {
XSSFWorkbook wb0 = new XSSFWorkbook(inputStream);
// 循环Sheets,默认取0，第一个sheet
// 获得Excel表的sheet的数目
int numberOfSheets = wb0.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
//获取sheet工作表
Sheet sheet = wb0.getSheetAt(i);
//获取sheet中第一行行号，表头行 0
int firstRowNum = sheet.getFirstRowNum();
//获取最后一行行号 ，从0开始，20行 ==0---19
int lastRowNum = sheet.getLastRowNum();
// 获取表头数据行
Row rowHead = sheet.getRow(firstRowNum);
for (int j = firstRowNum; j < lastRowNum; j++) { //表头不用参与遍历，所有循环少一次
Row row = sheet.getRow(j + 1);//获取第二行
if (rowHead!=null && !isRowEmpty(row)) {
Map map = new linkedHashMap<>();
//rowHead.getLastCellNum()获取最后一个不为空的列是第几个 从0开始，比实际大一 如：10列==0--10
//rowHead.getPhysicalNumberOfCells()获取不为空的列的个数
for (int k = 0; k < rowHead.getLastCellNum(); k++) {
// 获取表头和数据
Cell cellHead = rowHead.getCell(k);
Cell cell = row.getCell(k);
String cellValue = null;
String headValue = null;
// 处理各种带空数据
if(cellHead!=null) {
headValue = cellHead.toString().trim().replaceAll(" ", "");
}else {
headValue="";
}
if (cell != null && !cell.toString().trim().isEmpty()) {
cellValue = cell.toString().trim().replaceAll(" ", "");
} else {
cellValue = "";
}
// map封装
map.put(headValue, cellValue);
}
// list封装
list.add(map);
}
}
}
try {
inputStream.close();
} catch (Exception e) {
log.error("exception:", e);
}
} catch (Exception e) {
log.error("exception:", e);
}
return list;
}
//判断表行是否为空
public static boolean isRowEmpty(Row row) {
for(int i = row.getFirstCellNum();i
注意：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
