使用xlwings创建的动态数组导致公式被包裹在{}中。

huangapple
go评论127阅读模式
英文:

Dynamic array using xlwings causing formula to be wrapped with {}

问题

我正在尝试使用xlwings填充一个单元格，使用动态数组公式，但每次尝试时，公式要么在等号后面加上@，要么将公式放在{}之间，我似乎无法解决这个问题。以下是相关函数的代码：

  1. def process_files():
  2.     # 禁用按钮并更改图像
  3.     button_1.configure(state=tk.DISABLED, image=button_image_1_processing)
  4.     window.update_idletasks()  # 强制GUI更新
  6.     try:
  7.         update_entry_2('Process Started...')
  8.         directory = entry_1.get()
  9.         if not directory:
  10.             update_entry_2("Directory not selected.")
  11.             return
  13.         start_date_str = entry_6.get()
  14.         end_date_str = entry_5.get()
  15.         forward_date_str = entry_4.get()
  17.         if not validate_date(start_date_str):
  18.             update_entry_2('Start Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
  19.             return
  21.         if not validate_date(end_date_str):
  22.             update_entry_2('End Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
  23.             return
  25.         if not validate_date(forward_date_str):
  26.             update_entry_2('Forward Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
  27.             return
  29.         # 将日期从字符串转换为datetime对象
  30.         start_date = datetime.strptime(start_date_str, "%d/%m/%Y")
  31.         end_date = datetime.strptime(end_date_str, "%d/%m/%Y")
  32.         forward_date = datetime.strptime(forward_date_str, "%d/%m/%Y")
  34.         # 检查结束日期是否早于开始日期
  35.         if end_date < start_date:
  36.             update_entry_2("End date cannot be earlier than start date.")
  37.             return
  39.         # 检查前进日期是否早于开始日期
  40.         if forward_date < start_date:
  41.             update_entry_2("Forward date cannot be earlier than start date.")
  42.             return
  44.         # 检查结束日期是否早于前进日期
  45.         if end_date < forward_date:
  46.             update_entry_2("End date cannot be earlier than forward date.")
  47.             return
  49.         if not validate_currency(entry_3.get()):
  50.             update_entry_2('Account balance is not correct, this must be a number.')
  51.             return
  54.         xml_files = [f for f in os.listdir(directory) if f.endswith('.xml')]
  55.         paired_files = [(f, f.replace('.forward', '')) for f in xml_files if
  56.                         f.endswith('.forward.xml') and f.replace('.forward', '') in xml_files]
  58.         for fwd_file, back_file in paired_files:
  59.             update_entry_2(f"{fwd_file} Found, Converting to XLSX")
  61.             fwd_file_path = os.path.join(directory, fwd_file)
  62.             back_file_path = os.path.join(directory, back_file)
  63.             template_path = os.path.join(directory, 'SetFinderTemplate.xlsx')
  64.             fwd_xlsx_path = os.path.join(directory, fwd_file.replace('.xml', '.xlsx'))
  65.             back_xlsx_path = os.path.join(directory, back_file.replace('.xml', '.xlsx'))
  66.             output_file_path = os.path.join(directory, fwd_file.replace('.forward.xml', '_Filtered.xlsx'))
  68.             # 将XML转换为XLSX
  69.             convert_xml_to_xlsx(fwd_file_path, fwd_xlsx_path)
  70.             convert_xml_to_xlsx(back_file_path, back_xlsx_path)
  71.             update_entry_2('Files converted, importing data into Jake’s Template')
  73.             # 加载Excel模板
  74.             template_wb = load_workbook(template_path)
  76.             # 加载数据帧
  77.             fwd_df = pd.read_excel(fwd_xlsx_path)
  78.             back_df = pd.read_excel(back_xlsx_path)
  80.             # 确保列的顺序正确
  81.             fwd_columns = ['Pass', 'Forward Result', 'Back Result', 'Profit', 'Expected Payoff', 'Profit Factor',
  82.                         'Recovery Factor', 'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']
  83.             back_columns = ['Pass', 'Result', 'Profit', 'Expected Payoff', 'Profit Factor', 'Recovery Factor',
  84.                         'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']
  86.             fwd_df = fwd_df[fwd_columns]
  87.             back_df = back_df[back_columns]
  89.             # 获取工作表
  90.             fwd_sheet = template_wb['Fwd Data']
  91.             back_sheet = template_wb['Back Data']
  93.             # 将数据帧写入工作表
  94.             for i, row in enumerate(fwd_df.values, start=3):
  95.                 for j, value in enumerate(row, start=6):  # 从列F（6）开始
  96.                     fwd_sheet.cell(row=i, column=j, value=value)
  98.             for i, row in enumerate(back_df.values, start=3):
  99.                 for j, value in enumerate(row, start=1):
  100.                     back_sheet.cell(row=i, column=j, value=value)
  102.             instructions_notes_sheet = template_wb['INSTRUCTIONSNOTES']
  103.             instructions_notes_sheet['B15'] = entry_6.get()  # 开始日期
  104.             instructions_notes_sheet['B17'] = entry_5.get()  # 结束日期
  105.             instructions_notes_sheet['B16'] = entry_4.get()  # 前进日期
  106.             instructions_notes_sheet['B18'] = entry_3.get()  # 账户余额
  108.             # 保存工作簿
  109.             template_wb.save(output_file_path)
  110.             update_entry_2('Template populated and Filtered file saved')
  112.             # 加载工作簿
  113.             wb = xw.Book(output_file_path)
  115.             # 获取工作表
  116.             sheet = wb.sheets['RESULTS-ExcelVersion']
  118.             # 将公式分配给单元格A9
  119.             sheet.range(
  120.                 'A9').formula_array = '=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")'
  122.             # 保存并关闭工作簿
  123.             wb.save()
  124.             wb.close()

我不是很擅长编程，我已经尝试阅读了xlwings文档以及openpyxl和XlsxWriter，但都没有解决这个问题。我想要在单元格A9中的公式是：

=IFERROR(SORT(FILTER('Fwd Data'!B:P,'Fwd Data'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")

我还尝试在不带等号的情况下将公式放在单元格中，并在文件创建后由Python添加等号，但这也导致错误。我使用的是Office 365，并且

英文:

I am trying to populate a cell with a dynamic array formula using xlwings but each time I try the formula either ends up with @ after the = or the formula is being put inbetween {} and I can't seem to fix this. This is the code for the function in question:

  1. def process_files():
  2.     # Disable button and change image
  3.     button_1.configure(state=tk.DISABLED, image=button_image_1_processing)
  4.     window.update_idletasks()  # Force GUI update
  6.     try:
  7.         update_entry_2(&#39;Process Started...&#39;)
  8.         directory = entry_1.get()
  9.         if not directory:
  10.             update_entry_2(&quot;Directory not selected.&quot;)
  11.             return
  13.         start_date_str = entry_6.get()
  14.         end_date_str = entry_5.get()
  15.         forward_date_str = entry_4.get()
  17.         if not validate_date(start_date_str):
  18.             update_entry_2(&#39;Start Date is not in the correct format. Please enter a date in the format &quot;DD-MM-YYYY&quot;.&#39;)
  19.             return
  21.         if not validate_date(end_date_str):
  22.             update_entry_2(&#39;End Date is not in the correct format. Please enter a date in the format &quot;DD-MM-YYYY&quot;.&#39;)
  23.             return
  25.         if not validate_date(forward_date_str):
  26.             update_entry_2(&#39;Forward Date is not in the correct format. Please enter a date in the format &quot;DD-MM-YYYY&quot;.&#39;)
  27.             return
  29.         # Convert dates from strings to datetime objects
  30.         start_date = datetime.strptime(start_date_str, &quot;%d/%m/%Y&quot;)
  31.         end_date = datetime.strptime(end_date_str, &quot;%d/%m/%Y&quot;)
  32.         forward_date = datetime.strptime(forward_date_str, &quot;%d/%m/%Y&quot;)
  34.         # Check if end date is earlier than start date
  35.         if end_date &lt; start_date:
  36.             update_entry_2(&quot;End date cannot be earlier than start date.&quot;)
  37.             return
  39.         # Check if forward date is earlier than start date
  40.         if forward_date &lt; start_date:
  41.             update_entry_2(&quot;Forward date cannot be earlier than start date.&quot;)
  42.             return
  44.         # Check if end date is earlier than forward date
  45.         if end_date &lt; forward_date:
  46.             update_entry_2(&quot;End date cannot be earlier than forward date.&quot;)
  47.             return
  49.         if not validate_currency(entry_3.get()):
  50.             update_entry_2(&#39;Account balance is not correct, this must be a number.&#39;)
  51.             return
  54.         xml_files = [f for f in os.listdir(directory) if f.endswith(&#39;.xml&#39;)]
  55.         paired_files = [(f, f.replace(&#39;.forward&#39;, &#39;&#39;)) for f in xml_files if
  56.                         f.endswith(&#39;.forward.xml&#39;) and f.replace(&#39;.forward&#39;, &#39;&#39;) in xml_files]
  58.         for fwd_file, back_file in paired_files:
  59.             update_entry_2(f&quot;{fwd_file} Found, Converting to XLSX&quot;)
  61.             fwd_file_path = os.path.join(directory, fwd_file)
  62.             back_file_path = os.path.join(directory, back_file)
  63.             template_path = os.path.join(directory, &#39;SetFinderTemplate.xlsx&#39;)
  64.             fwd_xlsx_path = os.path.join(directory, fwd_file.replace(&#39;.xml&#39;, &#39;.xlsx&#39;))
  65.             back_xlsx_path = os.path.join(directory, back_file.replace(&#39;.xml&#39;, &#39;.xlsx&#39;))
  66.             output_file_path = os.path.join(directory, fwd_file.replace(&#39;.forward.xml&#39;, &#39;_Filtered.xlsx&#39;))
  68.             # Convert XML to XLSX
  69.             convert_xml_to_xlsx(fwd_file_path, fwd_xlsx_path)
  70.             convert_xml_to_xlsx(back_file_path, back_xlsx_path)
  71.             update_entry_2(&#39;Files converted, importing data into Jake’s Template&#39;)
  73.             # Load the excel template
  74.             template_wb = load_workbook(template_path)
  76.             # Load dataframes
  77.             fwd_df = pd.read_excel(fwd_xlsx_path)
  78.             back_df = pd.read_excel(back_xlsx_path)
  80.             # Make sure the columns are in the correct order
  81.             fwd_columns = [&#39;Pass&#39;, &#39;Forward Result&#39;, &#39;Back Result&#39;, &#39;Profit&#39;, &#39;Expected Payoff&#39;, &#39;Profit Factor&#39;,
  82.                         &#39;Recovery Factor&#39;, &#39;Sharpe Ratio&#39;, &#39;Custom&#39;, &#39;Equity DD %&#39;, &#39;Trades&#39;]
  83.             back_columns = [&#39;Pass&#39;, &#39;Result&#39;, &#39;Profit&#39;, &#39;Expected Payoff&#39;, &#39;Profit Factor&#39;, &#39;Recovery Factor&#39;,
  84.                         &#39;Sharpe Ratio&#39;, &#39;Custom&#39;, &#39;Equity DD %&#39;, &#39;Trades&#39;]
  86.             fwd_df = fwd_df[fwd_columns]
  87.             back_df = back_df[back_columns]
  89.             # Get the sheets
  90.             fwd_sheet = template_wb[&#39;Fwd Data&#39;]
  91.             back_sheet = template_wb[&#39;Back Data&#39;]
  93.             # Write dataframes to the sheets
  94.             for i, row in enumerate(fwd_df.values, start=3):
  95.                 for j, value in enumerate(row, start=6):  # Start from column F (6)
  96.                     fwd_sheet.cell(row=i, column=j, value=value)
  98.             for i, row in enumerate(back_df.values, start=3):
  99.                 for j, value in enumerate(row, start=1):
  100.                     back_sheet.cell(row=i, column=j, value=value)
  102.             instructions_notes_sheet = template_wb[&#39;INSTRUCTIONSNOTES&#39;]
  103.             instructions_notes_sheet[&#39;B15&#39;] = entry_6.get()  # Start Date
  104.             instructions_notes_sheet[&#39;B17&#39;] = entry_5.get()  # End Date
  105.             instructions_notes_sheet[&#39;B16&#39;] = entry_4.get()  # Forward Date
  106.             instructions_notes_sheet[&#39;B18&#39;] = entry_3.get()  # Account balance
  108.             # Save workbook
  109.             template_wb.save(output_file_path)
  110.             update_entry_2(&#39;Template populated and Filtered file saved&#39;)
  112.             # Load workbook
  113.             wb = xw.Book(output_file_path)
  115.             # Get the sheet
  116.             sheet = wb.sheets[&#39;RESULTS-ExcelVersion&#39;]
  118.             # Assign the formula to cell A9
  119.             sheet.range(
  120.                 &#39;A9&#39;).formula_array = &#39;=IFERROR(SORT(FILTER(\&#39;Fwd Data\&#39;!B:P,\&#39;Fwd Data\&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)&#39;
  122.             # Save and close the workbook
  123.             wb.save()
  124.             wb.close()

I am not super experienced with code and I have tried reading xlwings documentation along with openpyxl and also XlsxWriter to try and solve this problem but just keep hitting brick wall after brick wall. The formula I want to have in cell A9 is;

  1. =IFERROR(SORT(FILTER(&#39;Fwd Data&#39;!B:P,&#39;Fwd Data&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)

I have also tried putting the formula in the cell without the = and just getting python to add the = for me after the file has been created but this also resulted in an error. I have Office 365 and it is fully up to date so I know the formula is supported and when putting it in manually I get the result I wanted.

I have read through the forums and seen This post but no solutions I have seen have given results as yet. Also a lot of the posts are a year plus old so I was hoping that there may be a solution to this by now.

Any help anyone can offer is appreciated. Thank you

答案1

得分: 0

以下是翻译好的部分：

这个问题涉及到你的代码中的这些行：

  1. # Assign the formula to cell A9
  2. sheet.range(
  3.     '&#39;A9&#39;).formula_array = '&#39;=IFERROR(SORT(FILTER(\&#39;Fwd Data\&#39;!B:P,\&#39;Fwd Data\&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)&#39;'

考虑一下使用xlwings将公式返回到Excel单元格的以下三种变体：

  1. formula = &quot;&quot;&quot;=IFERROR(SORT(FILTER(\&#39;Fwd Data\&#39;!B:P,\&#39;Fwd Data\&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)&quot;&quot;&quot;
  3. # 1: .formula_array  &#39;={...}&#39;
  4. sheet.range(&quot;A9&quot;).formula_array = formula
  5. # 2: .formula  &#39;=@&#39;
  6. sheet.range(&quot;A9&quot;).formula = formula
  7. # 3: .formula2  &#39;=&#39;
  8. sheet.range(&quot;A9&quot;).formula2 = formula

你会发现，选项1的输出包含{}，选项2包含=@，而选项3只有=。

选项1 - .formula_array

这返回一个公式数组，也可以通过在Excel中突出显示多个单元格，在其中一个单元格中键入公式，然后按Ctrl + Shift + Enter来重新创建。

此Microsoft网页提供了关于何时使用这种方法的很好的解释。

选项2和3 - .formula 和 .formula2

正如你在帖子中提到的，这个答案对两者之间的区别提供了很好的解释。

Microsoft在这里解释了.formula2，其中还包含了两者的非常详细的比较链接。这个变体允许公式的输出溢出。

总结

所以，如果你不希望在返回的公式中出现{}或@，你应该在你的代码中使用.formula2。

还请注意，我在Excel公式周围使用了三重引号&quot;&quot;&quot;，因为公式中包含了&#39;和&quot;。

英文:

The question concerns these lines in your code:

  1. # Assign the formula to cell A9
  2. sheet.range(
  3.     &#39;A9&#39;).formula_array = &#39;=IFERROR(SORT(FILTER(\&#39;Fwd Data\&#39;!B:P,\&#39;Fwd Data\&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)&#39;

Consider the following three variants of returning a formula to an Excel cell with xlwings:

  1. formula = &quot;&quot;&quot;=IFERROR(SORT(FILTER(\&#39;Fwd Data\&#39;!B:P,\&#39;Fwd Data\&#39;!A:A=TRUE),2,-1),&quot;NO RESULTS FOUND WITHIN THESE THRESHOLDS&quot;)&quot;&quot;&quot;
  3. # 1: .formula_array  &#39;={...}&#39;
  4. sheet.range(&quot;A9&quot;).formula_array = formula
  5. # 2: .formula  &#39;=@&#39;
  6. sheet.range(&quot;A9&quot;).formula = formula
  7. # 3: .formula2  &#39;=&#39;
  8. sheet.range(&quot;A9&quot;).formula2 = formula

You will find that the output for option 1 contains {}, option 2 has =@, and option 3 is just =.

Option 1 - .formula_array

This returns an array of formula, which can also be recreated by highlighting multiple cells in Excel, typing a formula in one and pressing Ctrl + Shift + Enter.

This Microsoft webpage provides a good explanation on when this might be used.

Options 2 and 3 - .formula and .formula2

As mentioned in your post, this answer provides a good explanation on the differences between the two.

.formula2 is explained here by Microsoft, which also contains a link to a very detailed comparison of the two. This variation allows for spilling of outputs from the formula.

Summary

So, if you do not want either {} or @ in the returned formula, you should use .formula2 in your code.

Also note that I have used triple quotes &quot;&quot;&quot; around of the Excel formula, because the formula includes both &#39; and &quot;.

huangapple
  • 本文由 发表于 2023年6月2日 00:51:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/76384106.html
  • arrays
  • excel
  • python
  • xlwings
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定