title: When does apache poi override Excel formatting and when does it not?
body: <p>I would like to generate an Excel using Apache POI in which I can display about 2000 records, where each record comprises a date and a value.</p>
  
  <p>I would like this Excel to be formatted properly, colouring the cell backgrounds, and applying appropriate number formats.</p>
  
  <p>I can do both of these tasks, but I can not do the formatting as efficiently as I want.</p>
  
  <p>The 3 methods I have tried for applying the formatting are as follows: All three methods involve using a pre-formatted Excel template. The question is, however, how much formatting to do in Excel (and how to apply the formatting) and how much to do in Java.</p>
  
  <p><strong>Method 1</strong>: 
  Formatting one row in Excel itself, and copying the formatting using Java code. For instance:</p>
  
  <pre><code>Row existingRow = mySheet.getRow(4);
  Cell existingCell = existingRow.getCell(0);
  CellStyle currentStyle = existingCell.getCellStyle();
  
  for (int w = 0; w &lt; refData.size(); w++) {
      MyValues aa = refData.get(w);
      Row r = CellUtil.getRow(w + 4, mySheet);
      CellUtil.getCell(r, 0).setCellValue(aa.getMarketDate());
      if (w&gt;0) {
           CellUtil.getCell(r, 0).setCellStyle(currentStyle);
      }
  </code></pre>
  
  <p><strong>Method 2</strong>: Select the cells containing the required format in Excel and paste over the region I require (2000 rows) and then just fill in the data using Apache POI</p>
  
  <p><strong>Method 3</strong>: Apply the formatting to the columns using Excel, and then just fill in the data using Apache POI.</p>
  
  <p>The third method is by far preferable for me, because (a) I do not need to start programming Java code when I can just pre-format in Excel [note that my real-life problem includes tens of columns and not just one column] (b) Applying a format to a column is highly advantageous in terms of memory used by the workbook.</p>
  
  <p>The only problem is that when Apache POI writes to cells where the format is copied and pasted, then they are displayed fine. When it writes to cells where the format has been applied to the column, then it removes the formatting before pasting.</p>
  
  <p>Is there any way of getting around this? I assume that there isn't because Apache POI works by considering each row individually. For instance, to apply a format to a column, one needs to apply the format to each cell individually in the column </p>