Skip to content

Commit 59fa594

Browse files
chenguohuichenguohui
committed
Chapter 12 finished
1 parent 2926f69 commit 59fa594

28 files changed

+3390
-0
lines changed

‎12-excel/README.md

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
# openpyxl模块：处理Excel表格
2+
`pip install openpyxl`
3+
<pre>
4+
import openpyxl
5+
wb = openpyxl.load_workbook('example.xlsx')
6+
sheet = wb.get_sheet_by_name('Sheet1')
7+
</pre>
8+
9+
# 从电子表格文件中读取单元格涉及的所有函数、方法和数据类型。
10+
1. 导入 openpyxl 模块。
11+
2. 调用 openpyxl.load_workbook()函数。
12+
3. 取得 Workbook 对象。
13+
4. 调用 get_active_sheet()或 get_sheet_by_name()工作簿方法。
14+
5. 取得 Worksheet 对象。
15+
6. 使用索引或工作表的 cell()方法，带上 row 和 column 关键字参数。
16+
7. 取得 Cell 对象。
17+
8. 读取 Cell 对象的 value 属性。

‎12-excel/blankRowInserter.py

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
import openpyxl
2+
import sys
3+
from openpyxl.utils.cell import get_column_letter
4+
5+
if len(sys.argv) < 4:
6+
print('usage: py blankRowInserter.py [afterTheRow] [blankRowsNumber] [xlsx]')
7+
exit()
8+
9+
afterTheRow = int(sys.argv[1])
10+
blankRowsNumber = int(sys.argv[2])
11+
xlsxFile = sys.argv[3]
12+
13+
# Open xlsx and read it
14+
wbRead = openpyxl.load_workbook(xlsxFile)
15+
sheetRead = wbRead.get_active_sheet()
16+
17+
# Create sheet to be write
18+
wbWrite = openpyxl.Workbook()
19+
sheetWrite = wbWrite.get_active_sheet()
20+
21+
# copy first afterTheRow rows
22+
for i in range(1, afterTheRow+1):
23+
for j in range(1, sheetRead.max_column+1):
24+
coord = get_column_letter(j)+str(i)
25+
sheetWrite[coord] = sheetRead[coord].value
26+
# copy latter rows
27+
28+
for i in range(afterTheRow+1, sheetRead.max_row+1):
29+
for j in range(1, sheetRead.max_column+1):
30+
coordWrite = get_column_letter(j)+str(i+blankRowsNumber)
31+
coordRead = get_column_letter(j)+str(i)
32+
sheetWrite[coordWrite] = sheetRead[coordRead].value
33+
34+
# save sheet to file
35+
wbWrite.save(xlsxFile+'.blank.xlsx')
36+
37+

‎12-excel/census2010.py

Lines changed: 3143 additions & 0 deletions
Large diffs are not rendered by default.

‎12-excel/censuspopdata.xlsx

2.14 MB
Binary file not shown.

‎12-excel/example.xlsx

9.67 KB
Binary file not shown.

‎12-excel/example_copy.xlsx

4.57 KB
Binary file not shown.

‎12-excel/excelToText.py

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
import openpyxl
2+
import sys
3+
from openpyxl.utils.cell import get_column_letter
4+
5+
# arguments validation
6+
textList = []
7+
excelName = ''
8+
9+
# create workbook
10+
wb = openpyxl.Workbook()
11+
sheet = wb.get_active_sheet()
12+
13+
# open each file and save to workbook
14+
for i in range(1, sheet.max_column+1):
15+
with open(str(i)+'.txt', 'w') as f:
16+
for j in range(1, sheet.max_row+1):
17+
coord = get_column_letter(fileSeq) + str(rowSeq)
18+
f.write(sheet[coord].value)
19+
f.close()

‎12-excel/formula.py

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
import openpyxl
2+
3+
wb = openpyxl.Workbook()
4+
sheet = wb.active
5+
sheet['A1'] = 200
6+
sheet['A2'] = 300
7+
sheet['A3'] = '=SUM(A1:A2)'
8+
wb.save('writeFormula.xlsx')
9+

‎12-excel/multiplicationTable-10.xlsx

5.13 KB
Binary file not shown.

‎12-excel/multiplicationTable-20.xlsx

6.49 KB
Binary file not shown.

‎12-excel/multiplicationTable-20.xlsx.blank.xlsx

6.48 KB
Binary file not shown.

‎12-excel/multiplicationTable-20.xlsx.blank.xlsx.trans.xlsx

6.49 KB
Binary file not shown.

‎12-excel/multiplicationTable-6.xlsx

4.82 KB
Binary file not shown.

‎12-excel/multiplicationTable.py

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
import openpyxl
2+
import sys
3+
from openpyxl.utils.cell import get_column_letter
4+
5+
if len(sys.argv) < 2:
6+
print('usage: py multiplicationTable.py [NUM]')
7+
exit()
8+
num = int(sys.argv[1])
9+
10+
wb = openpyxl.Workbook()
11+
sheet = wb.get_active_sheet()
12+
13+
for i in range(1, num+1):
14+
# fill the first row
15+
sheet[get_column_letter(i+1)+'1'] = i
16+
# fill the first column
17+
sheet['A'+str(i+1)] = i
18+
19+
# fill the table
20+
for i in range(1, num+1):
21+
for j in range(1, num+1):
22+
sheet[get_column_letter(j+1)+str(i+1)] = i*j
23+
24+
wb.save('multiplicationTable-'+str(num)+'.xlsx')

‎12-excel/produceSales.xlsx

857 KB
Binary file not shown.

‎12-excel/readCensusExcel.py

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
#! python3
2+
# readCensusExcel.py - Tabulates population and number of census tracts for
3+
# each county.
4+
5+
import openpyxl, pprint
6+
print('Opening workbook...')
7+
wb = openpyxl.load_workbook('censuspopdata.xlsx')
8+
sheet = wb.get_sheet_by_name('Population by Census Tract')
9+
countyData = {}
10+
# Fill in countyData with each county's population and tracts.
11+
print('Reading rows...')
12+
for row in range(2, sheet.max_row + 1):
13+
# Each row in the spreadsheet has data for one census tract.
14+
state = sheet['B' + str(row)].value
15+
county = sheet['C' + str(row)].value
16+
pop = sheet['D' + str(row)].value
17+
18+
# Make sure the key for this state exists.
19+
countyData.setdefault(state, {})
20+
# Make sure the key for this county in this state exists.
21+
countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})
22+
23+
# Each row represents one census tract, so increment by one.
24+
countyData[state][county]['tracts'] += 1
25+
# Increase the county pop by the pop in this census tract.
26+
countyData[state][county]['pop'] += int(pop)
27+
28+
# Open a new text file and write the contents of countyData to it.
29+
print('Writing results...')
30+
resultFile = open('census2010.py', 'w')
31+
resultFile.write('allData = ' + pprint.pformat(countyData))
32+
resultFile.close()
33+
print('Done.')

‎12-excel/readExcel.py

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
import openpyxl
2+
3+
wb = openpyxl.load_workbook('example.xlsx')
4+
sheet = wb.get_sheet_by_name('Sheet1')
5+
print(sheet.cell(row=1, column=2).value)
6+
7+
for i in range(1, 8, 2):
8+
print(i, sheet.cell(row=i, column=2).value)
9+
10+
print(sheet.max_row, sheet.max_column)
11+
12+
for rowOfCellObjs in sheet['A1':'C3']:
13+
for cellObj in rowOfCellObjs:
14+
print(cellObj.coordinate, cellObj.value)
15+
print('--- END OF ROW ---')

‎12-excel/sampleChart.xlsx

6.72 KB
Binary file not shown.

‎12-excel/styled.xlsx

4.65 KB
Binary file not shown.

‎12-excel/stylingUnit.py

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
from openpyxl.styles import colors
2+
from openpyxl.styles import Font, Color
3+
from openpyxl import Workbook
4+
wb = Workbook()
5+
ws = wb.active
6+
sheet = wb.active
7+
ft = Font(size=24, italic=True)
8+
sheet['A1'].font = ft
9+
sheet['A1'] = '24 Italic'
10+
wb.save('styled.xlsx')

‎12-excel/textToExcel.py

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
import openpyxl
2+
import sys
3+
from openpyxl.utils.cell import get_column_letter
4+
5+
# arguments validation
6+
textList = []
7+
excelName = ''
8+
9+
# create workbook
10+
wb = openpyxl.Workbook()
11+
sheet = wb.get_active_sheet()
12+
13+
# open each file and save to workbook
14+
fileSeq = 0
15+
for file in textList:
16+
fileSeq += 1
17+
with open(file) as f:
18+
rowSeq = 1
19+
for line in f.readlines():
20+
coord = get_column_letter(fileSeq) + str(rowSeq)
21+
sheet[coord] = line
22+
23+
# save workbook
24+
wb.save(excelName)

‎12-excel/trans.xlsx

10.3 KB
Binary file not shown.

‎12-excel/trans.xlsx.trans.xlsx

4.79 KB
Binary file not shown.

‎12-excel/transposition.py

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
import openpyxl
2+
import sys
3+
from openpyxl.utils.cell import get_column_letter
4+
5+
if len(sys.argv) < 2:
6+
print('usage: py transposition.py [xlsx]')
7+
exit()
8+
9+
xlsxFile = sys.argv[1]
10+
11+
# Open xlsx and read it
12+
wbRead = openpyxl.load_workbook(xlsxFile)
13+
sheetRead = wbRead.get_active_sheet()
14+
15+
# Create sheet to be write
16+
wbWrite = openpyxl.Workbook()
17+
sheetWrite = wbWrite.get_active_sheet()
18+
19+
# Transposition
20+
for i in range(1, sheetRead.max_row+1):
21+
for j in range(1, sheetRead.max_column+1):
22+
coordRead = get_column_letter(j)+str(i)
23+
coordWrite = get_column_letter(i)+str(j)
24+
sheetWrite[coordWrite] = sheetRead[coordRead].value
25+
26+
# save sheet to file
27+
wbWrite.save(xlsxFile+'.trans.xlsx')
28+
29+

‎12-excel/updateProduce.py

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
#! python3
2+
# updateProduce.py - Corrects costs in produce sales spreadsheet.
3+
4+
import openpyxl
5+
6+
wb = openpyxl.load_workbook('produceSales.xlsx')
7+
sheet = wb.get_sheet_by_name('Sheet')
8+
9+
# The produce types and their updated prices
10+
PRICE_UPDATES = {'Garlic': 3.07,
11+
'Celery': 1.19,
12+
'Lemon': 1.27}
13+
14+
# Loop through the rows and update the prices.
15+
for rowNum in range(2, sheet.max_row): # skip the first row
16+
produceName = sheet.cell(row=rowNum, column=1).value
17+
if produceName in PRICE_UPDATES:
18+
sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]
19+
20+
wb.save('updatedProduceSales.xlsx')
21+

‎12-excel/updatedProduceSales.xlsx

601 KB
Binary file not shown.

‎12-excel/writeExcel.py

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
import openpyxl
2+
3+
wb = openpyxl.Workbook()
4+
sheet = wb.get_active_sheet()
5+
6+
sheet.title = 'Spam Spam Spam'
7+
print(wb.get_sheet_names())
8+
9+
wb.save('example_copy.xlsx')

‎12-excel/writeFormula.xlsx

4.64 KB
Binary file not shown.

0 commit comments

Comments
 (0)