Welcome, guest | Sign In | My Account | Store | Cart

I've found that I frequently need to open a file, do some processing on it and then load it into excel. This script provides a nice interface for opening the file using a wxPython dialog box, some code to load the result .csv file into excel using win32com and a function to contain the processing code. I should probably turn this into a module sometime...

Python, 53 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import sys, os
import wx
from win32com.client import Dispatch


def parse_data(data, output):
    # this is where you do the processing
    pass

if __name__ == "__main__":
    app = wx.App()
    #app.MainLoop()    # strangely enough this line is isn't needed. But creating the app is...

    args = sys.argv[1:]
    if len(args) < 1:
        # no arguments? then show dialog
        dlg = wx.FileDialog(None, "Choose a file", os.getcwd(), "", "*.*", wx.OPEN)
        if dlg.ShowModal() == wx.ID_OK:
            path = dlg.GetPath()
            filename = path
            dlg.Destroy()
        else:
            dlg.Destroy()
            sys.exit(1)
    else:
        # use command line arguments if they were given
        filename = args[0]

    if len(args) > 1:
        ofilename = args[1]
    else:
        # automagically choose an output filename
        ofilename = filename+".csv"


    # open and read the input
    file = open(filename, "r")
    data = file.readlines()
    file.close()
    # open the output file
    output = open(ofilename, "w")
    # do the real work
    parse_data(data, output)
    # close the output
    output.close()

    # do some excel formatting on the output
    xlapp = Dispatch("Excel.Application")
    xlapp.Visible = 1
    xlapp.Workbooks.Open(ofilename)
    sheet = xlapp.ActiveSheet
    # set the widths
    sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, 100)).EntireColumn.AutoFit()

Normally when I write a simple processing script for myself I just use command line arguments. But when I share those scripts with my coworkers having the GUI to open the file and starting up excel are necessities for some people. One problem is that if you have excel open (and minimized) the script loads the data into the current instance of excel, but leaves excel minimized, there is no indication of what's happened.

3 comments

David Langelaan 13 years, 10 months ago  # | flag

Nice. Hey,

I have the same problem in my workplace, and partly for that reason I decided to start learning python.

Today I just made my frist script(which was actually my own), for rearranging text files, to save me excel work. Your application will be a great addition to it! Thanks.

Just one question though, is this still platform independent? I know that by nature python is, but would this program still be able to open either the McIntosh , or Windows version of excel?

Anyways, this is great!

Brian Davis (author) 13 years, 10 months ago  # | flag

cross-platform? The win32com library works only on windows unfortunately. I don't have much experience with macs but I think there is an application scripting framework in OS X (cocoa?), and it's quite likely somebody has a python library to talk to it.

Brian Davis (author) 13 years, 8 months ago  # | flag

wx.App(0). By default a wx.App will suppress all output to the command line. By changing the wx.App() line to wx.App(0) messages will still appear on the command line.