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)