title: Getting specific data from an excel sheet JAVA
body: <p>I am trying to get specific data from an excel sheet, The data is dynamic. It can be anything really. The column headers are the only things i can use for placeholders, but the column header positions can vary in the sheet.</p>
  
  <p>For example i have a sheet like this :</p>
  
  <p>|Name| Surname| Value|</p>
  
  <p>|bar    | poo      | 5|</p>
  
  <p>|baz    | foo       | 7|</p>
  
  <p>But for example i need to traverse the sheet to get the surname column and then if i find surname = 'poo' i must then pull its corresponding value which in the sheet is in the next colum but this is dynamic. The surname and value column arent always next to each other, they can be in any position at the top. But if i find a specific 'thing' in the surname column i need to pull its value.</p>
  
  <p>I have managed to traverse through the sheet and store all the data in a 2d array And display that data. from the research ive done , this isnt an efficient approach as traversing and storing large data from sheets can use alot of memory. Ive read that you can read through an excel sheet and instead of storing those values in an array you can write them immediately to another sheet, if they match a certain condition. EG: (pseudo) If(columnheader ==  surname &amp;&amp; surname == foo )then get corresponding value, then write that value to a new sheet. </p>
  
  <p>Okay so my questions are :</p>
  
  <p>1.How do i achieve iterating through the sheet not storing it in an array and writing it straight to another sheet if it matches a condition?</p>
  
  <p>2.From the code i have below, how do i achieve sorting through the data in the array and finding if surname = foo get its corresponding value?</p>
  
  <p>Like i said the data in the sheet is dynamic except for the column headers, but there positions as headers are dynamic.</p>
  
  <p>Sorry for the long post , any help will be greatly appreciated.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>package demo.poi;
  
  
  import java.io.File;
  import java.io.FileInputStream;
  import java.io.FileOutputStream;
  import java.math.BigDecimal;
  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.xssf.usermodel.XSSFCell;
  import org.apache.poi.xssf.usermodel.XSSFRow;
  import org.apache.poi.xssf.usermodel.XSSFSheet;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
  
  public class test  {
  
      public static void main(String[] args) throws Exception {
  
          File excel = new File("test.xlsx");
          FileInputStream fis = new FileInputStream(excel);
  
          XSSFWorkbook wb = new XSSFWorkbook(fis);
          XSSFSheet ws = wb.getSheetAt(0);
          ws.setForceFormulaRecalculation(true);
  
          int rowNum = ws.getLastRowNum() + 1;
          int colNum = ws.getRow(0).getLastCellNum();
          int surnameHeaderIndex = -1, valueHeaderIndex = -1;
  
          //Read the headers first. Locate the ones you need
          XSSFRow rowHeader = ws.getRow(0);
          for (int j = 0; j &lt; colNum; j++) {
              XSSFCell cell = rowHeader.getCell(j);
              String cellValue = cellToString(cell);
              if("SURNAME".equalsIgnoreCase(cellValue)) {
                  surnameHeaderIndex = j;
              } else if("VALUE".equalsIgnoreCase(cellValue)) {
                  valueHeaderIndex = j;
              }
          }
  
          if(surnameHeaderIndex == -1 || valueHeaderIndex == -1) {
              throw new Exception("Could not find header indexes\nSurname : " + surnameHeaderIndex + " | Value : " + valueHeaderIndex);
          }
          //createnew workbook
          XSSFWorkbook workbook = new XSSFWorkbook();   
    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("data");
    
          for (int i = 1; i &lt; rowNum; i++) {
              XSSFRow row = ws.getRow(i);
             row = sheet.createRow(rowNum++);
              String surname = cellToString(row.getCell(surnameHeaderIndex));
              String value = cellToString(row.getCell(valueHeaderIndex));
              int cellIndex = 0;
              row.createCell(cellIndex++).setCellValue(surname);
              row.createCell(cellIndex++).setCellValue(value);
              
  
              
          }
          FileOutputStream fos = new FileOutputStream(new File("test1.xlsx"));
          workbook.write(fos);
          fos.close();
      }
  
      public static String cellToString(XSSFCell cell) {
  
          int type;
          Object result = null;
          type = cell.getCellType();
  
          switch (type) {
  
          case XSSFCell.CELL_TYPE_NUMERIC:
              result = BigDecimal.valueOf(cell.getNumericCellValue())
                      .toPlainString();
  
              break;
          case XSSFCell.CELL_TYPE_STRING:
              result = cell.getStringCellValue();
              break;
          case XSSFCell.CELL_TYPE_BLANK:
              result = "";
              break;
          case XSSFCell.CELL_TYPE_FORMULA:
              result = cell.getCellFormula();
          }
  
          return result.toString();
      }
  }</code></pre>
  </div>
  </div>
  </p>