Convert SpreadSheets to CSV files with Python and pyuno, Part 1v2
Some months back I developed some pyuno code for converting spreadsheets into CSV files from the command line. Pyuno being the Python interface to the OpenOffice runtime. One of the enhancement suggestions I got was to add the ability to extract all the sheets and/or a specific sheet rather than always extracting the first sheet. The following update to the code does just that.
By way of refreshing our memory, the converter program takes pairs of input and output files, for example:
$ python ssconverter.py file1.xls file1.csv file2.ods file2.csv
Each input file is a spreadsheet and it is converted into the corresponding output file as a CSV file. The new version has been enhanced so that you can append a sheet name or number to the end of the input file name to specify a particular sheet to extract, for example:
$ python ssconverter.py file1.xls:1 file1.csv
$ python ssconverter.py file1.xls:Sheet1 file1.csv
$ python ssconverter.py file2.ods@1 file2.csv
$ python ssconverter.py file2.ods@Sheet2 file2.csv
The sheet name or number (starting at 1) is appended to the input file name separated by a colon (:) or an at sign (@).
In addition, you can convert all the sheets in one pass by specifing a %d or a %s format specifier in the output file name:
$ python ssconverter.py file1.xls file1-%d.csv
$ python ssconverter.py file1.xls file1-%s.csv
If the format specifier is %d, then the sheet number is formatted into the output file name, if %s is specified then the sheet name is used. The %d specifier can include zero pad and width specifiers (eg %04d).
The new code has three main changes over the the original code. The first change is to check for an input file sheet specification:
# Check for sheet specification in input file name.
match = re.search(r'^(.*)[@:](.*)$', inputFile)
if os.path.exists(inputFile) or not match:
inputUrl = uno.systemPathToFileUrl(os.path.abspath(inputFile))
inputSheet = '1' # Convert fist sheet.
else:
inputUrl = uno.systemPathToFileUrl(os.path.abspath(match.group(1)))
inputSheet = match.group(2)
The regular expression search checks to see if the input file name can be broken into a file name part and a sheet name part. If it can, and only if the original unbroken input file name does not refer to an existing file, the pieces are used as the input file and sheet specification. Checking to see if the original unbroken input file name refers to an existing file allows you to specify input files that have colons and at signs in their names, and it continues to work even in the case where you have an input file with a colon or at sign in its name and you want to extract a particular sheet, for example:
$ python ssconverter.py this:month.xls:sales output.csv
will correctly recognize that the sheet name is "sales" and that the file name is "this:month.xls". Since the regular expression search is be default greedy the break will occur at the last colon and since "this:month.xls:sales" does not refer to an existing file the match will be used.
The second main change, is related to how the document is loaded. After getting the rest of the code to the point where I thought it ought to be working I discovered while testing that OpenOffice was always converting the first sheet. After some flailing at the code failed to fix the problem I started looking at a few other examples of pyuno code and the only thing I really noted was that many did not use the Hidden property when loading the document. So, I gave that a shot and sure enough it fixed the problem. The accompanying comment has a few more notes:
# Sheet activation does not work properly when Hidden is specified.
# Although the sheet does become the active sheet, it's not the sheet that
# gets saved if the spreadsheet is loaded with Hidden=True.
#
# Removing Hidden=True doesn't seem to change anything: nothing appears
# on the screen regardless of the Hidden value.
#
# document = self.desktop.loadComponentFromURL(inputUrl, "_blank", 0, ooutils.oo_properties(Hidden=True))
document = self.desktop.loadComponentFromURL(inputUrl, "_blank", 0, ooutils.oo_properties())
The third change is the one where the sheet or sheets are extracted. If a particular sheet is specified the sheet is activated first then saved to the output file:
# Activate the sheet to be converted.
if re.search(r'^\d+$', inputSheet):
sheet = sheets.getByIndex(int(inputSheet)-1)
else:
sheet = sheets.getByName(inputSheet)
controller.setActiveSheet(sheet)
outputUrl = uno.systemPathToFileUrl(os.path.abspath(outputFile))
document.storeToURL(outputUrl, props)
The regular expression here checks to see if the sheet name is a number or a string (sheet number or sheet name).
If all the sheets are being saved then the sheets are activated one by one, the output file name is formatted and the sheet is saved:
# Use the sheet number if the format is %d, otherwise the sheet name.
dfmt = re.search(r'%[0-9]*d', outputFile)
sfmt = re.search(r'%s', outputFile)
if dfmt or sfmt:
i = 0
while i < sheets.getCount():
# Activate the sheet.
sheet = sheets.getByIndex(i)
controller.setActiveSheet(sheet)
# Create output file name.
if dfmt:
ofile = outputFile % (i+1)
else:
ofile = outputFile % sheet.getName().replace(' ', '_')
if verbose: print " %s" % ofile
# Save the sheet to the output file.
outputUrl = uno.systemPathToFileUrl(os.path.abspath(ofile))
document.storeToURL(outputUrl, props)
i += 1
The code in its entirety appears below:
1 #!/usr/bin/python
2 #
3 # Convert spreadsheet to CSV file.
4 #
5 # Based on:
6 # PyODConverter (Python OpenDocument Converter) v1.0.0 - 2008-05-05
7 # Copyright (C) 2008 Mirko Nasato <mirko@artofsolving.com>
8 # Licensed under the GNU LGPL v2.1 - or any later version.
9 # http://www.gnu.org/licenses/lgpl-2.1.html
10 #
11
12 import os
13 import re
14 import ooutils
15
16 import uno
17 from com.sun.star.task import ErrorCodeIOException
18
19
20
21 class SSConverter:
22 """
23 Spreadsheet converter class.
24 Converts spreadsheets to CSV files.
25 """
26
27 def __init__(self, oorunner=None):
28 self.desktop = None
29 self.oorunner = None
30
31
32 def convert(self, inputFile, outputFile, verbose=False):
33 """
34 Convert the input file (a spreadsheet) to a CSV file.
35
36 The input file name can contain a sheet specification to specify a particular sheet.
37 The sheet specification is either a number or a sheet name.
38 The sheet specification is appended to the file name separated by a colon
39 or an at sign: ":" or "@".
40
41 If the output file name contains a %d or %s format specifier, then all the sheets
42 in the input file are converted, otherwise only the first sheet is converted.
43
44 If the output file name contains a %d format specifier then the sheet number
45 is used when formatting the output file name.
46 The format can contain a width specifier (eg %02d).
47
48 If the output file name contains a %s specifier then the sheet name is used
49 when formatting the output file name.
50 """
51
52 # Start openoffice if needed.
53 if not self.desktop:
54 if not self.oorunner:
55 self.oorunner = ooutils.OORunner()
56
57 self.desktop = self.oorunner.connect()
58
59 # Check for sheet specification in input file name.
60 match = re.search(r'^(.*)[@:](.*)$', inputFile)
61 if os.path.exists(inputFile) or not match:
62 inputUrl = uno.systemPathToFileUrl(os.path.abspath(inputFile))
63 inputSheet = '1' # Convert fist sheet.
64 else:
65 inputUrl = uno.systemPathToFileUrl(os.path.abspath(match.group(1)))
66 inputSheet = match.group(2)
67
68
69 # NOTE:
70 # Sheet activation does not work properly when Hidden is specified.
71 # Although the sheet does become the active sheet, it's not the sheet that
72 # gets saved if the spreadsheet is loaded with Hidden=True.
73 #
74 # Removing Hidden=True doesn't seem to change anything: nothing appears
75 # on the screen regardless of the Hidden value.
76 #
77 # document = self.desktop.loadComponentFromURL(inputUrl, "_blank", 0, ooutils.oo_properties(Hidden=True))
78 document = self.desktop.loadComponentFromURL(inputUrl, "_blank", 0, ooutils.oo_properties())
79
80 try:
81 props = ooutils.oo_properties(FilterName="Text - txt - csv (StarCalc)")
82 #
83 # Another useful property option:
84 # FilterOptions="59,34,0,1"
85 # 59 - Field separator (semicolon), this is the ascii value.
86 # 34 - Text delimiter (double quote), this is the ascii value.
87 # 0 - Character set (system).
88 # 1 - First line number to export.
89 #
90 # For more information see:
91 # http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
92
93 # To convert a particular sheet, the sheet needs to be active.
94 # To activate a sheet we need the spreadsheet-view, to get the spreadsheet-view
95 # we need the spreadsheet-controller, to get the spreadsheet-controller
96 # we need the spreadsheet-model.
97 #
98 # The spreadsheet-model interface is available from the document object.
99 # The spreadsheet-view interface is available from the controller.
100 #
101 controller = document.getCurrentController()
102 sheets = document.getSheets()
103
104 # If the output file name contains a %d or %s format specifier, convert all sheets.
105 # Use the sheet number if the format is %d, otherwise the sheet name.
106 dfmt = re.search(r'%[0-9]*d', outputFile)
107 sfmt = re.search(r'%s', outputFile)
108
109 if dfmt or sfmt:
110 i = 0
111 while i < sheets.getCount():
112 # Activate the sheet.
113 sheet = sheets.getByIndex(i)
114 controller.setActiveSheet(sheet)
115
116 # Create output file name.
117 if dfmt:
118 ofile = outputFile % (i+1)
119 else:
120 ofile = outputFile % sheet.getName().replace(' ', '_')
121
122 if verbose: print " %s" % ofile
123
124 # Save the sheet to the output file.
125 outputUrl = uno.systemPathToFileUrl(os.path.abspath(ofile))
126 document.storeToURL(outputUrl, props)
127 i += 1
128
129 else:
130 # Activate the sheet to be converted.
131 if re.search(r'^\d+$', inputSheet):
132 sheet = sheets.getByIndex(int(inputSheet)-1)
133 else:
134 sheet = sheets.getByName(inputSheet)
135
136 controller.setActiveSheet(sheet)
137 outputUrl = uno.systemPathToFileUrl(os.path.abspath(outputFile))
138 document.storeToURL(outputUrl, props)
139 finally:
140 if document: document.close(True)
141
142
143 if __name__ == "__main__":
144 from sys import argv
145 from os.path import isfile
146
147 if len(argv) == 2 and argv[1] == '--shutdown':
148 ooutils.oo_shutdown_if_running()
149 else:
150 if len(argv) < 3 or len(argv) % 2 != 1:
151 print "USAGE:"
152 print " python %s INPUT-FILE[:SHEET] OUTPUT-FILE ..." % argv[0]
153 print "OR"
154 print " python %s --shutdown" % argv[0]
155 exit(255)
156
157 try:
158 i = 1
159 converter = SSConverter()
160
161 while i+1 < len(argv):
162 print '%s => %s' % (argv[i], argv[i+1])
163 converter.convert(argv[i], argv[i+1], True)
164 i += 2
165
166 except ErrorCodeIOException, exception:
167 print "ERROR! ErrorCodeIOException %d" % exception.ErrCode
168 exit(1)
Mitch Frazier is an embedded systems programmer at Emerson Electric Co. Mitch has been a contributor to and a friend of Linux Journal since the early 2000s.
Recent Articles
