java 处理excel_Java花式处理EXCEL

java批量处理专题——用户将Excel上传到服务器，服务器如何解析Excel并将其解析为java对象？

image

1. 简单API介绍

1.1 workbook工作空间

由于Excel存在xls以及xlsx两种格式，所以创建方式也有所不同。

对于xls格式，需要使用HSSFWorkbook来创建对象；

对于xlsx格式，需要使用XSSFWorkbook来创建工作薄；

需要注意HSSFWorkBook与XSSSFWorkbook两个类都是Workbook接口的实现类。

String extString = filePath.substring(filePath.lastIndexOf("."));

InputStream is = null;

try {

is = new FileInputStream(filePath);

if (".xls".equals(extString)) {

wb = new HSSFWorkbook(is);

} else if (".xlsx".equals(extString)) {

wb = new XSSFWorkbook(is);

} else {

throw new BusinessException("文件格式错误");

}

首先，我们获取到文件后缀名，然后创建FileInputStream文件输入流。然后，根据文件格式的不同，选择不同的workbook。

1.2 Sheet

看到Excel模型中的sheet1(页面)了吗。其实就是定位到当前sheet进行处理的。

image

同样的创建Sheet的时候，也存在HSSFSheet和XSSFSheet这两种类型。所有我们使用其父类Sheet去处理对应的子类实现。

获取第一个sheet空间:

sheet = wb.getSheetAt(0);

1.3 Row

作用是定位到特定的行。

sheet.getPhysicalNumberOfRows()：返回物理定义的行数(不是工作表中的行数)

sheet.getLastRowNum()：返回此sheet中最后一行的数字编号，默认是从0开始。

sheet = wb.getSheetAt(0);

//获取sheet中，有数据的行数

int rownum = sheet.getPhysicalNumberOfRows();

//因为模板是在第四行开始读取，那么我们的直接定位到第四行

for (int i = 4; i < rownum; i++) {

//获取当前行

Row row = sheet.getRow(i);

if (row != null) {

//开始对cell进行处理。

}

}

1.4 Cell

定位到特定的表格

获取到cell上的数据，进行“业务处理”，当然不同的业务逻辑不同，这里写几个特殊的处理方法。

1. 获取cell上的值，下标从0开始

Cell cell= row.getCell(int index);

2. 判断Cell的单元格格式

image

单元格格式的枚举类型

public interface Cell {

public final static int CELL_TYPE_NUMERIC = 0;

public final static int CELL_TYPE_STRING = 1;

public final static int CELL_TYPE_FORMULA = 2;

public final static int CELL_TYPE_BLANK = 3;

public final static int CELL_TYPE_BOOLEAN = 4;

public final static int CELL_TYPE_ERROR = 5;

}

获取单元格的类型

int type1 = cell.getCellType();

if(type1==Cell.CELL_TYPE_STRING){

//TODO

}

判断单元格是否为空

if (cell == null || org.apache.commons.lang3.StringUtils.isBlank(cell.getStringCellValue())) {

failMap.put(i, "xx为空");

break;

}

设置单元格格式

cell.setCellType(Cell.CELL_TYPE_STRING);

若是文本格式，获取单元格里面的值

在批量上传的时候，推荐模板的单元格格式为文本模式。

cell.getStringCellValue();

如此一来，我们可以将Excel表格里面的数据全部解析出来。

2. 实战中的使用

当然，简单的API可以完成解析的，但是如何在项目中使用呢？

需求：对Excel处理完毕之后，需要记录成功多少笔，失败多少笔。

1. 用户点击上传成功之后，点击前端进行验证：

(1)创建List对象，将解析成功的Excel数据组装成对象保存在集合中；

(2)创建Map对象，保存解析失败的原因和row位置；

@RequestMapping("/validateExcel")

@ResponseBody

public ResponseVo validateExcel() {

ResponseVo vo = new ResponseVo();

//保存解析成功的数据

List successUser= new ArrayList();

//

Map rowSucMap = new HashMap();

//

Map failMap = new HashMap();

int count = 0;

try{

//Redis中取出文件名【上传的时候存入Redis中】

String key = "XXXX";

String fileName = jedisCluster.get(key);

if(StringUtils.isBlank(fileName)){

vo.setRetcode(ResponseVo.FAIL);

vo.setMessage("无批量文件需要验证");

return vo;

}

//获取文件

File source = new File(fileName);

count = batchUserService.filePaserExcel(successUser,source,rowSucMap,failMap);

//数据入库或调用远程接口

batchUserService.saveData(successUser);

//将错误信息写入到(原)Excel中

batchUserService.executeFailExcel(source.getAbsolutePath(),rowSucMap,failMap);

vo.setRetcode(ResponseVo.SUCC);

vo.setMessage("批量提现信息校验成功");

Map resultMap = new HashMap();

//写入成功笔数

resultMap.put("success", rowSucMap.size());

//写入失败笔数

resultMap.put("error", count - rowSucMap.size());

//保存到vo中，返回给前端

vo.setData(resultMap);

}catch (Exception e){

vo.setRetcode(ResponseVo.FAIL);

vo.setMessage("批量信息校验 原因：" + e.getMessage());

logger.error("批量信息校验 原因：" + e);

}

return vo;

}

解析原文件

调用filePaserExcel(解析成功的对象，目标文件，解析成功Map，解析失败的Map)方法,解析Excel对象。

public int fileParseExcel(List successUser, File file,Map rowSucMap, Map failMap){

String filePath = file.getAbsolutePath();

if (filePath == null) {

throw new BusinessException("文件路径为空");

}

String extString = filePath.substring(filePath.lastIndexOf("."));

InputStream is = null;

int count = 0;

try {

//获取WorkBook对象

is = new FileInputStream(filePath);

if (".xls".equals(extString)) {

wb = new HSSFWorkbook(is);

} else if (".xlsx".equals(extString)) {

wb = new XSSFWorkbook(is);

} else {

throw new BusinessException("文件格式错误");

}

int count = 0;

if (wb != null) {

//获取sheet对象

sheet = wb.getSheetAt(0);

//获取Sheet中的实际行

int rownum = sheet.getPhysicalNumberOfRows();

String type = "";

//在第四行进行解析

for (int i = 4; i < rownum; i++) {

Row row = sheet.getRow(i);

if (row != null) {

User user= new User();

//获取第一个单元格

Cell userId= row.getCell(0);

//默认模板已经是文本格式的

if (userId== null || org.apache.commons.lang3.StringUtils.isBlank(userId.getStringCellValue())) {

//因为sheet可能存在空白行，所以判断第一列为空则代表Excel遍历完毕

failMap.put(i, "用户为空");

break;

}

userId.setCellType(Cell.CELL_TYPE_STRING);

//每遍历完一行，count++

count++;

Cell age = row.getCell(1);

if (age == null || org.apache.commons.lang3.StringUtils.isBlank(age .getStringCellValue())) {

failMap.put(i, "年龄为空");

continue;

}

age .setCellType(Cell.CELL_TYPE_STRING);

age = age.getStringCellValue().trim();

//判断年龄是否满足其他业务

if(!age.contains(type)) {

failMap.put(i,"XXXXX失败");

continue;

}

//TODO其他字段验证

//将数据保存到user对象中

user.setUserId(userId.getStringCellValue().trim());

user.setUserId(age.getStringCellValue().trim());

//存到List中，入库或调用远程接口

sucAccount.add(batchWithdraw);

//将成功的Excel行号和userId保存到Map中

rowSucMap.put(i,userId.getStringCellValue().trim());

}

//遍历结束

}

} catch (FileNotFoundException e) {

logger.error(e.getMessage());

} catch (IOException e) {

logger.error(e.getMessage());

} catch (Exception e) {

e.printStackTrace();

}

//返回一共处理多少行

return count;

}

写入错误信息

对EXCEL写入错误信息，删除处理成功的行；

public void executeFailExcel(String filePath, Map rowSucMap, Map failMap) {

InputStream is = null;

FileOutputStream fout = null;

try {

Workbook wb = null;

if (filePath == null) {

throw new BusinessException("文件路径为空");

}

String extString = filePath.substring(filePath.lastIndexOf("."));

is = new FileInputStream(filePath);

if (".xls".equals(extString)) {

wb = new HSSFWorkbook(is);

} else if (".xlsx".equals(extString)) {

wb = new XSSFWorkbook(is);

}

//直接对上传的文件进行操作。

fout = new FileOutputStream(filePath);

if (wb != null) {

Sheet sheet = wb.getSheetAt(0);

//遍历keyset，keyset()中保存失败的行号

for (int rowNum : failMap.keySet()) {

Row row = sheet.getRow(rowNum);

if (row != null) {

Cell errMsg = row.createCell(9);

errMsg.setCellValue(failMap.get(rowNum));

}

}

//移除处理成功的行号

for (int rowNum : rowSucMap.keySet()) {

Row row = sheet.getRow(rowNum);

if (row != null) {

sheet.removeRow(row);

sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1);

}

}

}

//将wb写入到输出流

wb.write(fout);

} catch (FileNotFoundException e) {

logger.error(e.getMessage());

} catch (IOException e) {

logger.error(e.getMessage());

}

}

需要注意：

三个参数：起始位置n，终止位置m。-1代表(n-m)这个区间向上移动一位

sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1);

此时，处理后的Excel已经保存在了服务器的位置。