“@” symbol appearing after inserting IF formula into excel using Openpyxl

by

Answering my own question here. Thanks to JvdV for putting me on the right path.

In this answer I found what solved my question. I added this line before saving the excel file in my example code:

ws.formula_attributes['E9'] = {'t': 'array', 'ref': "E9:E9"}

This essentially sets the formula in cell ‘E9’ to be read as an array formula, which is shown in Excel by having the formula between curly braces ({}). In older versions of Excel this is done by, pressing ctrl-shift-enter when entering the formula. Note that writing these curly braces in the formula string does not work.

If anyone knows how the ‘ref’ part works, feel free add this in the comments.

More Related Contents:

  1. How to save a new sheet in an existing excel file, using Pandas?
  2. Read Excel cell value and not the formula computing it -openpyxl
  3. Editing workbooks with rich text in openpyxl
  4. Iterate over Worksheets, Rows, Columns
  5. Pandas: Looking up the list of sheets in an excel file
  6. How to access the real value of a cell using the openpyxl module for python
  7. Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl
  8. How to concatenate three excels files xlsx using python?
  9. Password Protecting Excel file using Python
  10. How to copy worksheet from one workbook to another one using openpyxl?
  11. Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?
  12. Modify an existing Excel file using Openpyxl in Python
  13. How to find the last row in a column using openpyxl normal workbook?
  14. python: why does replace not work?
  15. Fill cells with colors using openpyxl?
  16. Python openpyxl data_only=True returning None
  17. Insert row into Excel spreadsheet using openpyxl in Python
  18. openpyxl load_workbook() on a legit .xlsx file leads to a zipfile.BadZipFile error
  19. getting the row and column numbers from coordinate value in openpyxl
  20. xlsx and xlsm files return badzipfile: file is not a zip file
  21. Get cell color from .xlsx
  22. How to write a list to Excel column?
  23. No module named ‘openpyxl’ – Python 3.4 – Ubuntu
  24. Is it possible to get an Excel document’s row count without loading the entire document into memory?
  25. Openpyxl check for empty cell
  26. python: update dataframe to existing excel sheet without overwriting contents on the same sheet and other sheets
  27. How to write to an existing excel file without breaking formulas with openpyxl?
  28. Openpyxl max_row and max_column wrongly reports a larger figure
  29. Get sheet by name using openpyxl
  30. Setting styles in Openpyxl

Leave a Comment