python3.5 Operations Office Excel table (ii)

#第二篇:
# #openpyxl

Write a workbook:

from openpyxl import Workbookfrom openpyxl.compat import rangefrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = ‘testexcel.xlsx‘ws1 = wb.activews1.title = "range names"for row in range(1,40):ws1.append(range(600))ws2 = wb.create_sheet(title="Pi")ws2[‘F5‘] = 3.14ws3 = wb.create_sheet(title="Data")for row in range(10,20):    for col in range(27,54):      _ = ws3.cell(column=col,row=row,value="{0}".format(get_column_letter(col)))print(ws3[‘AA10‘].value)wb.save(filename = dest_filename)

Read an existing workbook:

from openpyxl import load_workbookwb = load_workbook(filename=‘testexcel.xlsx‘)sheet_ranges = wb[‘range names‘]print(sheet_ranges[‘D18‘].value)

In Load_workbook, you can use:
When a cell is read, Guess_types enables or disables (default) type inference.

Data_only controls whether a cell with a formula is a value that has a formula (the default) or the last time that Excel was reading a worksheet.

KEEP_VBA controls whether any Visual Basic elements are preserved (default). If they are kept, they are still non-editable.

Warning:
OPENPYXL currently does not read all possible items in the Excel file, so if you open and save the same name, the image and chart will be lost from the existing file.

# # #使用数字格式 (Using number formats):

import datetimefrom openpyxl import Workbookwb = Workbook()ws = wb.activews[‘A1‘] = datetime.datetime(2018,6,8)print(ws[‘A1‘].number_format)wb.guess_types = Truews[‘B1‘] = ‘3.14%‘wb.guess_types = Falseprint(ws[‘B1‘].value)print(ws[‘B1‘].number_format)

# # #使用公式 (Using formulae):

from openpyxl import Workbookwb = Workbook()ws = wb.activews["A1"] = "=SUM(1,1)"wb.save("formula.xlsx")

OPENPYXL never evaluates a formula, but you can check the name of the formula:

from openpyxl.utils import FORMULAEprint("HEX2DEC" in FORMULAE)

Attention:
If you want to use an unknown formula, these formulas must be prefixed with _XLFN.

# # #合并/Cancel merged cells
(Merge/unmerge cells)

When you merge cells, all the cells in the upper-left corner are removed from the worksheet.
To format the merged cell information, see style merge cells.

Merge cells:

from openpyxl.workbook import Workbookwb = Workbook()ws = wb.activews.merge_cells(‘A2:D2‘)#A2:D2合并单元格ws.merge_cells(start_row=2,start_column=1,end_row=4,end_column=4)wb.save(‘table\\merge.xlsx‘)

To cancel a merged cell:

from openpyxl.workbook import Workbookwb = Workbook()ws = wb.activews.merge_cells(‘A2:D2‘)#ws.unmerge_cells(‘A2:D2‘)ws.merge_cells(start_row=2,start_column=1,end_row=4,end_column=4)ws.unmerge_cells(start_row=2,start_column=1,end_row=4,end_column=4)wb.save(‘table\\merge.xlsx‘)

# # #插入图像 (Inserting an image)
From OPENPYXL import Workbook
From openpyxl.drawing.image import image

wb = Workbook()ws = wb.activews[‘A1‘] = ‘learning for me happy‘img = Image(‘images.PNG‘)ws.add_image(img,‘A1‘)wb.save(‘image.xlsx‘)

# # #Fold columns (outline) folding column (outline)

import openpyxlwb = openpyxl.Workbook()ws = wb.create_sheet()ws.column_dimensions.group(‘A‘,‘D‘,hidden=True)wb.save(‘table\\group.xlsx‘)

# # #格式化单元格
Using styles (working with styles):
Introduction (Introduction)

Styles are used to change the appearance of data when displayed on the screen. They are also used to determine the format of numbers.

Styles can be applied to the following areas:

The

font sets the font size, color, underline, and so on. Fill to set the pattern or color gradient border on the cell to set the border, cell alignment protection.
The following are the default values: Please refer to:
://openpyxl.readthedocs.io/en/2.5/styles.html#styling-merged-cells

  from openpyxl.styles import Patternfill,border,side,alignment,protectionfont = Font (name= ' Calibri ', size= 11,bold=false,italic=false,vertalign=none,underline= ' None ', strike=false,color= ' FF000000 ',) 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 (loc Ked=true,hidden=false)  

# # #单元格样式和命名样式 (Cell Styles and Named styles):

There are two types of styles: cell styles and named styles, also known as style templates.

# # # #Cell Styles
Cell styles are shared between objects, and once they are assigned, they cannot be changed. This prevents unwanted side effects, such as changing the style of a large number of cells, rather than just changing one style.

from openpyxl.styles import colorsfrom openpyxl.styles import Font,Colorfrom openpyxl import Workbookwb = Workbook()ws = wb.activea1 = ws[‘A1‘]d4 = ws[‘D4‘]ft = Font(color=colors.RED)a1 = ws[‘A1‘]d4 = ws[‘D4‘]ft = Font(color=colors.RED)a1.font = ftd4.font = fta1.font.italic = True  #is not alloweda1.font = Font(color=colors.RED,italic=True)

Copying styles (copy style):
Styles can also be copied.

from openpyxl.styles import Fontfrom copy import copyft1 = Font(name=‘Arial‘,size=14)ft2 = copy(ft1)ft2.name = "Tahoma"print(ft1.name)print(ft2.name)print(ft2.size)

# # # #Basic Font Colors (basic font color)
The color is usually the hexadecimal value of RGB or ARGB. The color module contains some handy constants.

from openpyxl.styles import Fontfrom openpyxl.styles.colors import REDfont = Font(color=RED)font = Font(color="FFBB00")

Traditional indexed colors as well as themes and colors are also supported.

from openpyxl.styles.colors import Colorc = Color(indexed=32)print(c)c = Color(theme=6,tint=0.5)print(c)

# # # #应用样式 (applying Styles):
The style is applied directly to the cell.

from openpyxl.workbook import Workbookfrom openpyxl.styles import Font,Fillwb = Workbook()ws = wb.activec = ws[‘A1‘]c.font = Font(size=12)wb.save(‘table\\style.xlsx‘)

Styles can also be applied to columns and rows, but note that this applies only to cells created after the file is closed (in Excel). If you want to apply a style to the entire row and column, you must apply the style to each cell. This is a limitation of the file format:

col = ws.column_dimensions[‘A‘]col.font = Font(bold=True)row = ws.row_dimensions[1]row.font = Font(underline="single")

# # # #样式合并单元格 (Styling merged cells):
Sometimes you want to format a series of cells as if they were a single object. Excel pretends that you can apply pseudo-types by merging cells (removing all cells except the upper-left cell) and then recreating the cells. Code reference:
Https://openpyxl.readthedocs.io/en/2.5/styles.html#styling-merged-cells

From openpyxl.styles import border,side,patternfill,font,gradientfill,alignmentfrom OPENPYXL import Workbookdef style _range (Ws,cell_range,border=border (), Fill=none,font=none,alignment=none): "" "App styles to a range of cells as if they were a single cell.:p Aram WS::p Aram Cell_range::p Aram border::p Aram fill::p Aram font::p Aram Alignment::return: "" "top = Bo Rder (top=border.top) left = border (left=border.left) right = Border (right=border.right) bottom = border (bottom= Border.bottom) First_cell = Ws[cell_range.split (":") [0]]if alignment:ws.merge_cells (Cell_range) first_cell.alignmen t = alignmentrows = ws[cell_range]if Font:first_cell.font = fontfor cell in rows[0]: Cell.border = Cell.border + to Pfor cell in rows[-1]: Cell.border = cell.border + bottomfor row in rows:l = row[0] r = row[-1] L.border = L. Border + Left R.border = R.border + Right if fill:for c in row:c.fill = FILLWB = Workbook () ws = Wb.activemy_cell = ws[' B2 ']my_cell.value = "my Cell "thin = Side (border_style=" thin ", color =" 000000 ") Double = Side (border_style=" Double ", color=" ff0000 ") border = Border (top=double,left=thin,right=thin,bottom=double) Fill = Patternfill ("Solid", fgcolor= "dddddd") Fill = GradientFill ( stop= ("000000", "FFFFFF")) Font = Font (b=true,color= "FF0000") Al = Alignment (horizontal= "center", vertical= "center") Style_range (WS, ' B2:f4 ', Border=border,fill=fill,font=font,alignment=al) wb.save ("Table\\styled.xlsx")

# # # #编辑页面设置 (Edit Page Setup):

from openpyxl.workbook  import Workbookwb = Workbook()ws = wb.activews.page_setup.orientation = ws.ORIENTATION_LANDSCAPEws.page_setup.paperSize = ws.PAPERSIZE_TABLOIDws.page_setup.fitToHeight = 0ws.page_setup.fitToWidth = 1wb.save("table\\styledone.xlsx")

# # # #命名样式 (Named Styles):
Named styles are mutable compared to cell styles. When you want to apply formatting to many different cells at once, they make sense. NB. Once you specify a named style for a cell, other changes to the style will not affect the cell.
Once a named style is registered in a workbook, it can simply be referenced by name.

# # # #创建一个命名样式 (Creating a Named Style):

from openpyxl.styles import NamedStyle,Font,Border,Sidehighlight = NamedStyle(name="highlight")highlight.font = Font(bold=True,size=20)bd = Side(style=‘thick‘,color="000000")highlight.border = Border(left=bd,top=bd,right=bd,bottom=bd)

Once a named style is created, it can be registered in the workbook.

wb.add_named_style(highlight)

However, when you assign them to cells for the first time, the named styles are also automatically registered:

ws[‘A1‘].style = highlight

Once registered, use the name assignment style:

ws[‘D5‘].style = ‘highlight‘

For example:
From OPENPYXL import Workbook
WB = Workbook ()
WS = Wb.active
Wb.add_named_style (Highlight)
ws[' A1 '].style = Highlight

ws[‘D5‘].style = ‘highlight‘wb.save("table\\styles.xlsx")

# # # #使用内置样式 (Using builtin styles):
The specification includes some built-in styles and can also be used. Unfortunately, the names of these styles are stored in their localized form. OPENPYXL will only recognize the English name, and it can only be written here. These are as follows:

Numeric format (number formats):

' Comma '
' Comma [0] '
' Currency '
' Currency [0] '
' Percent '

Informatization (Informative):

' Calculation '
' Total '
' Note '
' Warning Text '
' Explanatory Text '

Text style (text styles):

' Title '
' Headline 1 '
' Headline 2 '
' Headline 3 '
' Headline 4 '
' Hyperlink '
' Followed Hyperlink '
' Linked Cell '

Comparison (comparisons):

' Input '
' Output '
' Check Cell '
' Good '
' Bad '
' Neutral '

Emphasis (highlights):
' Accent1 '
'%-Accent1 '
'%-Accent1 '
'%-Accent1 '
' Accent2 '
'%-Accent2 '
'%-Accent2 '
'%-Accent2 '
' Accent3 '
'%-Accent3 '
'%-Accent3 '
'%-Accent3 '
' Accent4 '
'%-Accent4 '
'%-Accent4 '
'%-Accent4 '
' Accent5 '
'%-Accent5 '
'%-Accent5 '
'%-Accent5 '
' Accent6 '
'%-Accent6 '
'%-Accent6 '
'%-Accent6 '
' Pandas '

For more information about built-in styles:
Openpyxl.styles.builtins

If there is any mistake, please correct me.

Reference:
https://openpyxl.readthedocs.io/en/latest/

python3.5 Operations Office Excel table (ii)