java poi对同一个excel文件进行读写

环境

操作系统：win7
jdk： 1.7

需求

1、产品部的同事经常给我一批账号，然后去统计这些账号的登录次数。
他们一般给我这样的一份文件：

①我一般的做法就是先把这个账号全部读出来，用set集合装好后，再去统计登录次数。
②把统计好的结果导出到新的excel中。

关键上面第二步，产品部的人，不得不手动的重复复制粘贴操作。时间长了，他们就有意见啦。

改进

以前我总是导出到新的excel中，是因为我不知道怎么对同一份excel进行读写。
所以我特意研究下，原来那么简单。

先上代码：

try {
//连接数据库 start
MongoCredential credential = MongoCredential.createMongoCRCredential("gg_openapi", "gg_openapi", "gg..openapi#!".toCharArray());
ServerAddress serverAddress = new ServerAddress("106.75.51.20", 35520);
List<ServerAddress> addrs = new ArrayList<ServerAddress>();  
      addrs.add(serverAddress);
      List<MongoCredential> credentials = new ArrayList<MongoCredential>();
      credentials.add(credential);
      MongoClient mongoClient = new MongoClient(addrs, credentials);
      //连接数据库 end
    DB db = mongoClient.getDB("gg_openapi");

    DBCollection useropRecord = db.getCollection("userop_record");


File file = new File("C:\\Users\\yutao\\Desktop\\需要统计的报表0508.xlsx");
Set<String> accountNameSet = ExcelPoiCommon.getAccountNameSet(0, 2, file);
// 上面accountNameSet 就获取到了要统计的账号，上面我写成公共方法，下面会给出代码。
//统计登录次数 start
BasicDBObject match = new BasicDBObject();

match.append("type", new BasicDBObject("$in", new Object[]{0,1,2}));
match.append("code", "S2_l09");
match.append("account_name", new BasicDBObject("$in", accountNameSet));
BasicDBObject timeQuery = new BasicDBObject();
timeQuery.append("$lte", DateUtil.stringToDate("2016-12-31", "yyyy-MM-dd"));
timeQuery.append("$gte", DateUtil.stringToDate("2016-03-01", "yyyy-MM-dd"));
match.append("createtime", timeQuery);

            BasicDBObject group = new BasicDBObject();
            group.append("_id", "$account_name");
            group.append("loginCount", new BasicDBObject("$sum", 1));

            AggregationOutput output = useropRecord.aggregate(new BasicDBObject("$match", match), 
                                   new BasicDBObject("$group", group));

            Iterator<DBObject> iterator = output.results().iterator();

            Map<String, Object> countMap = new HashMap<String, Object>();
            while(iterator.hasNext()){
                DBObject o = iterator.next();
                countMap.put(o.get("_id").toString(), o.get("loginCount"));
            }
            //统计登录次数 end 得到了 countMap key:账号，value：次数
            //导出到同一份excel
            ExcelPoiCommon.readWriteExcel(file, countMap, 0, 2, 5);
        }catch (IOException e) {
            e.printStackTrace();
        }

①上面连接的数据库是mongodb库，版本是2.4

②ExcelPoiCommon.getAccountNameSet(0, 2, file);代码如下：

/**
     * 获得文件中account_name的集合
     * 
     * @param sheeetInt excel表中哪个sheet(从0开始)
     * @param cellInt excel表中哪一列开始读(从0开始)
     * 
     * @author yutao
     * @return 
     * @throws FileNotFoundException 
     * @date 2016年12月9日下午4:14:50
     */
    public static Set<String> getAccountNameSet(int sheetInt, int cellInt, File file) throws FileNotFoundException {
        if(file == null || !file.isFile()){
            throw new FileNotFoundException("文件名不能为空或文件的后缀格式不对");
        }
        FileInputStream in;
        Set<String> accountNameSet = new HashSet<String>();
        try {
            String name = file.getName();
            name = name.substring(name.lastIndexOf(".")+1);
            in = new FileInputStream(file);
            Workbook workbookIn = null;
            Sheet sheetAt = null;
            if("xlsx".equals(name)){
                workbookIn = new XSSFWorkbook(in);
                sheetAt = workbookIn.getSheetAt(sheetInt);
                System.out.println(sheetAt.getSheetName());
            }else if("xls".equals(name)){
                workbookIn = new HSSFWorkbook(in);
                sheetAt = workbookIn.getSheetAt(sheetInt);
            }else{
                throw new FileFormatException("文件的后缀格式不对");
            }

            int firstRowNum = sheetAt.getFirstRowNum();
            int lastRowNum = sheetAt.getLastRowNum();
            // 拿到所有的账号
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheetAt.getRow(i);
                Cell cell = row.getCell(cellInt);
                if(cell == null){
                    continue;
                }
                String cellValue = cell.getStringCellValue().trim();
                accountNameSet.add(cellValue);
            }
            workbookIn.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return accountNameSet;
    }

③ExcelPoiCommon.readWriteExcel(file, countMap, 0, 2, 5);代码如下：

/**
     * 读写同一份excel文件
     * @param file 需要读写的文件
     * @param countMap 统计的map集合（key:账号，value：次数）
     * @param sheetNum 读取哪个sheet（从0开始数）
     * @param cellNum 读哪一列（从0开始数）
     * @param writeCellNum 写入到哪一列（从0开始数）
     * @author yutao
     * @date 2017年5月9日下午2:50:45
     */
    public static void readWriteExcel(File file, Map<String, Object> countMap, int sheetNum, int cellNum, int writeCellNum){
        FileInputStream in;
        String name = file.getName();
        name = name.substring(name.lastIndexOf(".")+1);
        try {
            in = new FileInputStream(file);
            Workbook workbookIn = null;
            Sheet sheetAt = null;
            if("xlsx".equals(name)){
                workbookIn = new XSSFWorkbook(in);
                sheetAt = workbookIn.getSheetAt(sheetNum);
            }else if("xls".equals(name)){
                workbookIn = new HSSFWorkbook(in);
                sheetAt = workbookIn.getSheetAt(sheetNum);
            }else{
                throw new FileFormatException("文件的后缀格式不对");
            }
            System.out.println("----readWriteExcel---sheet---" + sheetAt.getSheetName());

            int firstRowNum = sheetAt.getFirstRowNum();
            int lastRowNum = sheetAt.getLastRowNum();
            // 根据账号、写入统计次数
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row rowReload = sheetAt.getRow(i);
                Cell cellReload = rowReload.getCell(cellNum);
                if(cellReload == null){
                    continue;
                }
                String cellValue = cellReload.getStringCellValue().trim();
                Cell cell5 = rowReload.getCell(writeCellNum);
                if(cell5 ==null){
                    cell5 = rowReload.createCell(writeCellNum);
                }
cell5.setCellValue(countMap.get(cellValue)==null?0:Integer.valueOf(countMap.get(cellValue).toString()));
            }
            in.close();
            FileOutputStream out = new FileOutputStream(file);
            workbookIn.write(out);
            workbookIn.close();
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

总结

区别：

以前导出到新的excel我是这么写的：

XSSFWorkbook workbook = new XSSFWorkbook();

而现在是：

workbookIn = new XSSFWorkbook(in);

剩下的就OK啦！