2

I have the following data in Excel:

Column(A) Column(B)   Column(C)
Header1   Header2     Header3
A         100         USD
B         200         USD
C         300         USD
D         400         USD
E         500         USD
...       ...         ...

I need to add this data to a dictionary in the following format (dict with tuples):

my_dict = {
  "A":(100,"USD"),
  "B":(200,"USD"),
  "C":(300,"USD"),
  "D":(400,"USD"),
  "E":(500,"USD")
  ...
}

I have PyXll for Excel 2010 installed and working properly, and I am using Python 2.7.

In particular, I'm having trouble figuring out how to reference Excel cells with PyXll.

Sabuncu
  • 5,095
  • 5
  • 55
  • 89
Maxim
  • 507
  • 1
  • 7
  • 18

1 Answers1

3

Although this does not completely answer your question with the usage of pyxll, you could do something such as creating a dictionary from a '.csv' file. CSV is a file type that is supported as a save type within Excel. Creating a dictionary from a csv file?

    import csv

    with open('Book1.csv', mode='r') as infile:
        reader = csv.reader(infile)
        with open('coors_new.csv', mode='w') as outfile:
            writer = csv.writer(outfile)
            mydict = {rows[0]:[rows[1],rows[2]] for rows in reader}

    print mydict

Output>> {'A': ['100', 'USD'], 'C': ['300', 'USD'], 'B': ['200', 'USD'], 'E': ['500', 'USD'], 'D': ['400', 'USD'], 'Header 1': ['Header 2', 'Header 3']}

Hopefully this can at least be used to test functionality in your case.

Community
  • 1
  • 1
The2ndSon
  • 307
  • 2
  • 7
  • I've done something similar with xlrd where I simply loop over the sheet and construct the dictionary and it works great. But it requires that I save the excel file every time which defeats the purpose of what I'm trying to do. – Maxim Jun 23 '14 at 15:14