Search in xlsx and xls file using java

Question

I have a large xlsx file which as huge amount of data on which I have to implement search option I have used Apache POI jar as well as jxl jar so that the search between rows and column have been made. But it took huge time to traverse between big data can some one help me that is any jar files or any other concept available to do the search faster on Excel files...

    String searchValue="my_value_to_search";
    for (int i = 0; i < sheet.getColumns(); i++) {
        for (int j = 0; j < sheet.getRows(); j++) {
            value = sheet.getCell(i, j);
            valueType = value.getType();
            String val=getCellType(valueType, value);
            if (val != null&&val==searchValue) {
                //   To do manipulation.
            }
        }
    }

Answer 1

Bottleneck is usually the huge amount of memory required to represent large XLSX files in memory at once. (XLS can't be that big by design, this is usually not a problem). To search in a really huge XLSX file without the memory problems, you could do this:

• the xlsx file is in fact a ZIP archive, you can open it and read the contents as if it is a ZIP file.
• inside the ZIP are folder "xl/worksheets" with files sheet1.xml (and sheet2.xml and so on)
• you can parse these XML files using a normal XmlReader (using callbacks for maximum performance and least memory consumption).

Hope that helps.