Excel文件檢查

讀寫Excel文件

from xlrd import open_workbookfrom xlwt import Workbookinput_file='sales_2013.xlsx'output_file='excel_copy.xlsx'output_workbook=Workbook()output_worksheet=output_workbook.add_sheet('jan_2013_output')with open_workbook(input_file) as workbook:    worksheet=workbook.sheet_by_name('january_2013')    for row_index in range(worksheet.nrows):        for column_index in range(worksheet.ncols):            output_worksheet.write(row_index,column_index,worksheet.cell_value(row_index,column_index))output_workbook.save(output_file)
1
2
3
4
5
6
7
8
9
10
11
12

格式化Excel文件中的數(shù)據(jù)

Excel行中的值滿足某個條件

import sysfrom datetime import datefrom xlrd import open_workbook,xldate_as_tuplefrom xlwt import Workbookinput_file='sales_2013.xlsx'output_file='g.xls'output_workbook=Workbook()output_worksheet=output_workbook.add_sheet('jan_2013_output')sale_amount_column_index=3with open_workbook(input_file) as workbook:    worksheet=workbook.sheet_by_name('january_2013')    data=[]    header=worksheet.row_values(0)    data.append(header)    for row_index in range(1,worksheet.nrows):        row_list=[]        sale_amount=worksheet.cell_value(row_index,sale_amount_column_index)        if sale_amount >1400.0:            for column_index in range(worksheet.ncols):                cell_value=worksheet.cell_value(row_index,column_index)                cell_type=worksheet.cell_type(row_index,column_index)                if cell_type ==3:                    date_cell=xldate_as_tuple(cell_value,workbook.datemode)                    date_cell=date(*date_cell[0:3]).strftime('%m%d%Y')                    row_list.append(date_cell)                else:                    row_list.append(cell_value)            if row_list:                data.append(row_list)    for list_index,output_list in enumerate(data):        for element_index,element in enumerate(output_list):            output_worksheet.write(list_index,element_index,element)output_workbook.save(output_file)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

pandas版本

Excel行中的值屬于某個集合

import pandas as pdimport sysinput_file='sales_2013.xlsx'output_file='value_in_set.xls'data_frame=pd.read_excel(input_file,'january_2013',index_col=None)inportant_dates=['01/24/2013','01/31/2013']data_frame_value_in_set=data_frame[data_frame['Purchase Date'].isin(inportant_dates)]writer=pd.ExcelWriter(output_file)data_frame_value_in_set.to_excel(writer,sheet_name='jan_2013_output',index=False)writer.save()
1
2
3
4
5
6
7
8
9
10

Excel行中的值匹配某個表達(dá)式

Excel基于索引選取特定的列

import pandas as pdinput_file='sales_2013.xlsx'output_file='value_in_column.xls'data_frame=pd.read_excel(input_file,'january_2013',index_col=None)data_frame_by_index=data_frame.iloc[:,[1,4]]writer=pd.ExcelWriter(output_file)data_frame_by_index.to_excel(writer,sheet_name='jan_13',index=False)writer.save()
1
2
3
4
5
6
7
8

Excel基于列標(biāo)題選取

工作表計數(shù)以及每個工作表中的行列計數(shù)

import globimport osimport sysfrom xlrd import open_workbookinput_directory = sys.argv[1]workbook_counter = 0for input_file in glob.glob(os.path.join(input_directory, '*.xls*')):    workbook = open_workbook(input_file)    print('Workbook: {}'.format(os.path.basename(input_file)))    print('Number of worksheets: {}'.format(workbook.nsheets))    for worksheet in workbook.sheets():        print('Worksheet name:', worksheet.name, '\tRows:',\                worksheet.nrows, '\tColumns:', worksheet.ncols)    workbook_counter += 1print('Number of Excel workbooks: {}'.format(workbook_counter))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在一個Excel文件所有工作表中選取特定行

在一個Excel所有工作表中選取特定列

import pandas as pdimport sysinput_file='sales_2013.xlsx'output_file='pandas_column_by_name_all_worksheets.xls'data_frame = pd.read_excel(input_file, sheet_name=None, index_col=None)column_output = []for worksheet_name, data in data_frame.items():    column_output.append(data.loc[:, ['Customer Name', 'Sale Amount']])selected_columns = pd.concat(column_output, axis=0, ignore_index=True)writer = pd.ExcelWriter(output_file)selected_columns.to_excel(writer, sheet_name='selected_columns_all_worksheets', index=False)writer.save()
1
2
3
4
5
6
7
8
9
10
11
12
13

在Excel工作簿中讀取一組工作表
也就是說只讀取某些工作表
下面包含兩種篩選方式：1 索引篩選 2 根據(jù)工作表名字篩選

將多個工作簿中的多個工作表整合到一個Excel中

import pandas as pdimport globimport osimport sysinput_path='./'output_file='all_data_all_workbooks.xls'all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))data_frames = []for workbook in all_workbooks:    all_worksheets = pd.read_excel(workbook, sheet_name=None, index_col=None)    for worksheet_name, data in all_worksheets.items():        data_frames.append(data)all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)writer = pd.ExcelWriter(output_file)all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks', index=False)writer.save()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

為每個工作簿和工作表計算總數(shù)和平均值