Openpyxl basic function demo code
demo code:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
summary
description
- openpyxl basic功能练习
- https://openpyxl.readthedocs.io/en/default/usage.html
:REQUIRES:
:TODO:
:AUTHOR: Pengtao.Fan
:ORGANIZATION:
:CONTACT: fanpengtao@gmail.com
:SINCE: Sun Aug 7 21:21:38 2016
:VERSION: 0.1
"""
#===============================================================================
# PROGRAM METADATA
#===============================================================================
__author__ = ''
__contact__ = ''
__copyright__ = ''
__license__ = ''
__date__ = 'Sun Aug 7 21:21:38 2016'
__version__ = '0.1'
#===============================================================================
# IMPORT STATEMENTS
#===============================================================================
#from visual import * # IMPORTS NumPy.*, SciPy.*, and Visual objects (sphere, box, etc.)
#import matplotlib.pyplot as plt # plt.plot(x,y) plt.show()
#from pylab import * # IMPORTS NumPy.*, SciPy.*, and matplotlib.*
#import os # os.walk(basedir) FOR GETTING DIR STRUCTURE
#import pickle # pickle.load(fromfile) pickle.dump(data, tofile)
#from tkFileDialog import askopenfilename, askopenfile
#from collections import namedtuple
#from ctypes import *
#import glob
#import random
#import cv2
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.utils import FORMULAE #to check the name of a formula
import pprint
import datetime
#===============================================================================
# METHODS
#===============================================================================
class OpenpyxlStudy():
def __init_(self):
pass
def getxl(self):
wb = load_workbook(filename = 'test.xlsx')
pprint.pprint(wb.get_sheet_names())
sheet_ranges = wb['ov']
#f14 = sheet_ranges['F14'] #<Cell ov.F14>
f14 = sheet_ranges['F14'].value #DEE
#f14 = sheet_ranges.cell('F14').value
#f14 = sheet_ranges.cell(raw = 15, column = 14)
print(f14)
def setxl(self):
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active #设置下次打开时的活动窗格
ws1.title = "range names"
for row in range(1, 40):
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
#这种cell赋值方法比较新
#注意get_column_letter
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
def Fommats(self):
'''Using number formats'''
#dest_filename = 'empty_book.xlsx'
#wb = load_workbook(filename = dest_filename)
wb = Workbook(guess_types=True)
ws = wb.active
# set data using a Python datetime
ws['A1'] = datetime.datetime.now()
print(ws['A1'].number_format)
# set percentage using a string followed by the percent sign
ws['B1'] = '3.14%'
print(ws['B1'].value)
print(ws['B1'].number_format)
wb.save("fommats.xlsx")
def Formulae(self):
'''excel自带数学公式功能'''
wb = Workbook()
ws = wb.active
# 添加excel自带的sum公式
ws['A1'] = '=SUM(1, 2)'
wb.save('formula.xlsx')
if "HEX2DEC" in FORMULAE:
print("HEX2DEC in FORMULAE")
else:
print("HEX2DEC not in FORMULAE")
def MergeCells(self):
'''合并单元格'''
'''Merge / Unmerge cells'''
wb = Workbook()
ws = wb.active #设置下次打开时的活动窗格
ws = wb.create_sheet(title="MergeCells")
ws.merge_cells('A1:B2')
#or
#ws.merge_cells(start_row=0,start_column=0,end_row=8,end_column=8)
ws['A1'] = 'You should see three logos below'
# create an image
#img = Image('image.jpg')
# add to worksheet and anchor next to cells
#ws.add_image(img, 'A1')
wb.save('MergeCells.xlsx')
pass
def UnMergeCells(self):
'''拆分单元格'''
'''Merge / Unmerge cells'''
pass
def InsertImage(self):
'''inset image at A1 cell'''
wb = Workbook()
ws = wb.active #设置下次打开时的活动窗格
ws.title = 'InsertImageSheet'
ws.merge_cells('A1:D7')
ws['A1'] = 'You should see three logos below'
# create an image
img = Image('image.jpg')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
#==============================================================================
# '''虽然A1占用了A3 格子,但不能如下写。'''
# ws2 = wb.create_sheet('SecondImageSheet')
# ws2.merge_cells('A1:D7')
# ws2['A3'] = 'You should see three logos below'
#
# # create an image
# img = Image('image.jpg')
# # add to worksheet and anchor next to cells
# ws.add_image(img, 'A3')
#==============================================================================
wb.save('InsertImage.xlsx')
def Foldcolumns(self):
'''隐藏某些列'''
wb = Workbook(True)
ws = wb.create_sheet()
ws.column_dimensions.group('A','D', hidden=True)
wb.save('Foldcolumns.xlsx')
#===============================================================================
# MAIN METHOD AND TESTING AREA
#===============================================================================
def main():
"""Description of main()"""
test = OpenpyxlStudy()
#test.getxl()
#test.setxl()
#test.Fommats()
#test.Formulae()
#test.MergeCells()
#test.InsertImage()
test.Foldcolumns()
if __name__ == '__main__':
main()