Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "what are the methods of batch implementation of multi-Excel and multi-Sheet merging with Python?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Preface

Admittedly, one by one open copy and paste is feasible, but this method is time-consuming and error-prone, several files can also be processed manually, if dozens or even hundreds, you will catch blind, but this problem for Python, so easy, let's have a look!

II. Project objectives

Python is used to realize the merge processing of multi-Excel and multi-Sheet.

III. Project preparation

Software: PyCharm

Required libraries: pandas, xlrd,os

IV. Project analysis

1) how to select Excel files to merge?

Using os, get all the Excel files you want to merge.

2) how to select the Sheet to be merged?

Use the xlrd library for Excel reading to get the Sheet name to be merged.

3) how to merge?

Using the pandas library, open all the Sheet names one by one, and append and merge the data through concat ().

4) how to save the file?

Save the data using to_excel to get the target file after the final merger.

V. Project realization

1. The first step is to import the required libraries

Import pandas as pd import xlrd import os

2. The second step is to select the Excel files to merge.

# to merge the file path path= "D:/b/" # get all the EXCEL names under the folder xlsx_names = [x for x in os.listdir (path) if x.endswith (".xlsx")]

3. Step 3: select the Sheet to be merged

# get the first EXCEL name xlsx_names1 = xlsx_names [0] aa = path + xlsx_names1 # Open the first EXCEL first_file_fh=xlrd.open_workbook (aa) # get the SHEET name first_file_sheet=first_file_fh.sheets ()

4. The fourth step is to merge the Sheet content in a loop.

# cycle for sheet_name in sheet_names by SHEET name: df = None # cycle by EXCEL name for xlsx_name in xlsx_names: sheet_na = pd.ExcelFile (path + xlsx_name). Sheet_names if sheet_name in sheet_na: # print (sheet_name) _ df = pd.read_excel (path + xlsx_name, sheet_name=sheet_name) Header=None) if df is None: df = _ df else: df = pd.concat ([df, _ df], ignore_index=True) else:continue

Step 5. Save the merged file.

Df.to_excel (excel_writer=writer, sheet_name=sheet_name, encoding= "utf-8", index=False) print (sheet_name + "saved successfully! a total of d, d." % (len (sheet_names), num) num + = 1 writer.save () writer.close ()

VI. Effect display

1. Excel data before processing:

2. Prompt for running progress:

3. The result of the merger:

Colored egg

The following two codes are provided by the small partners in the group, and the editor has also tested them by himself. The personal test is valid. Welcome to try it actively!

The code from the group friend Jayson:

#-*-coding: utf-8-*-# @ Author: hebe # @ Date: 2020-04-18 18:31:03 # @ Last Modified by: hebe # @ Last Modified time: 2020-04-18 19:40:48 import os import glob import openpyxl def merge_xlsx_files (xlsx_files): wb = openpyxl.load_workbook (xlsx_files [0]) ws = wb.active ws.title = "merged result" for filename in Xlsx_files [1:]: workbook = openpyxl.load_workbook (filename) sheet = workbook.active for row in sheet.iter_rows (min_row=1): values = [cell.value for cell in row] ws.append (values) return wb # path is very important here Must true. Def get_all_xlsx_files (path): xlsx_files = glob.glob (os.path.join (ritual C:\\ Users\\ pdcfi\\ Desktop\\,'* .xlsx') sorted (xlsx_files) Key=str.lower) return xlsx_files def main (): xlsx_files = get_all_xlsx_files (os.path.expanduser ('~ lmx')) wb = merge_xlsx_files (xlsx_files) wb.save ('merged_form.xlsx') if _ _ name__ =' _ main__': main () print ("all excel append OK!")

The code from my friend Liu Zaoqi:

#-*-coding: utf-8-*-from openpyxl import load_workbook, Workbook import glob path = "C:\\ Users\\ pdcfi\\ Desktop\\ excel\\" new_workbook = Workbook () new_sheet = new_workbook.active # use the flag variable to determine whether the header has been added to the new table As long as you add it once, you do not need to add flag = 0 for file in glob.glob (path +'/ * .xlsx'): workbook = load_workbook (file) sheet = workbook.active coloum_A = sheet ['A'] row_lst = [] for cell in coloum_A: if cell: print (cell.row) row_lst.append (cell.row) if Not flag: header = sheet [1] header_lst = [] for cell in header: header_lst.append (cell.value) new_sheet.append (header_lst) flag = 1 for row in row_lst: data_lst = [] for cell in sheet [row]: data_lst.append (cell.value) New_sheet.append (data_lst) new_workbook.save (path +'/'+ 'new table .xlsx')

The code from the group friend Engineer:

Import tkinter as tk from tkinter import filedialog import os import pandas as pd import glob root = tk.Tk () root.withdraw () # Select folder location filelocation = os.path.normpath (filedialog.askdirectory (initialdir=os.getcwd () lst = [] # read all files under the folder (both xls and xlsx read) for i in glob.glob (filelocation + "\\" + "*. *"): if os.path.splitext (I) [1] in [".xls" ".xlsx"]: lst.append (pd.read_excel (I)) # saves the merged excel file writer = pd.ExcelWriter (filedialog.asksaveasfilename (title= "Save", initialdir=filelocation, defaultextension= "xlsx", filetypes= [("Excel Workbook", "* .xlsx") ("Excel 97-2003 workbook", "* .xls")) pd.concat (lst) .to_excel (writer, 'all', index=False) writer.save () print ('\ n% d files have been merged successfully!' % len (lst))

This is the end of the content of "what are the methods of batch implementation of multi-Excel and multi-Sheet merging with Python". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.