title: Openpyxl using optimized write-only significantly increases the disk usage of the excel file
body: <p>A large production program was exporting lots of data into a spreadsheet using Openpyxl. It was very slow for large systems (eg: 4 hours). I realized that I could use Openpyxl optimized write-only mode to significantly speed up the time (~7 seconds). As far as I can tell, this was done correctly, the spreadsheets contain the same data, and according to LibreOffice, have the exact same number of cells.</p>
  
  <p>The problem lies in the disk usage of the excel file. The older, slower method used ~4MB of disk space, while the new optimized mode uses ~8MB. </p>
  
  <p><strong>What I have looked into</strong></p>
  
  <ul>
  <li>Possible difference in how string references are shared for lots of duplicate strings (which are prevalent). I found no change in the disk space used between a large amount of identical strings, and large amounts of unique strings between the two methods. (code used below)</li>
  </ul>
  
  <pre><code>from openpyxl import Workbook
  
  wb = Workbook(write_only=True)
  ws = wb.create_sheet(title='mem')
  
  for irow in range(10000):
      ws.append(['hi' for i in range(200)])
  wb.save('opt.xlsx')
  
  #####################################################
  
  wb2 = Workbook()
  sheet = wb2.active
  sheet.title = "mem2"
  
  for irow in range(1, 10001):
      for column in range(1, 201):
          cell = sheet.cell(row=irow, column=column)
          cell.value = 'hi'
  wb2.save('nonopt.xlsx')
  </code></pre>
  
  <p>Produced a spreadsheet of the same size</p>
  
  <ul>
  <li>Opening the spreadsheet in LibreOffice, then saving it in XML format cuts the data size of the optimized spreadsheet to almost match the non-optimized spreadsheet.</li>
  </ul>
  
  <p><strong>Answers I'm looking for</strong></p>
  
  <p>Since saving the spreadsheet reduces this data size, my thoughts are that either there is some wasteful metadata, or many empty cells that LibreOffice automatically removes. Both of which I cannot understand how they are produced by simply switching to a write-only mode, and storing the values in lists for rows instead of a cell class. So I am looking for:</p>
  
  <ul>
  <li><p>How to test my hypothesis for the data usage as I am not too sure how to go about checking these.</p></li>
  <li><p>Other possibilities for why the disk usage is larger</p></li>
  <li><p>If I tested the string reference incorrectly.</p></li>
  </ul>
  
  <p>If it is decided that the code itself would really be needed, I can attempt to put together a small demo of it, but currently it is very intermingled with code I cannot share. The speadsheets too I cannot share. Because of these, I don't expect the answers to 100% determine the issue, but possibly lead me towards confirming it, and updating the post.</p>
  
  <p>Thank you.</p>