Simultaneously input specific data to a specific sheet in many excels

problem

Have you ever updated the date to the same place in various EXCEL? The problem I want to solve this time is: I want to input the same sheet, the same place, and the same data in various EXCEL.

environment

window 10 python 3.7.2 Excel (xlsx extension) * Since the library openpyxl used only wants to support xlsx refer to / comparison of libraries that handle Excel files with Python

Effect diagram

First excel state The python file is saved in the same directory as the Excel file. If not, you should respecify the path that python will read the file into. To put it the other way around, if you put this python file in the directory of the Excel file you want to update, you can execute it.

I want to put the date in cell A1 of EXCEL with a different name.

Data entry screen Enter the data you want to enter, the sheet name, and the cell location on the screen created with python TK.
Enter the data and press the execute button to write the data to each EXCEL. [point] When executing, be sure to close the Excel you want to update and execute it, otherwise you will not be able to edit in the opened Excel and an error will occur.

If the specified sheet name does not exist in the Excel file, it cannot be updated and the file name is displayed below.

It's finally time to get into the code, first the overall code, and then each part.

# -*- coding: utf-8 -*-
"""
Input multiple sheets at the same time
"""
import glob
from openpyxl import load_workbook
import tkinter as tk
import tkinter.messagebox as tkm

def batExcel(term, sheet, cell):
	#Specify the master sheet to fill in
	Sheet = sheet

	#Select multiple files
	files = glob.glob('*.xlsx')
	successFile = []
	failFile = []

	#Loop through all files
	for file in files:
		wb = load_workbook(file)
		#Read all sheets
		sheetnames = (wb.sheetnames)
		
		#Determine if a master Sheet table exists
		if Sheet in sheetnames:
			#Select sheet
			ws = wb[Sheet]
			#Select cell
			ws[cell] = term
			wb.save(file)
			wb.close()
			successFile.append(file)
		else:
			failFile.append(file)

	#Return the EXCEL name of successful or unsuccessful input
	return successFile,failFile


#Incidents that occur when clicking on Bodin
#Three arguments, text->Data you want to enter, master->Sheet you want to enter, cell->Cell you want to enter
def button_click(text, master, cell):
	#Clear the original data in the list each time you press the button
	ListBox1.delete(1, tk.END)
	ListBox2.delete(1, tk.END)
	files = batExcel(text, master, cell)
	for i in range(len(files[0])):
		ListBox1.insert(tk.END, files[0][i])
	for j in files[1]:
		ListBox2.insert(tk.END, j)

#Create GUI screen
root = tk.Tk()

#Set title
root.title("Accounting period")

#Set screen size
root.geometry('400x500')

#Set accounting period label
Static1 = tk.Label(text='Please enter the accounting period in YYYYMM format')
Static1.pack()

#Set accounting period input field
Entry1_term = tk.Entry()
Entry1_term.pack()

#Set master sheet label
Static2 = tk.Label(text='Please enter the master name to be updated')
Static2.pack()

#Set the master sheet input field
Entry2_sheet = tk.Entry()
Entry2_sheet.pack()

#Set cell location label on master sheet
Static3 = tk.Label(text='Please enter the cell location to update')
Static3.pack()

#Set cell location input field on master sheet
Entry3_cell = tk.Entry()
Entry3_cell.pack()

#Set up a run button
Button = tk.Button(text='Run button', command=lambda: button_click(Entry1_term.get(), Entry2_sheet.get(), Entry3_cell.get()))
Button.pack()

#Installation success list box installed
ListBox1 = tk.Listbox(width=50, height=10)
ListBox1.insert(tk.END,'Successful accounting period update EXCEL:')
ListBox1.pack()

#Installation failure list box installed
ListBox2 = tk.Listbox(width=50, height=10)
ListBox2.insert(tk.END,'Accounting period update failed EXCEL because the master Sheet does not exist:')
ListBox2.pack()

#Reacts to Active GUI screens, such as mount clicks, screen closes, etc.
root.mainloop()

Let's create an input screen for tkinter data refer to / copy and paste! Tkinter


import tkinter as tk
import tkinter.messagebox as tkm

#Create GUI screen
root = tk.Tk()

#Set title
root.title("Accounting period")

#Set screen size
root.geometry('400x500')

#Set accounting period label
Static1 = tk.Label(text='Please enter the accounting period in YYYYMM format')
Static1.pack()

#Set accounting period input field
Entry1_term = tk.Entry()
Entry1_term.pack()

#Set master sheet label
Static2 = tk.Label(text='Please enter the master name to be updated')
Static2.pack()

#Set the master sheet input field
Entry2_sheet = tk.Entry()
Entry2_sheet.pack()

#Set cell location label on master sheet
Static3 = tk.Label(text='Please enter the cell location to update')
Static3.pack()

#Set cell location input field on master sheet
Entry3_cell = tk.Entry()
Entry3_cell.pack()

#Set up a run button
Button = tk.Button(text='Run button', command=lambda: button_click(Entry1_term.get(), Entry2_sheet.get(), Entry3_cell.get()))
Button.pack()

#Installation success list box installed
ListBox1 = tk.Listbox(width=50, height=10)
ListBox1.insert(tk.END,'Successful accounting period update EXCEL:')
ListBox1.pack()

#Installation failure list box installed
ListBox2 = tk.Listbox(width=50, height=10)
ListBox2.insert(tk.END,'Accounting period update failed EXCEL because the master Sheet does not exist:')
ListBox2.pack()

#Reacts to Active GUI screens, such as mount clicks, screen closes, etc.
root.mainloop()

Set the case where the screen is completed and the execute button is bound


Button = tk.Button(text='Run button', command=lambda: button_click(Entry1_term.get(), Entry2_sheet.get(), Entry3_cell.get()))
#The code below has the same effect
Button = tk.Button(text='Run button', command= button_click(Entry1_term.get(), Entry2_sheet.get(), Entry3_cell.get()))

[point] However, the argument should be Entry1_term.get (), once saved in a variable and used again as an argument, the value cannot be passed well (probably because it is a scope problem, I am not familiar with it, so I'm sorry ...) , I get an error in the code below


#Set input field
Entry1 = tk.Entry()
#Get the entered data
Entry1_value = Entry1.get()
#Set up a run button
Button = tk.Button(text='Run button', command=lambda: button_click(Entry1_value)) #error

When you click a button, you can enter specific data in a specific sheet at the same time in many excel, and define a button_click case.


#Incidents that occur when clicking on Bodin
#Three arguments, text->Data you want to enter, master->Sheet you want to enter, cell->Cell you want to enter
def button_click(text, master, cell):
	#Clear the original data in the list each time you press the button
	ListBox1.delete(1, tk.END)
	ListBox2.delete(1, tk.END)
	files = batExcel(text, master, cell)
	for i in range(len(files[0])):
		ListBox1.insert(tk.END, files[0][i])
	for j in files[1]:
		ListBox2.insert(tk.END, j)

The [point] function must be defined before it can be used, that is, the button_click function is Button = tk.Button (text ='execute button', command = button_click (Entry1_term.get (), Entry2_sheet.get () ), Entry3_cell.get ())) Write above

Define a function to input specific data to a specific sheet at the same time in a large number of excel The flow is as follows. -To write to all files, read the order of all files-> sheet-> cell, and first get [Sheet] [A1] of all files.


import glob
from openpyxl import load_workbook

def batExcel(term, sheet, cell):
	#Specify the master sheet to fill in
	Sheet = sheet

	#Select multiple files
	files = glob.glob('*.xlsx')
	successFile = []
	failFile = []

	#Loop through all files
	for file in files:
		wb = load_workbook(file)
		#Read all sheets
		sheetnames = (wb.sheetnames)
		
		#Determine if a master Sheet table exists
		if Sheet in sheetnames:
			#Select sheet
			ws = wb[Sheet]
			#Select cell
			ws[cell] = term
			wb.save(file)
			wb.close()
			successFile.append(file)
		else:
			failFile.append(file)

	#Return the EXCEL name of successful or unsuccessful input
	return successFile,failFile