Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: pass (nike.apache.org: domain of nick.burch@alfresco.com
 designates 207.126.144.147 as permitted sender)
Date: Thu, 11 Aug 2011 16:13:00 +0100 (BST)
From: Nick Burch <nick.burch@alfresco.com>
To: POI Users List <user@poi.apache.org>
Subject: Re: extra some records in the excel using apache pois
In-Reply-To: 
 <CA+FxMdW+uuFN-_2CHNd-Zju5x-fyqz4jG=mShHDtc7HmbawW2g@mail.gmail.com>
Message-ID: <alpine.DEB.2.00.1108111612410.15722@urchin.earth.li>
References: 
 <CA+FxMdVCPoO0VJsRxHWP9O4TEoo5s_6tPOjK90uUyF6TKt0CPw@mail.gmail.com>
 <CA+FxMdW+uuFN-_2CHNd-Zju5x-fyqz4jG=mShHDtc7HmbawW2g@mail.gmail.com>
User-Agent: Alpine 2.00 (DEB 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Thu, 11 Aug 2011, maven apache wrote:
> Can anyone give me some suggestion?

Why not just write the records as you identify they're needed?

Nick

> 2011/8/7 maven apache <apachemaven0@gmail.com>
>
>> 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?
>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org