Python scripting

Guided Practice 5.2 - Using Python – Interacting with Notepad

Task 1 – Program Python to Notepad

First you need to open the Notepad program and create a simple file shown below.

Save the file to your PythonFiles folder on your desktop: Save As Desktop Pythonfiles Notepad.txt

Close the notepad file.

Now we’re going to write a Python program to add information to the and then close the file when the student enters a key

Type the following

Enter some more write lines to the file and verify that they go into the notepad.txt file. Take a screenshot of your notepad.txt file.

Deliverables for Task 1

· Screenshot of your notepad.txt file

Task 2 – Running the Python code from the command line

Now we’re going to switch to running the Python code you just wrote from the command line.

From your system go to search and type cmd. When the command shows up click to open the command window.

Switch to your desktop by typing cd Desktop and then cd PythonFiles

Type into your command window python notepad.py

Your notepad should open and add another set of lines to your notepad file.

Close the notepad and then click on the command window. End the program by typing any character.

Run the program several more times. Is there any limitation to running this program? Can you run it more than once?

Deliverables for Task 2

· Answer questions about running the program

· Screenshot of notepad output

Task 3 – Using Excel in Python

In order for Python to work with Excel files we will be using the openpyxl library. This will allow us to open, read, and write Excel files using Python.

First, we need to install openpyxl using pip in our system.

pip install openpyxl

Now let’s create an excel spreadsheet and store it into the C:\PythonFiles folder. Enter the following program

from openpyxl import Workbook

import datetime

book = Workbook()

sheet = book.active

sheet[‘A1’] = ‘<StudentID>’

sheet['A2'] = <first part of your studentID number>

sheet['A3'] = <second part of your studentID number>

sheet['A4'] = '=A2+A3'

now = datetime.datetime.now()

sheet['A5'] = now

book.save("C:\PythonFiles\FirstExcel.xlsx")

Now open the Excel spreadsheet and verify that the program worked correctly. Take a screenshot of your first Excel file.

Let go slightly deeper and give a range of data in your program. Enter the following program

from openpyxl import Workbook

book = Workbook()

sheet = book.active

data = [

['A', 100, 1.0],

['B', 200, 2.0],

['C', 300, 3.0],

['D', 400, 4.0],

['E', 500, 5.0],

['F', 600, 6.0],

['G', 700, 7.0],

['H', 800, 8.0],

]

for row in data:

sheet.append(row)

book.save("C:\PythonFiles\SecondBook.xlsx")

Now open the Excel spreadsheet and verify that the program worked correctly. Take a screenshot of your second Excel file.

Now let’s read in the second spreadsheet, change the data and write out to a new file. Enter the following code.

import openpyxl

book = openpyxl.load_workbook("C:\PythonFiles\SecondBook.xlsx")

sheet = book.active

rows = sheet.rows

book.close()

values = []

for row in rows:

for cell in row:

value =(cell.value)

if(cell.column) == 2:

value = value * 10

values.append(value)

book = openpyxl.Workbook()

sheet = book.active

index = 0

rw = 1

col = 1

for val in values:

sheet.cell(row = rw, column = col).value = val

index += 1

col += 1

if index == 3:

rw += 1

col = 1

index = 0

book.save("C:\PythonFiles\ThirdBook.xlsx")

Now open the Excel spreadsheet and verify that the program worked correctly. Take a screenshot of your third Excel file.

Deliverables for Task 3

· Screenshot of your first Excel spreadsheet

· Screenshot of your second Excel spreadsheet

· Screenshot of your third Excel spreadsheet

Task 4 – Calling Python from inside of Excel

First let’s recreate our simple PDF reading program which we had earlier in the class.

import pdfplumber

pdfToString = ""

pdf_path = "C:\PythonFiles\SmallPDF.pdf"

with pdfplumber.open(pdf_path) as pdf:

for page in pdf.pages:

string = page.extract_text()

pdfToString += string

f = open('C:\PythonFiles\Small.txt', 'w')

f.write(pdfToString)

f.close()

You can see we are reading a PDF file and then writing the results out to a txt file.

Excel has macros which allow it to run Python programs directly from inside of a spreadsheet.

Start Excel and select a blank workbook.

Click on the View ribbon command and then select Macros and View Macros

Type the Macro name RunPython and then click the create button. This will open the Macro editor where you will create your Excel macro.

In the macro editor type the following commands:

Sub RunPython()

RetVal = Shell("Python C:\PythonFiles\ReadPDF.py")

fnum = FreeFile

Open "C:\PythonFiles\Small.txt" For Input As fnum

textData = Input(LOF(fnum), fnum)

Close fnum

tArray = Split(textData, vbCrLf)

For rw = LBound(tArray) To UBound(tArray) - 1

ActiveSheet.Cells(rw + 1, 1) = tArray(rw)

Next rw

End Sub

Run the macro in Excel. The data from the PDF file should be placed into the Excel spreadsheet. Take a screenshot of your Excel page.

Deliverables for Task 4

· Screenshot of your PDF read into the Excel spreadsheet