python Excel 基本使用

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#    @version : 0.0.1
#    @File    : 01.py
#    @Time    : 2018/6/19 0019 下午 3:53
#    @Site    : 
#    @Software: PyCharm
#    @Author  : singebogo
#    @Author_email: singbogo@163.com
#    @description: 

import  openpyxl
import sys

if sys.getdefaultencoding() != 'utf-8':
    reload(sys)
    sys.setdefaultencoding('utf-8')

#openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.
# wb = openpyxl.load_workbook('baidu.xlsx')
# # print(type(wb))
#
# #从工作薄中获取工作表
# sheetname = wb.get_sheet_names()
# # print(sheetname)   #[u'Sheet1', u'Sheet2', u'Sheet3']
# sheet= wb.get_sheet_by_name(u'Sheet1')
# # print(sheet)  #<Worksheet "Sheet1">
# # print(type(sheet))      #<class 'openpyxl.worksheet.worksheet.Worksheet'>
#
# anothersheet = wb.get_active_sheet()
# # print(anothersheet)
# # print(type(anothersheet))
#
# #从表中取得单元格
#
# # print(sheet['A1'])
# # print(sheet['A1'].value)
# c = sheet['B1']
# print(c)
#
# print ('Row:  ' + str(c.row) + '   column:   ' + str(c.column) + "    is:     " + c.value)  #Row:  1   column:   B    is:     测试数据2
# print('Cell  ' + c.coordinate + ' is ' + c.value)  #Cell  B1 is 测试数据2
#
#
# b = sheet.cell(row = 1, column = 2)
# print(b.value)
#
# for i in range(1, 8, 1):
#     print i, sheet.cell(row=i, column=2).value
#


#新版本openpyxl已不再支持该方法get_highest_row get_highest_cloumn，引入max_row,max_column属性替代。
# wb = openpyxl.load_workbook('baidu.xlsx')
# sheet = wb.get_sheet_by_name('Sheet1')
# print(sheet.max_row)
# print (sheet.max_column)
#
# # 列字母于数字之间的转换
# from openpyxl.utils import get_column_letter, column_index_from_string
#
# print (get_column_letter(1))
# print (column_index_from_string('A'))


#从表中取得行和列
# print (tuple(sheet['A1':'D8']))
#
# for rowOfCellObjects in sheet['A1':'D8']:
#     for cellObj in rowOfCellObjects:
#         print (cellObj.coordinate) +", "+ str((cellObj.value))
#     print("-----end of Row------")
#
# for cellObj in sheet.columns[1]:
#     print(cellObj.value)

#工作薄 工作表  单元格
# 1、导入openpyxl
# 2、调用openpyxl.load_workbook()函数
# 3、取得WorkBook对象
# 4、调用get_active_sheet() 或者get_sheet_name()方法
# 5、取得WorkSheet对象
# 6、使用索引或者工作表的cell(）方法，带上row column关键参数
# 7、取得cell 对象
# 8、读取Cell对象的value属性

# ---------------------------------------------------------------------------------------------------------------------------------
# 写入excel文档
# import openpyxl
# wb1 = openpyxl.Workbook()
# print(wb1.get_sheet_names())
# sheet1 = wb1.get_active_sheet()
# print(sheet1.title)
# sheet1.title = 'spam Recon Eggs sheet'
# print(wb1.get_sheet_names())
#
# #创建工作表
# wb1.create_sheet()
# wb1.create_sheet(index=0, title='First Sheet')
# wb1.create_sheet(index=2, title='Middle Sheet')
#
# #删除工作表
# wb1.remove_sheet(wb1.get_sheet_by_name('First Sheet'))
# wb1.remove_sheet(wb1.get_sheet_by_name('Middle Sheet'))
#
# #将值写入单元格
# sheet2 = wb1.get_sheet_by_name('spam Recon Eggs sheet')
# sheet2['A1'] = 'hello world'
# print(sheet2['A1'].value)
#
# # 保存
# wb1.save('expamle.xlsx')

# ---------------------------------------------------------------------------------------------------------------------------------
#  更新
# 1、利用更新信息建立数据结构
import openpyxl
from openpyxl.styles import Font

# wb = openpyxl.load_workbook('baidu.xlsx')
# sheet = wb.get_sheet_by_name('Sheet1')
# #the prodece types and theirs update prices
# PRICE_UPDATES = {'Garlic':3.08,
#                  'Celery':1.199,
#                  'Lemon':1.34}
# #检查所有的行，更新不正确的数据
# for rowNum in range(2, sheet.max_row):
#     Name = sheet.cell(row = rowNum, column = 1).value
#     if Name in PRICE_UPDATES:
#         sheet.cell(row = rowNum, column = 2).value = PRICE_UPDATES[Name]

#设置单元格的字体风格
# from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
# font = Font(name='Calibri',
#          size=11,
#                 bold=False,
#               italic=False,
#                vertAlign=None,
#               underline='none',
#               strike=False,
#                color='FFFFF000')
# fill = PatternFill(fill_type=None, start_color='FFFFFFFF',end_color='FF000000')
# border = Border(left=Side(border_style=None,      color='FF000000'),
#                 right=Side(border_style=None,     color='FF000000'),
#                 top=Side(border_style=None,       color='FF000000'),
#                 bottom=Side(border_style=None,    color='FF000000'),
#                 diagonal=Side(border_style=None,  color='FF000000'),
#                 diagonal_direction=0,    outline=Side(border_style=None, color='FF000000'),
#                 vertical=Side(border_style=None,  color='FF000000'),
#                 horizontal=Side(border_style=None, color='FF000000'))
#
# alignment=Alignment(horizontal='general',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)
# number_format = 'General'
# protection = Protection(locked=True,hidden=False)

# col = sheet.column_dimensions['A']
# col.font1 = Font(bold=True)
# row = sheet.row_dimensions[1]
# row.font1 = Font(underline="single")

#设置字体
# fontObj1 = Font(name='Times New Roman', bold=True)
# sheet['A1'].font = fontObj1
# sheet['A1'] = 'Bold Times New Roman'
# fontObj2 = Font(size=24, italic=True)
# sheet['B3'].font = fontObj2
# sheet['B3'] = '24 pt Italic'

# 函数
# sheet['B5'] = '=SUM(B1:B4)'

#Adjusting Rows and Columns  调整行列
#Setting Row Height and Column Width
# sheet['A1'] = 'Tall row'
# sheet['B2'] = 'Wide column'
# sheet.row_dimensions[1].height = 70
# sheet.column_dimensions['B'].width = 20

#Merging and Unmerging Cells  合并单元格
# sheet.merge_cells('A1:D3')
# sheet['A1'] = 'Twelve cells merged together.'
# sheet.merge_cells('C5:D5')
# sheet['C5'] = 'Two merged cells.'

# To unmerge cells, call the unmerge_cells() sheet method. Enter this into the interactive shell.  拆分单元格
# sheet.unmerge_cells('A1:D3')
# sheet.unmerge_cells('C5:D5')

#Freeze Panes  冻结窗口  In OpenPyXL, each Worksheet object has a freeze_panes attribute that can be set to a Cell object or a string of a cell’s coordinates
# freeze_panes setting                                          Rows and columns frozen
# sheet.freeze_panes = 'A2'                                         Row 1
# sheet.freeze_panes = 'B1'                                         Column A
# sheet.freeze_panes = 'C1'                                         Columns A and B
# sheet.freeze_panes = 'C2'                                         Row 1 and columns A and B
# sheet.freeze_panes = 'A1' or sheet.freeze_panes = None            No frozen panes

# sheet.freeze_panes = 'A2'

# wb.save('updatebaidu.xlsx')

# Charts
# OpenPyXL supports creating bar, line, scatter, and pie charts using the data in a sheet’s cells. To make a chart, you need to do the following:
#    Opnepyxl 支持利用单元格的数据，来创建条形图，折线图，散点图，饼图  BarChart()  LineChart()  ScatterChart(), PieChart()
# 1、Create a Reference object from a rectangular selection of cells.
#          1、从一个矩形区域选择的单元格，创建一个Reference对象
# 2、Create a Series object by passing in the Reference object.
#          2、 通过传入Reference对象，创建一个Series对象
# 3、Create a Chart object.
#           3、创建一个Chart对象
# 4、Append the Series object to the Chart object
#            4、将Serise对象添加到Chart对象
# 5、Add the Chart object to the Worksheet object, optionally specifying which cell the top left corner of the chart should be positioned..
#              5、 可选地设置 Chart 对象的 drawing.top、 drawing.left、 drawing.width 和 drawing.height变量。
#              6、将 Chart 对象添加到 Worksheet 对象。

# The Reference object requires some explaining. Reference objects are created by calling the openpyxl.chart.Reference() function and passing three arguments:
#       Reference对象是通过openpyxl.chart.Reference()函数并传入3个参数创建
# 1、The Worksheet object containing your chart data.
#   包含图表数据的worksheet对象
# 2、A tuple of two integers, representing the top-left cell of the rectangular selection of cells containing your chart data: The first integer in the tuple is the row,
#    and the second is the column. Note that 1 is the first row, not 0.
#    两个整数的元组，代表选择区域的左上角的单元格，该区域包含图表数据，元组中的第一个整数是行，第二个整数是列，注意第一行是1， 不是0
# 3、A tuple of two integers, representing the bottom-right cell of the rectangular selection of cells containing your chart data:
#      The first integer in the tuple is the row, and the second is the column.
#   两个整数元组，代表矩形选择区域的右下角单元格，该区域包含图表数据，元组中的第一个整数是行，第二个整数是列

# NOtices:
# We’ve created a bar chart by calling openpyxl.chart.BarChart(). You can also create line charts, scatter charts, and pie charts by calling openpyxl.chart.LineChart(),
# openpyxl.chart.ScatterChart(), and openpyxl.chart.PieChart().
# Unfortunately, in the current version of OpenPyXL (2.3.3), the load_workbook() function does not load charts in Excel files. Even if the Excel file has charts, the loaded Workbook object will not include them.
# If you load a Workbook object and immediately save it to the same .xlsx filename, you will effectively remove the charts from it.

import  openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
for i in range(1,11):
    sheet['A' + str(i)] = i
refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=5)
seriesObj = openpyxl.chart.Series(refObj, title='First series')
chartObj = openpyxl.chart.BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)

# chartObj.drawing.top = 50
# chartObj.drawing.left = 100
# chartObj.drawing.width = 300
# chartObj.drawing.height = 200

sheet.add_chart(chartObj, 'C1')
wb.save('sampleChart.xlsx')