Writing to Excel using Python
Python is an Object-Oriented high-level language. Python has an English-like syntax, which is very easy to read and write codes. Python is an interpreted language which means that it uses an interpreter instead of the compiler to run the code.
The interpreted language is processed at the run time thus takes less time to run. Python is also interactive, so we can directly run programs in the terminal itself.
Sometimes, we need to handle excel sheets while working in Python because excel sheets can contain loads of data that we want to load and work with. We can perform multiple operations on excel sheets using the xlwt module.
We can use the write method to write an excel sheet in Python. In addition to the write method, there are many other methods and logic to handle excel files.
XlsxWriter module is used to write the excel files in the .xslx extension. We can create multiple worksheets using this module and write texts, numbers, and formulas for them. This module also supports adding images, charts, etc.
We can install the xlsx writer using the command below:
Pip install xlsxwriter
Let us see an example of how we can write data in specific cells. But before writing the code, we should keep in mind that rows and columns in Xlsxwriter are zero-indexed, i.e., the first cell is A1(0,0), B1(0,1)…. and so on.
importxlsxwriter workbook = xlsxwriter.Workbook('Example.xlsx') worksheet = workbook.add_worksheet() row = 0 column = 0 content = ["Roahn", "Nikhil", "Roshan", "Mehak", "Mudit", "Muskaan", "Chandan"] for item in content : worksheet.write(row, column, item) column+=1 workbook.close()
Running this code will create an excel sheet named Example.csv in the same directory with the content:
Writing Data one by one in Excel.
To write data cell by cell in a worksheet, use the code below:
importxlsxwriter workbook = xlsxwriter.Workbook('Example2.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 123) #Int worksheet.write(1, 0, 123.45) #Float worksheet.write(2, 0, 'Rohan') #String worksheet.write(3, 0, None) # None worksheet.write(4, 0, True) # bool workbook.close()
After running this code, you will be able to see this content in an excel file named Example2.xslx:
123 123.45 Rohan TRUE
As you can see, we have written different data types in different cells in this example. Write() method automatically determines the data type of the data inserted and uses the functions for that datatype to enter the value in a cell.
Writing using xlwt module.
We can also use the xlwt module to write data In excel sheets. To install the xlwt module write this command in your console:
Pip install xlwt
After installing the module, let us see how to create and write an excel file using this module.
importxlwt #importing module fromxlwt import Workbook # creating workbook wb = Workbook() sheet1 = wb.add_sheet('Sheet 1') sheet1.write(1, 0, 'ISBT DEHRADUN') sheet1.write(2, 0, 'SHASTRADHARA') sheet1.write(3, 0, 'CLEMEN TOWN') sheet1.write(4, 0, 'RAJPUR ROAD') sheet1.write(5, 0, 'CLOCK TOWER') sheet1.write(0, 1, 'ISBT DEHRADUN') sheet1.write(0, 2, 'SHASTRADHARA') sheet1.write(0, 3, 'CLEMEN TOWN') sheet1.write(0, 4, 'RAJPUR ROAD') sheet1.write(0, 5, 'CLOCK TOWER') wb.save('Example3.xls')
In this code, we are using the write function to write data in an excel sheet. After running the code, an excel sheet will be created with the following content:
Muskaan Chandan Risk Chirag Ayush Rohan Nikhil Roshan Mudit Rahul
We can add some styles and format the cells of an excel sheet by adding multiple styles to it by using the xslx writer module. To do that, we just need to define our style and pass it to our write function as an argument.
importxlwt #importing module fromxlwt import Workbook wb = Workbook() worksheet = wb.add_sheet('Sheet 1') style =xlwt.easyxf('font: bold 1, color red;') row = 0 column = 0 content = ["Roahn", "Nikhil", "Roshan", "Mehak", "Mudit", "Muskaan", "Chandan"] for item in content : worksheet.write(row, column, item,style) column+=1 wb.save('Example4.xls')
After running the code, you will get the following output