title: How to use vlookup or formulas in SXSSFWorkbook with Apache POI?
body: <p>I am Java developer. I am trying to write a large <code>xlsx</code> file (Excel) using the <code>SXSSFWorkbook</code> class of the Apache POI library.</p>
  
  <p>I have no choice to write large file due have more than 200000 rows.
  but I need to use some formulas or vlookup with external sheet. when I use: </p>
  
  <pre><code>cell.setCellFormula(formulasString);
  </code></pre>
  
  <p>But it is not working and it is returning 0 value.</p>
  
  <p><strong>Q- How to use formulas with SXSSFWorkbook ?</strong></p>
  
  <p>My Code - </p>
  
  <pre><code>import java.io.FileOutputStream;
  import junit.framework.Assert;
  import org.apache.poi.ss.usermodel.Cell;
  import org.apache.poi.ss.usermodel.FormulaEvaluator;
  import org.apache.poi.ss.usermodel.Row;
  import org.apache.poi.ss.usermodel.Sheet;
  import org.apache.poi.ss.util.CellReference;
  import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  
  public class Demo2 
  {
      public static void main(String[] args) throws Throwable 
      {
            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
  
            FormulaEvaluator fEvaluator=wb.getCreationHelper().createFormulaEvaluator();
  
            Sheet sh = wb.createSheet();
            for(int rownum = 0; rownum &lt; 100000; rownum++)
            {
                 Row row = sh.createRow(rownum);
                 for(int cellnum = 0; cellnum &lt; 10; cellnum++)
                 {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                 }
  
                 Cell cell1 = row.createCell(12);
                 cell1.setCellType(Cell.CELL_TYPE_FORMULA);
                 String sF = "SUM(10,20,30)";
                 cell1.setCellFormula(sF);
                 fEvaluator.clearAllCachedResultValues();
  
                 Cell cell2 = row.createCell(13);
                 cell2.setCellValue("Test");
           }
  
           // Rows with rownum &lt; 900 are flushed and not accessible
           for(int rownum = 0; rownum &lt; 99900; rownum++)
           {
                Assert.assertNull(sh.getRow(rownum));
           }
  
           // ther last 100 rows are still in memory
           for(int rownum = 99900; rownum &lt; 100000; rownum++)
           {
                Assert.assertNotNull(sh.getRow(rownum));
           }
  
           FileOutputStream out = new FileOutputStream("sxssf.xlsx");
           wb.write(out);
           out.close();
  
           // dispose of temporary files backing this workbook on disk
           wb.dispose();
           System.out.println("Done");
      }
  }
  </code></pre>
  
  <p>Please, give me suggestion if possible.
  Thanks.</p>