With the help of openpyxl module, we can also write excel files in python.

This process is a bit similar to reading Excel spreadsheets in python.

Using python Excel writer, we will create an excel worksheet and write text, numbers and formulas in cells.

After modification, we will save the workbook.

We will also add and delete worksheets in excel workbooks, apply settings, fonts and styles, set the width and height of cells and ranges, and merge and Unmerge cells.

Python Excel create and save files:

import openpyxl as xl

mywb = xl.Workbook()

print(mywb.get_sheet_names())    # ['Sheet']

sheet = mywb.active

title = sheet.title              # 'Sheet'

sheet.title = 'MyNewTitle'

print(mywb.get_sheet_names())    # ['MyNewTitle']

mywb.save('NewExcelFile.xlsx')

Borrow existing excel, modify and generate new EXCEL

import openpyxl as xl

mywb = xl.load_workbook('bill.xlsx')

sheet = mywb.active

sheet.title = 'Working on Save as'

mywb.save('example_filetest.xlsx')

To create and delete worksheets in Excel:

import openpyxl as xl

mywb = xl.Workbook()
# mywb.get_sheet_names() -> ['Sheet']

mywb.create_sheet()    # <Worksheet "Sheet1">
# mywb.get_sheet_names() -> ['Sheet','Sheet1']

mywb.create_sheet(index=0, title='1st Sheet')
# mywb.get_sheet_names() -> ['1st Sheet','Sheet','Sheet1']

mywb.create_sheet(index=2, title='2nd Sheet')
# mywb.get_sheet_names() -> ['1st Sheet', 'Sheet', '2nd Sheet', 'Sheet1']

mywb.remove_sheet(mywb.get_sheet_by_name('1st Sheet'))
mywb.remove_sheet(mywb.get_sheet_by_name('Sheet1'))
# mywb.get_sheet_names() -> ['Sheet', '2nd Sheet']

mywb.save('savePath')
'''
Obviously, after removing two sheets from four sheets, there are only two sheets left. 
remove_sheet Method takes the worksheet object instead of the worksheet name,
Instead of creating an object for that particular worksheet and then deleting it,
We call get_sheet_by_name()And pass it to the worksheet name,
The value it returns is remove_sheet( ) method.
Finally, use save()Method to save the modified file.
In this case, delete the worksheet.
'''

Python excel writes values to cells: now we'll see how to write values to specific cells in an excel worksheet. Of course, we should know the address of the cell we want to write to.

>>> import openpyxl

>>> mywb = openpyxl.Workbook()

>>> mysheet = mywb.get_sheet_by_name('Sheet')

>>> mysheet['F6'] = 'Writing new Value!'

>>> mysheet['F6'].value    

'Writing new Value'

Apply different styles to the table to emphasize or emphasize certain rows or columns. It is very important to apply some uniform styles to excel worksheets, which can make the data clearer when reading.

Note, however, that you should be very careful with styling first, and then always save excel workbooks with different names.

First, we will import openpyxl, and then import the fonts and styles to be used in our code.

This is an example that creates a new workbook and sets cell F6 to a font with 32 point italics.

>>> import openpyxl

>>> from openpyxl.styles import Font, Style

>>> mywb = openpyxl.Workbook()

>>> mysheet = mywb.get_sheet_by_name('Sheet')

>>> italic32Font = Font(size=32, italic=True)

>>> sobj = Style(font=italic24Font)

>>> mysheet['F6'].style = sobj

>>> mysheet['F6'] = 'Applying Styles!'

>>> mywb.save('Appliedstyle.xlsx')

'''
stay openpyxl for excel In the worksheet,
Each cell has a style object,
The object is in the middle of the cell style Property.
We create a style object,
And assign it to style properties.
'''

The font object has four parameters:

1. Name: use string value and reference font name, such as "Arial".
2. Size: an integer value that refers to the size.
3. Bold: Boolean value. Bold font is True.
4. Italic: Boolean value; italic font is True.

In the following example, we will call the Font() method to create a Font object and store it in a variable,

The next step is to pass it as an argument to the Style() method.

We store that object in another variable,

And assign it to a specific cell object.

>>> import openpyxl

>>> from openpyxl.styles import Font, Style

>>> mywb = openpyxl.Workbook()

>>> mysheet = mywb.get_sheet_by_name('Sheet')

 

>>> firstFontObj = Font(name='Arial', bold=True)

>>> firstStyleObj = Style(font=firstFontObj)

>>> mysheet['F6'].style = firstStyleObj

>>> mysheet['F6'] = 'Bold Arial'

 

>>> secondFontObj = Font(size=32, italic=True)

>>> secondStyleObj = Style(font=secondFontObj)

>>> mysheet['D7'].style = secondStyleObj

>>> mysheet['D7'] = '32 pt Italic'

 

>>> mywb.save('ApplicationofStyles.xlsx')

Formula written in Python Excel:

# mysheet['F6'] = '=Sum(D7:D20)'

>>> import openpyxl

>>> mywb = openpyxl.Workbook()

>>> mysheet = mywb.active

>>> mysheet['F6'] = 500

>>> mysheet['F7'] = 800

>>> sheet['D3'] = '=SUM(F6:F7)'

>>> mywb.save('Applyingformula.xlsx')

Excel adjusts the rows and columns in the worksheet:

'''We can use openpyxl stay excel Set the row height and column width in the spreadsheet. We can also freeze rows or columns so that they are always displayed. We can also hide rows or columns.'''

>>> import openpyxl

>>> mywb = openpyxl.Workbook()

>>> mysheet = mywb.active

>>> mysheet['F6'] = 'Tall row'

>>> mysheet['D7'] = 'Wide column'

>>> mysheet.row_dimensions[3].height = 65

>>>mysheet.column_dimensions['F'].width = 25

>>>mywb.save('Heightandwidth.xlsx')

'''
excel The default row height in the spreadsheet is 12.75 Point. One point equals one/72 Inches.
You can set a value between 0 and 409.
The column width can be set to a value between 0 and 255.
It can be an integer or floating point value (decimal number).
If you set the width of the column to 0 or the height of the row to 0, it​​Will be hidden.
'''

Excel merge and cancel merge

'''merge_cells Method takes two cell addresses as its parameters.
The first cell is the upper left corner of the rectangular area to be merged, and the second cell is the lower right corner.
If you want to set the value of the merge range, use the address of the upper left cell of the entire merge range.
'''

>>> import openpyxl

>>>my wb = openpyxl.Workbook()

>>> mysheet = mywb.active

>>> mysheet.merge_cells('B2:D3')

>>> mysheet['A1'] = 'cells merged together.'

>>> mysheet.merge_cells('F6:F7')

>>> mysheet['G5'] = 'Two merged cells.'

>>> mywb.save('Mergingcells.xlsx')

If you want to cancel merging cells, use the following ideas.

>>> import openpyxl

>>> mywb = openpyxl.load_workbook('Mergingcells.xlsx')

>>> mysheet = mywb.active

>>> mysheet.unmerge_cells('B2:D3')

>>> mysheet.unmerge_cells('F6:F7')

>>> mywb.save('unmerged.xlsx')