Skip to content
Advertisement

How to copy a particular row from xsls file and export it to new xlsx file using Apache poi?

I have a folder with 216 xsls files. My logic is to loop throw the folder and read every file, copy the first row of each file and write that row to a new .xsls file.

I want to copy the first row without iterating throw the row and read every cell? for example, if you copy the first line in the .txt file or .csv file.

Also, this is a very slow process. Do you have tips to speed up this process?

 public static void listFilesForFolder(final File folder,final File exported, int skipLine) {
    try {
        for (final File fileEntry : folder.listFiles()) {
            
            String ext = fileEntry.getName().substring(fileEntry.getName().lastIndexOf('.') + 1);
            
            System.out.println("-->> " + fileEntry.getName() + "-->>");
            
            FileInputStream file = new FileInputStream(new File(fileEntry.getPath()));
            
            if (fileEntry.isDirectory()) {
                continue;
            } else if(ext.equals("xlsx")) {
                
                XSSFWorkbook myWorkBook = new XSSFWorkbook(file);
                XSSFSheet mySheet = myWorkBook.getSheetAt(0);
                
                Iterator<Row> rowIterator = mySheet.iterator();
                
                    Row row = rowIterator.next();
                    
                    row = rowIterator.next();
                    
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()){
                            case STRING:
                                System.out.print(cell.getStringCellValue() + "t");
                                break;
                            case NUMERIC:
                                System.out.print(cell.getNumericCellValue() + "t");
                                break;
                            default:
                        }
                    }
                    System.out.println("");
                file.close();
            }
        }
    }catch (Exception e){
        System.out.println(e.getMessage());
    }
    }

Advertisement

Answer

I found the answer to my question. You can not copy the whole row. You need to loop and read cell by cell. The solution is:

Declare rowInrecmenter, colIncremnter, xssfWorkbook = new XSSFWorkbook();, XSSFSheet createdSheet = xssfWorkbook.createSheet(“Put any name here”);

The variables above need to be outside the loop.

The rest of the codes are:

XSSFRow rowCreated = createdSheet.createRow((short)incr);

while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                        case STRING:
                            rowCreated.createCell(col).setCellValue(cell.getStringCellValue());
                            col++;
                            break;
                        case NUMERIC:
                            rowCreated.createCell(col).setCellValue(cell.getNumericCellValue());
                            col++;
                            break;
                        default:
                    }
                }
                xssfWorkbook.write(fileOut);
                incr++;
                col = 0;
                workbook.close();
                file.close();
            }
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }finally {
        fileOut.close();
        xssfWorkbook.close();

    }
Advertisement