Python script to extract excel block and format for latex table - requires pyGTK and pyExelerator


#!/usr/bin/env python

import pygtk
pygtk.require('2.0')
import gtk, gobject
import win32clipboard, win32con, random
from pyExcelerator import *

# Check for new pygtk: this is new class in PyGtk 2.4
if gtk.pygtk_version < (2,3,90):
print "PyGtk 2.3.90 or later required for this example"
raise SystemExit

dialog = gtk.FileChooserDialog("Open..",
None,
gtk.FILE_CHOOSER_ACTION_OPEN,
(gtk.STOCK_CANCEL, gtk.RESPONSE_CANCEL,
gtk.STOCK_OPEN, gtk.RESPONSE_OK))

dialog.set_default_response(gtk.RESPONSE_OK)

filter = gtk.FileFilter()
filter.set_name("Excel files")
filter.add_pattern("*.xls")
dialog.add_filter(filter)
dialog.set_current_folder("F:\\Projects\\2009\\NCAT\\Synthesis\\Excel files\\")

response = dialog.run()

if response == gtk.RESPONSE_OK:
xl = dialog.get_filename()
print xl
data = parse_xls(xl)
sheet_name=data[3][0].encode('cp866', 'backslashreplace')
values = data[3][1]
s1 = " " + str(int(values[(4,1)])) + " & " + str('%.2f' % round(values[(4,2)],2)) + " & " + str('%.2f' % round(values[(4,3)],2)) + " & " + str('%.2f' % round(values[(4,7)],2)) + " & " + str('%.2f' % round(values[(4,8)],2)) + " & " + str('%.2f' % round(values[(4,12)],2)) + " & " + str('%.2f' % round(values[(4,13)],2)) + r" \\" + "\r"
s2 = " " + str(int(values[(5,1)])) + " & " + str('%.2f' % round(values[(5,2)],2)) + " & " + str('%.2f' % round(values[(5,3)],2)) + " & " + str('%.2f' % round(values[(5,7)],2)) + " & " + str('%.2f' % round(values[(5,8)],2)) + " & " + str('%.2f' % round(values[(5,12)],2)) + " & " + str('%.2f' % round(values[(5,13)],2)) + r" \\" + "\r"
s3 = " " + str(int(values[(6,1)])) + " & " + str('%.2f' % round(values[(6,2)],2)) + " & " + str('%.2f' % round(values[(6,3)],2)) + " & " + str('%.2f' % round(values[(6,7)],2)) + " & " + str('%.2f' % round(values[(6,8)],2)) + " & " + str('%.2f' % round(values[(6,12)],2)) + " & " + str('%.2f' % round(values[(6,13)],2)) + r" \\" + "\r"
s4 = " " + str(int(values[(7,1)])) + " & " + str('%.2f' % round(values[(7,2)],2)) + " & " + str('%.2f' % round(values[(7,3)],2)) + " & " + str('%.2f' % round(values[(7,7)],2)) + " & " + str('%.2f' % round(values[(7,8)],2)) + " & " + str('%.2f' % round(values[(7,12)],2)) + " & " + str('%.2f' % round(values[(7,13)],2)) + r" \\" + "\r"
s5 = " " + str(int(values[(8,1)])) + " & " + str('%.2f' % round(values[(8,2)],2)) + " & " + str('%.2f' % round(values[(8,3)],2)) + " & " + str('%.2f' % round(values[(8,7)],2)) + " & " + str('%.2f' % round(values[(8,8)],2)) + " & " + str('%.2f' % round(values[(8,12)],2)) + " & " + str('%.2f' % round(values[(8,13)],2)) + r" \\" + "\r"
s6 = str(int(values[(9,1)])) + " & " + str('%.2f' % round(values[(9,2)],2)) + " & " + str('%.2f' % round(values[(9,3)],2)) + " & " + str('%.2f' % round(values[(9,7)],2)) + " & " + str('%.2f' % round(values[(9,8)],2)) + " & " + str('%.2f' % round(values[(9,12)],2)) + " & " + str('%.2f' % round(values[(9,13)],2)) + r" \\" + "\r"
s7 = str(int(values[(10,1)])) + " & " + str('%.2f' % round(values[(10,2)],2)) + " & " + str('%.2f' % round(values[(10,3)],2)) + " & " + str('%.2f' % round(values[(10,7)],2)) + " & " + str('%.2f' % round(values[(10,8)],2)) + " & " + str('%.2f' % round(values[(10,12)],2)) + " & " + str('%.2f' % round(values[(10,13)],2)) + r" \\" + "\r"
s8 = str(int(values[(11,1)])) + " & " + str('%.2f' % round(values[(11,2)],2)) + " & " + str('%.2f' % round(values[(11,3)],2)) + " & " + str('%.2f' % round(values[(11,7)],2)) + " & " + str('%.2f' % round(values[(11,8)],2)) + " & " + str('%.2f' % round(values[(11,12)],2)) + " & " + str('%.2f' % round(values[(11,13)],2)) + r" \\" + "\r"
s9 = str(int(values[(12,1)])) + " & " + str('%.2f' % round(values[(12,2)],2)) + " & " + str('%.2f' % round(values[(12,3)],2)) + " & " + str('%.2f' % round(values[(12,7)],2)) + " & " + str('%.2f' % round(values[(12,8)],2)) + " & " + str('%.2f' % round(values[(12,12)],2)) + " & " + str('%.2f' % round(values[(12,13)],2)) + r" \\" + "\r"
s10 = str(int(values[(13,1)])) + " & " + str('%.2f' % round(values[(13,2)],2)) + " & " + str('%.2f' % round(values[(13,3)],2)) + " & " + str('%.2f' % round(values[(13,7)],2)) + " & " + str('%.2f' % round(values[(13,8)],2)) + " & " + str('%.2f' % round(values[(13,12)],2)) + " & " + str('%.2f' % round(values[(13,13)],2)) + r" \\" + "\r"
s11 = str(int(values[(14,1)])) + " & " + str('%.2f' % round(values[(14,2)],2)) + " & " + str('%.2f' % round(values[(14,3)],2)) + " & " + str('%.2f' % round(values[(14,7)],2)) + " & " + str('%.2f' % round(values[(14,8)],2)) + " & " + str('%.2f' % round(values[(14,12)],2)) + " & " + str('%.2f' % round(values[(14,13)],2)) + r" \\" + "\r"
s12 = str(int(values[(15,1)])) + " & " + str('%.2f' % round(values[(15,2)],2)) + " & " + str('%.2f' % round(values[(15,3)],2)) + " & " + str('%.2f' % round(values[(15,7)],2)) + " & " + str('%.2f' % round(values[(15,8)],2)) + " & " + str('%.2f' % round(values[(15,12)],2)) + " & " + str('%.2f' % round(values[(15,13)],2)) + r" \\"
s = s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10 + s11 + s12
#cb = gtk.Clipboard()
#cb.set_text("Hello", len=-1)
win32clipboard.OpenClipboard()
win32clipboard.SetClipboardData(win32con.CF_TEXT, s)
win32clipboard.CloseClipboard()
#raw_input('Hit any key to continue...')
elif response == gtk.RESPONSE_CANCEL:
print 'Closed, no files selected'

dialog.destroy()