7 examples of 'how to extract data from excel using python' in Python

11
def processExcel(filePath, fileName):
12
    if "." in fileName:
13
        fileName = fileName.split('.')
14
        fileName = fileName[0]
15
    data = xlrd.open_workbook(filePath)
16
    table = data.sheets()[0]
17
    nrows = table.nrows
18
    ncols = table.ncols
19
20
    cs_fields_index = []#filed index
21
    golang_fields_index = []#filed index
22
    tableKeysIndex = []
23
24
    if table.nrows == 0 or table.ncols == 0:
25
        print("empty file:" + fileName)
26
27
    for index in range(ncols):
28
        CS_row = table.cell(1, index).value
29
        if CS_row == "C" or CS_row == "CS":
30
            cs_fields_index.append(index)
31
32
        if CS_row == "S" or CS_row == "CS":
33
            golang_fields_index.append(index)
34
35
    if len(cs_fields_index) > 0:
36
        cs_files.append(fileName)
37
        GenCSTableManagerFile(fileName, cs_fields_index, table)
38
        GenCSTableData(fileName, cs_fields_index, table)
39
40
    if len(golang_fields_index) > 0:
41
        go_files.append(fileName)
42
        GenGoTableManagerFile(fileName, golang_fields_index, table)
43
        GenGolangTableData(fileName, golang_fields_index, table)

67
def extract_sheet(self, sheet, book):
68
    matrix = []
69
    nrows = sheet.nrows
70
    ncols = sheet.ncols
71
    for rx in range(nrows):
72
        outrow = []
73
        for cx in range(ncols):
74
            cell = sheet.cell(rowx=rx, colx=cx)
75
            val = self.cell_to_python(cell, book)
76
            outrow.append(val)
77
        matrix.append(outrow)
78
    return matrix

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

8
def read(self):
9
    workbook = self.open_workbook()
10
    worksheet = self.get_worksheet(workbook)
11
12
    data = self.extract_data(worksheet)
13
    data = data[self.options.get('skip', 0):]
14
15
    headers = self.options.get('headers', True)
16
    if headers is True:
17
        headers, data = data[0], data[1:]
18
    if headers:
19
        return [dict(zip(headers, row)) for row in data]
20
    else:
21
        return data

44
def get_xls_to_list(excelname, sheetname):
45
	"""
46
	读取excel表，返回一个list,只是返回第一列的值
47
	return [1,2,3,4,5]
48
	"""
49
	datapath = os.path.join(data_path, excelname)
50
	excel = xlrd.open_workbook(datapath)
51
	table = excel.sheet_by_name(sheetname)
52
	result = [table.row_values(i)[0].strip() for i in range(1,table.nrows)]
53
	return result

89
def pull_data(num_days, sheet_name):
90
    start_date = sv.end_date  - timedelta(num_days)
91
    sps = setup_credentials()
92
    wks = sps.worksheet(sheet_name)
93
    
94
    csv_file = wks.export(format='csv')
95
    csv_buffer = StringIO.StringIO(csv_file)
96
    weights_data = pd.read_csv(csv_buffer, index_col=0, parse_dates=True, infer_datetime_format=True)
97
98
    earliest_date = weights_data.index[0].date()
99
    if start_date < earliest_date:
100
        start_date = earliest_date
101
102
    filtered_data = weights_data.ix[start_date:sv.end_date]
103
104
    return filtered_data

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)