Connecting Excel with Python

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_SP开发者_开发知识库AN[1])]
#print xldata
a = np.asarray(list(xldata), dtype='object')
print a

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.

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

• http://snippets.dzone.com/posts/show/2036
• http://oreilly.com/catalog/pythonwin32/chapter/ch12.html