How to write specific row when condition is met in another Excel sheet

Question

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. I don't know how to filter a specific row and put it in another Excel.

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();
}

I am getting the below error when I run Axel Richter's code which is shared below

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/io/output/UnsynchronizedByteArrayOutputStream
    at org.apache.poi.poifs.filesystem.FileMagic.valueOf(FileMagic.java:209)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:222)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:185)
    at writefile.main(writefile.java:92)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:351)

I have included below jars in my classpath

POI_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/poi-5.2.2.jar
POI_OOXML_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/poi-ooxml-full-5.2.2.jar
XML_BEANS_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/ooxml-lib/xmlbeans-5.0.3.jar
COM_COLL_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/commons-collections4-4.4.jar

COM_COMPRESS_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/ooxml-lib/commons-compress-1.21.jar
COM_CODEC_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/commons-codec-1.15.jar
COM_IO_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/commons-io-2.11.0.jar
COM_MATH_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/commons-math3-3.6.1.jar
LOG_J4_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/log4j-api-2.17.2.jar
SPARSE_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/lib/SparseBitSet-1.2.jar
COM_LOGG_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/ooxml-lib/commons-logging-1.2.jar
CURVE_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/ooxml-lib/curvesapi-1.07.jar
SLF4_LIB=$(TOP_DIR)/jar/poi-bin-5.2.2/ooxml-lib/slf4j-api-1.7.36.jar

Answer 1

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 CellUtil.copyCell to copy cells from one sheet to another.

The code is tested and works using current apache poi 5.2.2.

The first sheet of broker.xlsx looks like:

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++);
     for (Cell cellSrc : rowSrc) {
      Cell cellDest = rowDest.createCell(cellSrc.getColumnIndex());
      CellUtil.copyCell(cellSrc, 
       cellDest, 
       new CellCopyPolicy(),
       new CellCopyContext()
      );
     }         
    }  
    workbookDest.write(fileout);
   }
   
  }   
  
 }
}

The first sheet of brokerFiltered.xlsx then looks like: