打开Excel应用程序时如何使Python更改xlsx文件? [英] How to make Python change xlsx files when Excel app is opened?

问题描述

对不起，如果我问的是以前可能要问的问题，但我在Google和Stack Overflow论坛中找不到真正的答案.

Sorry if I ask a question that may have been asked before but I could not really find the answer in Google and Stack Overflow forums.

问题与openpyxl的用法有关，因为它是处理xlsx文件的最方便的库.

Question is connected with openpyxl usage as it is the most convenient library which works with xlsx files.

    import openpyxl

    wb = openpyxl.load_workbook("D:/Python.xlsx")
    sheet = wb.active

    i = 0

    sheet["A1"] = i

    wb.save("D:/Python.xlsx")

但是，它不适用于打开的excel文件.我收到错误消息

However, it does not work with an opened excel file. I get an error

    [Errno 13] Permission denied: 'D:/Python.xlsx'

我也发现了类似的问题:

I also found similar questions:

运行openpyxl python时出现PermissionError [errno 13]

写入打开的excel文档

该如何解决此错误并使Python在Excel中打开的文件起作用?我当前的版本是Python 2.7.另外，如果可能的话，在xlsm文件中制作相同魔术的解决方案是什么?

What can I do to solve this error and make Python work with an opened file in Excel? My current version is Python 2.7. Also, if possible, what is the solution of making the same magic in xlsm files?

UPD:

如果第一个问题没有解决方案(除了Google Docs，感谢@Alex)，是否可以编写使以下算法执行的代码: 1.关闭一个Excel应用程序并保存 2.用python做人员，然后将结果保存在Excel的某个目标位置 3.在Excel应用程序中打开Excel文件?

If there is no solution for the first question (except Google Docs maybe, thanks to @Alex), is it possible to write a code which makes the following algorithm perform: 1. Close an Excel app and save 2. Do staff in python, and save the results in some destination in Excel 3. Open Excel file in Excel app?

我知道该怎么做2.对1和3有何想法?

I know how to do 2. Any thoughts on 1 and 3?

UPD2:

xlwings确实做得很棒！正是我所需要的！谢谢！

xlwings really does awesome job! Just what I needed! Thanks!

我想提供一个对我有用的代码(只有其他用户可以在Google中找到并使用):

I want to provide a code that worked for me (just other users can find in Google and use):

   import xlwings as xl
   import time

   wb = xl.Workbook.active()
   sheet = wb.active

   iter = 10
   i = 0

   while True:
    i += 1
    if i <= iter:
     xl.Range("A1").value = i
     time.sleep(1)
     print(i)
    else:
     break

  wb.save()
  print("Done!")

推荐答案

OpenPyXL直接对文件进行操作；它与Excel程序无关，并且对Excel程序没有控制权.因此，如果要修改的文件已被Excel锁定，则OpenPyXL对此无能为力.

OpenPyXL operates directly on files; it has nothing to do with the Excel program, and has no control over the Excel program. As such, if the file it is trying to modify is locked by Excel, there is nothing OpenPyXL can do about that.

此问题被标记为excel-vba ，这当然是控制Excel程序的一种合适方法(因此，您可以在Excel中打开或关闭Excel时修改数据).如果您想要使用Python与Excel的接口，则目前最好的软件包是 xlwings .

This question is tagged with excel-vba and that is certainly one appropriate way to control the Excel program (so you can modify data while it is open in Excel, or close Excel). If you want an interface to Excel using Python, the best package these days is xlwings.

这篇关于打开Excel应用程序时如何使Python更改xlsx文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！