I am in a learning stage of java. I want to write a program in java which reads one excel file(.xlsx). This file has some columns and many rows. I want to write the data in another excel file(.xlsx)only the condition is met not all the data from existing file. my excel sheet looks like below

I want to filter only those rows with broker Edelweiss and put it in another excel sheet. I am aware how to copy all the data from one excel to another excel using java. But I don't know how to filter specific row and put it in another excel. Any help will be highly appreciable.

Here is my code.

         FileInputStream file = new FileInputStream(new File("broker.xlsx"));
        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) 
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();
             
            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue()   "t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue()   "t");
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }
}

}

CodePudding user response：

I will make my comment an answer.

I would open the source sheet and loop through all rows in it. For each row I would get the content of the column where "Broker" is stored. Then, if that content equals "Edelweiss" I would get that row into a Java collection, a list of rows for example. After that I would write the content of that Java collection into the result sheet.

The following complete example shows this.

It contains methods to get the last filled row in a special column of a sheet and to get the last filled column in a special row of a sheet. That is to determine the used cell range of a sheet.

It also contains a method to get the headings, which maps headings to column indexes. The headings must be in first row of the used cell range of the sheet.

It also shows how to use

Code:

import java.io.FileOutputStream;
import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

import java.util.Locale;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;

class ExcelFilterRowsToNewWorkbook {
    
 static int getLastFilledRow(Sheet sheet, int col) {
  int lastStoredRowNum = sheet.getLastRowNum();
  for (int r = lastStoredRowNum; r >= 0; r--) {
   Row row = sheet.getRow(r);
   if (row != null) {
    Cell cell = row.getCell(col);
    if (cell != null && cell.getCellType() != CellType.BLANK) return row.getRowNum();  
   }       
  }
  return -1; // the sheet is empty in that col 
 }
 
 static int getLastFilledColumn(Sheet sheet, int rowIdx) {
  int lastStoredCellNum = sheet.getRow(rowIdx).getLastCellNum();
  Row row = sheet.getRow(rowIdx);
  if (row != null) {
   for (int c = lastStoredCellNum; c >= 0; c--) {
    Cell cell = row.getCell(c);
    if (cell != null && cell.getCellType() != CellType.BLANK) return cell.getColumnIndex();  
   }       
  }
  return -1; // the sheet is empty in that row 
 }
 
 static Map<Integer, String> getHeadings(Sheet sheet) {
  DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  int firstRow = sheet.getFirstRowNum();
  int firstCol = sheet.getRow(firstRow).getFirstCellNum();
  int lastCol = getLastFilledColumn(sheet, firstRow);   
  Map<Integer, String> headings = new HashMap<Integer, String>();
  Row row = sheet.getRow(firstRow);
  if (row != null) {
   for (int c = firstCol; c <= lastCol; c  ) {
    Cell cell = row.getCell(c);
    headings.put(c, dataFormatter.formatCellValue(cell));      
   }
  }
  return headings;
 }

 static List<Row> filterRows(Sheet sheet, String filterHeading, String filterValue) {
  int filterCol = -1;
  Map<Integer, String> headings = getHeadings(sheet);
  for (Map.Entry<Integer, String> entry : headings.entrySet()) {
   if (entry.getValue().equals(filterHeading)) {
    filterCol = entry.getKey();
    break;
   }       
  }
  List<Row> rows = new ArrayList<Row>();
  // add the headings row
  int firstRow = sheet.getFirstRowNum();
  rows.add(sheet.getRow(firstRow));
  // add the fildered rows
  if (filterCol > -1) {
   DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
   dataFormatter.setUseCachedValuesForFormulaCells(true);
   int firstCol = sheet.getRow(firstRow).getFirstCellNum();
   int lastCol = getLastFilledColumn(sheet, firstRow);   
   int lastRow = getLastFilledRow(sheet, firstCol);
   for (int r = firstRow; r <= lastRow; r  ) {
    Row row = sheet.getRow(r);
    if (row != null && lastCol >= filterCol) {
     Cell cell = row.getCell(filterCol);
     String cellContent = dataFormatter.formatCellValue(cell);
     if (cellContent.equals(filterValue)) {
      rows.add(row);   
     }
    }
   }
  }
  return rows;  
 }
 
 public static void main(String[] args) throws Exception {
  
  try (Workbook workbookSrc = WorkbookFactory.create(new FileInputStream("./broker.xlsx")) ) {

   Sheet sheetSrc = workbookSrc.getSheetAt(0);
   // get filtered rows
   List<Row> rowsSrc = filterRows(sheetSrc, "Broker", "Edelweiss");
   
   // add filtered rows in new workbook
   try (Workbook workbookDest = WorkbookFactory.create(true);
        FileOutputStream fileout = new FileOutputStream("./brokerFiltered.xlsx") ) {
    Sheet sheetDest = workbookDest.createSheet();
    int r = 0;
    for (Row rowSrc : rowsSrc) {
     Row rowDest = sheetDest.createRow(r  );
     int c = 0;     
     for (Cell cellSrc : rowSrc) {
      Cell cellDest = rowDest.createCell(c  );
      CellUtil.copyCell(cellSrc, 
       cellDest, 
       new CellCopyPolicy(),
       new CellCopyContext()
      );
     }         
    }  
    workbookDest.write(fileout);
   }
   
  }   
  
 }
}

The first sheet of brokerFiltered.xlsx then looks like: