java使用POI进行Excel操作

Java花式处理EXCEL

zwb_jianshu关注

2019.07.04 10:04:07字数 902阅读 2,407

批量处理1——文件的上传（bootstrap+Ajax+SSM）
批量处理2——Java花式处理EXCEL
批量处理3——Excel文件导出
批量处理4——java处理压缩文件

Java知多少——相对路径和绝对路径
HTTP知多少——Content-disposition（文件下载）

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

推荐阅读：解析EXCEL的API方法

 

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

作用是定位到特定的行。

getPhysicalNumberOfRows和getLastRowNum的区别这两个方法都是判断sheet是否有数据。
区别：

• 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<BatchUser> successUser= new ArrayList();
        //<处理失败的Excel行数，Excel上的第一个cell的数据>
        Map<Integer ,String> rowSucMap = new HashMap<Integer ,String>();
        //<处理失败的Excel行数，Excel上的第一个cell的数据>
        Map<Integer ,String> failMap = new HashMap<Integer ,String>();

        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<String, Object> resultMap = new HashMap<String, Object>();
            //写入成功笔数
            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<CliBatchWithdraw> successUser, File file,Map<Integer, String> rowSucMap, Map<Integer, String> 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<Integer, String> rowSucMap, Map<Integer, String> 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已经保存在了服务器的位置。

文章参考：
使用POI进行Excel操作的总结一——创建Workbook，Sheet，Row以及Cell

拒绝贫穷

 

使用POI进行Excel操作的总结一——创建Workbook，Sheet，Row以及Cell

前段时间，看在其他的网站上给出Excel文档的导入与导出操作，感觉很酷的样子，所以就学习了一下如何使用POI进行Excel的操作，现在对之前的学习过程进行一个总结。

一、现在普遍使用的Excel文档有xls以及xlsx这两种Excel文档，其中xls格式的Excel文档又分为5.0/95工作簿以及97-2003工作簿这两种。需要注意的是，由于5.0/95工作簿的版本太低，现在的POI文档不再支持这种类型的Excel文档的读取工作，当试图读取这种类型的Excel文档的时候，POI会抛出一个异常（OldExcelFormatException）。我现在使用的POI是3.14版本。

二、Workbook的创建

1、由于Excel中存在xls以及xlsx这两种格式，所以创建方式也有所不同。其中对于xls格式的文档，需要使用HSSFWorkbook来创建工作簿对象，而对于xlsx格式的Excel文档，则需要使用XSSFWrokbook来创建工作簿。有一点需要注意的是，HSSFWorkbook与XSSFWorkbook这两个类其实都是Workbook接口的一个实现类。好了，下面就是创建工作簿对象的代码：

    //创建一个不存在的excel文件
    private static Workbook createWorkbookIfNotExist(String fileName) throws Exception {
        Workbook wb = null;
        
        if(fileName.endsWith(".xls")) {
            wb = new HSSFWorkbook();
        } else if(fileName.endsWith(".xlsx")) {
            wb = new XSSFWorkbook();
        } else {
            throw new Exception("文件类型错误！");
        }
        
        try{
            OutputStream output = new FileOutputStream(fileName);
            wb.write(output);
        }catch(FileNotFoundException e) {
            System.out.println("文件创建失败，失败原因为：" + e.getMessage());
            throw new FileNotFoundException();
        }
        System.out.println(fileName + "文件创建成功！");
        
        return wb;
    }
    
    //创建一个新的或者已存在的Excel文档的Workbook
    public static Workbook createWorkbook(String fileName) throws Exception {
        InputStream input = null;
        Workbook wb = null;
        
        try{
            input = new FileInputStream(fileName);
            wb = WorkbookFactory.create(input);
        } catch(FileNotFoundException e) {
            System.out.println("要打开的文件不存在，正试图创建该文件，请稍后……！");
            wb = createWorkbookIfNotExist(fileName);
        } catch(OldExcelFormatException e) {
            System.out.println("文件打开失败，原因：要打开的Excel文件版本过低！");
            throw new OldExcelFormatException("文件版本过低");
        } finally {
            if(input != null) {
                input.close();
            }
        }
        
        return wb;
    }

2、创建Sheet的时候，同样的也存在HSSFSheet以及XSSHSheet这两种类型。同样的HSSFSheet以及XSSFSheet这两个类也是Sheet接口的实现类。如果直接使用实现类进行创建Sheet的话，那么，对于不同的格式，需要使用不同的实现方式，尽管实现的方式都一致。由于Java的多态关系，在这里，我使用Sheet接口进行处理。代码：

    //创建sheet
    public static Sheet createSheet(Workbook wb , String sheetName) {
        Sheet sheet = wb.getSheet(sheetName);
        
        if(sheet == null) {
            System.out.println("表单" + sheetName + "不存在，试图创建该sheet，请稍后……");
            sheet = wb.createSheet(sheetName);
            System.out.println("名为" + sheetName +"的sheet创建成功！");
        }
        
        return sheet;
    }

3、对于Row的创建，也是一样，也是使用Row这个接口进行实现。代码：

    //创建行row
    public static Row createRow(Sheet sheet , int rowNum) {
        Row row = sheet.getRow(rowNum);
        
        if(row == null) {
            System.out.println("行号为：" + rowNum + "的行不存在，正试图创建该行，请稍后……");
            row = sheet.createRow(rowNum);
            System.out.println("行号为：" + rowNum + "的行创建成功！");
        }
        
        return row;
    }

4、对于Cell也是一样，同样使用Cell接口进行编程。代码：

   //创建单元格cell
    public static Cell createCell(Row row , int cellNum) {
        Cell cell = row.getCell(cellNum);
        
        if(cell == null) {
            System.out.println("该单元格不存在，正在试图创建该单元格，请稍后……");
            cell = row.createCell(cellNum);
            System.out.println("该单元格创建成功！");
        }
        
        return cell;
    }

以上几个操作就是Workbook、Sheet、Row以及Cell的创建工作。另外，当对Excel文档操作完成之后，需要使用Workbook的write方法保存一下，然后上述的改动才会保存在你创建的Excel文档中。代码：

wb03.write(new FileOutputStream("C:\Users\Administrator\Desktop\03styleExcel.xls"));
wb07.write(new FileOutputStream("C:\Users\Administrator\Desktop\07styleExcel.xlsx"));

其中wb03就是使用上述方法创建的xls格式的Excel文档，wb07则是xlsx格式的文档。

好了，这篇博文到这里就结束了。如果文章中有什么错误或不足的地方，希望各位大侠多多指点。后面的博文，会陆续介绍如何往Excel文档中写入不同格式的数据，以及边框、图片、合并单元格、隐藏与显示行等操作。