Using Java to read and clean Excel file

Question

I'm trying to create a Java application that can read and clean an Excel file in .xlsx format (by comparing it to a master list, also in Excel format) before writing it to a new Excel file. I also need to be able to post the cleaned file to a web app, but that's for later.

I'm using Apache POI, and have been able to successfully read the Excel file (in the output section in Eclipse it doesn't start from the first row though).

Here's where I'm stuck. I've been able to read the rows using an iterator, but how do I extract all these values to variables? Can I create a data table?

public class ReadAndCleanExcel {

    public void readExcel(String filePath) {

        Object[][] data = null;
        final DataFormatter df = new DataFormatter();

        try {
            FileInputStream file = new FileInputStream(new File(filePath));

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

            int rownum = 0;
            int colnum = 0;
            Row r=rowIterator.next();

            int rowcount=sheet.getLastRowNum();
            int colcount=r.getPhysicalNumberOfCells();

            data = new Object[rowcount][colcount];

            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();

                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                colnum = 0;

                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    data[rownum][colnum] =  df.formatCellValue(cell);
                    System.out.print(df.formatCellValue(cell).toUpperCase() + " ");
                    colnum++;

                }

                rownum++;
                System.out.println();

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

            e.printStackTrace();

        }
    }

Have a look at this answer https://stackoverflow.com/a/33632416/10631518 The user creates a new Excel document. You could adapt this snippet to your needs. — AllirionX, Oct 11 '19 at 03:48
What do you mean by "extract all these values to variables"? You can use a List for instance — Villat, Oct 11 '19 at 03:49
@Villat, thanks! But what if the dataset is a few hundred rows long? Does that mean i have to create a list for each single row? — Ray, Oct 11 '19 at 05:19

Using Java to read and clean Excel file

0 Answers0