Chapter 12 – Working with Excel Spreadsheets
非计算机出身，最近一直在看《Automate the Boring Stuff with Python》，为了能够很好的理解和学习，一直坚持在做文后的练习题。但一个人学习实在是太累太难，准备将自己的做的练习题记录下来，希望得到高手的指导，来拓展个人解决问题的思路、提升代码质量。
先从第十二章开始吧， 前面练习题会陆续给补上。
传送门：
Chapter 13 – Working with PDF and Word Documents
Chapter 14 – Working with CSV Files and JSON Data
Chapter 15 – Keeping Time, Scheduling Tasks, and Launching Programs
Chapter 17 – Manipulating Images
Chapter 18 – Controlling the Keyboard and Mouse with GUI Automation
Multiplication Table Maker
Create a program multiplicationTable.py that takes a number N from the command line and creates an N×N multiplication table in an Excel spreadsheet. For example, when the program is run like this:
>>>py multiplicationTable.py 6
... it should create a spreadsheet that looks like this.
#! python3
# multiplicationTable.py
import openpyxl
from openpyxl.styles import Font
import sys
wb = openpyxl.Workbook()
sheet = wb.active
fontObj = Font(name='Times New Roman',bold=True)
n = int(sys.argv[1])
for i in range(1, n+1):
sheet['A' + str(i+1)] = i
sheet.cell(row=1,column=i+1).value = i
sheet['A'+ str(i+1)].font = fontObj
sheet.cell(row=1,column=i+1).font = fontObj
for j in range(1, n+1):
for k in range(1, n+1):
sheet.cell(row = j+1 ,column= k+1).value = j*k
wb.save('multilTable6.xlsx')
print('Done')
Blank Row Inserter
Create a program blankRowInserter.py that takes two integers and a filename string as command line arguments. Let’s call the first integer N and the second integer M. Starting at row N, the program should insert M blank rows into the spreadsheet. For example, when the program is run like this:
>>> python blankRowInserter.py 3 2 myProduce.xlsx
... the “before” and “after” spreadsheets should look like the following pic:
Before (left) and after (right) the two blank rows are inserted at row 3
#! python3
# blankRowInserter.py
import openpyxl,sys
#from openpyxl.utils import column_index_from_string
if len(sys.argv)!=4:
print('Fail!')
sys.exit()
N = int(sys.argv[1])
M = int(sys.argv[2])
file = sys.argv[3]
wb = openpyxl.load_workbook(file)
wbNew = openpyxl.Workbook()
sheet1 = wb.active
sheet2 = wbNew['Sheet']
for rowObj in sheet1.rows:
for cellObj in rowObj:
#print(cellObj.column)
if cellObj.row<N:
sheet2.cell(row=cellObj.row,column=int(cellObj.column)).value = cellObj.value
else:
sheet2.cell(row=cellObj.row+M,column=int(cellObj.column)).value = cellObj.value
wbNew.save('exampleInseter.xlsx')
print('Done!')
Text Files to Spreadsheet
Write a program to read in the contents of several text files (you can make the text files yourself) and insert those contents into a spreadsheet, with one line of text per row. The lines of the first text file will be in the cells of column A, the lines of the second text file will be in the cells of column B, and so on.
Use the readlines() File object method to return a list of strings, one string per line in the file. For the first file, output the first line to column 1, row 1. The second line should be written to column 1, row 2, and so on. The next file that is read with readlines() will be written to column 2, the next file to column 3, and so on.
这个练习题，我稍微做了变化，没有按要求将一行文本写入一个单元格。我是将每一个文字分别写入单元格，遇到回车就另起一行。 在练习中发现，中文和英文文章是有区别的，因为中文没有空格分隔，而英文文章是由空格分隔每个单词的。
#! python3
# txtInsertExl.py -Read the contents of text files and insert those content into spreadsheet,
# with one line of text per row.
import openpyxl,os
txtFile = open('txtInsertExl_Demo.txt')
txtContentList = txtFile.readlines()
wb = openpyxl.Workbook()
sheet = wb['Sheet']
for i in range(len(txtContentList)):
for j in range(len(txtContentList[i].split())):
rw = i+1
col = j+1
sheet.cell(row=rw,column=col).value = txtContentList[i].split()[j]
wb.save('txtInsertSheet.xlsx')
txtFile.close()
print('Done')
#-------------------我是分隔符-------------------------#
#! python3
# txtInsertExl.py -Read the contents of text files and insert those content into spreadsheet,
# with one line of text per row.
import openpyxl,os
txtFile = open('txtInsertExl_Demo.txt')
txtContentStr = txtFile.read() #
wb = openpyxl.Workbook()
sheet = wb['Sheet']
for i in range(len(txtContentStr.split('\n'))):
for j in range(len(txtContentStr.split('\n')[i])):
rw = i+1
col = j+1
sheet.cell(row=rw,column=col).value = txtContentStr.split('\n')[i][j]
wb.save('txtInsertSheet.xlsx')
txtFile.close()
print('Done')
Spreadsheet to Text Files
Write a program that performs the tasks of the previous program in reverse order: The program should open a spreadsheet and write the cells of column A into one text file, the cells of column B into another text file, and so on.
#! python3
# sheetToTxt.py
import os,openpyxl
# load the spreadsheet
wb = openpyxl.load_workbook('myProduces.xlsx')
sheet = wb.active # take up the active sheet.
# create the list of the filenames
fileNames = ['1.txt','2.txt','3.txt','4.txt']
for i in range(len(fileNames)):
txtFileObj = open(fileNames[i],'w')
for j in range(sheet.max_row):
c = i+1 # column addressed by number starting at A (1)
r = j+1 # row addressed by numbers starting at 1
txtFileObj.write(str(sheet.cell(row=r,column=c).value))
txtFileObj.close()
print('Done')
Spreadsheet Cell Inverter
Write a program to invert the row and column of the cells in the spreadsheet. For example, the value at row 5, column 3 will be at row 3, column 5 (and vice versa). This should be done for all cells in the spreadsheet. For example, the “before” and “after” spreadsheets would look something like this:
而关于列转行的问题，一直没有好的解决思路，写的代码只能解决一个行数和列数完全相等的极端情况，郁闷之极啊！直接贴代码，寻求高手指点，小弟在此拜谢！
#! python3
# columnIvertRow.py
import openpyxl
wb = openpyxl.load_workbook('myProduces.xlsx')
sheet = wb['Sheet1']
wbNew = openpyxl.Workbook()
sheet2 = wbNew['Sheet']
for rowObj in sheet.rows:
for cellObj in rowObj:
sheet2.cell(row=cellObj.row,column=cellObj.column).value = sheet.cell(row=cellObj.column,column=cellObj.row).value
wbNew.save('myProducesInvert.xlsx')
print('Done')
以上是第12章的练习题，实在是因为非科班出身，自学的太累，还请各位不吝指教。多谢！
