1.导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
2.代码
package com.ynsj.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtil {
private final static String excel2003L = ".xls"; //2003- 版本的excel
private final static String excel2007U = ".xlsx"; //2007+ 版本的excel
/**
* Excel导入
*/
public static List<Map> getBankListByExcel(InputStream in, String fileName) throws Exception {
List<Map> list = new ArrayList<>(128);
//创建Excel工作薄
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
// 获取第一个sheet
sheet = work.getSheetAt(0);
if (sheet == null) {
return null;
}
// 读取第一行,作为结果集的标题
Map<Integer, Object> title = new HashMap(16);
//读取第一行
row = sheet.getRow(sheet.getFirstRowNum());
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
title.put(i, row.getCell(i).toString());
}
row = null;
// 给每一行赋值,不包括标题行
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
Map content = new HashMap(row.getLastCellNum());
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
if (row.getCell(j) == null) {
continue;
}
cell = row.getCell(j);
content.put(title.get(j).toString(), getCellValue(cell).toString());
}
list.add(content);
}
return list;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
wb = new HSSFWorkbook(inStr); //2003-
} else if (excel2007U.equals(fileType)) {
wb = new XSSFWorkbook(inStr); //2007+
} else {
throw new Exception("解析的文件格式有误!");
}
return wb;
}
/**
* 描述:对表格中数值进行格式化
*/
public static Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
String dataFormatString = cell.getCellStyle().getDataFormatString();
if ("General".equals(dataFormatString)) {
value = df.format(cell.getNumericCellValue());
} else if (dataFormatString.contains("yy")) { // 包含日期格式的年份的话,就格式化成年份
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
3.测试代码
@ApiOperation(value = "新增会员 - 导入表格", notes = "新增会员 - 导入表格")
@PostMapping("import_excel")
public Map<String, Object> importExcel(@RequestParam(value = "file", required = true) MultipartFile file, HttpServletResponse response, HttpSession session) throws Exception {
try {
Map result = new HashMap(8);
//使用工具类, 获取到excel中的数据
List<Map> rowlist = ExcelUtil.getBankListByExcel(file.getInputStream(), file.getOriginalFilename());
result.put("result", CollectionUtils.isEmpty(rowlist) ? new HashMap<>(1) : rowlist.get(0));
return result;
} catch (Exception e) {
this.log.error(e.getMessage(), e);
throw e;
}
}
4.测试结果
5.文章参考链接
https://blog.csdn.net/zzzgd_666/article/details/80583969