MonsterFileMuncher

gistfile1.txt

I need Java 17 code to query an Excel file, finding in a "Client_Home" tab a row which contains a given search string in a column identified by a title "Description" at row 2
ChatGPT

To query an Excel file in Java, particularly to find a row in a specific tab ('Client_Home') where a column ('Description') contains a given search string, you can use the Apache POI library. This library provides APIs for Microsoft Office formats, including Excel.

Here is a sample code snippet that demonstrates how you can achieve this in Java 17:

    Include Apache POI Dependency: First, include the necessary Apache POI dependencies in your project. If you are using Maven, add the following to your pom.xml:

    xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Java Code to Query Excel File:

java

    import org.apache.poi.ss.usermodel.*;
    import java.io.File;
    import java.io.FileInputStream;
    import java.util.Iterator;

    public class ExcelSearch {
        
        public static void main(String[] args) {
            String excelFilePath = "path/to/your/excel/file.xlsx";
            String searchTab = "Client_Home";
            String searchColumnTitle = "Description";
            String searchString = "your_search_string";

            try {
                FileInputStream file = new FileInputStream(new File(excelFilePath));
                Workbook workbook = WorkbookFactory.create(file);
                Sheet sheet = workbook.getSheet(searchTab);

                if (sheet != null) {
                    Row titleRow = sheet.getRow(1); // Rows are 0-based, row 2 is indexed as 1
                    int column = -1;

                    // Find the column index for the 'Description' column
                    for (Cell cell : titleRow) {
                        if (cell.getStringCellValue().trim().equals(searchColumnTitle)) {
                            column = cell.getColumnIndex();
                            break;
                        }
                    }

                    if (column == -1) {
                        System.out.println("Description column not found");
                        return;
                    }

                    // Iterate through rows to find the search string
                    for (Row row : sheet) {
                        Cell cell = row.getCell(column);
                        if (cell != null && cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains(searchString)) {
                            System.out.println("Found in row: " + row.getRowNum());
                            // Process the row as needed
                        }
                    }
                } else {
                    System.out.println("Sheet not found");
                }

                workbook.close();
                file.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    Running the Code: Replace path/to/your/excel/file.xlsx with the actual path to your Excel file, and your_search_string with the string you want to search for.

This code opens the specified Excel file, looks for the 'Client_Home' tab, and then searches for the specified string in the 'Description' column. Note that Apache POI treats rows and columns as 0-based indexes. So, row 2 is indexed as 1, and the first column is indexed as 0.

Remember to handle exceptions and edge cases as needed for your specific use case.