5

I have a .xlsx file in which multiple worksheets are there (with some content). I want to write some data into specific sheets say sheet1 and sheet5. Right now I am doing it using xlrd, xlwt, and xlutils copy() function. But is there any way to do it by opening the file in append mode and adding the data and save it (Like as we do it for the text/CSV files)?

Here is my code:

rb = open_workbook("C:\text.xlsx",formatting_info='True')
wb = copy(rb)
Sheet1 = wb.get_sheet(8)
Sheet2 = wb.get_sheet(7)
Sheet1.write(0,8,'Obtained_Value')
Sheet2.write(0,8,'Obtained_Value')
value1 = [1,2,3,4]  
value2 = [5,6,7,8]
for i in range(len(value1)):
 Sheet1.write(i+1,8,value1[i])
for j in range(len(value2)):
 Sheet2.write(j+1,8,value2[j])
wb.save("C:\text.xlsx")
Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
user3487900
  • 111
  • 1
  • 1
  • 6

2 Answers2

9

You can do it using the openpyxl module or using the xlwings module

  1. Using openpyxl

    from openpyxl import workbook #pip install openpyxl
from openpyxl import load_workbook

wb = load_workbook("C:\text.xlsx")
sheets = wb.sheetnames
Sheet1 = wb[sheets[8]]
Sheet2 = wb[sheets[7]]
#Then update as you want it
Sheet1 .cell(row = 2, column = 4).value = 5 #This will change the cell(2,4) to 4
wb.save("HERE PUT THE NEW EXCEL PATH")

    the text.xlsx file will be used as a template, all the values from text.xlsx file together with the updated values will be saved in the new file

  2. Using xlwings

     import xlwings 
 wb = xlwings.Book("C:\text.xlsx")  
 Sheet1 = wb.sheets[8]
 Sheet2 = wb.sheets[7]
 #Then update as you want it
 Sheet1.range(2, 4).value = 4 #This will change the cell(2,4) to 4
 wb.save()
 wb.close()

    Here the file will be updated in the text.xlsx file but if you want to have a copy of the file you can use the code below

    shutil.copy("C:\text.xlsx", "C:\newFile.xlsx") #copies text.xslx file to newFile.xslx

    and use 

    wb = xlwings.Book("C:\newFile.xlsx") instead of wb = xlwings.Book("C:\text.xlsx")

    As a user of both modules I prefer the second one over the first one.

BeLeTh
  • 131
  • 1
  • 5
1

For manipulating existing excel files you should use openpyxl. Other common libraries like the ones you are using dont support manipulating existing excel files. A workaround is to

  • save your output file as a different name - text_temp.xlsx
  • delete your original file - text.xlsx
  • rename your output file - text_temp.xlsx to text.xlsx
Vikas Ojha
  • 6,742
  • 6
  • 22
  • 35
  • Thanks for the reply. I got to know that openpyxl supports only .xls files not .xlsx files. And with the workaround you suggested, you mean to say create a new file with the content of existing file and the new content whichever I wanted to add and save it and then rename it to original file? – user3487900 Jun 09 '15 at 06:41
  • Yes. And openpyxl supports xlsx flawlessly. – Vikas Ojha Jun 09 '15 at 06:43
  • Thank you. I will look into openpyxl library. – user3487900 Jun 09 '15 at 06:46
  • Hi. Does openpyxl support .xls file? I am getting an error message as below: InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format. – user3487900 Jun 10 '15 at 04:09
  • Yeah, that's right, openpyxl does not support .xls files. – Vikas Ojha Jun 10 '15 at 06:55