10 examples of 'how to read xlsx file in pandas' in Python

Every line of 'how to read xlsx file in pandas' code snippets is scanned for vulnerabilities by our powerful machine learning engine that combs millions of open source libraries, ensuring your Python code is secure.

All examples are scanned by Snyk Code

By copying the Snyk Code Snippets you agree to
this disclaimer
13def test_xlsx(self):
14 with open('examples/test.xlsx', 'rb') as f:
15 output = xlsx.xlsx2csv(f)
16
17 with open('examples/testxlsx_converted.csv', 'r') as f:
18 self.assertEquals(f.read(), output)
Important

Use secure code every time

Secure your code as it's written. Use Snyk Code to scan source code in minutes – no build needed – and fix issues immediately. Enable Snyk Code

11def fromxlsx(filename, sheet=None, range_string=None, min_row=None,
12 min_col=None, max_row=None, max_col=None, read_only=False,
13 **kwargs):
14 """
15 Extract a table from a sheet in an Excel .xlsx file.
16
17 N.B., the sheet name is case sensitive.
18
19 The `sheet` argument can be omitted, in which case the first sheet in
20 the workbook is used by default.
21
22 The `range_string` argument can be used to provide a range string
23 specifying a range of cells to extract.
24
25 The `min_row`, `min_col`, `max_row` and `max_col` arguments can be
26 used to limit the range of cells to extract. They will be ignored
27 if `range_string` is provided.
28
29 The `read_only` argument determines how openpyxl returns the loaded
30 workbook. Default is `False` as it prevents some LibreOffice files
31 from getting truncated at 65536 rows. `True` should be faster if the
32 file use is read-only and the files are made with Microsoft Excel.
33
34 Any other keyword arguments are passed through to
35 :func:`openpyxl.load_workbook()`.
36
37 """
38
39 return XLSXView(filename, sheet=sheet, range_string=range_string,
40 min_row=min_row, min_col=min_col, max_row=max_row,
41 max_col=max_col, read_only=read_only, **kwargs)
56def open_excel_input_file(self, path):
57 if not path or path == '-':
58 if six.PY2:
59 return six.BytesIO(sys.stdin.read())
60 else:
61 return six.BytesIO(sys.stdin.buffer.read())
62 else:
63 return open(path, 'rb')
34def read_excel_file(f):
35 dialect = csv.Sniffer().sniff(codecs.EncodedFile(f, "utf-8").read(1024))
36 #f.open()
37 return UnicodeCsvReader(codecs.EncodedFile(f, "utf-8"),
38 "utf-8", dialect=dialect)
84def WriteToExcel():
85 f = open('wordbank.csv','w')
86 list = CountInstancesGlobal()
87 for twoplet in list:
88 f.write(str(twoplet[0]))
89 f.write(',')
90 f.write(str(twoplet[1]))
91 f.write('\n')
92 f.close()
29def load_workbook(self, filepath_or_buffer):
30 from xlrd import open_workbook
31
32 if hasattr(filepath_or_buffer, "read"):
33 data = filepath_or_buffer.read()
34 return open_workbook(file_contents=data)
35 else:
36 return open_workbook(filepath_or_buffer)
488def excel(self):
489 """Query data base and build nsf and doe excels."""
490 rc = self.rc
491 if not rc.people:
492 sys.exit("please rerun specifying --people PERSON")
493 if isinstance(rc.people, str):
494 rc.people = [rc.people]
495 since_date = get_since_date(rc)
496 target = rc.people[0]
497 query_results = self.query_ppl(target, since_date=since_date)
498 self.render_template1(**query_results)
499 self.render_template2(**query_results)
16def copy_excel(excelpath1, excelpath2):
17 """复制excel,把 excelpath1 数据复制到 excelpath2"""
18 wb2 = openpyxl.Workbook()
19 wb2.save(excelpath2)
20 # 读取数据
21 wb1 = openpyxl.load_workbook(excelpath1)
22 wb2 = openpyxl.load_workbook(excelpath2)
23 sheets1 = wb1.sheetnames
24 sheets2 = wb2.sheetnames
25 sheet1 = wb1[sheets1[0]]
26 sheet2 = wb2[sheets2[0]]
27 max_row = sheet1.max_row # 最大行数
28 max_column = sheet1.max_column # 最大列数
29 for m in list(range(1, max_row + 1)):
30 for n in list(range(97, 97 + max_column)): # chr(97)='a'
31 n = chr(n) # ASCII字符
32 i = '%s%d' % (n, m) # 单元格编号
33 cell1 = sheet1[i].value # 获取data单元格数据
34 sheet2[i].value = cell1 # 赋值到test单元格
35 wb2.save(excelpath2) # 保存数据
36 wb1.close() # 关闭excel
37 wb2.close()
100@staticmethod
101def data_from_excel(path, stripstr=True):
102 """Get data from Excel through xlrd.
103
104 Args:
105 path (str): The path where to find the Excel file.
106 stripstr (bool): Remove trailing / leading whitespace from text?
107
108 Returns:
109 A list of worksheets, matching the source Excel file.
110 """
111 result = []
112 with xlrd.open_workbook(path) as book:
113 datemode = book.datemode
114 for i in range(book.nsheets):
115 ws = book.sheet_by_index(i)
116 my_ws = Worksheet.from_sheet(ws, datemode, stripstr)
117 result.append(my_ws)
118 return result
68def worksheet_from_excel(excel_sheet):
69 worksheet = Worksheet()
70 for col in range(excel_sheet.ncols):
71 for row in range(excel_sheet.nrows):
72 cell = excel_sheet.cell(row, col)
73 if cell.ctype == XL_CELL_ERROR:
74 formula = '=%s' % (error_text_from_code[cell.value], )
75 elif cell.ctype == XL_CELL_DATE:
76 formula = '=DateTime(%s, %s, %s, %s, %s, %s)' % xldate_as_tuple(
77 cell.value, excel_sheet.book.datemode)
78 else:
79 formula = unicode(excel_sheet.cell(row, col).value)
80 worksheet[col + 1, row + 1].formula = formula
81 return worksheet

Related snippets