Optimised Modes

Read-only mode

Sometimes, you will need to open or write extremely large XLSX files, and the common routines in fastpyxl won't be able to handle that load. Fortunately, there are optimised modes that enable you to read and write unlimited amounts of data with (near) constant memory consumption.

Introducing fastpyxl.worksheet._read_only.ReadOnlyWorksheet:

from fastpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
    for cell in row:
        print(cell.value)

# Close the workbook after reading
wb.close()

Warning

  • fastpyxl.worksheet._read_only.ReadOnlyWorksheet is read-only
  • Unlike a normal workbook, a read-only workbook will use lazy loading. The workbook must be explicitly closed with the close() method.

Cells returned are not regular fastpyxl.cell.cell.Cell but fastpyxl.cell.read_only.ReadOnlyCell.

Worksheet dimensions

Read-only mode relies on applications and libraries that created the file providing correct information about the worksheets, specifically the used part of it, known as the dimensions. Some applications set this incorrectly. You can check the apparent dimensions of a worksheet using ws.calculate_dimension(). If this returns a range that you know is incorrect, say A1:A1 then simply resetting the max_row and max_column attributes should allow you to work with the file:

ws.reset_dimensions()

Indexed mode

For sparse or random cell access, read_only=True can be expensive: every lookup re-opens the worksheet ZIP member and re-scans from the start of the sheet. fastpyxl adds an opt-in indexed strategy that builds row and shared-string byte-offset indexes over decompressed package parts, then seeks to the needed row/<si> on demand:

from fastpyxl import load_workbook
wb = load_workbook("large_file.xlsx", access="indexed")
ws = wb["big_data"]
print(ws["Z100000"].value)
wb.close()

access="indexed" implies read-only worksheets (fastpyxl.worksheet._indexed.IndexedWorksheet, a subclass of ReadOnlyWorksheet). Styles remain eagerly loaded. Missing cells still return the EMPTY_CELL singleton and never densify the sheet. As with read_only, merges, comments, tables, and drawings are not bound.

Large sheet / shared-string parts (>16 MiB uncompressed) stream to a temp file instead of staying in a Python bytes object. Index construction scans that file via mmap, so spooling avoids a second full in-process copy—but peak RSS during inflate/index can still be on the order of the decompressed part while the ZIP member is being expanded and scanned. Prefixed SpreadsheetML tags such as <x:row> are not indexed in v1 (unprefixed Excel-style tags only); those cells appear missing.

Indexed mode is not aimed at beating a full sequential dump: index build plus a second pass can lose to normal or read_only iteration. Prefer it when the workload is dominated by sparse / random hits. There is no row-level parse cache yet, so many hits inside one row re-parse that row each time.

Write-only mode

Here again, the regular fastpyxl.worksheet.worksheet.Worksheet has been replaced by a faster alternative, the fastpyxl.worksheet._write_only.WriteOnlyWorksheet. When you want to dump large amounts of data make sure you have lxml installed.

>>> from fastpyxl import Workbook >>> wb = Workbook(write_only=True) >>> ws = wb.create_sheet() >>> >>> # now we'll fill it with 100 rows x 200 columns >>> >>> for irow in range(100): ... ws.append(['%d' % i for i in range(200)]) >>> # save the file >>> wb.save('new_big_file.xlsx') # doctest: +SKIP

If you want to have cells with styles or comments then use a fastpyxl.cell.WriteOnlyCell

>>> from fastpyxl import Workbook >>> wb = Workbook(write_only = True) >>> ws = wb.create_sheet() >>> from fastpyxl.cell import WriteOnlyCell >>> from fastpyxl.comments import Comment >>> from fastpyxl.styles import Font >>> cell = WriteOnlyCell(ws, value="hello world") >>> cell.font = Font(name='Courier', size=36) >>> cell.comment = Comment(text="A comment", author="Author's Name") >>> ws.append([cell, 3.14, None]) >>> wb.save('write_only_file.xlsx')

This will create a write-only workbook with a single sheet, and append a row of 3 cells: one text cell with a custom font and a comment, a floating-point number, and an empty cell (which will be discarded anyway).

Warning

  • Unlike a normal workbook, a newly-created write-only workbook does not contain any worksheets; a worksheet must be specifically created with the create_sheet() method.
  • In a write-only workbook, rows can only be added with append(). It is not possible to write (or read) cells at arbitrary locations with cell() or iter_rows().
  • It is able to export unlimited amount of data (even more than Excel can handle actually), while keeping memory usage under 10Mb.
  • A write-only workbook can only be saved once. After that, every attempt to save the workbook or append() to an existing worksheet will raise an fastpyxl.utils.exceptions.WorkbookAlreadySaved exception.
  • Everything that appears in the file before the actual cell data must be created before cells are added because it must written to the file before then. For example, freeze_panes should be set before cells are added.