java poi写入excel文件_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);

Listaddrs = new ArrayList();

addrs.add(serverAddress);

Listcredentials = new ArrayList();

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");

SetaccountNameSet = 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));

Iteratoriterator = output.results().iterator();

MapcountMap = new HashMap();

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 SetgetAccountNameSet(int sheetInt, int cellInt, File file) throws FileNotFoundException {

if(file == null || !file.isFile()){

throw new FileNotFoundException("文件名不能为空或文件的后缀格式不对");

}

FileInputStream in;

SetaccountNameSet = new HashSet();

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, MapcountMap, 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啦！