Read Xlsx File in Java

Read XLSX file in Java

AFAIK there are no xlsx-libraries available yet. But there are some for old xls:

One library is jxls which internally uses the already mentioned POI.

2 other links: Handle Excel files, Java libraries to read and write Excel XLS document files.

How to read .xlsx file with apache poi?

I would iterate over the rows in the sheet and get the string content of cell 7 (H) from each row. If that string fulfills the requirement equalsIgnoreCase the searched value, that row is one of the result rows, else not.

One could collect the result rows in a List<Row>. Then this List contains the result rows after that.

Example:

ExcelWorkbook.xlsx:

Code:

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

import java.util.List; 
import java.util.ArrayList; 

import java.io.FileInputStream;

public class ExcelReadRowsByColumnValue {

 public static void main(String[] args) throws Exception {

  String filePath = "./ExcelWorkbook.xlsx";

  String toSearch = "coresystem";
  int searchColumn = 7; // column H
  List<Row> results = new ArrayList<Row>();

  DataFormatter dataFormatter = new DataFormatter();
  Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) { // iterate over all rows in the sheet
   Cell cellInSearchColumn = row.getCell(searchColumn); // get the cell in seach column (H)
   if (cellInSearchColumn != null) { // if that cell is present
    String cellValue = dataFormatter.formatCellValue(cellInSearchColumn, formulaEvaluator); // get string cell value
    if (toSearch.equalsIgnoreCase(cellValue)) { // if cell value equals the searched value
     results.add(row); // add that row to the results
    }
   }
  }

  // print the results
  System.out.println("Found results:");
  for (Row row : results) {
   int rowNumber = row.getRowNum()+1;
   System.out.print("Row " + rowNumber + ":\t");
   for (Cell cell : row) {
    String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.print(cellValue + "\t");
   }
   System.out.println();
  }

  workbook.close();
 }
}

Result:

Reading from an Excel (xlsx) file using Apache POI library throws NullPointerException

The NullPointerException is thrown at line 33 of ClassLoaderResourceLoader. The line is:

return _classLoader.getResourceAsStream(resourceName);

Which means that _classLoader is null. _classLoader is passed to the constructor.

The ClassLoaderResourceLoader object is created at line 182 of SchemaTypeSystemImpl:

_classloader = indexclass.getClassLoader();
...
_resourceLoader = new ClassLoaderResourceLoader(_classloader);

Which means that _classloader is null, which means that indexclass.getClassLoader() is null.

When can getClassLoader() return null? The documentation says:

Some implementations may use null to represent the bootstrap class
loader. This method will return null in such implementations if this
class was loaded by the bootstrap class loader.

Bootstrap? Does it ring a bell? Yes, you put all your jars in the Bootstrap Entries, which means that they are loaded by the bootstrap class loader. As I said in a comment (which you seem to have ignored), the correct location for jars is the WEB-INF\lib directory of the application. So just put them in there and it will work.

Reading Big XLS and XLSX files

Okay, so I've tried replicating your excel file and I completly threw the XLSX2CSV out the window. I don't think the approach of converting the xlsx into csv is the right one because, as depending on your XLSX format, it can read all the empty rows (you probably know that because you've set a row counter of 60k). not only that but if we're taking into consideration fields, it may or may not cause incorrect output with special characters, like your problem.

What I've done is I've used this library https://github.com/davidpelfree/sjxlsx to read and re-write the file. It's pretty much straight-forward and the new xlsx generated file has the fields corrected.

I suggest you try this approach (maybe not with this lib), of trying to re-write the file in order to correct it.

How to read xlsx files in Java 1.4?

Your best shot would be https://en.osdn.jp/projects/poi-jdk14/releases/ (from Workarounds for using apache poi 3.9 with JDK 4) but I make no guarantees as to how well the port went, what bugs it introduced (and it will have introduced some!), and how well it performs, or even if it's actually what it says it is.

Given that Java 5 was released in 2004, Java 6 in 2006, and Java 1.4 was officially EOL'd in 2008, I think you're out of luck. You'd have to back-port Apache POI to 1.4 (which is what you'll find at the above link), or write the code yourself, neither of which is likely to be a lot of fun.

Read Excel from Apache poi java

It looks, you are using the wrong file type for the unsupported Class.

HSSFWorkbook class will support only .xls file type whreas XSSFWorkbook class will support both .xls and .xlsx file type

Please do any one of the below steps

1. Change the file extension as .xls (.xlsx file is supported only in
   XSSF not in HSSF.)
2. Change the HSSF as XSSF by keeping the xlsx file extension

Reading Excel File Column in Java

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class XlReader {

    public static void main(String[] args) {
        try (InputStream inp = (FileInputStream) files.getInputStream()) {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(0);
            List<String> columnNames = new ArrayList<>();
            for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
                Cell cell = iterator.next();
                String value = cell.getStringCellValue();
                columnNames.add(value);
            }
            Iterator<Row> iterator = sheet.iterator();
            if (iterator.hasNext()) {
                iterator.next();
            }
            List<String> rows = new ArrayList<>();
            while (iterator.hasNext()) {
                row = iterator.next();
                Cell code = row.getCell(0);
                double d = code.getNumericCellValue();
                int k = Double.valueOf(d).intValue();
                StringBuilder sb = new StringBuilder();
                for (int i = 1; i < columnNames.size(); i++) {
                    sb = new StringBuilder();
                    sb.append(k);
                    sb.append(',');
                    sb.append(columnNames.get(i));
                    sb.append(',');
                    Cell cell = row.getCell(i);
                    sb.append(String.format("%.2f", cell.getNumericCellValue()));
                    rows.add(sb.toString());
                }
            }
            rows.forEach(System.out::println);
        }
        catch (IOException xIo) {
            xIo.printStackTrace();
        }
    }
}

Result:

10001,BO1,200.00
10001,BO2,400.00
10001,BO3,0.00
10002,BO1,0.00
10002,BO2,100.00
10002,BO3,500.00

---

Related Topics