Data Validations & Conditional Formattings with write_only=True

[Nicolas Rozain]

Hello,

I recently faced an issue where I had to create 150 MB Excel file with nothing else than int, float, str, bool, data validations (lists) and conditional formattings (colors).

The default mode in which I created the workbook (=without any argument) explode the memory usage (16 GB) of my server so I had to enable the write_only parameter.

It fixed the RAM consumption (only 2-3% of total RAM are used now) but data validations and conditional formattings caused Tracebacks, telling that WriteOnlyWorksheet does not allow such definition.

However, after a quick look into OpenPyXL's code, I found out that I could declare data_validations and conditional_formatting attributes on concerned sheets (to be respectively openpyxl.worksheet.datavalidation.DataValidationList and openpyxl.formatting.formatting.ConditionalFormattingList), use them as usual and it should be used to generate the Excel file.

I did this "hack" at it seems to work fine.

Could you tell me if this usage is risky? Do you intent to propose this in future release?

I use OpenPyXL 3.0.7.

Many thanks in advance for your support.

My code looks like this:

wb = openpyxl.Workbook(write_only=True)
sheet = wb.create_sheet(title="MySheet")

if not hasattr(sheet, "data_validations"):
     sheet.data_validations = openpyxl.worksheet.datavalidation. DataValidationList()
dv = DataValidation(
    type="list",
    formula1='"add,delete,update"',
    allow_blank=True)
sheet.data_validations.append(dv)
dv.add('A2:A10')

if not hasattr(sheet, "conditional_formatting"):
    sheet.conditional_formatting = openpyxl.formatting.formatting. ConditionalFormattingList()
sheet.conditional_formatting.add(
    'B2:B10' ,
    openpyxl.formatting.rule.FormulaRule(
        formula=['ISBLANK($A1)'],
        stopIfTrue=True,
        fill=openpyxl.styles.PatternFill(

            start_color='FA8072',

            end_color='FA8072',

            fill_type='solid')))

wb.save('MyExcel.xlsx")

[Charlie Clark]

On 8 Jun 2021, at 11:59, Nicolas Rozain wrote:

I did this "hack" at it seems to work fine.
Could you tell me if this usage is risky? Do you intent to propose this in
future release?

One of the major changes in openpyxl 3.x was that the code for the various type of worksheet was harmonised, meaning that more features are likely to be supported. When it comes to write-only mode, pretty much anything that is stored in the XML after the cell data should be possible. Conversely, things like column formats have to be written before cell data and so are more difficult to deal. And the situation for read-only worksheets is reversed: you can look at column definitions but conditional formatting, etc. is at the bottom of the file largely negating any speed advantages.

There are, however, some challenges: if you want to use tables then you'll need to do some work yourself that openpyxl normally does. As the same could be true for other features I haven't got round to adding them yet but it should be fairly straightforward. Feel free to submit a PR… ;-)

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226