title: Redefine Named Excel Range then Save using Apache POI
body: <p>Using Apache POI, I'm able to find a named range:</p>
  
  <pre><code>XSSFName[] ranges = new XSSFName[workbook.getNumberOfNames()];
  for (int i = 0; i &lt; _wb.getNumberOfNames(); i++)
      ranges[i] = workbook.getNameAt(i);
  </code></pre>
  
  <p>With that, I'm able to cell an AreaReference:</p>
  
  <pre><code>AreaReference area = new AreaReference(ranges[0].getRefersToFormula());
  </code></pre>
  
  <p>And then finally I can get all the cells within that range:</p>
  
  <pre><code>CellReference[] cells = area.getAllReferencedCells();
  </code></pre>
  
  <p>That all works just fine. Burt I have a use case where I have to redefine the area that the range covers. Is there a way to do that? I notice that the <code>range.getRefersToFormula()</code> method return a String, something like <code>MySheet!$A$1:$B$8</code>. There is a <code>range.setRefersToFormula(String formula)</code>, but I've got to believe there's a way other than resorting to writing an excel range formula parser on my own. Is there no way to generate an AreaReference with a set to <code>Cell</code> references of something more type-safe? Do I actually have to generate a <code>String</code> to represent the new range? I would think there would be API somewhere to help me with this but I can't seem to find it.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I found some API, but it doesn't seem to work, at least it doesn't save properly. Here's what I did.</p>
  
  <pre><code>AreaReference newArea = new AreaReference(firstCell, lastCell);
  ranges[0].setRefersToFormula(newArea.formatAsString())
  </code></pre>
  
  <p>It seems to set the formula correctly, but when I stream the workbook back out to disk, the range is completely wrong.</p>