# Copyright (c) 2011-2014, B.I.Stepanov Institute of Physics, National Academy
# of Sciences of Belarus.
#
# 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, write to the Free Software Foundation, Inc., 51
# Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

"""Additional functions to work with Microsoft Excel files."""

import datetime
import xlrd

# **** Public functions *******************************************************
def getColumnIndex(columnName):
    """Return the zero-based numeric index of an Excel column whose letter
    index is 'columnName'.

    'columnName' has to be a sequence of uppercase latin letters."""

    assert len(columnName) > 0

    # Calculate index among columns with names of the same length.
    index = 0
    for char in columnName:
        digit = ord(char) - ord('A')
        assert 0 <= digit < 26
        index = index * 26 + digit

    # Take columns with shorter names into account.
    for i in range(len(columnName) - 1):
        index += 26**(i+1)

    assert xlrd.colname(index) == columnName
    return index

def readDate(cellValue, cellType, workBookDateMode):
    """Return a 'datetime.date' instance holding the value of an Excel cell or
    'None' if the value is not a date."""

    if cellType != xlrd.XL_CELL_DATE:
        return None

    try:
        dateTuple = xlrd.xldate_as_tuple(cellValue, workBookDateMode)
    except xlrd.XLDateError:
        return None

    # Treat cells that specify both date and time as invalid.
    if any(dateTuple[i] != 0.0 for i in (3, 4, 5)):
        return None

    return datetime.date(dateTuple[0], dateTuple[1], dateTuple[2])

def readTime(cellValue, cellType, workBookDateMode):
    """Return a 'datetime.time' instance holding the value of an Excel cell or
    'None' if the value is not a time."""

    if cellType != xlrd.XL_CELL_DATE:
        return None

    try:
        dateTuple = xlrd.xldate_as_tuple(cellValue, workBookDateMode)
    except xlrd.XLDateError:
        return None

    # Treat cells that specify both date and time as invalid.
    if any(dateTuple[i] != 0.0 for i in (0, 1, 2)):
        return None

    return datetime.time(dateTuple[3], dateTuple[4], dateTuple[5])

def checkWorksheetExists(filePath, worksheetName):
    """Check if 'filePath' points to a valid Excel file that may be opened for
    reading and contains a worksheet of the given name."""

    try:
        # 'on_demand' means 'load the requested worksheets only'.
        workBook = xlrd.open_workbook(filePath, on_demand = True)
    except (IOError, xlrd.XLRDError):
        # The file is not valid or may not be opened for reading.
        return False

    return worksheetName in workBook.sheet_names()