Excel with Python

In this article, you will learn how to connect python with excel and how to manipulate excel sheets using python. 

Workbook:  A Workbook also known as a Spreadsheet is the main file that we can deal with while working with Excel. a workbook is a collection of multiple worksheets or can be a single worksheet.


Worksheet: A Worksheet or simply Sheet divide the different portion of the Workbook. It store the data and contains 
1,048,576 rows and 16,384 columns(A to XFD).


Columns: It is a vertical group of cells with an alphabetic identity from A to XFD.


Rows: It is a horizontal set of cells with numeric identity from 1 to 1048576.


Worksheet Tab: It shows all the Worksheet present in the Workbook. By default, they are named Sheet1, Sheet2, or Sheet3.


 
Active cell: It is a cell in which we are currently working.
 

Let's Go->
1) To Work in Excel with python, Firstly we need to install a third-party module called openpyxl.     
    pip install openpyxl    

2) After Installing, Import this module into your Python File.
        import openpyxl        

Creating an Excel File:
To create an Excel file Firstly we need to create a Workbook. As we discussed Earlier a Workbook or SpreadSheet is the main file where we work. It is Another word of your Excel File.
After Creating Workbook it is time to Save it.
                                                   
  # --------------------Creating Excel File------------------

  # Creating a Workbook
  my_workbook = openpyxl.Workbook()

  # Saving our Excel file
  my_workbook.save(filename='our_excel_file.xlsx')
                                           
Here, we save our file as  'our_excel_file.'.  Note that the 'xlsx' is the extension for the Excel file. After giving the file name we need to add this extension.

Opening an Excel File:
To open an Excel File we need to use the load_workbook(filename) function from openpyxl. This function load or open Workbook or Excel file and save its data into the Given variable so that we can access its attributes and methods by using the variable.

# --------------------Open Excel File------------------

wb = openpyxl.load_workbook('our_excel_file.xlsx')
Here, wb is our workbook variable, and we load an Excel file by calling the load_workbook() function 

Reading and Writing an Excel Spreadsheet:

Afterload our workbook we need to read data from our worksheet.
# get the active sheet from wb
 active = wb.active

 # creating a new sheet in a workbook
 new_sheet = wb.create_sheet('my_sheet', index=1)

 # Lets gave Active Sheet a title
 active.title = "active1"

 # Change the Tab colour of our Active Sheet
 active.sheet_properties.tabColor = "1072BA"

 # Get all sheets names from our Workbook(wb)
 all_sheets = wb.sheetnames

wb.active gives you the name of the active sheet default is "Sheet 1". 
wb.create_sheet(sheet name, index) creates a new blank SpreadSheet with the name that you give in the sheet name parameter and the index indicates at which place you want to insert your sheet.

we also specify our sheet title(sheet name) by saying sheet name, title = new_name in our case the sheet is an active sheet.

we can change the sheet default properties by calling the sheet.sheet_properties again in our case the sheet is an active sheet. The tab color specifies the color of the Worksheet Tab.

wb. sheetnames give you the name of all sheets present in the workbook(like Sheet1, Sheet2, Sheet3,  etc).
 
Assign a value to a specific cell:
You can assign value to a specific cell in three ways:
→ using cell name as a key
→ using .value method
→ using rows and column notation

 # To assign value using cell name as a key
 active['A2'] = "1"

 # To assign value using .value method
 cell = active['A3']
 cell.value = "2"

 # To assign value to a cell using rows and column notation
 active.cell(row=4, column=1, value="3")
In the first way, you use cell A2 and give it a value of 1.

In a second way first, you use A2 cell and then assign it a value by using .value.
In a third way, you point to a particular cell by indicating its columns and rows by the .cell() function and assign it a value.

Getting a Range of Cells:
You get a range of cells in three ways:
→ by slicing
→ by iterating rows
→ by iterating cols

 # Get a range of cells by slicing
  range_of_cells = active["A2:A4"]
  convert_to_list = list(range_of_cells)
  print(convert_to_list)

 # Get a range of cells by iterating rows
  rows_of_cells = active.iter_rows(min_row=1, max_row=5, 
                                 min_col=1, max_col=1)
  print(rows_of_cells.__next__())
  print(rows_of_cells.__next__())
  print(rows_of_cells.__next__())

 # Get a range of cells by iterating cols
  columns_of_cells = active.iter_cols(min_row=1, max_row=1, 
                                                                    min_col=2, max_col=4)

  print(columns_of_cells.__next__())
  print(columns_of_cells.__next__())
  print(columns_of_cells.__next__())

In the first way, you will get a range of cells by simply slicing like we did in normal python programs. It will return a tuple so we convert it using the built-in list(iterator) method so we can change it as our need.

In the second way as its name suggest it will return a generator object of rows. In this you point maximum and minimum columns as your need we take it 1 because here we want an iterator of rows of single columns, also we gave minimum rows is 1 and maximum rows are 5 so that it gives a range of rows between 1 to 5. And at last, we iterate them by using __next__().

In a third way, we do the same as we did in a second way, but Instead of Rows, we select a Range of Columns between 1 to 4.

Set Headers:
Let's set Headers For Our Columns.
 # give cell headers
 headers = ["Name", "Class", "Address"]
 for columns, header  in  list(zip(active.iter_cols(min_row=1, max_row=1, 
                                           min_col=2, max_col=4), headers)):
    for column in columns:
        column.value = header

 wb.save(filename='our_excel_file.xlsx')

Here We set Name, Class, Address as Headers for our Columns.
We set minimum columns to 2 so that the column value starts with the B1 cell and also set the maximum column to 4 so that we would access B1, C1, D1 cells.
After this, we set their values to the elements of our list.
Here, you notice the zip built-in function. It simply Zipping or combine our two iterators in  the way:    [('B1', 'Name'), ('C1', 'Class'), ('D1', 'Address')]
and, at last, we save it to save our changes.

Adding or Getting Comments:
# -----------------------Adding Comments--------------------

 message = "This is a Simple Message"
 author = "Hjack"
 my_comment = openpyxl.comments.Comment(message, author)
 active['A3'].comment = my_comment

 my_workbook.save('our_excel_file.xlsx')

 # ----------------------Getting Comments--------------------

 print(active['A1'].comment)

 my_workbook.save('our_excel_file.xlsx')

To add comments in our excel sheet we need Comment() class from openpyxl. comment. then, we set the message and author for our comment. After this, we set our comment to A3 cell, and at last, again we save it to save our changes.

 
Creating Multiplication Table:
 # ---------------Creating Multiplication Table--------------

 import openpyxl

 my_workbook = openpyxl.load_workbook('our_excel_file.xlsx')
 active = my_workbook.active

 for rows in range(2, 12):
    for columns in range(2, 12):
        active.cell(row=rows, column=columns, 
                    value=((rows-1)*(columns-1)))

 my_workbook.save('our_excel_file.xlsx')

Here we create a Nested loop first for rows and second for columns.
we start the loop from 2 because we insert elements from the B column and 2nd row.
Then we use .cell() to select the specific cell and assign it a value of multiplication of rows and columns.
Here we use (rows-1) and (columns-1) to start multiplication from 1, not 2.
and, at last, we save it to save our changes.
 
Output:


Post a Comment

0 Comments