penpyxl 格式_penpyxl basic function demo code

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'] #

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()