#!/usr/bin/env python # =================================================== # THIS SOFTWARE IS BUILD TO WORK BEST WITH THE # "excel-parser" NODE PACKAGE. # =================================================== __author__ = "Shekhar R. Thawali " __version__ = "0.1.2" __license__ = "GPL-2+" import csv, datetime, zipfile, sys import codecs, cStringIO, os, json import xml.parsers.expat from xml.dom import minidom try: from argparse import ArgumentParser except ImportError: print("\n******************************************************************") print(" argparse is required to run this script.") print(" It is available here: https://pypi.python.org/pypi/argparse") print("****************************************************************\n") sys.exit(1) try: import xlrd except ImportError: print("\n******************************************************************") print(" xlrd is required to run this script.") print(" It is available here: http://pypi.python.org/pypi/xlrd") print("****************************************************************\n") sys.exit(1) FORMATS = { 'general' : 'float', '0' : 'float', '0.00' : 'float', '#,##0' : 'float', '#,##0.00' : 'float', '0%' : 'percentage', '0.00%' : 'percentage', '0.00e+00' : 'float', 'mm-dd-yy' : 'date', 'd-mmm-yy' : 'date', 'd-mmm' : 'date', 'mmm-yy' : 'date', 'h:mm am/pm' : 'date', 'h:mm:ss am/pm' : 'date', 'h:mm' : 'time', 'h:mm:ss' : 'time', 'm/d/yy h:mm' : 'date', '#,##0 ;(#,##0)' : 'float', '#,##0 ;[red](#,##0)' : 'float', '#,##0.00;(#,##0.00)' : 'float', '#,##0.00;[red](#,##0.00)' : 'float', 'mm:ss' : 'time', '[h]:mm:ss' : 'time', 'mmss.0' : 'time', '##0.0e+0' : 'float', '@' : 'float', 'yyyy\\-mm\\-dd' : 'date', 'dd/mm/yy' : 'date', 'hh:mm:ss' : 'time', "dd/mm/yy\\ hh:mm" : 'date', 'dd/mm/yyyy hh:mm:ss' : 'date', 'yy-mm-dd' : 'date', 'd-mmm-yyyy' : 'date', 'm/d/yy' : 'date', 'm/d/yyyy' : 'date', 'dd-mmm-yyyy' : 'date', 'dd/mm/yyyy' : 'date', 'mm/dd/yy hh:mm am/pm' : 'date', 'mm/dd/yyyy hh:mm:ss' : 'date', 'yyyy-mm-dd hh:mm:ss' : 'date', } STANDARD_FORMATS = { 0 : 'general', 1 : '0', 2 : '0.00', 3 : '#,##0', 4 : '#,##0.00', 9 : '0%', 10 : '0.00%', 11 : '0.00e+00', 12 : '# ?/?', 13 : '# ??/??', 14 : 'mm-dd-yy', 15 : 'd-mmm-yy', 16 : 'd-mmm', 17 : 'mmm-yy', 18 : 'h:mm am/pm', 19 : 'h:mm:ss am/pm', 20 : 'h:mm', 21 : 'h:mm:ss', 22 : 'm/d/yy h:mm', 37 : '#,##0 ;(#,##0)', 38 : '#,##0 ;[red](#,##0)', 39 : '#,##0.00;(#,##0.00)', 40 : '#,##0.00;[red](#,##0.00)', 45 : 'mm:ss', 46 : '[h]:mm:ss', 47 : 'mmss.0', 48 : '##0.0e+0', 49 : '@', } def convert2csv(inFile, outFile, sheetFlag, sheetId=0, sheetName=None): fileName, fileExtension = os.path.splitext(inFile) if fileExtension == '.xls': writer = csv.writer(outFile, quoting=csv.QUOTE_MINIMAL, delimiter=',', dialect="excel") workbook = xlrd.open_workbook(filename=inFile) convertXls(workbook, sheetFlag, sheetId, sheetName, writer) elif fileExtension == '.xlsx': writer = csv.writer(outFile, quoting=csv.QUOTE_MINIMAL, delimiter=',') ziphandle = zipfile.ZipFile(inFile) convertXlsx(ziphandle, sheetFlag, sheetId, sheetName, writer) else: print Exception("Invalid file format") sys.exit(1) def convertXlsx(ziphandle, sheetFlag, sheetId, sheetName, writer): shared_strings = parse(ziphandle, SharedStrings, "xl/sharedStrings.xml") styles = parse(ziphandle, Styles, "xl/styles.xml") workbook = parse(ziphandle, Workbook, "xl/workbook.xml") try: shared_strings = parse(ziphandle, SharedStrings, "xl/sharedStrings.xml") styles = parse(ziphandle, Styles, "xl/styles.xml") workbook = parse(ziphandle, Workbook, "xl/workbook.xml") if sheetFlag: print json.JSONEncoder().encode(workbook.sheets) elif sheetId > 0 or sheetName != None: if sheetId > 0: field = 'id' value = sheetId elif sheetName != None: field = 'name' value = sheetName else: print Exception("Wrong selection") sys.exit(1) wSheet = None for sheet in workbook.sheets: if sheet[field] == value: wSheet = Sheet(workbook, shared_strings, styles, ziphandle.read("xl/worksheets/sheet%i.xml" %sheet['id'])) break if not wSheet: print Exception("Sheet %s not found" %value) sys.exit(1) wSheet.to_csv(writer) else: for sheet in workbook.sheets: wSheet = Sheet(workbook, shared_strings, styles, ziphandle.read("xl/worksheets/sheet%i.xml" %sheet['id'])) wSheet.to_csv(writer) finally: ziphandle.close() def convertXls(workbook, sheetFlag, sheetId, sheetName, writer): worksheets = [] sheetnames = workbook.sheet_names() wSheet = None if sheetFlag: for index, sheet in enumerate(sheetnames): worksheets.append({"name": sheet, "id": index+1}) print json.JSONEncoder().encode(worksheets) elif sheetId > 0 or sheetName != None: if sheetId > 0: sheetId = sheetId-1 try: wSheet = workbook.sheet_by_index(sheetId) except Exception as ex: print Exception("Sheet %s not found" %sheetId) elif sheetName != None: try: wSheet = workbook.sheet_by_name(sheetName) except Exception as ex: print Exception("Sheet '%s' not found" %sheetName) if not wSheet: print Exception("Sheet %s not found" %sheetName) sys.exit(1) wSheet = XSheet(wSheet, writer) wSheet.to_csv() else: for sheet in sheetnames: wSheet = workbook.sheet_by_name(sheet) wSheet = XSheet(wSheet, writer) wSheet.to_csv() def parse(ziphandle, klass, filename): instance = klass() if filename in ziphandle.namelist(): instance.parse(ziphandle.read(filename)) return instance class Workbook: def __init__(self): self.sheets = [] self.date1904 = False def parse(self, data): workbookDoc = minidom.parseString(data) if len(workbookDoc.firstChild.getElementsByTagName("fileVersion")) == 0: self.appName = 'unknown' else: self.appName = workbookDoc.firstChild.getElementsByTagName("fileVersion")[0]._attrs['appName'].value try: self.date1904 = workbookDoc.firstChild.getElementsByTagName("workbookPr")[0]._attrs['date1904'].value.lower().strip() != "false" except Exception as ex: pass sheets = workbookDoc.firstChild.getElementsByTagName("sheets")[0] for sheetNode in sheets.getElementsByTagName("sheet"): attrs = sheetNode._attrs name = attrs["name"].value if self.appName == 'xl': if attrs.has_key('r:id'): id = int(attrs["r:id"].value[3:]) else: id = int(attrs['sheetId'].value) else: if attrs.has_key('sheetId'): id = int(attrs["sheetId"].value) else: id = int(attrs['r:id'].value[3:]) self.sheets.append({'name': name, 'id': id}) class Styles: def __init__(self): self.numFmts = {} self.cellXfs = [] def parse(self, data): styles = minidom.parseString(data).firstChild # numFmts numFmtsElement = styles.getElementsByTagName("numFmts") if len(numFmtsElement) == 1: for numFmt in numFmtsElement[0].childNodes: numFmtId = int(numFmt._attrs['numFmtId'].value) formatCode = numFmt._attrs['formatCode'].value.lower().replace('\\', '') self.numFmts[numFmtId] = formatCode # cellXfs cellXfsElement = styles.getElementsByTagName("cellXfs") if len(cellXfsElement) == 1: for cellXfs in cellXfsElement[0].childNodes: if (cellXfs.nodeName != "xf"): continue numFmtId = int(cellXfs._attrs['numFmtId'].value) self.cellXfs.append(numFmtId) class SharedStrings: def __init__(self): self.parser = None self.strings = [] self.si = False self.t = False self.rPh = False self.value = "" def parse(self, data): self.parser = xml.parsers.expat.ParserCreate() self.parser.CharacterDataHandler = self.handleCharData self.parser.StartElementHandler = self.handleStartElement self.parser.EndElementHandler = self.handleEndElement self.parser.Parse(data) def handleCharData(self, data): if self.t: self.value+= data def handleStartElement(self, name, attrs): if name == 'si': self.si = True self.value = "" elif name == 't' and self.rPh: self.t = False elif name == 't' and self.si: self.t = True elif name == 'rPh': self.rPh = True def handleEndElement(self, name): if name == 'si': self.si = False self.strings.append(self.value) elif name == 't': self.t = False elif name == 'rPh': self.rPh = False class Sheet: def __init__(self, workbook, sharedString, styles, data): self.parser = None self.writer = None self.sharedString = None self.styles = None self.in_sheet = False self.in_row = False self.in_cell = False self.in_cell_value = False self.in_cell_formula = False self.columns = {} self.rowNum = None self.colType = None self.s_attr = None self.data = None self.dateformat = None self.skip_empty_lines = True self.data = data self.workbook = workbook self.sharedStrings = sharedString.strings self.styles = styles def to_csv(self, writer): self.writer = writer self.parser = xml.parsers.expat.ParserCreate() self.parser.CharacterDataHandler = self.handleCharData self.parser.StartElementHandler = self.handleStartElement self.parser.EndElementHandler = self.handleEndElement self.parser.Parse(self.data) def handleCharData(self, data): if self.in_cell_value: self.data = data # default value if self.colType == "s": # shared string self.data = self.sharedStrings[int(data)] elif self.colType == "b": # boolean self.data = (int(data) == 1 and "TRUE") or (int(data) == 0 and "FALSE") or data elif self.s_attr: s = int(self.s_attr) # get cell format format = None xfs_numfmt = self.styles.cellXfs[s] if self.styles.numFmts.has_key(xfs_numfmt): format = self.styles.numFmts[xfs_numfmt] elif STANDARD_FORMATS.has_key(xfs_numfmt): format = STANDARD_FORMATS[xfs_numfmt] # get format type if format and FORMATS.has_key(format): format_type = FORMATS[format] if format_type == 'date': # date/time try: if self.workbook.date1904: date = datetime.datetime(1904, 01, 01) + datetime.timedelta(float(data)) else: date = datetime.datetime(1899, 12, 30) + datetime.timedelta(float(data)) if self.dateformat: # str(dateformat) - python2.5 bug, see: http://bugs.python.org/issue2782 self.data = date.strftime(str(self.dateformat)) else: dateformat = format.replace("yyyy", "%Y").replace("yy", "%y"). \ replace("hh:mm", "%H:%M").replace("h", "%H").replace("%H%H", "%H").replace("ss", "%S"). \ replace("d", "%e").replace("%e%e", "%d"). \ replace("mmmm", "%B").replace("mmm", "%b").replace(":mm", ":%M").replace("m", "%m").replace("%m%m", "%m"). \ replace("am/pm", "%p") self.data = date.strftime(str(dateformat)).strip() except (ValueError, OverflowError): # invalid date format self.data = data elif format_type == 'time': # time self.data = str(float(data) * 24*60*60) def handleStartElement(self, name, attrs): if self.in_row and name == 'c': self.colType = attrs.get("t") self.s_attr = attrs.get("s") cellId = attrs.get("r") if cellId: self.colNum = cellId[:len(cellId)-len(self.rowNum)] self.colIndex = 0 else: self.colIndex+= 1 #self.formula = None self.data = "" self.in_cell = True elif self.in_cell and name == 'v': self.in_cell_value = True #elif self.in_cell and name == 'f': # self.in_cell_formula = True elif self.in_sheet and name == 'row' and attrs.has_key('r'): self.rowNum = attrs['r'] self.in_row = True self.columns = {} self.spans = None if attrs.has_key('spans'): self.spans = [int(i) for i in attrs['spans'].split(":")] elif name == 'sheetData': self.in_sheet = True def handleEndElement(self, name): if self.in_cell and name == 'v': self.in_cell_value = False #elif self.in_cell and name == 'f': # self.in_cell_formula = False elif self.in_cell and name == 'c': t = 0 for i in self.colNum: t = t*26 + ord(i) - 64 self.columns[t - 1 + self.colIndex] = self.data self.in_cell = False if self.in_row and name == 'row': if len(self.columns.keys()) > 0: d = [""] * (max(self.columns.keys()) + 1) for k in self.columns.keys(): d[k] = self.columns[k].encode("utf-8") if self.spans: l = self.spans[0] + self.spans[1] - 1 if len(d) < l: d+= (l - len(d)) * [''] # write line to csv if not self.skip_empty_lines or d.count('') != len(d): self.writer.writerow(d) self.in_row = False elif self.in_sheet and name == 'sheetData': self.in_sheet = False class XSheet: def __init__(self, wSheet, writer): self.wSheet = None self.writer = None self.rows = [] self.queue = cStringIO.StringIO() self.wSheet = wSheet self.writer = writer self.convert() def convert(self): if self.wSheet: num_rows = self.wSheet.nrows - 1 num_cells = self.wSheet.ncols - 1 emptyType = set([xlrd.XL_CELL_BLANK, xlrd.XL_CELL_EMPTY]); curr_row = -1 while curr_row < num_rows: curr_row += 1 liste = [] row = self.wSheet.row(curr_row) if not all(x in emptyType for x in row): curr_cell = -1 while curr_cell < num_cells: curr_cell += 1 cell_type = self.wSheet.cell_type(curr_row, curr_cell) cell_value = self.wSheet.cell_value(curr_row, curr_cell) if cell_type == xlrd.XL_CELL_BOOLEAN: try: liste.append(str(int(cell_value))) except (ValueError, OverflowError): liste.append(cell_value) elif cell_type == xlrd.XL_CELL_NUMBER: liste.append(str(cell_value)) elif cell_type == xlrd.XL_CELL_DATE: try: cell_value = datetime.datetime(1899, 12, 30) + datetime.timedelta(float(cell_value)) format = "d-mmm-yy"; dateformat = format.replace("yyyy", "%Y").replace("yy", "%y"). \ replace("hh:mm", "%H:%M").replace("h", "%H").replace("%H%H", "%H").replace("ss", "%S"). \ replace("d", "%e").replace("%e%e", "%d"). \ replace("mmmm", "%B").replace("mmm", "%b").replace(":mm", ":%M").replace("m", "%m").replace("%m%m", "%m"). \ replace("am/pm", "%p") cell_value = cell_value.strftime(str(dateformat)).strip() except (ValueError, OverflowError): cell_value = cell_value liste.append(str(cell_value)); elif cell_type == xlrd.XL_CELL_TEXT: cell_value = cell_value.encode("UTF-8", "strict") liste.append(cell_value) else: liste.append(cell_value) self.rows.append(liste) def to_csv(self): if self.rows: for row in self.rows: self.writer.writerow(row) if __name__ == "__main__": parser = ArgumentParser(usage = "%%prog [options] infile [outfile]") parser.add_argument('-x', "--infile", dest="inFile", required = True, help="filename of the source spreadsheet"); parser.add_argument('-c', "--outfile", dest="outFile", help="filename to save the generated csv file") parser.add_argument('-n', "--sheetnumber", dest="sheetId", default=0, type=int, help="sheet no to convert (0 for all sheets)") parser.add_argument("-w", "--sheetname", dest="sheetName", type=str, help="sheet name to convert", default=None); parser.add_argument("-W", "--worksheets", dest="sheetFlag", help="list all worksheets inside specified spreadsheet", action="store_true"); args = parser.parse_args() if not len(sys.argv) > 1: parser.print_help() else: if args.sheetFlag: convert2csv(args.inFile, sys.stdout, args.sheetFlag) else: if args.inFile: if args.outFile: outfile = open(args.outFile, 'w+b'); else: outfile = open('convert.csv', 'w+b'); convert2csv(args.inFile, outfile, args.sheetFlag, args.sheetId, args.sheetName) outfile.close() else: parser.error("File is required")