Python xlrd.XLRDError() Examples

def check_excel_table_sheet_by_index(self, filename, csvfile):
        import xlrd

        pth = os.path.join(self.dirpath, filename)
        xls = ExcelFile(pth)
        df = xls.parse(0, index_col=0, parse_dates=True)
        df2 = self.read_csv(csvfile, index_col=0, parse_dates=True)
        df3 = xls.parse(1, skiprows=[1], index_col=0, parse_dates=True)
        tm.assert_frame_equal(df, df2, check_names=False)
        tm.assert_frame_equal(df3, df2, check_names=False)

        df4 = xls.parse(0, index_col=0, parse_dates=True, skipfooter=1)
        df5 = xls.parse(0, index_col=0, parse_dates=True, skip_footer=1)
        tm.assert_frame_equal(df4, df.ix[:-1])
        tm.assert_frame_equal(df4, df5)

        self.assertRaises(xlrd.XLRDError, xls.parse, 'asdf') 

def test_excel_sheet_by_name_raise(self, *_):
        import xlrd

        gt = DataFrame(np.random.randn(10, 2))
        gt.to_excel(self.path)

        xl = ExcelFile(self.path)
        df = read_excel(xl, 0, index_col=0)

        tm.assert_frame_equal(gt, df)

        with pytest.raises(xlrd.XLRDError):
            read_excel(xl, "0") 

def convert_xsl_to_csv(contents):
    """Converts data in xsl (or xslx) format to CSV."""
    try:
        book = xlrd.open_workbook(file_contents=contents)
    except xlrd.XLRDError as e:
        return None, str(e)
    except UnicodeDecodeError:
        return None, 'The encoding of the file is unknown.'
    if book.nsheets == 0:
        return None, 'The uploaded file contains no sheets.'
    sheet = book.sheet_by_index(0)
    table = []
    for row in xrange(sheet.nrows):
        table_row = []
        for col in xrange(sheet.ncols):
            value = None
            cell_value = sheet.cell_value(row, col)
            cell_type = sheet.cell_type(row, col)
            if cell_type == xlrd.XL_CELL_TEXT:
                value = cell_value
            elif cell_type == xlrd.XL_CELL_NUMBER:
                value = str(int(cell_value))
            elif cell_type == xlrd.XL_CELL_BOOLEAN:
                value = 'true' if cell_value else 'false'
            elif cell_type == xlrd.XL_CELL_DATE:
                # TODO(ryok): support date type.
                pass
            table_row.append(value)
        table.append(table_row)

    csv_output = StringIO.StringIO()
    csv_writer = csv.writer(csv_output)
    csv_writer.writerows(table)
    return csv_output.getvalue(), None 

def open(self, source, encoding=None):
        self.close()
        self.__encoding = encoding
        self.__bytes = self.__loader.load(source, mode='b', encoding=encoding)

        # Get book
        file_contents = self.__bytes.read()
        try:
            self.__book = xlrd.open_workbook(
                file_contents=file_contents,
                encoding_override=encoding,
                formatting_info=True,
                logfile=sys.stderr
            )
        except NotImplementedError:
            self.__book = xlrd.open_workbook(
                file_contents=file_contents,
                encoding_override=encoding,
                formatting_info=False,
                logfile=sys.stderr
            )

        # Get sheet
        try:
            if isinstance(self.__sheet_pointer, six.string_types):
                self.__sheet = self.__book.sheet_by_name(self.__sheet_pointer)
            else:
                self.__sheet = self.__book.sheet_by_index(self.__sheet_pointer - 1)
        except (xlrd.XLRDError, IndexError):
            message = 'Excel document "%s" doesn\'t have a sheet "%s"'
            raise exceptions.SourceError(message % (source, self.__sheet_pointer))
        self.__fragment = self.__sheet.name

        # Reset parser
        self.reset() 

def unit_defs_from_sheet(sheet, column_names):
  """A generator that parses a worksheet containing UNECE code definitions.

  Args:
    sheet: An xldr.sheet object representing a UNECE code worksheet.
    column_names: A list/tuple with the expected column names corresponding to
                  the unit name, code and suffix in that order.
  Yields: Lines of Python source code that define OpenHTF Unit objects.
  """
  seen = set()
  try:
    col_indices = {}
    rows = sheet.get_rows()
    
    # Find the indices for the columns we care about.
    for idx, cell in enumerate(six.next(rows)):
      if cell.value in column_names:
        col_indices[cell.value] = idx

    # loop over all remaining rows and pull out units.
    for row in rows:
      name = row[col_indices[column_names[0]]].value.replace("'", r'\'')
      code = row[col_indices[column_names[1]]].value
      suffix = row[col_indices[column_names[2]]].value.replace("'", r'\'')
      key = unit_key_from_name(name)
      if key in seen:
        continue
      seen.add(key)

      # Split on ' or ' to support the units like '% or pct'
      for suffix in suffix.split(' or '):
        yield "%s = UnitDescriptor('%s', '%s', '''%s''')\n" % (
            key, name, code, suffix)
        yield "ALL_UNITS.append(%s)\n" % key

  except xlrd.XLRDError:
    sys.stdout.write('Unable to process the .xls file.') 

def test_excel_sheet_by_name_raise(self):
        _skip_if_no_xlrd()
        import xlrd

        with ensure_clean(self.ext) as pth:
            gt = DataFrame(np.random.randn(10, 2))
            gt.to_excel(pth)
            xl = ExcelFile(pth)
            df = read_excel(xl, 0)
            tm.assert_frame_equal(gt, df)

            with pytest.raises(xlrd.XLRDError):
                read_excel(xl, '0') 

def test_excel_table_sheet_by_index(self):

        excel = self.get_excelfile('test1')
        dfref = self.get_csv_refdf('test1')

        df1 = read_excel(excel, 0, index_col=0)
        df2 = read_excel(excel, 1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df1 = excel.parse(0, index_col=0)
        df2 = excel.parse(1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df3 = read_excel(excel, 0, index_col=0, skipfooter=1)
        df4 = read_excel(excel, 0, index_col=0, skip_footer=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])
        tm.assert_frame_equal(df3, df4)

        df3 = excel.parse(0, index_col=0, skipfooter=1)
        df4 = excel.parse(0, index_col=0, skip_footer=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])
        tm.assert_frame_equal(df3, df4)

        import xlrd
        with pytest.raises(xlrd.XLRDError):
            read_excel(excel, 'asdf') 

def _load_workbook(self):
        try:
            self.workbook = pd.ExcelFile(self.path_to_excel_file)

        except XLRDError as ex:
            logger.error("invalid format of excel file '%s' (%s)" % (self.path_to_excel_file, ex), exc_info=True)
            raise InvalidExcelFileFormat("invalid file format") from ex

        except Exception:
            logger.fatal("unable to read workbook at '%s'" % self.path_to_excel_file, exc_info=True)
            raise 

def terna_file_to_initial_dataframe(filepath):
    '''
    Parse the xml or read excel directly, 
    returning the data from the file in a simple-index dataframe.

    Some files are formated as xml, some are pure excel files.
    This function handles both cases.

    Parameters:
    ----------
    filepath: str 
        The path of the file to process

    Returns:
    ----------
    df: pandas.DataFrame
        A pandas dataframe containing the data from the specified file.

    '''
    # First, we'll try to parse the file as if it is xml.
    try:
        excelHandler = ExcelHandler()
        parse(filepath, excelHandler)

        # Create the dataframe from the parsed data
        df = pd.DataFrame(excelHandler.tables[0][2:],
                          columns=excelHandler.tables[0][1])

        # Convert the "Generation [MWh]"-column to numeric
        df['Generation [MWh]'] = pd.to_numeric(df['Generation [MWh]'])
    except:
        # In the case of an exception, treat the file as excel.
        try:
            df = pd.read_excel(filepath, header=1)
        except xlrd.XLRDError:
            df = pd.DataFrame()

    return df 

def download_860m(settings):
    """Load the entire 860m file into memory as an ExcelFile object.

    Parameters
    ----------
    settings : dict
        User-defined settings loaded from a YAML file. This is where the EIA860m
        filename is defined.

    Returns
    -------
    [type]
        [description]
    """
    try:
        fn = settings["eia_860m_fn"]
    except KeyError:
        # No key in the settings file
        logger.info("Trying to determine the most recent EIA860m file...")
        fn = find_newest_860m()

    # Only the most recent file will not have archive in the url
    url = f"https://www.eia.gov/electricity/data/eia860m/xls/{fn}"
    archive_url = f"https://www.eia.gov/electricity/data/eia860m/archive/xls/{fn}"

    local_file = DATA_PATHS["eia_860m"] / fn
    if local_file.exists():
        logger.info(f"Reading a local copy of the EIA860m file {fn}")
        eia_860m = pd.ExcelFile(local_file)
    else:
        logger.info(f"Downloading the EIA860m file {fn}")
        try:
            download_save(url, local_file)
            eia_860m = pd.ExcelFile(local_file)
        except XLRDError:
            logger.warning("A more recent version of EIA-860m is available")
            download_save(archive_url, local_file)
            eia_860m = pd.ExcelFile(local_file)
        # write the file to disk

    return eia_860m 

def test_excel_table_sheet_by_index(self, ext):

        excel = self.get_excelfile('test1', ext)
        dfref = self.get_csv_refdf('test1')

        df1 = read_excel(excel, 0, index_col=0)
        df2 = read_excel(excel, 1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df1 = excel.parse(0, index_col=0)
        df2 = excel.parse(1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df3 = read_excel(excel, 0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        with tm.assert_produces_warning(FutureWarning, check_stacklevel=False):
            df4 = read_excel(excel, 0, index_col=0, skip_footer=1)
            tm.assert_frame_equal(df3, df4)

        df3 = excel.parse(0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        import xlrd
        with pytest.raises(xlrd.XLRDError):
            read_excel(excel, 'asdf') 

def test_excel_table_sheet_by_index(self, ext):

        excel = self.get_excelfile('test1', ext)
        dfref = self.get_csv_refdf('test1')

        df1 = read_excel(excel, 0, index_col=0)
        df2 = read_excel(excel, 1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df1 = excel.parse(0, index_col=0)
        df2 = excel.parse(1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df3 = read_excel(excel, 0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        with tm.assert_produces_warning(FutureWarning, check_stacklevel=False):
            df4 = read_excel(excel, 0, index_col=0, skip_footer=1)
            tm.assert_frame_equal(df3, df4)

        df3 = excel.parse(0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        import xlrd
        with pytest.raises(xlrd.XLRDError):
            read_excel(excel, 'asdf') 

def test_excel_sheet_by_name_raise(self):
        _skip_if_no_xlrd()
        import xlrd

        with ensure_clean(self.ext) as pth:
            gt = DataFrame(np.random.randn(10, 2))
            gt.to_excel(pth)
            xl = ExcelFile(pth)
            df = xl.parse(0)
            tm.assert_frame_equal(gt, df)

            self.assertRaises(xlrd.XLRDError, xl.parse, '0') 

def test_excel_sheet_by_name_raise(self, merge_cells, engine, ext):
        import xlrd

        gt = DataFrame(np.random.randn(10, 2))
        gt.to_excel(self.path)
        xl = ExcelFile(self.path)
        df = read_excel(xl, 0)
        tm.assert_frame_equal(gt, df)

        with pytest.raises(xlrd.XLRDError):
            read_excel(xl, '0') 

def test_excel_table_sheet_by_index(self, ext):

        excel = self.get_excelfile('test1', ext)
        dfref = self.get_csv_refdf('test1')

        df1 = read_excel(excel, 0, index_col=0)
        df2 = read_excel(excel, 1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df1 = excel.parse(0, index_col=0)
        df2 = excel.parse(1, skiprows=[1], index_col=0)
        tm.assert_frame_equal(df1, dfref, check_names=False)
        tm.assert_frame_equal(df2, dfref, check_names=False)

        df3 = read_excel(excel, 0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        with tm.assert_produces_warning(FutureWarning, check_stacklevel=False):
            df4 = read_excel(excel, 0, index_col=0, skip_footer=1)
            tm.assert_frame_equal(df3, df4)

        df3 = excel.parse(0, index_col=0, skipfooter=1)
        tm.assert_frame_equal(df3, df1.iloc[:-1])

        import xlrd
        with pytest.raises(xlrd.XLRDError):
            read_excel(excel, 'asdf') 

def xlsx_do_file(afile):
    try:
        axl = open_workbook(afile)
    except XLRDError as e:
        log_error(str(e), afile)
        return
    xlsx_do_xlsx(axl, afile) 

def xlsx_do_data(data, afile):
    try:
        axl = open_workbook(file_contents=data)
    except XLRDError as e:
        log_error(str(e), afile)
        return
    xlsx_do_xlsx(axl, afile) 

def test_excel_sheet_by_name_raise(self, *_):
        import xlrd

        gt = DataFrame(np.random.randn(10, 2))
        gt.to_excel(self.path)

        xl = ExcelFile(self.path)
        df = read_excel(xl, 0, index_col=0)

        tm.assert_frame_equal(gt, df)

        with pytest.raises(xlrd.XLRDError):
            read_excel(xl, "0")