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

来源：互联网发布：淘宝一直不发货会怎样编辑：程序博客网时间：2024/05/22 12:53

环境

操作系统：win7
jdk： 1.7

需求

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

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

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

改进

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

先上代码：

try {//连接数据库 startMongoCredential 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 就获取到了要统计的账号，上面我写成公共方法，下面会给出代码。//统计登录次数 startBasicDBObject 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啦！

0 0