def load_workbook(filename, use_iterators=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.reader.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
try:
# Python 2
is_file_instance = isinstance(filename, file)
except NameError:
# Python 3
from io import BufferedReader
is_file_instance = isinstance(filename, BufferedReader)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if "b" not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, "r", ZIP_DEFLATED)
except BadZipfile:
try:
repair_central_directory(filename)
archive = ZipFile(filename, "r", ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook()
if use_iterators:
wb._set_optimized_read()
try:
_load_workbook(wb, archive, filename, use_iterators)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
def load_workbook(filename, use_iterators=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.reader.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
try:
# Python 2
is_file_instance = isinstance(filename, file)
except NameError:
# Python 3
from io import BufferedReader
is_file_instance = isinstance(filename, BufferedReader)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
try:
repair_central_directory(filename)
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook()
if use_iterators:
wb._set_optimized_read()
try:
_load_workbook(wb, archive, filename, use_iterators)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
try:
filename = repair_central_directory(filename)
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile, e:
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError), e:
raise InvalidFileException(unicode(e))
wb = Workbook()
if use_iterators:
wb._set_optimized_read()
try:
_load_workbook(wb, archive, filename, use_iterators)
except KeyError, e:
raise InvalidFileException(unicode(e))
archive.close()
return wb
def _load_workbook(wb, archive, filename, use_iterators):
valid_files = archive.namelist()
# get workbook-level information
def test_create_sheet_readonly():
wb = Workbook()
wb._set_optimized_read()
wb.create_sheet()
def test_create_sheet_readonly():
wb = Workbook()
wb._set_optimized_read()
wb.create_sheet()
def load_workbook(filename,
use_iterators=False,
keep_vba=KEEP_VBA,
guess_types=False,
data_only=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
is_file_instance = isinstance(filename, file)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
try:
f = repair_central_directory(filename, is_file_instance)
archive = ZipFile(f, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook(guess_types=guess_types, data_only=data_only)
if use_iterators:
wb._set_optimized_read()
if guess_types:
warnings.warn(
'Data types are not guessed when using iterator reader')
try:
_load_workbook(wb, archive, filename, use_iterators, keep_vba)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
def load_workbook(filename, use_iterators=False, keep_vba=False, guess_types=True):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.reader.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
try:
# Python 2
is_file_instance = isinstance(filename, file)
except NameError:
# Python 3
from io import BufferedReader
is_file_instance = isinstance(filename, BufferedReader)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
try:
f = repair_central_directory(filename, is_file_instance)
archive = ZipFile(f, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook(guess_types=guess_types)
if use_iterators:
wb._set_optimized_read()
if not guess_types:
warnings.warn('please note that data types are not guessed '
'when using iterator reader, so you do not need '
'to use guess_types=False')
try:
_load_workbook(wb, archive, filename, use_iterators, keep_vba)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
if not keep_vba:
archive.close()
return wb
def test_create_sheet_readonly():
wb = Workbook()
wb._set_optimized_read()
with pytest.raises(ReadOnlyWorkbookException):
wb.create_sheet()
def load_workbook(filename, use_iterators=False, keep_vba=KEEP_VBA, guess_types=False, data_only=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
is_file_instance = isinstance(filename, file)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
file_format = os.path.splitext(filename)[-1]
if file_format not in SUPPORTED_FORMATS:
if file_format == '.xls':
msg = ('openpyxl does not support the old .xls file format, '
'please use xlrd to read this file, or convert it to '
'the more recent .xlsx file format.')
elif file_format == '.xlsb':
msg = ('openpyxl does not support binary format .xlsb, '
'please convert this file to .xlsx format if you want '
'to open it with openpyxl')
else:
msg = ('openpyxl does not support %s file format, '
'please check you can open '
'it with Excel first. '
'Supported formats are: %s') % (file_format,
','.join(SUPPORTED_FORMATS))
raise InvalidFileException(msg)
try:
f = repair_central_directory(filename, is_file_instance)
archive = ZipFile(f, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook(guess_types=guess_types, data_only=data_only)
if use_iterators:
wb._set_optimized_read()
if guess_types:
warnings.warn('Data types are not guessed when using iterator reader')
try:
_load_workbook(wb, archive, filename, use_iterators, keep_vba)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, "r", ZIP_DEFLATED)
except BadZipfile:
try:
repair_central_directory(filename)
archive = ZipFile(filename, "r", ZIP_DEFLATED)
except BadZipfile, e:
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError), e:
raise InvalidFileException(unicode(e))
wb = Workbook()
if use_iterators:
wb._set_optimized_read()
try:
_load_workbook(wb, archive, filename, use_iterators)
except KeyError, e:
raise InvalidFileException(unicode(e))
archive.close()
return wb
def _load_workbook(wb, archive, filename, use_iterators):
valid_files = archive.namelist()
# get workbook-level information
def load_workbook(filename, use_iterators=False, keep_vba=KEEP_VBA, guess_types=False, data_only=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
is_file_instance = isinstance(filename, file)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
try:
f = repair_central_directory(filename, is_file_instance)
archive = ZipFile(f, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook(guess_types=guess_types, data_only=data_only)
if use_iterators:
wb._set_optimized_read()
if guess_types:
warnings.warn('Data types are not guessed when using iterator reader')
try:
_load_workbook(wb, archive, filename, use_iterators, keep_vba)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
def test_create_sheet_readonly():
wb = Workbook()
wb._set_optimized_read()
with pytest.raises(ReadOnlyWorkbookException):
wb.create_sheet()
def load_workbook(filename,
use_iterators=False,
keep_vba=KEEP_VBA,
guess_types=False,
data_only=False):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param use_iterators: use lazy load for cells
:type use_iterators: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:rtype: :class:`openpyxl.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
is_file_instance = isinstance(filename, file)
if is_file_instance:
# fileobject must have been opened with 'rb' flag
# it is required by zipfile
if 'b' not in filename.mode:
raise OpenModeError("File-object must be opened in binary mode")
try:
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
except BadZipfile:
file_format = os.path.splitext(filename)[-1]
if file_format not in SUPPORTED_FORMATS:
if file_format == '.xls':
msg = ('openpyxl does not support the old .xls file format, '
'please use xlrd to read this file, or convert it to '
'the more recent .xlsx file format.')
elif file_format == '.xlsb':
msg = ('openpyxl does not support binary format .xlsb, '
'please convert this file to .xlsx format if you want '
'to open it with openpyxl')
else:
msg = ('openpyxl does not support %s file format, '
'please check you can open '
'it with Excel first. '
'Supported formats are: %s') % (
file_format, ','.join(SUPPORTED_FORMATS))
raise InvalidFileException(msg)
try:
f = repair_central_directory(filename, is_file_instance)
archive = ZipFile(f, 'r', ZIP_DEFLATED)
except BadZipfile:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
except (BadZipfile, RuntimeError, IOError, ValueError):
e = exc_info()[1]
raise InvalidFileException(unicode(e))
wb = Workbook(guess_types=guess_types, data_only=data_only)
if use_iterators:
wb._set_optimized_read()
if guess_types:
warnings.warn(
'Data types are not guessed when using iterator reader')
try:
_load_workbook(wb, archive, filename, use_iterators, keep_vba)
except KeyError:
e = exc_info()[1]
raise InvalidFileException(unicode(e))
archive.close()
return wb
