Can anyone give me some suggestion?
2011/8/7 maven apache <[email protected]>
> I am using the apache poi to handle the .xlsx file.
>
> I have two .xlsx files, part.xlsx,and full.xlsx,
>
> they own the same structure.
>
> Each record(The Row object in the poi) have three colmn:name,age,location.
>
> There are almost 5000 rows in the part.xlsx,and 40000 rows in the
> full.xlsx.
>
> Now I want to extra the rows from the full.xlsx who have the same value
> with that in the part.xlsx.
>
> for example:
>
> The part.xlsx:
>
> Name age location
> kk 23 USA
> bb 24 England
> ......
>
> The full.xlsx
>
> Name age location
> kk 23 USA
> bb 24 England
> xx 25 USA
> ......
>
> Now I want to extra the 'kk' and 'bb' rows and save them to a new file.
>
> This is the code code:
> -------------------------------------------------------
> List<User> usersInpart=new ArrayList<User>();
> List<Row> rows_to_be_saved=new ArrayList<Row>();
>
> //read the part.xlsx and save them.
> FileInputStream fis_part=new FileInputStream("part.xlsx");
> WorkBook wb_part=WorkbookFactory.create(fis_part);
> Sheet st_part=wb_part.getSheetAt(0);
> for(Row row : st_part){
> if(row.getRowNum()==0) continue; //skip the first row(the title)
> User u=new User();
> u.setName(row.getCell(0).getRichStringValue().getString().trim());
> u.setAge(row.getCell(1).getNumericCellValue());
> u.setLocation(row.getCell(2).getRichStringValue().getString().trim());
> usersInpart.add(u);
> }
> fis_part.close();
>
>
> //read the full.xlsx
>
> FileInputStream fis_full=new FileInputStream("full.xlsx");
> WorkBook wb_full=WorkbookFactory.create(fis_full);
> Sheet st_full=wb_full.getSheetAt(0);
> for(Row row : st_full){
> if(row.getRowNum()==0) continue; //skip the first row(the title)
>
> String name=row.getCell(0).getRichStringValue().getString().trim();
> double age=row.getCell(1).getNumericCellValue();
> String location=row.getCell(2).getRichStringValue().getString().trim();
>
> for(User u : usersInpart){
> if(u.getName.equals(name) && u.getAge==age &&
> u.getLocation().equals(location))
> rows_to_be_saved.add(row);
> }
> }
> fis_full.close();
>
> //write the selected rows to file
>
> WorkBook wb_res=WorkbookFactory.create(fis_full);
> Sheet st_res=wb_res.createSheet(0);
>
> int i=0;
> for (Row row : rows_to_be_saved) {
> Row rw=st_res.createRow(i);
>
> int k=0;
> for (Cell cell : row) {
> switch (cell.getCellType()) {
> case Cell.CELL_TYPE_STRING:
>
> rw.createCell(k).setCellValue(cell.getRichStringCellValue().getString());
> break;
> case Cell.CELL_TYPE_NUMERIC:
> if (DateUtil.isCellDateFormatted(cell)) {
>
> rw.createCell(k).setCellValue(cell.getDateCellValue());
> } else {
>
> rw.createCell(k).setCellValue(cell.getNumericCellValue());
> }
> break;
> case Cell.CELL_TYPE_BOOLEAN:
>
> rw.createCell(k).setCellValue(cell.getBooleanCellValue());
> break;
> case Cell.CELL_TYPE_FORMULA:
> rw.createCell(k).setCellValue(cell.getCellFormula());
> break;
> default:
> }
> k++;
> }
> i++;
> }
> //save the wb_res
> wb_res.write(new FileOutputStrem("xx.xlsx"));
> ---------------------------------------------------------------------------
> now I wonder any good ideas to save the file?
>
> Since I have saved the selected rows in the "rows_to_be_saved".
>
> And I create the new sheet "st_res",any way I can save these rows to the
> "st_res" directly? Since now,I have create each row according the rows in
> the "rows_to_be_saved".
>
> So there will be two List of rows. I think this is a waste of memory.
>
> Any suggestion?
>
>
