title: Applying price and percentage formatting in Apache POI
body: <p>Java 8 and Apache POI 4.1.x here.</p>
  
  <p>The expected "cell formatting behavior" of Excel, as I've gathered from being an Excel user for most of my life, is as follows:</p>
  
  <ul>
  <li>There appears to be an underlying "<em>internal value</em>" of a cell, and then there is the "<em>visualization</em>" of that value</li>
  <li>The internal value is a number, value, etc. that is the actual/real value of that cell</li>
  <li>The visualization is an <strong>optional</strong> way of representing the internal value to the end user
  
  <ul>
  <li>Example: The internal value of a cell might be <code>0.678261</code>, but the cell might be formatted to handle decimals as percentages with hundredths-place precision, and so the end user might see that cell represented as <code>67.83%</code>. But if they were to use it in a formula, or modify its value, the underlying value of <code>0.678261</code> is what would be used/modified</li>
  </ul></li>
  </ul>
  
  <p><strong>I'm trying to figure out how to do the same with POI.</strong></p>
  
  <p>Meaning, I would like to use POI's API to write an internal value to a cell, but then configure that cell to (visually) represent the value with a different formatting applied.</p>
  
  <p>My two use cases are:</p>
  
  <ol>
  <li>Representing a numeric/decimal as a valid price (e.g. visually representing <code>203.9483495949</code> as <code>$203.95</code> to the end users, or <code>0.8375733</code> as <code>$0.84</code>); and</li>
  <li>Representing a numeric/decimal as a valid percentage (e.g. visually representing <code>0.009383484</code> as <code>1.00%</code> to the end users, or <code>0.53282</code> as <code>53.28%</code>)</li>
  </ol>
  
  <p>Currently I'm writing these values as follows:</p>
  
  <pre><code>BigDecimal validPrice = BigDecimal.valueOf(203.9483495949);
  BigDecimal validPct = BigDecimal.valueOf(0.53282);
  
  Row nextRow = sheet.createRow(rowNum);
  
  nextRow.createCell(0).setCellValue(validPrice.doubleValue());
  nextRow.createCell(1).setCellValue(validPct.doubleValue());
  </code></pre>
  
  <p>But when I write the data to an Excel file, I just see those same raw/internal values visualized (<code>203.9483495949</code> and <code>0.53282</code> respectively) in the columns, and I have to <strong>manually</strong> set formatting on them after I open the files up. Instead, rather than forcing end users to apply this formatting manually, I'd like POI to apply the formatting in the code, so that when the files are opened, they are formatted as <code>$203.95</code> and <code>53.28%</code> aleady.</p>
  
  <p>Any idea as to how to do this?</p>