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

13
def 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)

11
def 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)

56
def 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')

34
def 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)

84
def 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()

29
def 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)

488
def 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)

16
def 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
101
def 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

68
def 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