def test_not_corrupted(self):
with self.assertRaises(Exception) as context:
xlrd_demo.open_workbook(from_this_dir('corrupted_error.xls'))
self.assertTrue('Workbook corruption' in str(context.exception))
xlrd_demo.open_workbook(from_this_dir('corrupted_error.xls'),
ignore_workbook_corruption=True)
def test_excel_comments(self):
book = open_workbook(from_this_dir('test_comments_excel.xlsx'))
sheet = book.sheet_by_index(0)
note_map = sheet.cell_note_map
self.assertEqual(len(note_map), 1)
self.assertEqual(note_map[(0, 1)].text, 'hello')
def test_merged_cells(self):
book = xlrd_demo.open_workbook(from_this_dir('xf_class.xls'),
formatting_info=True)
sheet3 = book.sheet_by_name('table2')
row_lo, row_hi, col_lo, col_hi = sheet3.merged_cells[0]
self.assertEqual(sheet3.cell(row_lo, col_lo).value, 'MERGED')
self.assertEqual((row_lo, row_hi, col_lo, col_hi), (3, 7, 2, 5))
def test_read_ragged(self):
book = xlrd_demo.open_workbook(from_this_dir('ragged.xls'), ragged_rows=True)
sheet = book.sheet_by_index(0)
self.assertEqual(sheet.row_len(0), 3)
self.assertEqual(sheet.row_len(1), 2)
self.assertEqual(sheet.row_len(2), 1)
self.assertEqual(sheet.row_len(3), 4)
self.assertEqual(sheet.row_len(4), 4)
def test_excel_comments_with_multi_sheets(self):
book = open_workbook(from_this_dir('test_comments_excel_sheet2.xlsx'))
sheet = book.sheet_by_index(1)
note_map = sheet.cell_note_map
self.assertEqual(len(note_map), 1)
self.assertEqual(note_map[(1, 1)].text, 'Note lives here')
self.assertEqual(len(book.sheet_by_index(0).cell_note_map), 0)
def test_for_github_issue_101(self):
# Test for non-Excel file with forward slash file separator
# https://github.com/python-excel/xlrd_demo/issues/101
workbook = xlrd_demo.open_workbook(
from_this_dir('self_evaluation_report_2014-05-19.xlsx'))
worksheet = workbook.sheet_by_index(0)
# Test reading sample data from the worksheet.
cell = worksheet.cell(0, 0)
self.assertEqual(cell.value, 'one')
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_TEXT)
def test_for_github_issue_150(self):
# Test for non-Excel file with a non-lowercase worksheet filename.
# https://github.com/python-excel/xlrd_demo/issues/150
workbook = xlrd_demo.open_workbook(from_this_dir('issue150.xlsx'))
worksheet = workbook.sheet_by_index(0)
# Test reading sample data from the worksheet.
cell = worksheet.cell(0, 1)
self.assertEqual(cell.value, 'Cycle')
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_TEXT)
cell = worksheet.cell(1, 1)
self.assertEqual(cell.value, 1)
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_NUMBER)
def test_for_github_issue_96(self):
# Test for non-Excel file with forward slash file separator and
# lowercase names. https://github.com/python-excel/xlrd_demo/issues/96
workbook = xlrd_demo.open_workbook(
from_this_dir('apachepoi_49609.xlsx'))
worksheet = workbook.sheet_by_index(0)
# Test reading sample data from the worksheet.
cell = worksheet.cell(0, 1)
self.assertEqual(cell.value, 'Cycle')
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_TEXT)
cell = worksheet.cell(1, 1)
self.assertEqual(cell.value, 1)
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_NUMBER)
def test_for_github_issue_75(self):
# Test <cell> inlineStr attribute without <si> child.
# https://github.com/python-excel/xlrd_demo/issues/75
workbook = xlrd_demo.open_workbook(
from_this_dir('apachepoi_52348.xlsx'))
worksheet = workbook.sheet_by_index(0)
# Test an empty inlineStr cell.
cell = worksheet.cell(0, 0)
self.assertEqual(cell.value, '')
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_EMPTY)
# Test a non-empty inlineStr cell.
cell = worksheet.cell(1, 2)
self.assertEqual(cell.value, 'Category')
self.assertEqual(cell.ctype, xlrd_demo.book.XL_CELL_TEXT)
def test_merged_cells_xlsx(self):
book = xlrd_demo.open_workbook(from_this_dir('merged_cells.xlsx'))
sheet1 = book.sheet_by_name('Sheet1')
expected = []
got = sheet1.merged_cells
self.assertEqual(expected, got)
sheet2 = book.sheet_by_name('Sheet2')
expected = [(0, 1, 0, 2)]
got = sheet2.merged_cells
self.assertEqual(expected, got)
sheet3 = book.sheet_by_name('Sheet3')
expected = [(0, 1, 0, 2), (0, 1, 2, 4), (1, 4, 0, 2), (1, 9, 2, 4)]
got = sheet3.merged_cells
self.assertEqual(expected, got)
sheet4 = book.sheet_by_name('Sheet4')
expected = [(0, 1, 0, 2), (2, 20, 0, 1), (1, 6, 2, 5)]
got = sheet4.merged_cells
self.assertEqual(expected, got)
def test_ragged_rows_tidied_with_formatting(self):
# For now, we just check this doesn't raise an error.
open_workbook(from_this_dir('issue20.xls'), formatting_info=True)
def test_tilde_path_expansion(self):
with tempfile.NamedTemporaryFile(suffix='.xlsx',
dir=os.path.expanduser('~')) as fp:
shutil.copyfile(from_this_dir('text_bar.xlsx'), fp.name)
# For now, we just check this doesn't raise an error.
open_workbook(os.path.join('~', os.path.basename(fp.name)))
def test_names_demo(self):
# For now, we just check this doesn't raise an error.
open_workbook(
from_this_dir(os.path.join('..', 'examples', 'namesdemo.xls')), )
def setUp(self):
self.book = open_workbook(from_this_dir('sharedstrings_alt_location.xlsx'))
def main(cmd_args):
import optparse
global options
usage = "\n%prog [options] command [input-file-patterns]\n" + cmd_doc
oparser = optparse.OptionParser(usage)
oparser.add_option("-l",
"--logfilename",
default="",
help="contains error messages")
oparser.add_option(
"-v",
"--verbosity",
type="int",
default=0,
help="level of information and diagnostics provided")
oparser.add_option(
"-m",
"--mmap",
type="int",
default=-1,
help="1: use mmap; 0: don't use mmap; -1: accept heuristic")
oparser.add_option("-e",
"--encoding",
default="",
help="encoding override")
oparser.add_option(
"-f",
"--formatting",
type="int",
default=0,
help="0 (default): no fmt info\n"
"1: fmt info (all cells)\n",
)
oparser.add_option(
"-g",
"--gc",
type="int",
default=0,
help=
"0: auto gc enabled; 1: auto gc disabled, manual collect after each file; 2: no gc"
)
oparser.add_option(
"-s",
"--onesheet",
default="",
help="restrict output to this sheet (name or index)")
oparser.add_option("-u",
"--unnumbered",
action="store_true",
default=0,
help="omit line numbers or offsets in biff_dump")
oparser.add_option("-d",
"--on-demand",
action="store_true",
default=0,
help="load sheets on demand instead of all at once")
oparser.add_option("-t",
"--suppress-timing",
action="store_true",
default=0,
help="don't print timings (diffs are less messy)")
oparser.add_option("-r",
"--ragged-rows",
action="store_true",
default=0,
help="open_workbook(..., ragged_rows=True)")
options, args = oparser.parse_args(cmd_args)
if len(args) == 1 and args[0] in ("version", ):
pass
elif len(args) < 2:
oparser.error("Expected at least 2 args, found %d" % len(args))
cmd = args[0]
xlrd_demo_version = getattr(xlrd_demo, "__VERSION__",
"unknown; before 0.5")
if cmd == 'biff_dump':
xlrd_demo.dump(args[1], unnumbered=options.unnumbered)
sys.exit(0)
if cmd == 'biff_count':
xlrd_demo.count_records(args[1])
sys.exit(0)
if cmd == 'version':
print("xlrd_demo: %s, from %s" %
(xlrd_demo_version, xlrd_demo.__file__))
print("Python:", sys.version)
sys.exit(0)
if options.logfilename:
logfile = LogHandler(open(options.logfilename, 'w'))
else:
logfile = sys.stdout
mmap_opt = options.mmap
mmap_arg = xlrd_demo.USE_MMAP
if mmap_opt in (1, 0):
mmap_arg = mmap_opt
elif mmap_opt != -1:
print('Unexpected value (%r) for mmap option -- assuming default' %
mmap_opt)
fmt_opt = options.formatting | (cmd in ('xfc', ))
gc_mode = options.gc
if gc_mode:
gc.disable()
for pattern in args[1:]:
for fname in glob.glob(pattern):
print("\n=== File: %s ===" % fname)
if logfile != sys.stdout:
logfile.setfileheading("\n=== File: %s ===\n" % fname)
if gc_mode == 1:
n_unreachable = gc.collect()
if n_unreachable:
print("GC before open:", n_unreachable,
"unreachable objects")
try:
t0 = time.time()
bk = xlrd_demo.open_workbook(
fname,
verbosity=options.verbosity,
logfile=logfile,
use_mmap=mmap_arg,
encoding_override=options.encoding,
formatting_info=fmt_opt,
on_demand=options.on_demand,
ragged_rows=options.ragged_rows,
)
t1 = time.time()
if not options.suppress_timing:
print("Open took %.2f seconds" % (t1 - t0, ))
except xlrd_demo.XLRDError as e:
print("*** Open failed: %s: %s" % (type(e).__name__, e))
continue
except KeyboardInterrupt:
print("*** KeyboardInterrupt ***")
traceback.print_exc(file=sys.stdout)
sys.exit(1)
except BaseException as e:
print("*** Open failed: %s: %s" % (type(e).__name__, e))
traceback.print_exc(file=sys.stdout)
continue
t0 = time.time()
if cmd == 'hdr':
bk_header(bk)
elif cmd == 'ov': # OverView
show(bk, 0)
elif cmd == 'show': # all rows
show(bk)
elif cmd == '2rows': # first row and last row
show(bk, 2)
elif cmd == '3rows': # first row, 2nd row and last row
show(bk, 3)
elif cmd == 'bench':
show(bk, printit=0)
elif cmd == 'fonts':
bk_header(bk)
show_fonts(bk)
elif cmd == 'names': # named reference list
show_names(bk)
elif cmd == 'name_dump': # named reference list
show_names(bk, dump=1)
elif cmd == 'labels':
show_labels(bk)
elif cmd == 'xfc':
count_xfs(bk)
else:
print("*** Unknown command <%s>" % cmd)
sys.exit(1)
del bk
if gc_mode == 1:
n_unreachable = gc.collect()
if n_unreachable:
print("GC post cmd:", fname, "->", n_unreachable,
"unreachable objects")
if not options.suppress_timing:
t1 = time.time()
print("\ncommand took %.2f seconds\n" % (t1 - t0, ))
return None
def test_excel_comments_multiline(self):
book = open_workbook(from_this_dir('test_comments_excel.xlsx'))
sheet = book.sheet_by_index(1)
note_map = sheet.cell_note_map
self.assertEqual(note_map[(1, 2)].text, '1st line\n2nd line')
def test_xlsx(self):
# For now, we just check this doesn't raise an error.
open_workbook(from_this_dir('reveng1.xlsx'))
def setUp(self):
self.book = xlrd_demo.open_workbook(from_this_dir('Formate.xls'),
formatting_info=True)
self.sheet = self.book.sheet_by_name(u('Blätt1'))
def setUp(self):
path = from_this_dir('biff4_no_format_no_window2.xls')
self.book = open_workbook(path)
self.sheet = self.book.sheet_by_index(0)
def test_xlsx_lower_case_cellnames(self):
# Check if it opens with lower cell names
open_workbook(from_this_dir('test_lower_case_cellnames.xlsx'))
def setUp(self):
self.book = xlrd_demo.open_workbook(from_this_dir('profiles.xls'), formatting_info=True)
def test_tidy_dimensions(self):
book = xlrd_demo.open_workbook(from_this_dir('merged_cells.xlsx'))
for sheet in book.sheets():
for rowx in xrange(sheet.nrows):
self.assertEqual(sheet.row_len(rowx), sheet.ncols)
def setUp(self):
self.book = open_workbook(from_this_dir('profiles.xls'))
def test_BYTES_X00(self):
# For now, we just check this doesn't raise an error.
open_workbook(from_this_dir('picture_in_cell.xls'),
formatting_info=True)
def test_xlsx_simple(self):
# For now, we just check this doesn't raise an error.
open_workbook(from_this_dir('text_bar.xlsx'))
def setUp(self):
self.book = xlrd_demo.open_workbook(from_this_dir('profiles.xls'),
formatting_info=True)
self.sheet = self.book.sheet_by_name('PROFILEDEF')
def test_err_cell_empty(self):
# For cell with type "e" (error) but without inner 'val' tags
open_workbook(from_this_dir('err_cell_empty.xlsx'))
def test_excel_comments_no_t_elements(self):
book = open_workbook(from_this_dir('test_comments_excel.xlsx'))
sheet = book.sheet_by_index(3)
note_map = sheet.cell_note_map
self.assertEqual(note_map[(0, 0)].text, '')
def setUp(self):
book = xlrd_demo.open_workbook(from_this_dir('formula_test_names.xls'))
self.sheet = book.sheet_by_index(0)
Revenue -1 0 checks if "Revenue" exists in global scope
"""
sys.stdout.write(text)
if len(sys.argv) != 5:
usage()
sys.exit(0)
arg_pattern = sys.argv[1] # glob pattern e.g. "foo*.xls"
arg_name = sys.argv[2] # see below
arg_scope = sys.argv[3] # see below
# 0: no show,
# 1: only non-empty cells,
# 2: all cells
arg_show_contents = int(sys.argv[4])
for fname in glob.glob(arg_pattern):
book = xlrd_demo.open_workbook(fname)
if arg_name == "*":
# Examine book.name_obj_list to find all names
# in a given scope ("*" => all scopes)
do_scope_query(book, arg_scope, arg_show_contents)
elif arg_scope == "*":
# Using book.name_map to find all usage of a name.
show_name_details(book, arg_name, arg_show_contents)
else:
# Using book.name_and_scope_map to find which if any instances
# of a name are visible in the given scope, which can be supplied
# as -1 (global) or a sheet number or a sheet name.
show_name_details_in_scope(book, arg_name, arg_scope,
arg_show_contents)
