import xlwt
import xlrd
import os, sys
def get_file_list(folder_path):
"""
Get File List Under the Specific Folder
:return Yield Full Name for each file
:parameter folder_path: Specific Folder
"""
for root, dis, files in os.walk(folder_path):
for file in files:
yield (os.path.join(root, file))
def read_excel_row(filename, sheet_index=0):
"""
Read Specific sheet by Index in Excel File
:param filename: file name of Excel
:param sheet_index: Sheet Index. Default 0 ( first sheet)
:return: row value
"""
wbk = xlrd.open_workbook(filename)
sht = wbk.sheet_by_index(sheet_index)
for row in range(sht.nrows):
yield (sht.row_values(row))
def main(file_path, sheet_index=0, result_file_name='Result.xls', result_sheet_name='Result'):
"""
Merge Excel Files into 1 File and 1 Sheet
Save the file under the same folder with specific name
:param file_path: Excel files
:param sheet_index: Specific index. Start from 0, default 0
:param result_file_name: New File name. Default is 'Result.xls'
:param result_sheet_name: New Sheet Name. Default is 'Result'
:return:
"""
# create new workbook
wbk = xlwt.Workbook()
# create new sheet
sht = wbk.add_sheet(result_sheet_name)
# start row is first row
row_number = 0
# each file
for file in get_file_list(file_path):
# if the file name meet the requirement
if file.endswith('.xls') and file != os.path.join(file_path, result_file_name):
# read each row in the file
for row_value in read_excel_row(file,sheet_index):
# read each column and write to the new file
col_number = 0
for col_value in row_value:
sht.write(row_number, col_number, col_value)
col_number += 1
row_number = +1
# save file
wbk.save(os.path.join(file_path,result_file_name))
if __name__ == '__main__':
main(sys.path[0])
