This is the functionality we needed.
But how did we achieve this?Here is what happened.
First you read “sample.
xls” using the xlrd package and saved it into a variable rb in an object format.
Then you copied that object into a variable wb using the copy method of xlutils package.
This means that at that moment, both variables rb & wb contain the same object data.
Now, we access the first sheet of Excel using the index zero and store its object data into variable sheet , and now you can use all the functional capabilities of the xlwt package to finally save your Excel sheet with the same name as before, “sample.
xls”.
If you have carefully followed then you may have figured out that we really didn’t append data to an existing file.
Instead what we did was to copy the data of the existing file into memory using xlrd , then writing it to a new Excel file using the copy method of xlutils package, and then writing the new data into that new Excel file, while saving it with same name as before so that it can be overwritten.
I am afraid there isn’t a more elegant way to achieve this if you are working with older .
xls files.
The story doesn’t end here.
This was all about working with .
xls files but what if you wanted to do the same thing but this time you have got .
xlsx files to work with.
In such a case, you are in luck because you can work with openpyxl.
Now we will discuss use of the Python package openpyxl for creating spreadsheets in the .
xlsx format.
As I discussed above, that if you have lots of data to work with and many clients to report that data to, then knowing your way around both .
xls and .
xlsx (older and newer Microsoft Excel versions respectively) formats is a great skill that can come in handy lots of times.
The package openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.
openpyxl — A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.
4.
2 documentationEdit descriptionopenpyxl.
readthedocs.
ioYes, you read that right you can read, write and append data using only one single package.
You can install this package using the following command on terminal or command prompt (Prerequisite: pip must be installed on you system.
For install instructions of pip visit this link).
pip install openpyxlAfter the installation is complete, open the text editor to create a new file and give it any name you like; I am giving it the name excelScript.
py .
Inside the script write the following code:import openpyxldef main(): book = openpyxl.
Workbook() book.
create_sheet('Sample Sheet') book.
save('Sample.
xlsx')if __name__ == '__main__': main()The code above creates a blank Excel file with the name “Sample.
xlsx” , containing a single sheet named “Sheet 1”.
This was an example of .
xlsx file creation.
Run this script by executingpython excelScript.
pyon your command line.
When you open this .
xlsx file you will see that instead of one there are two sheets created in the file.
This happens because when you make an object of the Workbook() method of the openpyxl package, it creates a sheet called “Sheet” by default on calling that method.
If the sheet names don’t matter to your work then you can start using this sheet directly and you don’t need to create another sheet.
If your work requires only the use of one sheet and you can start writing data directly to this sheet.
Later, I will be showing you how to remove that default sheet Sheet from your .
xlsx file.
To write data to Excel change the code to the following:import openpyxldef main(): book = openpyxl.
Workbook() book.
create_sheet('Sample')# Acquire a sheet by its name sheet = book.
get_sheet_by_name('Sample')# Writing to sheet sheet.
cell(row=1, column=1).
value = 'sample'book.
save('Sample.
xlsx')if __name__ == '__main__': main()The code above will write “sample” on the 1st row and the 1st column.
Keep in mind that openpyxl reads row and column indices starting from one.
This way you can write into your Excel file and create as many sheets you like.
Run the script above usingpython excelScript.
pyin your command line.
You can give it styling also.
The following code shows you how to do so:import openpyxldef main(): book = openpyxl.
Workbook() book.
create_sheet('Sample')# Acquire a sheet by its name sheet = book.
get_sheet_by_name('Sample')# Writing to sheet sheet.
cell(row=1, column=1).
value = 'sample'# To set alignment of text inside cell and text wrapping sheet.
cell(row=1, column=1).
alignment = openpyxl.
styles.
Alignment(horizontal='center', vertical='center', wrap_text=True)# To make font bold or italic sheet.
cell(row=1, column=1).
font = openpyxl.
styles.
Font(bold=True, italic=True)book.
save('Sample.
xlsx')if __name__ == '__main__': main()This will write “sample” on the 1st row and 1st column.
It will be both horizontally and vertically center aligned.
For information on more methods provided by openpyxl you can read the documentation here.
You can also merge cells, just like we did using xlwt , in openpyxl.
The following code shows how to achieve merged cells using openpyxl:import openpyxldef main(): book = openpyxl.
Workbook() book.
create_sheet('Sample')# Acquire a sheet by its name sheet = book.
get_sheet_by_name('Sample')# Merging first 3 columns of 1st row sheet.
merge_cells('A1:C1')# Writing to sheet sheet.
cell(row=1, column=1).
value = 'sample'book.
save('Sample.
xlsx')if __name__ == '__main__': main()The code above demonstrates how to merge cells when you know the exact designations of the cells to merge.
I knew the designation of the 1st column of the 1st row which is A1 , and the designation of the 3rd column of the 1st row which is C1.
In this case, I merged them directly using the merge_cells method of openpyxl.
This was one of the two methods to merge cells using openpyxl.
Here is the demonstration of the 2nd method:import openpyxldef main(): book = openpyxl.
Workbook() book.
create_sheet('Sample')# Acquire a sheet by its name sheet = book.
get_sheet_by_name('Sample')# Merging first 3 columns of 1st 3 rows r1 = 1 r2 = 3 c1 = 1 c2 = 3 sheet.
merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2)# Writing to sheet sheet.
cell(row=1, column=1).
value = 'sample'book.
save('Sample.
xlsx')if __name__ == '__main__': main()This method comes in handy when data is written dynamically to the Excel file.
If you are not sure of the exact designations of the cells to merge then you can use this method.
This will merge the 1st three columns of 1st three rows and make it into a single cell.
When merging cells always keep in mind that you need to write the value into the first column of the merged cell otherwise the value will not reflect in the excel file.
You can still apply styling to a merged cell in the same way demonstrated.
Now coming to the part of deleting that extra sheet that is created by default on calling the Workbook() method of openpyxl.
This is how it can be done:import openpyxldef main(): book = openpyxl.
Workbook()book.
create_sheet('Sample')extraSheet = book.
get_sheet_by_name('Sheet') book.
remove_sheet(extraSheet)book.
save('Sample.
xlsx')if __name__ == '__main__': main()Here we acquired the sheet named “Sheet” in object form using get_sheet_by_name() method and stored in the extraSheet variable and then removed the sheet by calling the remove_sheet() method on the extraSheet variable.
This was all about creating a new .
xlsx file, writing to it, merging cells, and styling it.
Now, you might be wondering how to read and append data into .
xlsx file using openpyxl.
First of all let’s look at reading an .
xlsx file using openpyxl.
Just as writing, you can use openpyxl.
load_workbook() to open an existing workbook:import openpyxldef main(): book = openpyxl.
load_workbook('Sample.
xlsx') print book.
get_sheet_names() # ['Sheet2', 'New Title', 'Sheet1']# Get a sheet to read sheet = book.
get_sheet_by_name('Sheet1')# No of written Rows in sheet r = sheet.
max_row# No of written Columns in sheet c = sheet.
max_column# Reading each cell in excel for i in xrange(1, r+1): for j in xrange(1, c+1): print sheet.
cell(row=i, column=j).
valueif __name__ == '__main__': main()The code above will print all the written cells in the Excel file.
The for loop starts from 1 instead of 0 because openpyxl starts indexing from 1.
You can also read cells using the designations of the cells:# prints 1st row, 1st column directlyprint sheet['A1']# get a range of cellscells = sheet['A1:C1']for cell in cells[0]: print cell.
valueI selected cells[0] because sheet['A1:C1'] creates a tuple of tuple and all the cell objects are stored at oth index of the tuple hence, cells[0].
Finally, let’s see how to append data to .
xlsx file.
The following code demonstrates this process:import openpyxldef main(): book = openpyxl.
load_workbook('Sample.
xlsx') sheet = book.
get_sheet_by_name('Sample')sheet.
cell(row=5, column=1).
value = 'Appended Data'book.
save('Sample.
xlsx')if __name__ == '__main__': main()The process is very simple indeed.
I just loaded the existing .
xlsx file into an object and saved it into book variable by calling the load_workbook() method on “Sample.
xlsx” file.
I then grabbed the sheet “Sample” by calling the get_sheet_by_name() method on the book object.
Now, I can simply start appending data to the sheet in the same way when creating a new file.
All those methods that are available at the time of creation of file are available now.
Finally, when you are done writing data to the file don’t forget to save the file by calling the save() method.
Additionally, don’t forget to use a code beautifier when coding and try to follow PEP8 standards to make the code more readable.
Use PyLint before going live.
This will help you maintain a coding standard in your project.
The journey does not end here.
This was just the basics of how to read, write and append data to .
xls or .
xlsx files.
You should explore these packages more and make your algorithms according to your requirements depending on the form in which you want your data to be represented inside Excel.
Thanks for reading!.To read more such articles visit:Tarun Gupta – MediumRead writing from Tarun Gupta on Medium.
Trying to spread technical knowledge I have, along with some thoughts about…medium.
com.. More details