可能有的地方不太严谨,欢迎指正
需要的jar包有:
poi-3.17.jar poi-ooxml-3.17.jar poi-ooxml-schemas-3.17.jar xmlbeans-3.0.2.jar dom4j-1.6.1.jar
// excel回显
@RequestMapping(value = "/excel", produces = "application/json;charset=UTF-8")
@ResponseBody
public List excel(HttpServletRequest request, HttpServletResponse response) throws Exception {
List a = new ArrayList<>();
// 将当前上下文初始化给 CommonsMutipartResolver (多部分解析器)
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(equest.getSession().getServletContext());
// 检查form中是否有enctype="multipart/form-data"
if (multipartResolver.isMultipart(request)) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("ends");
return js.excel(file);
} else {
return a;
}
}
// excel导入
public List<Object> excel(MultipartFile file) throws IOException, SQLException, EncryptedDocumentException, InvalidFormatException {
List<Object> a = new ArrayList<Object>();
String fileName = file.getOriginalFilename();
InputStream is = null;
is = file.getInputStream();
if (is == null) {
String str = "文件为空";
a.add(str);
return a;
} else {
Workbook excel = WorkbookFactory.create(is);
//当将文件定义为子类时需要的判断条件太多,且复杂,所以我这里直接定义了一个父类
/*if (fileName.endsWith("xls")) {
excel = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
excel = new XSSFWorkbook(is);
} else {
a.add("文件格式错误");
return a;
}*/
Sim sim = null;
List<Object> list = new ArrayList<Object>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < excel.getNumberOfSheets(); numSheet++) {
Sheet sheet = excel.getSheetAt(numSheet);
if (sheet == null)
continue;
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
DecimalFormat df = new DecimalFormat("#");
Row row = sheet.getRow(rowNum);
if (row == null)
continue;
sim = new Sim();
// 循环列Cell
// 0卡号 1时间 2运营商
Cell cell0 = row.getCell(0);
if (cell0 == null)
continue;
/*cell.getCellType(): CELL_TYPE_NUMERIC 数值型 0,CELL_TYPE_STRING 字符串型 1,
CELL_TYPE_FORMULA 公式型 2,CELL_TYPE_BLANK 空值 3,CELL_TYPE_BOOLEAN 布尔型 4,
CELL_TYPE_ERROR 错误 5
*/
//数值型
if (cell0.getCellType() == 0) {
sim.setSimId(df.format(cell0.getNumericCellValue()));
} else {
sim.setSimId(cell0.getStringCellValue());
}
System.out.println("打印信息-->" + sim.getSimId());
Cell cell1 = row.getCell(1);
if (cell1 == null)
continue;
//当单元格数据为日期格式时查到的数据仍为数值型,且为long类型数据,使用以下方法可以转化为自己想要的格式
if (cell1.getCellType() == 0) {
double value = cell1.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
sim.setTime(new SimpleDateFormat("yyyy-MM-dd").format(date));
} else {
sim.setTime(cell1.getStringCellValue());
}
System.out.println("打印信息-->" + sim.getTime());
Cell cell2 = row.getCell(2);
if (cell2 == null)
continue;
sim.setOperator(cell2.getStringCellValue());
System.out.println("打印信息-->" + sim.getOperator());
list.add(sim);
}
}
return list;
}
}