【无标题】

POI excel读取

1.引包

org.apache.poi
poi
3.9


org.apache.poi
poi-ooxml
3.9

2.核心代码
//controller 接收
@RequestParam(required = false) MultipartFile jcrjstz_uploadfile

String name = jcrjstz_uploadfile.getOriginalFilename();
String type = FilenameUtils.getExtension(name);
AjaxResult res = new AjaxResult();
if (jcrjstz_uploadfile.isEmpty()) {
res.setTotalcount(101);
res.setMsg(“导入文件为空！”);
return res;
}
if (!“xlsx”.equalsIgnoreCase(type) && !“xls”.equalsIgnoreCase(type)) {
res.setTotalcount(101);
res.setMsg(“请按下载模板格式上传文件！”);
return res;
}
bean.setFiletype(type);
long size= jcrjstz_uploadfile.getSize();
if (size>210241024) {
res.setTotalcount(101);
res.setMsg(“文件大小超过2MB,请按要求上传文件！”);
return res;
}
bean.setFiles(jcrjstz_uploadfile.getBytes());

===================================
取值赋值
if (“xlsx”.equalsIgnoreCase(filetype)){
XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bytearray));
logger.info("--------------0002—0003–001");
XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0); //取表
//取行
//XSSFRow titleRow = sheet.getRow(0);//标题
int row_len=sheet.getPhysicalNumberOfRows();
logger.info("--------------0002—0003–002:"+row_len);
for (int rowIndex = 1; rowIndex <row_len ; rowIndex++) {
XSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
//Map<String, String> map = new LinkedHashMap<String, String>();
SqlParameterExt spx1 = new SqlParameterExt(false);
grzh=getString(row.getCell(1),filetype);
spx1.add(new StringValue(grzh)); // 含有excel个人账号

	lst.add(spx1);
	/*
	//循环取每个单元格(cell)的数据
	int cell_len=xssfRow.getPhysicalNumberOfCells();
	for (int cellIndex = 0; cellIndex < cell_len; cellIndex++) {
		//XSSFCell titleCell = titleRow.getCell(cellIndex);
		XSSFCell xssfCell = xssfRow.getCell(cellIndex);
		//map.put(cellIndex+"",getString(xssfCell,XSSFCell.class));
	}
	//list.add(map);
	 */
}
logger.info("--------------0002---0003--003");
//处理数据

}else if(“xls”.equalsIgnoreCase(filetype)){
logger.info("--------------0002—0003–002");
HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytearray));
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0); //
//取行
//XSSFRow titleRow = sheet.getRow(0);//标题
int row_len=sheet.getPhysicalNumberOfRows();
logger.info("--------------0002—0003–002:"+row_len);
for (int rowIndex = 1; rowIndex <row_len ; rowIndex++) {
HSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
SqlParameterExt spx1 = new SqlParameterExt(false);
grzh=getString(row.getCell(1),filetype);
spx1.add(new StringValue(grzh)); // 含有excel个人账号
lst.add(spx1);

}
logger.info("--------------0002---0003--003");

}