使用 XlsxWriter 将数据框和按钮添加到同一张表 [英] Add dataframe and button to same sheet with XlsxWriter

问题描述

我能够创建一个 excel 文件，其中一张是数据框中的数据，另一张是运行宏的按钮我需要的是数据帧中的数据而不是同一张表中的按钮

I am able to create an excel file with in one sheet the data from a data frame and in a second sheet a button to run a macro What I need is to have both the data from the dataframe than the button in the same sheet

这是我发现我尝试修改的代码:

This is the code I found that I have tried to modify:

import pandas as pd
import xlsxwriter

df = pd.DataFrame({'Data': [10, 20, 30, 40]})

writer = pd.ExcelWriter('hellot.xlsx', engine='xlsxwriter')
worksheet = workbook.add_worksheet()
#df.to_excel(writer, sheet_name='Sheet1')

workbook  = writer.book
workbook.filename = 'test.xlsm'
worksheet = workbook.add_worksheet()
workbook.add_vba_project('./vbaProject.bin')

worksheet.write('A3', 'Press the button to say hello.')

#Add a button tied to a macro in the VBA project.
worksheet.insert_button('A1', {'macro':   'start',
                              'caption': 'Press Me',
                              'width':   80,
                              'height':  30})

df.to_excel(writer, sheet_name ='Sheet2') 

writer.save()


workbook.close()

推荐答案

我知道你只是问如何在同一个工作表中插入按钮，但我决定检查宏如何与 xlsxwriter 一起工作，所以我写了一个完整的关于如何添加宏的教程.

I know that you simply asked how to insert the button in the same sheet but i decided to check how the macros are working with xlsxwriter, so i wrote a complete tutorial on how to add a macro.

1) 首先，我们需要手动创建一个包含宏的文件，以便将其提取为 bin 文件并稍后使用 xlsxwriter 注入.所以我们创建一个新的excel文件，进入Developer选项卡，Visual Basic，Insert Module并编写以下代码:

1) Firstly we need to create manually a file which will contain the macro in order to extract it as a bin file and inject it later using xlsxwriter. So we create a new excel file, go to Developer tab, Visual Basic, Insert Module and write the following code:

Sub TestMsgBox()
    MsgBox "Hello World!"
End Sub

使用 xlsm 扩展名保存文件以包含宏，例如作为 Book1.xlsm.

Save the file with xlsm extension to contain the macro, e.g. as Book1.xlsm.

2) 现在我们需要解压 bin 文件.打开您的 cmd 并浏览到您保存 Book1.xlsm 的目录.然后通过文件资源管理器浏览到安装 python 的文件夹(或虚拟环境文件夹)并搜索 vba_extract.py.将此脚本复制到与 Book1.xlsm 相同的文件夹中.然后输入cmd:

2) Now we need to extract the bin file. Open your cmd and browse to the directory where you have saved the Book1.xlsm. Then browse through file explorer to the folder where you have installed python (or to the virtual environment folder) and search for vba_extract.py. Copy this script into the same folder as the Book1.xlsm. Then type in cmd:

python vba_extract.py Book1.xlsm

通过这种方式，您将提取宏并在同一文件夹中创建 vbaProject.bin 文件.

This way you will extract the macro and create the vbaProject.bin file in the same folder.

3) 现在是创建最终文件的时候了.删除 Book1.xlsm 和 vba_extract.py 文件，因为不再需要它们并运行以下代码:

3) Now it's time to create the final file. Delete the Book1.xlsm and the vba_extract.py files as they 're not needed anymore and run the following code:

import pandas as pd

# Create a test dataframe
df = pd.DataFrame({'Data': [10, 20, 30, 40]})

# Import it through the xlsxwriter
writer = pd.ExcelWriter('hello_world.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Create the workbook and the worksheet
workbook = writer.book
workbook.filename = 'hello_world.xlsm' # rename the workbook to xlsm extension
worksheet = writer.sheets['Sheet1']

# Inject the bin file we extracted earlier
workbook.add_vba_project('./vbaProject.bin')

# Insert a description
worksheet.write('B1', 'Press the button to say hello.')

#Add a button tied to a macro in the VBA project.
worksheet.insert_button('B2', {'macro': 'TestMsgBox',
                              'caption': 'Press Me',
                              'width': 80, 'height':  30})

# Finally write the file
writer.save()

现在按钮与您的数据和工作在同一张表中:

Now button is in the same sheet as your data and working:

这篇关于使用 XlsxWriter 将数据框和按钮添加到同一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！