Python script: Copy information from excel in one go

Completion date: March 15, 2019

This version 4 adds the try and except error.  Better coding, it should always work (hopefully).  The dimension of the application is set, and the position of the application (where to appear on the screen) is also set.

==================================

#! python 3.6.4 -- Modified on Mar 15 2019 (by Bonnie Chan)

import pyperclip
import openpyxl
import os
from tkinter import *
from tkinter import messagebox

#Get the workbook, worksheet
file = "AR.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']

#Customer name, salesman, Project name, Job no, PO no, Service amount
#column_list_letter = ['I', 'G', 'H', 'Q', 'P', 'N']
column_list_for_content = [9, 7, 8, 17, 16, 14]
column_list_for_subj = [[9, " - "], [8, " ("], [17, ")"]]

root = Tk()
root.title('Copycat v4.0')
root.geometry("300x150+400+400")

lbl = Label(root, text="Which row?")
lbl.pack()

user_input = Entry(root)
user_input.pack()

btn_copysubj = Button(root, text="Copy Subject", command=lambda: copy_subj())
btn_copysubj.pack()

btn_copycontent = Button(root, text="Copy Content", command=lambda: copy_content())
btn_copycontent.pack()

def copy_subj():
    try:
        user_input_row = int(user_input.get())

        content = ""

        for c in column_list_for_subj:
            # Get the specific row info and append to the content
            content = content + str(ws.cell(row=user_input_row, column=c[0]).value)

            # Add the special characters
            content = content + c[1]

        #copy the content to clipboard
        pyperclip.copy(content)
        messagebox.showinfo("Copycat", "Subject Copied.")
 
    except:
        messagebox.showwarning("Warning", "Your input was not a valid row number.")

def copy_content():
    try:
        user_input_row = int(user_input.get())

        content = ""

        for c in column_list_for_content:
            # Get the first row headers
            content = content + ws.cell(row=1, column=c).value + ": "

            # Get the specific row info and append to the content
            content = content + str(ws.cell(row=user_input_row, column=c).value)

            # Add the carriage return
            content = content + "\r\n"

        #copy the content to clipboard
        pyperclip.copy(content)
        messagebox.showinfo("Copycat", "Content Copied.")

    except:
        messagebox.showwarning("Warning", "Your input was not a valid row number.")

root.mainloop()

==========END==========

Completion date: March 14, 2019

This version 3 adds options to copy the email subject line or the email content information.

==================================

#! python 3.6.4 -- Modified on Mar 14 2019

import pyperclip
import openpyxl
import os
from tkinter import *
from tkinter import messagebox

#Get the workbook, worksheet
file = "AR.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']

#Customer name, salesman, Project name, Job no, PO no, Service amount
#column_list_letter = ['I', 'G', 'H', 'Q', 'P', 'N']
column_list_for_content = [9, 7, 8, 17, 16, 14]
column_list_for_subj = [[9, " - "], [8, " ("], [17, ")"]]

root = Tk()
root.title('Copycat')

lbl = Label(root, text="Which row?")
lbl.pack()

user_input = Entry(root)
user_input.pack()

btn_copysubj = Button(root, text="Copy Subject", command=lambda: copy_subj())
btn_copysubj.pack()

btn_copycontent = Button(root, text="Copy Content", command=lambda: copy_content())
btn_copycontent.pack()

def copy_subj():
    user_input_row = int(user_input.get())

    content = ""

    for c in column_list_for_subj:
        # Get the specific row info and append to the content
        content = content + str(ws.cell(row=user_input_row, column=c[0]).value)

        # Add the special characters
        content = content + c[1]

    #copy the content to clipboard
    pyperclip.copy(content)
    messagebox.showinfo("Copycat", "Subject Copied.")
 

def copy_content():
    user_input_row = int(user_input.get())

    content = ""

    for c in column_list_for_content:
        # Get the first row headers
        content = content + ws.cell(row=1, column=c).value + ": "

        # Get the specific row info and append to the content
        content = content + str(ws.cell(row=user_input_row, column=c).value)

        # Add the carriage return
        content = content + "\r\n"

    #copy the content to clipboard
    pyperclip.copy(content)
    messagebox.showinfo("Copycat", "Content Copied.")

root.mainloop()

==========END==========



Completion date: February 21, 2019

This version 2 puts the command line input to a GUI input.  The reason is to avoid running the program over and over again.  Moreover, I can utilize GUI on an executable file.

==================================

#! python 3.6.4 -- Modified on Feb 21 2019

import pyperclip
import openpyxl
import os
from tkinter import *
from tkinter import messagebox

#Get the workbook, worksheet
file = "AR.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']

#Customer name, salesman, Project name, Job no, PO no, Service amount
#column_list_letter = ['I', 'G', 'H', 'Q', 'P', 'N']
column_list = [9, 7, 8, 17, 16, 14]

root = Tk()
root.title('Copycat')

lbl = Label(root, text="Which row?")
lbl.pack()

user_input = Entry(root)
user_input.pack()

btn_ok = Button(root, text="OK", command=lambda: copy_content())
btn_ok.pack()

def copy_content():
    user_input_row = int(user_input.get())

    content = ""

    for c in column_list:
        # Get the first row headers
        content = content + ws.cell(row=1, column=c).value + ": "

        # Get the specific row info and append to the content
        content = content + str(ws.cell(row=user_input_row, column=c).value)

        # Add the carriage return
        content = content + "\r\n"

    #copy the content to clipboard
    pyperclip.copy(content)
    messagebox.showinfo("Copycat", "Copied.")

root.mainloop()

==========END==========



Completion date: February 17, 2019

My new job duty requires me to check with other parties enquiring the progress of project cases.   I process about 40 new cases every month and I often need to email them if I couldn't reach the project managers by phone.  For every new case I am emailing, I have to gather all the information from my excel file (Customer name, salesman, Project name, Job no, PO no, Service amount)...I need to find a fast copy solution.  Basically I am just copying the first header row and the data information in a specific row (specified by user).  Here is the first draft.

==================================
#! python 3.6.4 -- Created on Feb 17 2019

import pyperclip
import openpyxl
import os

#Get the workbook, worksheet
file = "AR.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']

#Customer name, salesman, Project name, Job no, PO no, Service amount
#column_list_letter = ['I', 'G', 'H', 'Q', 'P', 'N']
column_list = [9, 7, 8, 17, 16, 14]

user_input = input("Which row?")
user_input_row = int(user_input)

content = ""

for c in column_list:
    # Get the first row headers
    content = content + ws.cell(row=1, column=c).value + ": "

    # Get the specific row info and append to the content
    content = content + str(ws.cell(row=user_input_row, column=c).value)

    # Add the carriage return
    content = content + "\r\n"

#copy the content to clipboard
pyperclip.copy(content)
print("Copied to clipboard.")

==========END==========