1、导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.6</version>
</dependency>
2、ServiceImpl
@Override
public int ExcelBunk(MultipartFile file) throws Exception {
try {
/**
*
* 判断文件版本
*/
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
InputStream ins = file.getInputStream();
Workbook wb = null;
if (suffix.equals("xlsx")) {
wb = new XSSFWorkbook(ins);
} else {
wb = new HSSFWorkbook(ins);
}
/**
* 获取excel表单
*/
Sheet sheet = wb.getSheetAt(0);
List<CellRangeAddress> combineCell = getCombineCell(sheet);
/**
* line = 6 :从表的第七行开始获取记录
*/
String bunkLibraryId = null;
if (null != sheet) {
for (int line = 6; line <= sheet.getLastRowNum(); line++) {
Row row = sheet.getRow(line);
//如果表格是空的就直接跳出这次循环
if (null == row) {
continue;
}
/**
* 获取第一个单元格的内容
*/
//接收值取到的值
String stg; //获取到的值
String stg2; //获取到的值
String stg3; //获取到的值
String cellValue = getCellValue(row.getCell(0));
if (cellValue != null) {
//获取列
Cell cell1 = row.getCell(0);
stg = getCellValue(cell1);
Cell cell2 = row.getCell(2);
stg2 = getCellValue(cell2);
//判断是不是合并的单元格
//第一个参数是表单,第二个参数的行下标,第三个参数是列下标
if (isMergedRegion(sheet, line, 3) == true) {
//是就取合并的单元格里的值
stg3 = isCombineCell(combineCell, row.getCell(3), sheet);
} else {
//不是就直接取值
Cell cell3 = row.getCell(3);
stg3 = getCellValue(cell3);
}
//把取到的数据拼接成自己想要的格式(可以根据自己的需求自己拼接或者存储在实体类中)
if (stg3 != null && !stg3.equals("") && stg != null && !stg.equals("")) {
if (bunkLibraryId == null) {
bunkLibraryId = stg3 + "," + stg + "," + stg2;
} else {
bunkLibraryId += "、" + stg3 + "," + stg + "," + stg2;
}
}
}
}
// System.out.println("-----------" + bunkLibraryId);
}
//这里把 Excel表中取到的数据存到数据库中
String s = RoomArrangement(bunkLibraryId);
if (s == "ok") {
return 1;
}
} catch (Exception e) {
logger.error("service方法:ExcelBunk()", e);
e.printStackTrace();
}
return 0;
}
/**
* 执行SQL
* @param bunkLibraryId
* @return
*/
public String RoomArrangement(String bunkLibraryId) {
try {
// 这里就根据自己的需求写DAO存储数据
} catch (Exception e) {
logger.error("service方法:RoomArrangement", e);
e.printStackTrace();
}
return "ok";
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 判断单元格是否为合并单元格,是的话则将单元格的值返回
*
* @param listCombineCell 存放合并单元格的list
* @param cell 需要判断的单元格
* @param sheet sheet
* @return
*/
public String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet)
throws Exception {
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
String cellValue = null;
for (CellRangeAddress ca : listCombineCell) {
//获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
Row fRow = sheet.getRow(firstR);
Cell fCell = fRow.getCell(firstC);
cellValue = getCellValue(fCell);
break;
}
} else {
cellValue = "";
}
}
return cellValue;
}
/**
* 合并单元格处理,获取合并行
*
* @param sheet
* @return List<CellRangeAddress>
*/
public List<CellRangeAddress> getCombineCell(Sheet sheet) {
List<CellRangeAddress> list = new ArrayList<>();
//获得一个 sheet 中合并单元格的数量
int sheetmergerCount = sheet.getNumMergedRegions();
//遍历所有的合并单元格
for (int i = 0; i < sheetmergerCount; i++) {
//获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca);
}
return list;
}
/**
* 获取单元格的值(获取不同类型,都返回String)
*
* @param cell
* @return
*/
private String getCellValue(Cell cell) {
String st = null;
if (cell == null || cell.equals("")) {
st = null;
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
//公式
st = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//数值型
st = cell.getNumericCellValue() + "";
st = st.substring(0, st.lastIndexOf("."));
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
//字符串型
st = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
//空值
st = cell.getStringCellValue();
}
return st;
}
3、Controller
@RequestMapping(value = "/import")
//这个返回值是自己定义JSON(自己写个实体类去定义一个JSON,返回给前端)
public CommonResponse excelImport(MultipartFile file) {
CommonResponse responseResult = new CommonResponse();
try {
int aa = excelService.ExcelBunk(file);
if (aa == 1) {
responseResult.setCode(200);
responseResult.setMsg("提交成功!");
} else {
responseResult.setCode(400);
responseResult.setMsg("Excel导入出错!请联系管理员。");
}
} catch (Exception e) {
logger.error("controller方法:excelImport()",e);
e.printStackTrace();
}
return responseResult;
}
4、前端(用的是Layui框架)
<button type="button" class="layui-btn" id="bedExcel"><i class="layui-icon"></i>导入Excel</button>
<script>
$(function () {
layui.use(['table', "form", "layer", 'upload'], function () {
var upload = layui.upload;
upload.render({
elem: '#bedExcel'
, url: '自己的URL路径'
, method: 'get' //默认:get请求
, accept: 'file' //普通文件
,before: function () {
//加载图层(程序未执行完成之前会有一个转圈圈的图层)
upload_coverage = layer.load(2);
}
, done: function (res) {
if (res.code == 200) {
//关闭加载图层
layer.close(upload_coverage);
//回调弹框
layer.alert("绑定成功!", {closeBtn: 0}, function () {
layer.closeAll();
window.location.href = "/roomnumber/pagebunklibrary";
});
} else {
//关闭加载图层
layer.close(upload_coverage);
//回调弹框
layer.alert("Excel导入出错,请联系管理人员!", {closeBtn: 0}, function () {
layer.closeAll();
window.location.href = "/roomnumber/pagebunklibrary";
});
}
}
,error: function(){
//关闭加载图层
layer.close(upload_coverage);
layer.alert("未找到服务器!!", {closeBtn: 0}, function () {
layer.closeAll();
});
}
});
}
<script/>