3

I am trying to find a library that overwrites an existing cell to change its contents using Python.

what I want to do:

  1. read from .xlsx file
  2. compare cell data determine if change is needed.
  3. change data in cell Eg. overwrite date in cell 'O2'
  4. save file.

I have tried the following libraries:

    • xlsxwriter
  2. combination of:
    • xlrd
    • xlwt
    • xlutils
    • openpyxl

xlsxwriter only writes to a new excel sheet and file. combination: works to read from .xlsx but only writes to .xls openpyxl: reads from existing file but doesn't write to existing cells can only create new rows and cells, or can create entire new workbook

Any suggestions would greatly be appreciated. Other libraries? how to manipulate the libraries above to overwrite data in an existing file?

user2853442
  • 331
  • 5
  • 15
  • Why no mention of driving excel via a COM object? It's usually the wrong answer, but when external libraries fall short it's the right one. (This assumes that you are using Windows and have Excel installed.) – Steven Rumbalski Feb 12 '15 at 17:58
  • Yes I have windows and excel installed. But I am not familiar with a COM object(what it is and how to use it). I have about 100 .xlsx files that I want to overwrite specific cells with new data. I thought python would be easiest. but so far I was wrong – user2853442 Feb 12 '15 at 18:02
  • See my answer to [Opening the Excel application from Python](http://stackoverflow.com/questions/6282230/opening-the-excel-application-from-python). – Steven Rumbalski Feb 12 '15 at 18:16
  • So once I open it how would I go about changing data in a single cell? – user2853442 Feb 12 '15 at 21:46

2 Answers2

4
from win32com.client import Dispatch
import os

xl = Dispatch("Excel.Application")
xl.Visible = True # otherwise excel is hidden

# newest excel does not accept forward slash in path
wbs_path = r'C:\path\to\a\bunch\of\workbooks'

for wbname in os.listdir(wbs_path):
    if not wbname.endswith(".xlsx"):
        continue
    wb = xl.Workbooks.Open(wbs_path + '\\' + wbname)
    sh = wb.Worksheets("name of sheet")
    sh.Range("A1").Value = "some new value"
    wb.Save()
    wb.Close()
xl.Quit()
Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
  • 1
    Wow I can't thank you enough. That is an awesome solution for exactly what I was trying to do! I need to look into pywin32 more in depth, it looks like there is alot of useful things in it. Thank-you – user2853442 Feb 12 '15 at 22:33
0

Alternatively you can use xlwing, which (if I had to guess) seems to be using this approach under the hood.

>>> import xlwings as xw
>>> wb = xw.Book()  # this will create a new workbook
>>> wb = xw.Book('FileName.xlsx')  # connect to an existing file in the current working directory
>>> wb = xw.Book(r'C:\path\to\file.xlsx')  # on Windows: use raw strings to escape backslashes
cdiggins
  • 17,602
  • 7
  • 105
  • 102