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