Let us say I have an excel file called test.xlsx, which is a workbook with three sheets, where sheet1 is called hello1, sheet2 two is called hello2, and sheet3 is called bye.
Now, I’d like to read the file, and then re-write the same file but ONLY change the values in (column B, row 11) of the sheet named hello2, and (column D, row 14) of sheet named bye. The values I want to give there are ‘test’ (a string), and 135, respectively (i.e., write test in sheet hello2 and 14 in sheet bye).
You may be wondering why I am asking such odd question but basically I am looking to gain some of the following skills/knowledge:
- To be able to read a workbook and then a specific sheet of an excel file using python
- To be able to write to an excel sheet at a given position using python
Note: for reference, I can use any version of python in redhat server, the excel file was generated with my mac, saved as xlsx format using excel for mac 2011. version 14.0.1, and then I copied the excel file to redhat server.
Advertisement
Answer
I’d suggest using the xlwt, xlrd, and xlutils modules (which you can find here: python-excel.org).
With xlrd, xlwt and xlutils, you use xlrd to read a workbook, and then you use xlutils to make a writeable copy. Since what you’re doing isn’t dependent on the values already in the cells, you won’t need to use xlrd at all except for opening the book.
A quick mockup of your code would look like:
import xlrd, xlwt
from xlutils.copy import copy
read_book = xlrd.open_workbook("Path/To/Doc", formatting_info=True) #Make Readable Copy
write_book = copy(read_book) #Make Writeable Copy
write_sheet1 = write_book.get_sheet(1) #Get sheet 1 in writeable copy
write_sheet1.write(1, 11, 'test') #Write 'test' to cell (B, 11)
write_sheet2 = write_book.get_sheet(2) #Get sheet 2 in writeable copy
write_sheet2.write(3, 14, '135') #Write '135' to cell (D, 14)
write_book.save("New/File/Path") #Save the newly written copy. Enter the same as the old path to write over