How to use pyExcelerator

by Yuji

I found this blog after hours of searching, and I keep referring back to it. I forgot what it was and started searching for it again and had to use terms like pyExcelerator + blog to find the guide.

Anyways, this is a great help for the not so documented pyExcelerator.

http://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-my-sort.html

 

Note that what comes after here is mainly for me to read back on in the future. I don’t think it makes a whole lotta sense.

I just created something to handle our orders. Currently we have to manually take orders and type out an excel file to do that.

Its the classic programming idea: Spend an hours to finish something in 5 minutes, instead of spending an hour to do it by hand.

Our platform gives us orders in CSV format. Our logistics people take it in a different format. I don’t want to do each by hand.

I wrote something to take the CSV with data, and then a CSV that has a template.

I used the python csv library to split the CSV into keys (the first row) and filled a dictionary with each key having its corresponding value. I then split the template CSV’s data fields into dict keys containing a pair of numbers (row, column).
Therefore, if the original CSV had a field called “BillAddress1″, I would make a template that has the same field and the script would spit back the coordinates.

That way I can write the data from this list into the right spots in the template.

Template CSV to Keys and Coordinates:
reader = csv.reader(file(filename))
 self.d = {}
  reader_list = []
  for item in reader:
  %nbsp;reader_list.append(item)

  for item in reader_list:
    for subitem in item:
      if subitem:
        self.d[subitem] = (reader_list.index(item), item.index(subitem))
    return self.d

This spits out a dictionary of whatever items are in the CSV that correspond to their positions.
If you filled one field in the entire sheet with “Hello” in position (10, 5), it would return a dictionary with key: “Hello” and value (10, 5)

I used those values to position my data in the actual sheet.
For example, say the real CSV has 3 rows.
| Name | Sex | Age |
| Yuji | M | 21 |
| Bob | M | 50 |

My script converts the CSV into a list, so that it can be iterated over.
Simply declare a new list and loop through the object returned by csv.reader() and append each row to the blank list.

I then make a copy of the first item (the keys), and remove that from the list. List.pop(0)
Then, I convert each item in the list to dictionary keys.

So List[0][‘KEY’] = value

Now I just need to create the writing mechanism.

I convert a CSV template to decide where our data goes.

Say I have this template:
| Name | | |
| Age | | |
| | | Sex |

My script converts those into dict keys w/ location pairs. So dict[‘Name’] = 0,0

Therefore, when I write using pyExcelerator:
self.keys = the dictionary with location pairs (the template)
self.orders = list of dictionaries like so [{a:b,c:d}, {a:b, c:d}]


for order in self.orders:
 sheet = self.workbook.add_sheet("Order")
 for item in self.keys:
  a, b = self.keys[item]
  sheet.write(a, b, item)
  sheet.write(a, b+1, order[item])

Done!

About these ads