7. python File Manipulation tool OPENPYXL Tools 2

Create a workbook

• Using OPENPYXL it is not necessary to create a new. xlsx in the system, all we need to do is introduce the workbook class and start calling it.

  >>> from openpyxl import Workbook>>> wb = Workbook()

• A workbook (workbook) is created with at least one new sheet (worksheet). You can get a running worksheet by calling openpyxl.workbook.Workbook.active () .

  >>> ws = wb.active

  Note: The function calls the index of the worksheet (_active_sheet_index), which is 0 by default. Unless you modify this value, you are using the function to work on the first sheet.
• Create a new table using Openpyxl.workbook.Workbook.create_sheet ()

  >>> ws1 = wb.create_sheet() #默认插在工作簿末尾# or>>> ws2 = wb.create_sheet(0) # 插入在工作簿的第一个位置

• The system is automatically named when the worksheet is created. They are named in sequence (Sheet, Sheet1, Sheet2, ...). You can modify the name of the worksheet by calling the following properties:

  ws.title = "New Title"

• The background color of the tab bar is white by default. You can change the font color of the tab bar by providing a RRGGBB color code:

  ws.sheet_properties.tabColor = "1072BA"

  
  Modify the color effect. png
• Once you get the name of the worksheet, you can get the worksheet by workbook key or Openpyxl.workbook.Workbook.get_sheet_by_name () method

  >>> ws3 = wb["New Title"]>>> ws4 = wb.get_sheet_by_name("New Title")>>> ws is ws3 is ws4True

• You can get all worksheets for a workbook by using the openpyxl.workbook.Workbook.get_sheet_names () method.

  >>> print(wb.get_sheet_names())[‘Sheet2‘, ‘New Title‘, ‘Sheet1‘]

  You can also cycle through all the worksheets

  >>> for sheet in wb:            print(sheet.title)

Manipulating data using one cell

Now that we know how to use a sheet, let's start by modifying the contents of cells in the worksheet

• Cells can be directly derived directly from their index

  >>> c = ws[‘A4‘]

  Through the above statement, the cell will be returned at A4, and if not present, a new one will be created in A4. The value of the cell can also be assigned directly

  >>> ws[‘A4‘] = 4

• The Openpyxl.worksheet.Worksheet.cell () method is also available to get the cell

  >>> c = ws.cell(‘A4‘)

• You can also get cells based on row and column values

  >>> d = ws.cell(row = 4, column = 2)

  Note: When a worksheet is created, it does not contain cells. Only when a cell is fetched is it created. In this way we do not create cells that we never use, thus reducing memory consumption.

Warning: Because of these features, you will be creating in memory if you traverse the cells instead of the one you want to use. such as the following:

>>> for i in range(1,101):              for j in range(1,101):                   ws.cell(row = i, column = j)

The code above will create 100*100 cells in memory.
Of course, there are ways to clean up these unwanted cells, which we will introduce later.

Use multiple cells

• Get multiple cells using a slice

  >>> cell_range = ws[‘A1‘:‘C2‘]

• Use the openpyxl.worksheet.Worksheet.iter_rows () method to get multiple cells

  >>> tuple(ws.iter_rows(‘A1:C2‘))((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),(<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))>>> for row in ws.iter_rows(‘A1:C2‘):            for cell in row:                  print cell<Cell Sheet1.A1><Cell Sheet1.B1><Cell Sheet1.C1><Cell Sheet1.A2><Cell Sheet1.B2><Cell Sheet1.C2>

• If you need to iterate over all the rows or columns in the file, you can use theopenpyxl.worksheet.Worksheet.rows ()

  >>> ws = wb.active>>> ws[‘C9‘] = ‘hello world‘>>> ws.rows((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

  or useopenpyxl.worksheet.Worksheet.columns ()Method

  >>> ws.columns((<Cell Sheet.A1>,<Cell Sheet.A2>,<Cell Sheet.A3>,<Cell Sheet.A4>,<Cell Sheet.A5>,<Cell Sheet.A6>,...<Cell Sheet.B7>,<Cell Sheet.B8>,<Cell Sheet.B9>),(<Cell Sheet.C1>,<Cell Sheet.C2>,<Cell Sheet.C3>,<Cell Sheet.C4>,<Cell Sheet.C5>,<Cell Sheet.C6>,<Cell Sheet.C7>,<Cell Sheet.C8>,<Cell Sheet.C9>))

Data storage

• Once we have a Openpyxl.cell.Cell, we can assign a value to the cell directly

  >>> c.value = ‘hello, world‘>>> print(c.value)‘hello, world‘>>> d.value = 3.14>>> print(d.value)3.14

• You can also use other types and formats in Python

  >>> wb = Workbook(guess_types=True)>>> c.value = ‘12%‘>>> print(c.value)0.12>>> import datetime>>> d.value = datetime.datetime.now()>>> print d.valuedatetime.datetime(2010, 9, 10, 22, 25, 18)>>> c.value = ‘31.50‘>>> print(c.value)31.5

Save to File

• The simplest and safest way to save a workbook is to use the openpyxl.workbook.Workbook openpyxl.workbook.Workbook.save () method

  >>> wb = Workbook()>>> wb.save(‘balances.xlsx‘)

  ！ Special WARNING: This action will overwrite all contents of the original file with what is now written without knowing the hint
• You can also as_template=true, save the file as a template

  >>> wb = load_workbook(‘document.xlsx‘)>>> wb.save(‘document_template.xltx‘, as_template=True)

• If As_template=false (default), save the file or template as a file

  >>> wb = load_workbook(‘document_template.xltx‘)>>> wb.save(‘document.xlsx‘, as_template=False)

  >>> wb = load_workbook(‘document.xlsx‘)>>> wb.save(‘new_document.xlsx‘, as_template=False)

  Warning: You should monitor the properties and file extensions of the data when you save the file to the file template, or vice versa; otherwise, you may not be able to open the workbook.
  For example, the following:

  >>> wb = load_workbook(‘document.xlsx‘)>>> # Need to save with the extension *.xlsx>>> wb.save(‘new_document.xlsm‘)>>> # MS Excel can‘t open the document>>>>>> # or>>>>>> # Need specify attribute keep_vba=True>>> wb = load_workbook(‘document.xlsm‘)>>> wb.save(‘new_document.xlsm‘)>>> # MS Excel can‘t open the document>>>>>> # or>>>>>> wb = load_workbook(‘document.xltm‘, keep_vba=True)>>> # If us need template document, then we need specify extension as *.xltm.>>> # If us need document, then we need specify attribute as_template=False.>>> wb.save(‘new_document.xlsm‘, as_template=True)>>> # MS Excel can‘t open the document

Import from File

• The same way you write files, you can introduce Openpyxl.load_workbook () to open a workbook that already exists

  >>> from openpyxl import load_workbook>>> wb2 = load_workbook(‘test.xlsx‘)>>> print wb2.get_sheet_names()[‘Sheet2‘, ‘New Title‘, ‘Sheet1‘]

Leelom
Links: http://www.jianshu.com/p/642456aa93e2
Source: Pinterest
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

7. python File Manipulation tool OPENPYXL Tools 2