memory footprint and read only

Created originally on Bitbucket by Matt King

I'm probably missing the point of read_only mode but I have the following function that I'm profiling with memory_profiler

@profile
def create_csv(fn):
    from openpyxl import load_workbook
    wb = load_workbook(filename=fn, read_only=True)
    ws = wb.active

    cell_count = 0
    for row in ws.rows:
        for cell in row:
            cell_count += 1
    print cell_count

and as you can see the memory used goes up to 1GB or so when loading the workbook:

Line #    Mem usage    Increment   Line Contents
================================================
    19   32.273 MiB   32.273 MiB   @profile
    20                             def create_csv(fn):
    21   38.848 MiB    6.574 MiB       from openpyxl import load_workbook
    22 1102.047 MiB 1063.199 MiB       wb = load_workbook(filename=fn, read_only=True)
    23 1102.047 MiB    0.000 MiB       ws = wb.active
    24
    25 1102.047 MiB    0.000 MiB       cell_count = 0
    26 1102.047 MiB -36564981.566 MiB       for row in ws.rows:
    27 1102.047 MiB -365645323.121 MiB           for cell in row:
    28 1102.047 MiB -329080799.082 MiB               cell_count += 1
    29  518.207 MiB -583.840 MiB       print cell_count

Now if I change read_only=False and run again:

Line #    Mem usage    Increment   Line Contents
================================================
    20   28.062 MiB   28.062 MiB   @profile
    21                             def create_csv(fn):
    22   38.367 MiB   10.305 MiB       from openpyxl import load_workbook
    23 1136.023 MiB 1097.656 MiB       wb = load_workbook(filename=fn, read_only=False)
    24 1136.027 MiB    0.004 MiB       ws = wb.active
    25
    26 1136.027 MiB    0.000 MiB       cell_count = 0
    27 1136.266 MiB    0.238 MiB       for row in ws.rows:
    28 1136.266 MiB    0.000 MiB           for cell in row:
    29 1136.266 MiB    0.000 MiB               cell_count += 1
    30 1136.281 MiB    0.016 MiB       print cell_count

I don't see much difference. This is a 120MB excel file with some fairly large text in some of the cells. I've tried agateexcel and pyexcel and they each profile at about 1GB to process the same file.

My goal is to convert to CSV without a large memory overhead. But I guess unzipping and reading the xml has to be done somehow.

The contents of my xlsx look like

  Length      Date    Time    Name
---------  ---------- -----   ----
     1258  01-11-2018 21:00   [Content_Types].xml
      531  01-11-2018 21:00   _rels/.rels
      641  01-11-2018 21:00   xl/_rels/workbook.xml.rels
      886  01-11-2018 21:00   docProps/app.xml
      940  01-11-2018 21:00   docProps/core.xml
    10140  01-11-2018 21:00   xl/theme/theme1.xml
      375  01-11-2018 21:00   xl/workbook.xml
 22932034  01-11-2018 21:00   xl/worksheets/sheet1.xml
396420174  01-11-2018 21:00   xl/sharedStrings.xml
      823  01-11-2018 21:00   xl/styles.xml
---------                     -------
419367802                     10 files