FakeExcel class

iMC is our product for managing and recording employee-related incidents. My father wrote the product in Python with wxWidgets so in theory getting it to run on Ubuntu should have been easy. However there were a few tricky issues because of dependencies on Excel and the COM interface that's available in win32 python that allows you to control Excel programatically.

In preparation for one of our new ventures (Alexander Bar) I needed to get iMC up and running on Ubuntu as we are going to use iMC to help manage our bar staff so what I did was to write a FakeExcel class which provides same methods but reads and writes the files as plain old CSV. If the file being read is a .xls I quickly run it through xls2csv. So unfortunately one loses the nice formatting and calculations in the report templates but for now it works as a solution to be able to get data in and out of iMC. There'll be many missing methods and properties as I only wrote replacements for what was being used in iMC.

By the way if you're in Gauteng go check out the iMC stand at the HRD Expo at the Sandton Convention centre this week.

import os
import subprocess
import csv
import tempfile

'''
FakeExcel.py - reading/writing of "xls" files (actually csv)

If the file being read is a .xls we quickly run it through xls2csv. 
Please use, modify, redistribute freely. Lots of missing methods and properties - please post any modifications/improvements. 
It would be very nice if someone made this work with Python Uno so we could retain formatting & calculations.

Dependency: catdoc package (apt-get install catdoc)

Nicholas Spagnoletti
nicholas@nitric.co.za
2011-10-02

'''

def openx(path):
    'Open a file on windows using whatever system default opener is and if that fails then with gnome-open'
    try:
        os.startfile(path)
    except AttributeError:
        subprocess.Popen(["gnome-open", path])



class Value(object):
    'Class for getting and setting cell Values'
    def __init__(self, parent, rownum, colnum):
        self.parent=parent
        self.rownum=rownum
        self.colnum=colnum
    def __repr__(self):
        return self.Value
    def __setattr__(self, a, v):
        if a=='Value':
            self.parent._cells[(self.rownum, self.colnum)] = v
        else:
            return super(Value,self).__setattr__(a,v)
            
    def __getattr__(self, a):
        if a=='Value':
            try:
                return self.parent._cells[(self.rownum, self.colnum)]
            except KeyError:
                return None

    
class Sheet(object):
    'Class corresponding to sheet in Workbook (only ever have one sheet as we are actually dealing with csv'
    def __init__(self, parent):
        self._cells = {}
        self.parent=parent
        sample = self.parent.file.read(2096)
        dialect = csv.Sniffer().sniff(sample)
        self.parent.file.seek(0)
        r = csv.reader(self.parent.file, dialect=dialect)
        for rownum, line in enumerate(r):
            for colnum, v in enumerate(line):
                if v!= u'\x0c':
                    nv = Value(self, rownum+1, colnum+1)
                    nv.Value = v
      
            
    def Cells(self, row, col):
        nv = Value(self, row, col)
        return nv
            

class Workbooks(object):
    'Opens and parses .csv and .xls files, sets a Sheet, and provides method for saving out as .csv'
    path=None
    def __init__(self, parent):
        self.parent=parent
    def Open(self, path):
        self.path=path
        if path.endswith('.xls'):
            s = subprocess.Popen(["xls2csv", path], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            csv, err = s.communicate()
            t = tempfile.TemporaryFile()
            t.write(csv)
            t.seek(0)
            self.file = t
        else:
            self.file = open(path)
        
        sheet = Sheet(self)
        self.parent.ActiveSheet=sheet
        Sheets = (sheet,)
        return self
    def SaveAs(self, path):
        self.path=path
        with open(path, 'w') as f:
            w = csv.writer(f)
            keys = sorted(self.parent.ActiveSheet._cells.keys())
            cells = self.parent.ActiveSheet._cells
            prevrow = None
            for k in keys:
                row, col = k
                if prevrow!=row and row>1:
                    w.writerow(line)
                if prevrow!=row:
                    line = []
                line.append(cells.get(k))
                prevrow = row
            
    def Close(self, SaveChanges=False):
        if SaveChanges:
            self.SaveAs(self.path)
        self.parent.ActiveSheet._cells = {}

class FakeExcel(object):
    'Instantiates a Workbooks object'
    def __init__(self):
        self.Workbooks = Workbooks(self)
    def Quit(self):
        pass
  
        
        

if __name__=="__main__":
    excel = FakeExcel()
    excel.Workbooks.Open('/tmp/public.xls')
    sheet = excel.ActiveSheet
    row = 1
    while sheet.Cells(row,1).Value != None:
        print sheet.Cells(row,1).Value
        row += 1
    #now let's change a val
    sheet.Cells(3,1).Value = 'Changed'
    row = 1
    while sheet.Cells(row,1).Value != None:
        print sheet.Cells(row,1).Value
        row += 1
    excel.Workbooks.SaveAs('/tmp/public.csv')
    #Open on the desktop
    openx('/tmp/public.csv')