Filtering column in excel using java poi

Question

I have a large excel file. I want to filter a column "Mainly used for" for values "mainly used for mobile". Then I need to store the corresponding values in the "Number Series" column in a list. I have a code to start with. However I am not able to do the filtering part and storing it to an array list. Could you please help me out here.

I did some digging and have modified the code. However I have not been able to meet my requirement. I have following problems -

*The code only selects two columns and displays their contents. Not able to filter :(

*The excel has column names with spaces. So I am getting the error. As the excel is generated by the user, we have no control over column names. How to deal with the column name with spaces ??

*Excel has alpha-numeric values, how to deal with them?

Could you please help me out here.

package com.excel;
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;*/
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;


public class Test  {

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

        File excel = new File("D:\\FileDownload\\example.xls");
        //File excel = new File("D:\\FileDownload\\Sample_Filtered.xls");
        FileInputStream fis = new FileInputStream(excel);

        //XSSFWorkbook wb = new XSSFWorkbook(fis);
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        //org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(fis);
        HSSFSheet ws = wb.getSheetAt(0);
       // org.apache.poi.ss.usermodel.Sheet ws = wb.getSheetAt(0);
        ws.setForceFormulaRecalculation(true);

        int rowNum = ws.getLastRowNum() + 1;
        int colNum = ws.getRow(0).getLastCellNum();
        int mainlyUsedForHeaderIndex = -1, mobileSeriesHeaderIndex = -1;

        //Read the headers first. Locate the ones you need
        HSSFRow rowHeader = ws.getRow(0);
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = rowHeader.getCell(j);
            String cellValue = cellToString(cell);
            if("Mainly used for".equalsIgnoreCase(cellValue)) {
            //if("MainlyFor".equalsIgnoreCase(cellValue)) {
                mainlyUsedForHeaderIndex = j;
            } else if("Number Series".equalsIgnoreCase(cellValue)) {
              //else if("MobileSeries".equalsIgnoreCase(cellValue)) {
                mobileSeriesHeaderIndex = j;
            }
          }

        if(mainlyUsedForHeaderIndex == -1 || mobileSeriesHeaderIndex == -1) {
            throw new Exception("Could not find header indexes\n Mainly used for : " + mainlyUsedForHeaderIndex + " | Number Series: " + mobileSeriesHeaderIndex);
        }else{
            System.out.println("Indexes are found!!!");
        }


        //createnew workbook
        XSSFWorkbook workbook = new XSSFWorkbook();         
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("data");

        for (int i = 1; i < rowNum; i++) {
            HSSFRow row = ws.getRow(i);
            //row = sheet.createRow(rowNum++);
            String MainlyUsed = cellToString(row.getCell(mainlyUsedForHeaderIndex));
            String ForMobile = cellToString(row.getCell(mobileSeriesHeaderIndex));
            int cellIndex = 0;
            XSSFRow newRow = sheet.createRow(i-1); 
            newRow.createCell(cellIndex++).setCellValue(MainlyUsed);
            newRow.createCell(cellIndex++).setCellValue(ForMobile );



        }
        FileOutputStream fos = new FileOutputStream(new File("D:\\FileDownload\\test1.xlsx"));
        System.out.println("File generated");
        workbook.write(fos);
        fos.close();
    }

    public static String cellToString(HSSFCell cell) {

        int type;
        Object result = null;
        type = cell.getCellType();

        switch (type) {/*

        case HSSFCell.CELL_TYPE_NUMERIC:
            result = BigDecimal.valueOf(cell.getNumericCellValue())
                    .toPlainString();

            break;
        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            result = "";
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();*/

        case HSSFCell.CELL_TYPE_BLANK:
              result="";
              break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
              //
              result = cell.getBooleanCellValue();
              break;
        case HSSFCell.CELL_TYPE_ERROR:
              //
              break;

        case HSSFCell.CELL_TYPE_FORMULA:
              result = cell.getCellFormula();
              break;
        case HSSFCell.CELL_TYPE_NUMERIC:
              //
              result = cell.getNumericCellValue();
              break;
        case HSSFCell.CELL_TYPE_STRING:
                result= cell.getRichStringCellValue();
               // result = cell.getStringCellValue();
              break;
    }


        return result.toString();
    }
}

Answer 1

I am able to meet my requirement using following entirely different approach.

package com.excel;

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.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelRead {

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

            String fileName = "D:\\FileDownload\\example.xls";
            String cellContent = "Mainly used for mobile";
            int rownr=0;
            int colnr = 0; //column from which you need data to store in array list

            InputStream input = new FileInputStream(fileName);

            HSSFWorkbook wb = new HSSFWorkbook(input);
            HSSFSheet sheet = wb.getSheetAt(0);
            List MobileSeries=new ArrayList();
            MobileSeries = findRow(sheet, cellContent);

            if(MobileSeries !=null){
                for(Iterator iter=MobileSeries.iterator();iter.hasNext();){
                    System.out.println(iter.next());


            }
            }
            //output(sheet, rownr, colnr);

            finish();
        }

        private static void output(HSSFSheet sheet, int rownr, int colnr) {
            /*
             * This method displays the total value of the month
             */

            HSSFRow row = sheet.getRow(rownr);
            HSSFCell cell = row.getCell(colnr);

                    System.out.println("Your total is: " + cell);           
        }

        private static List findRow(HSSFSheet sheet, String cellContent) {
            List MobileSeries=new ArrayList();
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
                            //System.out.println("Row numbers are"+row.getRowNum());
                            int rownumber=row.getRowNum();
                            //return row.getRowNum();
                            HSSFRow row1 = sheet.getRow(rownumber);
                            HSSFCell cell1 = row1.getCell(0);
                            MobileSeries.add(cell1);
                        }
                    }
                }
            }    
            return MobileSeries;


        }

        private static void finish() {

            System.exit(0);
        }
    }