Connecting Excel with Python

Question

Using code below, I can get the data to print. How would switch code to xlrd?

How would modify this code to use a xls file that is already open and visible. So, file is open first manually, then script runs.

And, gets updated.

and then get pushed into Mysql

import os
from win32com.client import constants, Dispatch
import numpy as np

#----------------------------------------
# get data from excel file
#----------------------------------------
XLS_FILE = "C:\\xtest\\example.xls"
ROW_SPAN = (1, 16)
COL_SPAN = (1, 6)
app = Dispatch("Excel.Application")
app.Visible = True
ws = app.Workbooks.Open(XLS_FILE).Sheets(1)
xldata = [[ws.Cells(row, col).Value 
              for col in xrange(COL_SPAN[0], COL_SPAN[1])] 
             for row in xrange(ROW_SPAN[0], ROW_SPAN[1])]
#print xldata
a = np.asarray(list(xldata), dtype='object')
print a

Because it's awesome: pyspread

nmichaels
– nmichaels

2011-08-04 21:07:56 +00:00
Commented Aug 4, 2011 at 21:07 — nmichaels
– nmichaels, Commented Aug 4, 2011 at 21:07

cwallenpoole · Accepted Answer · 2011-08-04 22:58:23Z

1

If you mean that you want to modify the current file, I'm 99% sure that is not possible and 100% sure that it is a bad idea. In order to alter a file, you need to have write permissions. Excel creates a file lock to prevent asynchronous and simultaneous editing. If a file is open in Excel, then the only thing which should be modifying that file is... Excel.

If you mean that you want to read the file currently in the editor, then that is possible -- you can often get read access to a file in use, but it is similarly unwise -- if the user hasn't saved, then the user will see one set of data, and you'll have another set of data on disk.

While I'm not a fan of VB, that is a far better bet for this application -- use a macro to insert the data into MySQL directly from Excel. Personally, I would create a user with insert privileges only, and then I would try this tutorial.

answered Aug 4, 2011 at 22:58

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Merlin Over a year ago

I have done this in VBA, looking for python solution. I would like excel change excel. And, python do numpy math and the movement into mysql. But, with above code a "new" Workopens every time.

cwallenpoole Over a year ago

Have you considered using OpenOffice? That has Python bindings.

dgorissen · Accepted Answer · 2011-10-19 16:40:09Z

0

If you want to manipulate an already open file, why not use COM?

answered Oct 19, 2011 at 16:40

dgorissen

6,3253 gold badges48 silver badges55 bronze badges

Collectives™ on Stack Overflow

Connecting Excel with Python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related