poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maven apache <apachemav...@gmail.com>
Subject Re: extra some records in the excel using apache pois
Date Fri, 12 Aug 2011 11:15:20 GMT
In fact,when I get the rows which match my requirement,I want to write them
directly,however I have not found any api that can make it.

For exmaple:

Sheet st=wb.createSheet(0);
st.setRows(mySelectedRows);

2011/8/11 Nick Burch <nick.burch@alfresco.com>

> 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<user-unsubscribe@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message