path: root/Tools/XlsToCsv/xlsx2csv.py

#!/usr/bin/env python
#
#   Copyright information
#
#	Copyright (C) 2010-2018 Dilshod Temirkhodjaev <tdilshod@gmail.com>
#
#   License
#
#	This program is free software; you can redistribute it and/or modify
#	it under the terms of the GNU General Public License as published by
#	the Free Software Foundation; either version 2 of the License, or
#	(at your option) any later version.
#
#	This program is distributed in the hope that it will be useful,
#	but WITHOUT ANY WARRANTY; without even the implied warranty of
#	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#	GNU General Public License for more details.
#
#	You should have received a copy of the GNU General Public License
#	along with this program. If not, see <http://www.gnu.org/licenses/>.
from __future__ import print_function

__author__ = "Dilshod Temirkhodjaev <tdilshod@gmail.com>"
__license__ = "GPL-2+"
__version__ = "0.7.9"

import csv, datetime, zipfile, string, sys, os, re, signal
import xml.parsers.expat
from xml.dom import minidom

try:
    # python2.4
    from cStringIO import StringIO
except:
    pass
try:
    from argparse import ArgumentParser
except:
    # python2.4
    from optparse import OptionParser

# see also ruby-roo lib at: http://github.com/hmcgowan/roo
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 h:mm am/pm': 'date',
    'mm/dd/yy hh:mm': 'date',
    'mm/dd/yyyy h:mm am/pm': 'date',
    'mm/dd/yyyy hh:mm:ss': 'date',
    'yyyy-mm-dd hh:mm:ss': 'date',
    '#,##0;(#,##0)': 'float',
    '_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)': 'float',
    '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)': 'float'
}
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: '@',
}
CONTENT_TYPES = {
    'shared_strings',
    'styles',
    'workbook',
    'worksheet',
    'relationships',
}

DEFAULT_APP_PATH = "/xl"
DEFAULT_WORKBOOK_PATH = DEFAULT_APP_PATH + "/workbook.xml"

def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)

class XlsxException(Exception):
    pass


class InvalidXlsxFileException(XlsxException):
    pass


class SheetNotFoundException(XlsxException):
    pass


class OutFileAlreadyExistsException(XlsxException):
    pass


class Xlsx2csv:
    """
     Usage: Xlsx2csv("test.xslx", **params).convert("test.csv", sheetid=1)
     Input:
       xlsxfile - path to file or filehandle
     options:
       sheetid - sheet no to convert (0 for all sheets)
       sheetname - sheet name to convert
       dateformat - override date/time format
       timeformat - override time format
       floatformat - override float format
       quoting - if and how to quote
       delimiter - csv columns delimiter symbol
       sheetdelimiter - sheets delimiter used when processing all sheets
       skip_empty_lines - skip empty lines
       skip_trailing_columns - skip trailing columns
       hyperlinks - include hyperlinks
       include_sheet_pattern - only include sheets named matching given pattern
       exclude_sheet_pattern - exclude sheets named matching given pattern
       exclude_hidden_sheets - exclude hidden sheets
    """

    def __init__(self, xlsxfile, **options):
        options.setdefault("delimiter", ",")
        options.setdefault("quoting", csv.QUOTE_MINIMAL)
        options.setdefault("sheetdelimiter", "--------")
        options.setdefault("dateformat", None)
        options.setdefault("timeformat", None)
        options.setdefault("floatformat", None)
        options.setdefault("scifloat", False)
        options.setdefault("skip_empty_lines", False)
        options.setdefault("skip_trailing_columns", False)
        options.setdefault("escape_strings", False)
        options.setdefault("no_line_breaks", False)
        options.setdefault("hyperlinks", False)
        options.setdefault("include_sheet_pattern", ["^.*$"])
        options.setdefault("exclude_sheet_pattern", [])
        options.setdefault("exclude_hidden_sheets", False)
        options.setdefault("merge_cells", False)
        options.setdefault("ignore_formats", [''])
        options.setdefault("lineterminator", "\n")
        options.setdefault("outputencoding", "utf-8")

        self.options = options
        try:
            self.ziphandle = zipfile.ZipFile(xlsxfile)
        except (zipfile.BadZipfile, IOError):
            raise InvalidXlsxFileException("Invalid xlsx file: " + str(xlsxfile))

        self.py3 = sys.version_info[0] == 3

        self.content_types = self._parse(ContentTypes, "/[Content_Types].xml")
        self.shared_strings = self._parse(SharedStrings, self.content_types.types["shared_strings"])
        self.styles = self._parse(Styles, self.content_types.types["styles"])
        self.workbook = self._parse(Workbook, self.content_types.types["workbook"])
        workbook_relationships = list(filter(lambda r: "book" in r, self.content_types.types["relationships"]))[0]
        self.workbook.relationships = self._parse(Relationships, workbook_relationships)
        if self.options['no_line_breaks']:
            self.shared_strings.replace_line_breaks()
        elif self.options['escape_strings']:
            self.shared_strings.escape_strings()

    def __del__(self):
        # make sure to close zip file, ziphandler does have a close() method
        self.ziphandle.close()

    def getSheetIdByName(self, name):
        for s in self.workbook.sheets:
            if s['name'] == name:
                return s['index']
        return None

    def convert(self, outfile, sheetid=1, sheetname=None):
        """outfile - path to file or filehandle"""
        if sheetname:
            sheetid = self.getSheetIdByName(sheetname)
            if not sheetid:
                raise XlsxException("Sheet '%s' not found" % sheetname)
        if sheetid > 0:
            self._convert(sheetid, outfile)
        else:
            if isinstance(outfile, str):
                if not os.path.exists(outfile):
                    os.makedirs(outfile)
                elif os.path.isfile(outfile):
                    raise OutFileAlreadyExistsException("File " + str(outfile) + " already exists!")
            for s in self.workbook.sheets:
                sheetname = s['name']
                sheetstate = s['state']

                # filter hidden sheets
                if sheetstate in ('hidden', 'veryHidden') and self.options['exclude_hidden_sheets']:
                    continue

                # filter sheets by include pattern
                include_sheet_pattern = self.options['include_sheet_pattern']
                if type(include_sheet_pattern) == type(""):  # optparser lib fix
                    include_sheet_pattern = [include_sheet_pattern]
                if len(include_sheet_pattern) > 0:
                    include = False
                    for pattern in include_sheet_pattern:
                        include = pattern and len(pattern) > 0 and re.match(pattern, sheetname)
                        if include:
                            break
                    if not include:
                        continue

                # filter sheets by exclude pattern
                exclude_sheet_pattern = self.options['exclude_sheet_pattern']
                if type(exclude_sheet_pattern) == type(""):  # optparser lib fix
                    exclude_sheet_pattern = [exclude_sheet_pattern]
                exclude = False
                for pattern in exclude_sheet_pattern:
                    exclude = pattern and len(pattern) > 0 and re.match(pattern, sheetname)
                    if exclude:
                        break
                if exclude:
                    continue

                if not self.py3:
                    sheetname = sheetname.encode('utf-8')
                of = outfile
                if isinstance(outfile, str):
                    of = os.path.join(outfile, sheetname + '.csv')
                elif self.options['sheetdelimiter'] and len(self.options['sheetdelimiter']):
                    of.write(self.options['sheetdelimiter'] + " " + str(s['index']) + " - " + sheetname + self.options['lineterminator'])
                self._convert(s['index'], of)

    def _convert(self, sheet_index, outfile):
        closefile = False
        if isinstance(outfile, str):
            if sys.version_info[0] == 2:
                outfile = open(outfile, 'wb+')
            elif sys.version_info[0] == 3:
                outfile = open(outfile, 'w+', encoding=self.options['outputencoding'], newline="")
            else:
                sys.stderr.write("error: version of your python is not supported: " + str(sys.version_info) + "\n")
                sys.exit(1)
            closefile = True
        try:
            writer = csv.writer(outfile, quoting=self.options['quoting'], delimiter=self.options['delimiter'],
                                lineterminator=self.options['lineterminator'])

            sheets_filtered = list(filter(lambda s: s['index'] == sheet_index, self.workbook.sheets))
            if len(sheets_filtered) == 0:
                eprint("Sheet with index %i not found or can't be handled" % sheet_index)
                return 1

            sheet_path = None
            # using sheet relation information
            if 'relation_id' in sheets_filtered[0] and sheets_filtered[0]['relation_id'] is not None:

                relation_id = sheets_filtered[0]['relation_id']
                if relation_id in self.workbook.relationships.relationships and \
                                'target' in self.workbook.relationships.relationships[relation_id]:
                    relationship = self.workbook.relationships.relationships[relation_id]
                    sheet_path = relationship['target']
                    if not (sheet_path.startswith("/xl/") or sheet_path.startswith("xl/")):
                        sheet_path = "/xl/" + sheet_path

            sheet_file = None
            if sheet_path is None:
                sheet_path = "/xl/worksheets/sheet%i.xml" % sheet_index
                sheet_file = self._filehandle(sheet_path)
                if sheet_file is None:
                    sheet_path = None
            if sheet_path is None:
                sheet_path = "/xl/worksheets/worksheet%i.xml" % sheet_index
                sheet_file = self._filehandle(sheet_path)
                if sheet_file is None:
                    sheet_path = None
            if sheet_path is None and sheet_index == 1:
                sheet_path = self.content_types.types["worksheet"]
                sheet_file = self._filehandle(sheet_path)
                if sheet_file is None:
                    sheet_path = None
            if sheet_file is None and sheet_path is not None:
                sheet_file = self._filehandle(sheet_path)
            if sheet_file is None:
                raise SheetNotFoundException("Sheet %i not found" % sheet_index)
            sheet = Sheet(self.workbook, self.shared_strings, self.styles, sheet_file)
            try:
                relationships_path = os.path.join(os.path.dirname(sheet_path),
                                                  "_rels",
                                                  os.path.basename(sheet_path) + ".rels")
                sheet.relationships = self._parse(Relationships, relationships_path)
                sheet.set_dateformat(self.options['dateformat'])
                sheet.set_timeformat(self.options['timeformat'])
                sheet.set_floatformat(self.options['floatformat'])
                sheet.set_skip_empty_lines(self.options['skip_empty_lines'])
                sheet.set_skip_trailing_columns(self.options['skip_trailing_columns'])
                sheet.set_include_hyperlinks(self.options['hyperlinks'])
                sheet.set_merge_cells(self.options['merge_cells'])
                sheet.set_scifloat(self.options['scifloat'])
                sheet.set_ignore_formats(self.options['ignore_formats'])
                if self.options['escape_strings'] and sheet.filedata:
                    sheet.filedata = re.sub(r"(<v>[^<>]+)&#10;([^<>]+</v>)", r"\1\\n\2",
                                            re.sub(r"(<v>[^<>]+)&#9;([^<>]+</v>)", r"\1\\t\2",
                                                   re.sub(r"(<v>[^<>]+)&#13;([^<>]+</v>)", r"\1\\r\2", sheet.filedata)))
                sheet.to_csv(writer)
            finally:
                sheet_file.close()
                sheet.close()
        finally:
            if closefile:
                outfile.close()

    def _filehandle(self, filename):
        for name in filter(lambda f: filename and f.lower() == filename.lower()[1:], self.ziphandle.namelist()):
            # python2.4 fix
            if not hasattr(self.ziphandle, "open"):
                return StringIO(self.ziphandle.read(name))
            return self.ziphandle.open(name, "r")
        return None

    def _parse(self, klass, filename):
        instance = klass()
        filehandle = self._filehandle(filename)
        if filehandle:
            instance.parse(filehandle)
            filehandle.close()
        return instance


class Workbook:
    def __init__(self):
        self.sheets = list()
        self.date1904 = False

    def parse(self, filehandle):
        workbookDoc = minidom.parseString(filehandle.read())
        if workbookDoc.firstChild.namespaceURI:
            fileVersion = workbookDoc.firstChild.getElementsByTagNameNS(workbookDoc.firstChild.namespaceURI,
                                                                        "fileVersion")
        else:
            fileVersion = workbookDoc.firstChild.getElementsByTagName("fileVersion")
        if len(fileVersion) == 0:
            self.appName = DEFAULT_APP_PATH
        else:
            try:
                if workbookDoc.firstChild.namespaceURI:
                    self.appName = \
                        workbookDoc.firstChild.getElementsByTagNameNS(
                            workbookDoc.firstChild.namespaceURI, "fileVersion")[0]._attrs['appName'].value
                else:
                    self.appName = workbookDoc.firstChild.getElementsByTagName("fileVersion")[0]._attrs['appName'].value
            except KeyError:
                # no app name
                self.appName = DEFAULT_APP_PATH
        try:
            if workbookDoc.firstChild.namespaceURI:
                self.date1904 = \
                    workbookDoc.firstChild.getElementsByTagNameNS(
                        workbookDoc.firstChild.namespaceURI, "workbookPr")[0]._attrs['date1904'].value.lower().strip() \
                    != "false"
            else:
                self.date1904 = \
                    workbookDoc.firstChild.getElementsByTagName("workbookPr")[0] \
                        ._attrs['date1904'].value.lower().strip() \
                    != "false"
        except:
            pass

        if workbookDoc.firstChild.namespaceURI:
            sheets = workbookDoc.firstChild.getElementsByTagNameNS(workbookDoc.firstChild.namespaceURI, "sheets")[0]
        else:
            sheets = workbookDoc.firstChild.getElementsByTagName("sheets")[0]
        if workbookDoc.firstChild.namespaceURI:
            sheetNodes = sheets.getElementsByTagNameNS(workbookDoc.firstChild.namespaceURI, "sheet")
        else:
            sheetNodes = sheets.getElementsByTagName("sheet")
        for i, sheetNode in enumerate(sheetNodes):
            attrs = sheetNode._attrs
            name = attrs["name"].value
            state = None
            if 'state' in attrs:
                state = attrs["state"].value
            relation_id = None
            if 'r:id' in attrs:
                relation_id = attrs['r:id'].value
            self.sheets.append(
                {
                    'name': name,
                    'relation_id': relation_id,
                    'index': i + 1,
                    'id': i + 1, # remove id starting 0.8.0 version
                    'state': state
                }
            )


class ContentTypes:
    def __init__(self):
        self.types = {}
        for type in CONTENT_TYPES:
            self.types[type] = None

    def parse(self, filehandle):
        types = minidom.parseString(filehandle.read()).firstChild
        if not types:
            return
        if types.namespaceURI:
            overrideNodes = types.getElementsByTagNameNS(types.namespaceURI, "Override")
        else:
            overrideNodes = types.getElementsByTagName("Override")
        for override in overrideNodes:
            attrs = override._attrs
            type = attrs.get('ContentType').value
            name = attrs.get('PartName').value
            if type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml":
                self.types["workbook"] = name
            elif type == "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml":
                self.types["styles"] = name
            elif type == "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml":
                # BUG preserved only last sheet
                self.types["worksheet"] = name
            elif type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml":
                self.types["shared_strings"] = name
            elif type == "application/vnd.openxmlformats-package.relationships+xml":
                if self.types["relationships"] is None:
                    self.types["relationships"] = list()
                self.types["relationships"].append(name)

        if self.types["workbook"] is None:
            self.types["workbook"] = DEFAULT_WORKBOOK_PATH
        if self.types["relationships"] is None:
            self.types["relationships"] = [os.path.dirname(self.types["workbook"]) + "/_rels/" + \
                                           os.path.basename(self.types["workbook"]) + ".rels"]


class Relationships:
    def __init__(self):
        self.relationships = {}

    def parse(self, filehandle):
        doc = minidom.parseString(filehandle.read())
        if doc.namespaceURI:
            relationships = doc.getElementsByTagNameNS(doc.namespaceURI, "Relationships")
        else:
            relationships = doc.getElementsByTagName("Relationships")
        if not relationships:
            return
        if doc.namespaceURI:
            relationshipNodes = relationships[0].getElementsByTagNameNS(doc.namespaceURI, "Relationship")
        else:
            relationshipNodes = relationships[0].getElementsByTagName("Relationship")
        for rel in relationshipNodes:
            attrs = rel._attrs
            rId = attrs.get('Id')
            if rId:
                vtype = attrs.get('Type')
                target = attrs.get('Target')
                self.relationships[str(rId.value)] = {
                    "type": vtype and str(vtype.value) or None,
                    "target": target and str(target.value) or None
                }


class Styles:
    def __init__(self):
        self.numFmts = {}
        self.cellXfs = []

    def parse(self, filehandle):
        styles = minidom.parseString(filehandle.read()).firstChild
        # numFmts
        if styles.namespaceURI:
            numFmtsElement = styles.getElementsByTagNameNS(styles.namespaceURI, "numFmts")
        else:
            numFmtsElement = styles.getElementsByTagName("numFmts")
        if len(numFmtsElement) == 1:
            for numFmt in numFmtsElement[0].childNodes:
                if numFmt.nodeType == minidom.Node.ELEMENT_NODE:
                    numFmtId = int(numFmt._attrs['numFmtId'].value)
                    formatCode = numFmt._attrs['formatCode'].value.lower().replace('\\', '')
                    self.numFmts[numFmtId] = formatCode

        if styles.namespaceURI:
            cellXfsElement = styles.getElementsByTagNameNS(styles.namespaceURI, "cellXfs")
        else:
            cellXfsElement = styles.getElementsByTagName("cellXfs")
        if len(cellXfsElement) == 1:
            for cellXfs in cellXfsElement[0].childNodes:
                if cellXfs.nodeType != minidom.Node.ELEMENT_NODE or not (
                                cellXfs.nodeName == "xf" or cellXfs.nodeName.endswith(":xf")):
                    continue
                if cellXfs._attrs and 'numFmtId' in cellXfs._attrs:
                    numFmtId = int(cellXfs._attrs['numFmtId'].value)
                    if self.chk_exists(numFmtId) == None:
                        numFmtId = int(cellXfs._attrs['applyNumberFormat'].value)
                    self.cellXfs.append(numFmtId)
                else:
                    self.cellXfs.append(None)

    # When Unknown Numformat ID assign applyNumberFormat
    def chk_exists(self, numFmtId):
        xfs_numfmt = numFmtId
        format_str = None
        if xfs_numfmt in self.numFmts:
            format_str = self.numFmts[xfs_numfmt]
        elif xfs_numfmt in STANDARD_FORMATS:
            format_str = STANDARD_FORMATS[xfs_numfmt]
        return format_str


class SharedStrings:
    def __init__(self):
        self.parser = None
        self.strings = []
        self.si = False
        self.t = False
        self.rPh = False
        self.value = ""

    def parse(self, filehandle):
        self.parser = xml.parsers.expat.ParserCreate()
        self.parser.CharacterDataHandler = self.handleCharData
        self.parser.StartElementHandler = self.handleStartElement
        self.parser.EndElementHandler = self.handleEndElement
        self.parser.ParseFile(filehandle)

    def escape_strings(self):
        for i in range(0, len(self.strings)):
            self.strings[i] = self.strings[i].replace("\r", "\\r").replace("\n", "\\n").replace("\t", "\\t")

    def replace_line_breaks(self):
        for i in range(0, len(self.strings)):
            self.strings[i] = self.strings[i].replace("\r", " ").replace("\n", " ").replace("\t", " ")

    def handleCharData(self, data):
        if self.t:
            self.value += data

    def handleStartElement(self, name, attrs):
        # ignore namespace
        i = name.find(":")
        if i >= 0:
            name = name[i + 1:]

        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):
        # ignore namespace
        i = name.find(":")
        if i >= 0:
            name = name[i + 1:]

        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, filehandle):
        self.py3 = sys.version_info[0] == 3
        self.parser = None
        self.writer = None
        self.sharedString = None
        self.styles = None
        self.relationships = None
        self.columns_count = -1

        self.in_sheet = False
        self.in_row = False
        self.in_cell = False
        self.in_cell_value = False

        self.columns = {}
        self.lastRowNum = 0
        self.rowNum = None
        self.colType = None
        self.cellId = None
        self.s_attr = None
        self.data = None
        self.max_columns = -1

        self.dateformat = None
        self.timeformat = "%H:%M"  # default time format
        self.floatformat = None
        self.skip_empty_lines = False
        self.skip_trailing_columns = False

        self.filedata = None
        self.filehandle = filehandle
        self.workbook = workbook
        self.sharedStrings = sharedString.strings
        self.styles = styles

        self.hyperlinks = {}
        self.mergeCells = {}
        self.ignore_formats = []

        self.colIndex = 0
        self.colNum = ""

    def close(self):
        # Make sure Worksheet is closed, parsers lib does not have a close() function, so simply delete it
        self.parser = None

    def set_dateformat(self, dateformat):
        self.dateformat = dateformat

    def set_timeformat(self, timeformat):
        if timeformat:
            self.timeformat = timeformat

    def set_floatformat(self, floatformat):
        self.floatformat = floatformat

    def set_skip_empty_lines(self, skip):
        self.skip_empty_lines = skip

    def set_skip_trailing_columns(self, skip):
        self.skip_trailing_columns = skip

    def set_ignore_formats(self, ignore_formats):
        self.ignore_formats = ignore_formats

    def set_merge_cells(self, mergecells):
        if not mergecells:
            return
        if not self.filedata:
            self.filedata = self.filehandle.read()
        data = str(self.filedata)  # python3: convert byte buffer to string

        # find worksheet tag, we need namespaces from it
        start = data.find("<worksheet")
        if start < 0:
            return
        end = data.find(">", start)
        worksheet = data[start: end + 1]

        # find hyperlinks part
        start = data.find("<mergeCells")
        if start < 0:
            # hyperlinks not found
            return
        end = data.find("</mergeCells>")
        data = data[start: end + 13]

        # parse hyperlinks
        doc = minidom.parseString(worksheet + data + "</worksheet>").firstChild

        if doc.namespaceURI:
            mergeCells = doc.getElementsByTagNameNS(doc.namespaceURI, "mergeCell")
        else:
            mergeCells = doc.getElementsByTagName("mergeCell")
        for mergeCell in mergeCells:
            attrs = mergeCell._attrs
            if 'ref' in attrs.keys():
                rangeStr = attrs['ref'].value
                rng = rangeStr.split(":")
                if len(rng) > 1:
                    for cell in self._range(rangeStr):
                        self.mergeCells[cell] = {}
                        self.mergeCells[cell]['copyFrom'] = rng[0]

    def set_scifloat(self, scifloat):
        self.scifloat = scifloat

    def set_include_hyperlinks(self, hyperlinks):
        if not hyperlinks or not self.relationships or not self.relationships.relationships:
            return
        # we must read file first to get hyperlinks, but we don't wont to parse whole file
        if not self.filedata:
            self.filedata = self.filehandle.read()
        data = str(self.filedata)  # python3: convert byte buffer to string

        # find worksheet tag, we need namespaces from it
        start = data.find("<worksheet")
        if start < 0:
            return
        end = data.find(">", start)
        worksheet = data[start: end + 1]

        # find hyperlinks part
        start = data.find("<hyperlinks>")
        if start < 0:
            # hyperlinks not found
            return
        end = data.find("</hyperlinks>")
        data = data[start: end + 13]

        # parse hyperlinks
        doc = minidom.parseString(worksheet + data + "</worksheet>").firstChild
        if doc.namespaceURI:
            hiperlinkNodes = doc.getElementsByTagNameNS(doc.namespaceURI, "hyperlink")
        else:
            hiperlinkNodes = doc.getElementsByTagName("hyperlink")
        for hlink in hiperlinkNodes:
            attrs = hlink._attrs
            ref = rId = None
            for k in attrs.keys():
                if k == "ref":
                    ref = str(attrs[k].value)
                if k.endswith(":id"):
                    rId = str(attrs[k].value)
            if not ref or not rId:
                continue
            rel = self.relationships.relationships.get(rId)
            if not rel:
                continue
            target = rel.get('target')
            for cell in self._range(ref):
                self.hyperlinks[cell] = target

    def to_csv(self, writer):
        self.writer = writer
        self.parser = xml.parsers.expat.ParserCreate()
        self.parser.buffer_text = True
        self.parser.CharacterDataHandler = self.handleCharData
        self.parser.StartElementHandler = self.handleStartElement
        self.parser.EndElementHandler = self.handleEndElement
        if self.filedata:
            self.parser.Parse(self.filedata)
        else:
            self.parser.ParseFile(self.filehandle)

    def handleCharData(self, data):
        if self.in_cell_value:
            format_type = None
            format_str = "general"
            self.collected_string += data
            self.data = self.collected_string
            if self.colType == "s":  # shared string
                format_type = "string"
                self.data = self.sharedStrings[int(self.data)]
            elif self.colType == "b":  # boolean
                format_type = "boolean"
                self.data = (int(data) == 1 and "TRUE") or (int(data) == 0 and "FALSE") or data
            elif self.colType == "str" or self.colType == "inlineStr":
                format_type = "string"
                self.data = data
            elif self.s_attr:
                s = int(self.s_attr)

                # get cell format
                xfs_numfmt = None
                if s < len(self.styles.cellXfs):
                    xfs_numfmt = self.styles.cellXfs[s]
                if xfs_numfmt in self.styles.numFmts:
                    format_str = self.styles.numFmts[xfs_numfmt]
                elif xfs_numfmt in STANDARD_FORMATS:
                    format_str = STANDARD_FORMATS[xfs_numfmt]

                # get format type
                if not format_str:
                    eprint("unknown format %s at %d" % (format_str, xfs_numfmt))
                    return

                if format_str in FORMATS:
                    format_type = FORMATS[format_str]
                elif re.match("^\d+(\.\d+)?$", self.data) and re.match(".*[hsmdyY]", format_str) and not re.match(
                        '.*\[.*[dmhys].*\]', format_str):
                    # it must be date format
                    if float(self.data) < 1:
                        format_type = "time"
                    else:
                        format_type = "date"
                elif re.match("^-?\d+(.\d+)?$", self.data) or (
                            self.scifloat and re.match("^-?\d+(.\d+)?([eE]-?\d+)?$", self.data)):
                    format_type = "float"
                if format_type == 'date' and self.dateformat == 'float':
                    format_type = "float"
            elif self.colType == "n":
                format_type = "float"

            if format_type and not format_type in self.ignore_formats:
                try:
                    if format_type == 'date':  # date/time
                        if self.workbook.date1904:
                            date = datetime.datetime(1904, 1, 1) + datetime.timedelta(float(self.data))
                        else:
                            date = datetime.datetime(1899, 12, 30) + datetime.timedelta(float(self.data))
                        if self.dateformat:
                            # str(dateformat) - python2.5 bug, see: http://bugs.python.org/issue2782
                            self.data = date.strftime(str(self.dateformat))
                        else:
                            # ignore ";@", don't know what does it mean right now
                            # ignore "[$-409], [$-f409], [$-16001]" and similar format codes
                            dateformat = re.sub(r"\[\$\-[A-z0-9]*\]", "", format_str, 1) \
                                .replace(";@", "").replace("yyyy", "%Y").replace("yy", "%y") \
                                .replace("hh:mm", "%H:%M").replace("h", "%I").replace("%H%H", "%H") \
                                .replace("ss", "%S").replace("dddd", "d").replace("dd", "d").replace("d", "%d") \
                                .replace("am/pm", "%p").replace("mmmm", "%B").replace("mmm", "%b") \
                                .replace(":mm", ":%M").replace("m", "%m").replace("%m%m", "%m")
                            self.data = date.strftime(str(dateformat)).strip()
                    elif format_type == 'time':  # time
                        t = int(round((float(self.data) % 1) * 24 * 60 * 60, 6))  # it should be in seconds
                        d = datetime.time(int((t // 3600) % 24), int((t // 60) % 60), int(t % 60))
                        self.data = d.strftime(self.timeformat)
                    elif format_type == 'float' and ('E' in self.data or 'e' in self.data):
                        self.data = str(self.floatformat or '%f') % float(self.data)
                    # if cell is general, be aggressive about stripping any trailing 0s, decimal points, etc.
                    elif format_type == 'float' and format_str == 'general':
                        self.data = ("%f" % (float(self.data))).rstrip('0').rstrip('.')
                    elif format_type == 'float' and format_str[0:3] == '0.0':
                        if self.floatformat:
                            self.data = str(self.floatformat) % float(self.data)
                        else:
                            L = len(format_str.split(".")[1])
                            if '%' in format_str:
                                L += 1
                            self.data = ("%." + str(L) + "f") % float(self.data)
                    elif format_type == 'float':
                        # unsupported float formatting
                        self.data = ("%f" % (float(self.data))).rstrip('0').rstrip('.')

                except (ValueError, OverflowError):  # this catch must be removed, it's hiding potential problems
                    eprint("Error: potential invalid date format.")
                    # invalid date format
                    pass

    def handleStartElement(self, name, attrs):
        has_namespace = name.find(":") > 0
        if self.in_row and (name == 'c' or (has_namespace and name.endswith(':c'))):
            self.colType = attrs.get("t")
            self.s_attr = attrs.get("s")
            self.cellId = attrs.get("r")
            if self.cellId:
                self.colNum = self.cellId[:len(self.cellId) - len(self.rowNum)]
                self.colIndex = 0
            else:
                self.colIndex += 1
            self.data = ""
            self.in_cell = True
        elif self.in_cell and (
                    (name == 'v' or name == 'is') or (has_namespace and (name.endswith(':v') or name.endswith(':is')))):
            self.in_cell_value = True
            self.collected_string = ""
        elif self.in_sheet and (name == 'row' or (has_namespace and name.endswith(':row'))) and ('r' in attrs):
            self.rowNum = attrs['r']
            self.in_row = True
            self.colIndex = 0
            self.colNum = ""
            self.columns = {}
            self.spans = None
            if 'spans' in attrs:
                self.spans = [int(i) for i in attrs['spans'].split(" ")[-1].split(":")]
        elif name == 't':
            # reset collected string
            self.collected_string = ""

        elif name == 'sheetData' or (has_namespace and name.endswith(':sheetData')):
            self.in_sheet = True
        elif name == 'dimension':
            rng = attrs.get("ref").split(":")
            if len(rng) > 1:
                start = re.match("^([A-Z]+)(\d+)$", rng[0])
                if (start):
                    end = re.match("^([A-Z]+)(\d+)$", rng[1])
                    startCol = start.group(1)
                    endCol = end.group(1)
                    self.columns_count = 0
                    for cell in self._range(startCol + "1:" + endCol + "1"):
                        self.columns_count += 1

    def handleEndElement(self, name):
        has_namespace = name.find(":") > 0
        if self.in_cell and ((name == 'v' or name == 'is' or name == 't') or (
                    has_namespace and (name.endswith(':v') or name.endswith(':is')))):
            self.in_cell_value = False
        elif self.in_cell and (name == 'c' or (has_namespace and name.endswith(':c'))):
            t = 0
            for i in self.colNum: t = t * 26 + ord(i) - 64
            d = self.data
            if self.hyperlinks:
                hyperlink = self.hyperlinks.get(self.cellId)
                if hyperlink:
                    d = "<a href='" + hyperlink + "'>" + d + "</a>"
            if self.colNum + self.rowNum in self.mergeCells.keys():
                if 'copyFrom' in self.mergeCells[self.colNum + self.rowNum].keys() and \
                                self.mergeCells[self.colNum + self.rowNum]['copyFrom'] == self.colNum + self.rowNum:
                    self.mergeCells[self.colNum + self.rowNum]['value'] = d
                else:
                    d = self.mergeCells[self.mergeCells[self.colNum + self.rowNum]['copyFrom']]['value']

            self.columns[t - 1 + self.colIndex] = d

        if self.in_row and (name == 'row' or (has_namespace and name.endswith(':row'))):
            if len(self.columns.keys()) > 0:
                if min(self.columns.keys()) < 0: # Weird
                    d = []
                    keys = self.columns.keys()
                    keys.sort()
                    for k in keys:
                        val = self.columns[k]
                        if not self.py3:
                            val = val.encode("utf-8")
                        d.append(val)
                else:
                    d = [""] * (max(self.columns.keys()) + 1)
                    for k in self.columns.keys():
                        val = self.columns[k]
                        if not self.py3:
                            val = val.encode("utf-8")
                        d[k] = val
                if self.spans:
                    l = self.spans[1]
                    if len(d) < l:
                        d += (l - len(d)) * ['']

                # write empty lines
                if not self.skip_empty_lines:
                    for i in range(self.lastRowNum, int(self.rowNum) - 1):
                        self.writer.writerow([])
                    self.lastRowNum = int(self.rowNum)

                # write line to csv
                if not self.skip_empty_lines or d.count('') != len(d):
                    while len(d) < self.columns_count:
                        d.append("")

                    if self.skip_trailing_columns:
                        if self.max_columns < 0:
                            self.max_columns = len(d)
                            while len(d) > 0 and d[-1] == "":
                                d = d[0:-1]
                                self.max_columns = self.max_columns - 1
                        elif self.max_columns > 0:
                            d = d[0:self.max_columns]
                    self.writer.writerow(d)

            self.in_row = False
        elif self.in_sheet and (name == 'sheetData' or (has_namespace and name.endswith(':sheetData'))):
            self.in_sheet = False

    # rangeStr: "A3:C12" or "D5"
    # example: for cell in _range("A1:Z12"): print cell
    def _range(self, rangeStr):
        rng = rangeStr.split(":")
        if len(rng) == 1:
            yield rangeStr
        else:
            start = re.match("^([A-Z]+)(\d+)$", rng[0])
            end = re.match("^([A-Z]+)(\d+)$", rng[1])
            if not start or not end:
                return
            startCol = start.group(1)
            startRow = int(start.group(2))
            endCol = end.group(1)
            endRow = int(end.group(2))
            col = startCol
            while True:
                for row in range(startRow, endRow + 1):
                    yield col + str(row)
                if col == endCol:
                    break
                t = 0
                for i in col: t = t * 26 + ord(i) - 64
                col = ""
                while t >= 0:
                    col = chr(t % 26 + 65) + col
                    t = t // 26 - 1


def convert_recursive(path, sheetid, outfile, kwargs):
    for name in os.listdir(path):
        fullpath = os.path.join(path, name)
        if os.path.isdir(fullpath):
            convert_recursive(fullpath, sheetid, outfile, kwargs)
        else:
            outfilepath = outfile
            if len(outfilepath) == 0 and fullpath.lower().endswith(".xlsx"):
                outfilepath = fullpath[:-4] + 'csv'

            print("Converting %s to %s" % (fullpath, outfilepath))
            try:
                Xlsx2csv(fullpath, **kwargs).convert(outfilepath, sheetid)
            except zipfile.BadZipfile:
                print("File %s is not a zip file" % fullpath)


if __name__ == "__main__":
    try:
        signal.signal(signal.SIGPIPE, signal.SIG_DFL)
        signal.signal(signal.SIGINT, signal.SIG_DFL)
    except AttributeError:
        pass

    if "ArgumentParser" in globals():
        parser = ArgumentParser(description="xlsx to csv converter")
        parser.add_argument('infile', metavar='xlsxfile', help="xlsx file path")
        parser.add_argument('outfile', metavar='outfile', nargs='?', help="output csv file path")
        parser.add_argument('-v', '--version', action='version', version=__version__)
        nargs_plus = "+"
        argparser = True
    else:
        parser = OptionParser(usage="%prog [options] infile [outfile]", version=__version__)
        parser.add_argument = parser.add_option
        nargs_plus = 1
        argparser = False

    if sys.version_info[0] == 2 and sys.version_info[1] < 5:
        inttype = "int"
    else:
        inttype = int
    parser.add_argument("-a", "--all", dest="all", default=False, action="store_true",
                        help="export all sheets")
    parser.add_argument("-c", "--outputencoding", dest="outputencoding", default="utf-8", action="store",
                        help="encoding of output csv ** Python 3 only ** (default: utf-8)")
    parser.add_argument("-d", "--delimiter", dest="delimiter", default=",",
                        help="delimiter - columns delimiter in csv, 'tab' or 'x09' for a tab (default: comma ',')")
    parser.add_argument("--hyperlinks", "--hyperlinks", dest="hyperlinks", action="store_true", default=False,
                        help="include hyperlinks")
    parser.add_argument("-e", "--escape", dest='escape_strings', default=False, action="store_true",
                        help="Escape \\r\\n\\t characters")
    parser.add_argument("--no-line-breaks", "--no-line-breaks", dest='no_line_breaks', default=False, action="store_true",
                        help="Replace \\r\\n\\t with space")
    parser.add_argument("-E", "--exclude_sheet_pattern", nargs=nargs_plus, dest="exclude_sheet_pattern", default="",
                        help="exclude sheets named matching given pattern, only effects when -a option is enabled.")
    parser.add_argument("-f", "--dateformat", dest="dateformat",
                        help="override date/time format (ex. %%Y/%%m/%%d)")
    parser.add_argument("-t", "--timeformat", dest="timeformat",
                        help="override time format (ex. %%H/%%M/%%S)")
    parser.add_argument("--floatformat", dest="floatformat",
                        help="override float format (ex. %%.15f)")
    parser.add_argument("--sci-float", dest="scifloat", default=False, action="store_true",
                        help="force scientific notation to float")
    parser.add_argument("-I", "--include_sheet_pattern", nargs=nargs_plus, dest="include_sheet_pattern", default="^.*$",
                        help="only include sheets named matching given pattern, only effects when -a option is enabled.")
    parser.add_argument("--exclude_hidden_sheets", default=False, action="store_true",
                        help="Exclude hidden sheets from the output, only effects when -a option is enabled.")
    parser.add_argument("--ignore-formats", nargs=nargs_plus, type=str, dest="ignore_formats", default=[''],
                        help="Ignores format for specific data types.")
    parser.add_argument("-l", "--lineterminator", dest="lineterminator", default="\n",
                        help="line terminator - lines terminator in csv, '\\n' '\\r\\n' or '\\r' (default: \\n)")
    parser.add_argument("-m", "--merge-cells", dest="merge_cells", default=False, action="store_true",
                        help="merge cells")
    parser.add_argument("-n", "--sheetname", dest="sheetname", default=None,
                        help="sheet name to convert")
    parser.add_argument("-i", "--ignoreempty", dest="skip_empty_lines", default=False, action="store_true",
                        help="skip empty lines")
    parser.add_argument("--skipemptycolumns", dest="skip_trailing_columns", default=False, action="store_true",
                        help="skip trailing empty columns")
    parser.add_argument("-p", "--sheetdelimiter", dest="sheetdelimiter", default="--------",
                        help="sheet delimiter used to separate sheets, pass '' if you do not need delimiter, or 'x07' "
                             "or '\\f' for form feed (default: '--------')")
    parser.add_argument("-q", "--quoting", dest="quoting", default="minimal",
                        help="quoting - fields quoting in csv, 'none' 'minimal' 'nonnumeric' or 'all' (default: minimal)")
    parser.add_argument("-s", "--sheet", dest="sheetid", default=1, type=inttype,
                        help="sheet number to convert")

    if argparser:
        options = parser.parse_args()
    else:
        (options, args) = parser.parse_args()
        if len(args) < 1:
            parser.print_usage()
            sys.stderr.write("error: too few arguments" + os.linesep)
            sys.exit(1)
        options.infile = args[0]
        options.outfile = len(args) > 1 and args[1] or None

    if len(options.delimiter) == 1:
        pass
    elif options.delimiter == 'tab' or options.delimiter == '\\t':
        options.delimiter = '\t'
    elif options.delimiter == 'comma':
        options.delimiter = ','
    elif options.delimiter[0] == 'x':
        options.delimiter = chr(int(options.delimiter[1:]))
    else:
        sys.stderr.write("error: invalid delimiter\n")
        sys.exit(1)

    if options.quoting == 'none':
        options.quoting = csv.QUOTE_NONE
    elif options.quoting == 'minimal':
        options.quoting = csv.QUOTE_MINIMAL
    elif options.quoting == 'nonnumeric':
        options.quoting = csv.QUOTE_NONNUMERIC
    elif options.quoting == 'all':
        options.quoting = csv.QUOTE_ALL
    else:
        sys.stderr.write("error: invalid quoting\n")
        sys.exit(1)

    if options.lineterminator == '\n':
        pass
    elif options.lineterminator == '\\n':
        options.lineterminator = '\n'
    elif options.lineterminator == '\\r':
        options.lineterminator = '\r'
    elif options.lineterminator == '\\r\\n':
        options.lineterminator = '\r\n'
    else:
        sys.stderr.write("error: invalid line terminator\n")
        sys.exit(1)

    if options.sheetdelimiter == '--------':
        pass
    elif options.sheetdelimiter == '':
        pass
    elif options.sheetdelimiter == '\\f':
        options.sheetdelimiter = '\f'
    elif options.sheetdelimiter[0] == 'x':
        options.sheetdelimiter = chr(int(options.sheetdelimiter[1:]))
    else:
        sys.stderr.write("error: invalid sheet delimiter\n")
        sys.exit(1)

    kwargs = {
        'delimiter': options.delimiter,
        'quoting': options.quoting,
        'sheetdelimiter': options.sheetdelimiter,
        'dateformat': options.dateformat,
        'timeformat': options.timeformat,
        'floatformat': options.floatformat,
        'scifloat': options.scifloat,
        'skip_empty_lines': options.skip_empty_lines,
        'skip_trailing_columns': options.skip_trailing_columns,
        'escape_strings': options.escape_strings,
        'no_line_breaks': options.no_line_breaks,
        'hyperlinks': options.hyperlinks,
        'include_sheet_pattern': options.include_sheet_pattern,
        'exclude_sheet_pattern': options.exclude_sheet_pattern,
        'exclude_hidden_sheets': options.exclude_hidden_sheets,
        'merge_cells': options.merge_cells,
        'outputencoding': options.outputencoding,
        'lineterminator': options.lineterminator,
        'ignore_formats': options.ignore_formats
    }
    sheetid = options.sheetid
    if options.all:
        sheetid = 0

    outfile = options.outfile or sys.stdout
    try:
        if os.path.isdir(options.infile):
            convert_recursive(options.infile, sheetid, outfile, kwargs)
        else:
            xlsx2csv = Xlsx2csv(options.infile, **kwargs)
            if options.sheetname:
                sheetid = xlsx2csv.getSheetIdByName(options.sheetname)
                if not sheetid:
                    raise XlsxException("Sheet '%s' not found" % options.sheetname)
            xlsx2csv.convert(outfile, sheetid)
    except XlsxException:
        _, e, _ = sys.exc_info()
        sys.stderr.write(str(e) + "\n")
        sys.exit(1)