Python Write Excel File

Python Write Excel File

The Python xlwt module is used to write an excel file and perform multiple operations on it. It can be used to write text, numbers, and formulas for multiple worksheets. It also supports a feature like formatting, images. Chart, page setup, auto filters, and many others.

Python does not come with the xlwt module; we need to install it using the pip from the command line.

pip install xlswriter

Writing excel file openpyxl module

It is defined as a package which is commonly suggested if you want to read and write .xlsx, xlsm, xltx, and xltm files. You can check it by running type(wb).

The Python provides the load_workbook () function which takes an argument and returns a workbook object, which denotes the file. It should be remembered that you are in the same directory where your spreadsheet is located. Otherwise, the Python interpreter will show an error.

We can easily use for loop with the help of the range() function to help print out the values of the rows that have values in column 2. If those specific cells are empty, we will get none.

import openpyxl
# Workbook() function of openpyxl is used to  
# create a new blank Workbook object
wb = openpyxl.Workbook()
# Get workbook active sheet
# from the active attribute
sheet = wb.active
 # Cell object is created by
# using sheet object's cell() method.
c1 = sheet.cell(row=1, column=1)
# writing values to cells
c1.value = "HIMANSHU"
c2 = sheet.cell(row=1, column=2)
c2.value = "DUBEY"
# Once have a Worksheet object, one can
# access a cell object by its name also.
# A2 is denoted column = 1 & row = 2.
c3 = sheet['A2']
c3.value = "RAHUL"
# B2 is denoted column = 2 & row = 2.
c4 = sheet['B2']
c4.value = "TRIVEDI"
c5 = sheet['A2']
c5.value = "RAHUL"
# B2 is denoted column = 2 & row = 2.
c6 = sheet['B2']
c6.value = "TRIVEDI"
# Anytime we modify the Workbook object
# the file will not be saved until you call
# the save() workbook method.
wb.save("C:\\Users\\DEVANSH SHARMA\\Desktop\\demo.xlsx") 

Output:

We will get the following file

 

Writing data to Excel file with xlwt

We can use the xlwt package, instead of the XlsxWriter package to create the spreadsheets that contain the data. It is an another package for writing data, formatting information, etc. and suitable for writing the data and format information to files with .xls extension. It can perform multiple operations on the spreadsheet.

It allows performing operations such as formatting, images, charts, page setup, auto filters, conditional formatting, and many others.

The Python library pandas provide excellent methods for reading all kinds of data from excel files. We can also import the results back to pandas.

Consider the following program

import xlwt
from xlwt import Workbook
# Workbook is created
wb = Workbook() 
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1')
 sheet1.write(1, 0, 'ANAND VIHAR')
 sheet1.write(2, 0, 'LAXMI NAGAR')
 sheet1.write(3, 0, 'SARITA VIHAR')
 sheet1.write(4, 0, 'LODHI GARDEN')
 sheet1.write(5, 0, 'SONIYA VIHAR')
 sheet1.write(6, 0, 'PITAMPURA')
 sheet1.write(7, 0, 'YAMUNA BANK')
 sheet1.write(8, 0, 'RAJIV CHAWK')
 wb.save('xlwt Address1.xls') 

Output:

We will get the excel file named Address1.xls

Writing data to Excel file with pyexcel

We can easily export arrays back to a spreadsheet by using the save_as() function and by passing the name of the destination file to the dest_file_name argument.

It allows us to specify the delimiter and add dest_delimiter argument. You can pass the symbol that you want to use as a delimiter in-between " ".

import pyexcel
# make sure you had pyexcel installed
a_list_of_dictionaries = [
     {
         "Name": 'Adam',
         "Age": 28
     },
     {
         "Name": 'Beatrice',
         "Age": 29
     },
     {
         "Name": 'Ceri',
         "Age": 30
     },
     {
         "Name": 'Dean',
         "Age": 26
    } ]
pyexcel.save_as(records=a_list_of_dictionaries, dest_file_name="Book1.xls")