0

I'm using openpyxl to create worksheets that contain some custom Excel formulas. One of the custom Excel formulas returns an array, but I don't know the size of the array (i.e. number of rows or columns) ahead of time.

Suppose this is the custom Excel formula:

=GETVALUES(<some input>)

If I use this formula in Excel 365, the array will dynamically resize based on the return dimension of GETVALUES, as desired. But if I set the formula in openpyxl (e.g. ws["A1"].value = '=_xldudf_GETVALUES(<some input>)') then open this workbook in Excel 365, I get an @ sign after the equal sign.

I saw this article that suggests setting the formula_attributes: the t field to array and the ref field to a cell range. Since I don't know the size of the returned array, I don't think I'll know what to set for the ref field.

Is there a way to set the formula in openpyxl (without knowing the size of the returned array) such that Excel 365 will display the entire array dynamically? If not, is there another Excel API for Python that would be able to handle this?

mlin
  • 1
  • You can't rely on the `@` symbol in Excel to tell you anything other than something special is happening that you can't fully control in the GUI. Openpyxl 3.1 includes improved (and changed) support for both Array and Table formulae but I have no idea how relevant this is for this particular formula. – Charlie Clark Sep 08 '21 at 09:05

1 Answers1

0

I had a similar problem, and I am still looking for a good solution.

The temporary solution I found is to write the formula incorrect in python, then use find and replace in excel to fix the formula.

So if you want to put “=getvalues(text)” into a cell put

“=keyword123(text)” instead.

Then open the excel file, press Ctrl-h to open the “find and replace” window.

Replace

keyword123

with

getvalues

If you still have @ signs, find and replace @ with nothing. By default, find and replace works on the current active worksheet. You can press options to expand the search to the entire workbook. The important thing is to make sure the “keyword123” is a value not found anywhere else in the excel file.

Obviously this isn’t very efficient if you are producing multiple files, but works fine if everything is in one workbook.

Troy Robinson
  • 3
  • 1