主要是关于对excel的操作以及库openpyxl的使用

文件保存此处待完善

文件保存

下载txt文件保存到硬盘

import requests
res = requests.get('http://www.gutenberg.org/cache/epub/1112/pg1112.txt')
res.raise_for_status()
tfile = open('pg_get.txt','wb')              # 使用 wb 写二进制模式打开文件,目的是保存该文本的Unicode编码
for chunk in res.iter_content(100000):       # iter_content()方法在循环的每次迭代中,返回一定字节内容。
    tfile.write(chunk)
tfile.close()

下载图片、视频等文件保存

import requests
image_url = 'http://httpbin.org/image/png'
file_path = 'test.png'
response = requests.get(image_url,timeout = 5)
with open(file_path,'wb') as f:
    f.write(response.content)
from IPython.display import Image,display
display(Image(filename = file_path))

下载图片时,我们还可以直接把响应内容存到PIL.Image中:
from PIL import Image
from io import BytesIO
from IPython.display import display

image = Image.open(BytesIO(response.content))
print(image.height,image.width)
image.save(file_path)
display(image)

csv文件保存(1)

import csv

file_path = 'test.csv'
with open(file_path,'w')as f:
    writer = csv.writer(f,delimiter = '\t',quotechar = '"',quoting = csv.QUOTE_ALL)
    writer.writerow(['C1','C2','C3'])   # 写一行,这一行同时也是标题
    data = [(1,2,3),(4,5,6)]
    writer.writerows(data) # 写多行

with open(file_path,'r')as f:
    reader = csv.reader(f,delimiter = '\t',quotechar = '"',quoting = csv.QUOTE_ALL)
    for row in reader:
        print('\t'.join(row))

csv文件保存(2)

csv.writer在写入文件时要将unicode字符串进行编码,因为Python地默认编码是ascii,

所以如果要写入的内容包含非ASCII字符时,就会出现UnicodeEncodeError。

第一种在调用writerow之前先将unicode字符串编码成UTF-8字符串,

第二种直接使用unicodecsv写入unicode字符串:

import unicodecsv

file_path = 'test.csv'
with open(file_path, 'wb')as f:
    writer = unicodecsv.writer(f, delimiter='\t', quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerow(['省份', '省会'])  # 写一行,标题
    data = [("河南", "郑州"), ("河北", "石家庄")]
    writer.writerows(data)  # 写多行

with open(file_path, 'rb')as f:
    reader = unicodecsv.reader(f, delimiter='\t', quotechar='"', quoting=csv.QUOTE_ALL)
    for row in reader:
        print('\t'.join(row))

openpyxl模块让python程序能够读取和修改Excel电子表格文件,生成需要的新点子表格。

# pip install openpyxl
## Excel 基本操作及读
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')     # 打开excel
print(wb.sheetnames)                            # 显示工作表列表
sheet = wb['Sheet1']                            # 按名字获取工作表
print(sheet.max_row)                            # 工作表最大行数
print(sheet.max_column)                         # 工作表最大列数
print(sheet.title)                              # 工作表名字
print(sheet)
type(sheet)
sheetb = wb.active                              # 获取当前活动工作表
print(sheetb)
print(sheet['A1'])                              # 获取Cell单元格A1
print(sheet['A1'].value)
c = sheet['B1']
print(c.value)
print('Row ' + str(c.row) + ',Column ' + c.column + ' is ' + c.value)
print('Cell ' + c.coordinate + ' is ' + c.value) 
# cell()方法取单元格
sheet.cell(row=1,column=2)                      # <Cell 'Sheet1'.B1>
print(sheet.cell(row=1,column=2).value )
for i in range(1,8,2):
    print(i,sheet.cell(row=i,column=2).value)
# 列字母与数字转换
import openpyxl
from openpyxl.utils  import get_column_letter,column_index_from_string
print(get_column_letter(1))                     # 列A
print(get_column_letter(27))                    # 列AA
print(get_column_letter(703))                   # 列AAA
print(column_index_from_string('A'))            # 列1
print(column_index_from_string('AHP'))          # 列900 
# 取行和列
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']
get_column_letter(sheet.max_column)
tuple(sheet['A1':'C3'])                         # sheet['A1':'C3']选取从A1到C3的矩形区域的Cell对象
for rowOfCellObjects in sheet['A1':'C3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate,cellObj.value )
    print('--- End of Row ---')
print('--- 下面仅显示第二列---')
for row in range(1,sheet.max_row ):             # 循环显示一列
    print(sheet['B'+str(row)].value)  
    # print(sheet.cell(row=row,column=2).value)  # 与上一句效果等同


## example1:人口普查结果计算
# Tabulates population and number of census tracts for each country.
import openpyxl,pprint
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}
# Fill in countyData with each county's population and tracts.
#   {'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
#        'Aleutians West': {'pop': 5561, 'tracts': 2},
print('Reading rows...')
for row in range(2,sheet.max_row + 1):
    # Each row in the spreadsheet has data for one census tract.
    state = sheet['B'+str(row)].value
    county = sheet['C'+str(row)].value
    pop = sheet['D'+str(row)].value
    # Make sure the key of this state exists.
    countyData.setdefault(state,{})
    # Make sure the key of this county in this state exists.
    countyData[state].setdefault(county,{'tracts':0,'pop':0})
    # Each row represents one census tract,so increment by one.
    countyData[state][county]['tracts'] += 1
    # Increase the county pop by the pop in this census tract.
    countyData[state][county]['pop'] += int(pop)
# Open a new text file and write the contents of countyData to it.
print('Writing results...')
resultFile = open('census2010.py','w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')
# Read the resultdata...
import census2010
print(census2010.allData['AK']['Anchorage']['pop'])


## 写入 Excel
import openpyxl
# 修改并拷贝表
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.title = 'Spam_1'
wb.save('example_copy.xlsx')
# 创建工作表
wb = openpyxl.Workbook()
print(wb.sheetnames)
sheet = wb.active
sheet.title = 'Spam_2'
print(wb.sheetnames)
wb.create_sheet()                               # 创建sheet
print(wb.sheetnames)
wb.create_sheet(index=0,title='First sheet')    # 按参数创建sheet
wb.create_sheet(index=2,title='Middle sheet')
print(wb.sheetnames)
del wb['Middle sheet']                          # 删除sheet
sheet = wb['Sheet']
sheet['A1'] = 'Hello'                           # 修改 Cell 值
print(sheet['A1'].value)


## example2: 修改销售清单中某几个产品对应的价格
import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']
# The produce types and their updated prices
PRICE_UPDATES = {'Garlic':3.07,
                 'Celery':1.19,
                 'Lemon':1.27}
# Loop through the rows and update the prices.
for rowNum in range(2,sheet.max_row):
    produceName = sheet.cell(row=rowNum,column=1).value
    if produceName in PRICE_UPDATES:
        sheet.cell(row=rowNum,column=2).value = PRICE_UPDATES[produceName]
wb.save('produceSales_U.xlsx')


## 设置字体、公式、行高、列宽、合并拆分单元格
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb['Sheet']
italic24Font = Font(size=24,italic=True)        # 定义字体
sheet['A1'].font = italic24Font                 # 设置单元格字体
sheet['A1'] = 'HelloFont'
sheet['B3'].font = Font(name='Times New Roman',bold=True)  # 设置单元格字体
sheet['B3'] = 'Bold Times New Roman'
sheet['F1'] = 200
sheet['F2'] = 300
sheet['F3'] = '=sum(F1:F2)'                     # 设置公式
sheet['D4'] = 'Tall row'
sheet['E5'] = 'Wide column'
sheet.row_dimensions[4].height = 70             # 设置行高
sheet.column_dimensions['E'].width = 20         # 设置列宽
sheet.merge_cells('A6:D7')                      # 合并单元格
sheet['A6'] = 'Merge 8 cells together'
sheet.merge_cells('A8:C9')                      # 合并单元格
sheet['A8'] = 'Merge 6 cells'
sheet.unmerge_cells('A8:C9')                    # 拆分单元格
wb.save('styled.xlsx')


# 加载文件时显示公式或公式结果
import openpyxl
wb = openpyxl.load_workbook('styled.xlsx')
sheet = wb['Sheet']
print(sheet['F3'].value)
wb1 = openpyxl.load_workbook('styled.xlsx',data_only=True)
sheet1 = wb1['Sheet']
print(sheet1['F3'].value)


## 冻结窗口
# 单元格上边所有行和左边所有列冻结,但单元格所在行列不冻结
import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active
# sheet.freeze_panes = 'B1'                       # 冻结列A
# sheet.freeze_panes = 'C1'                       # 冻结列A和B
# sheet.freeze_panes = 'C2'                       # 冻结行1和列A和列B
# sheet.freeze_panes = 'A1'                       # 无冻结
# sheet.freeze_panes = None                       # 无冻结
sheet.freeze_panes = 'A2'                         # 设置第一行为冻结
wb.save('freezeExample.xlsx')


## 创建图表
import openpyxl
from openpyxl.chart import BarChart,Series,Reference
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1,11):
    sheet['A'+str(i)] = i
refObj = Reference(sheet,min_col=1,min_row=1,max_row=10,max_col=1)
chartObj = BarChart()
chartObj.type='col'                              # col为纵向,bar为横向
chartObj.title='Bar chart'
chartObj.y_axis.title='Test_y'
chartObj.x_axis.title='Test_x'
chartObj.add_data(refObj,titles_from_data=True)
sheet.add_chart(chartObj)
wb.save('sampleChart.xlsx')





不要害怕告别,要相信,四季更替,花开花落,都是命中注定。