title: Filtering column in excel using java poi
body: <p>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.</p>
  
  <p>I did some digging and have modified the code. However I have not been able to meet my requirement. I have following problems -</p>
  
  <p>*The code only selects two columns and displays their contents. Not able to filter :(</p>
  
  <p>*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 ??</p>
  
  <p>*Excel has alpha-numeric values, how to deal with them?</p>
  
  <p>Could you please help me out here. </p>
  
  <pre><code>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 &lt; 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 &lt; 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();
      }
  }
  </code></pre>