Python Forum
Write data into existing Excel (xlsx) file with multiple sheets
Thread Rating:
  • 1 Vote(s) - 1 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Write data into existing Excel (xlsx) file with multiple sheets
#1
I have an excel (xlsx) file with 11 worksheets and I need to insert the contents of a text file from Row 3 onwards in Sheet 2 named 'Filtered'. Currently, this is the code I am trying

#!/usr/bin/env python

import csv
from openpyxl.reader.excel import load_workbook
import xlrd
from xlutils import copy as xl_copy


with open('S12_final.txt') as tab_file: #open tab text file
   tab_reader = csv.reader(tab_file, delimiter='\t')
   xls_readable_book = load_workbook('S12.xlsx') #load workbook
   xls_writeable_book = xl_copy.copy(xls_readable_book)
   xls_writeable_sheet = xls_writeable_book.get_sheet_by_name('Filtered') #write data on this sheet
   for row_index, row in enumerate(tab_reader):
       xls_writeable_sheet.write(row_index, 0, row[0])
       xls_writeable_sheet.write(row_index, 1, row[1])
   xls_writeable_book.save('S12.xlsx') #save excel file
and I end up with this error

Error:
Traceback (most recent call last):  File "./tab2excel_a.py", line 24, in <module>    xls_writeable_book = xl_copy.copy(xls_readable_book)  File "/usr/local/lib/python2.7/dist-packages/xlutils-1.6.0-py2.7.egg/xlutils/copy.py", line 19, in copy    w  File "/usr/local/lib/python2.7/dist-packages/xlutils-1.6.0-py2.7.egg/xlutils/filter.py", line 937, in process    reader(chain[0])  File "/usr/local/lib/python2.7/dist-packages/xlutils-1.6.0-py2.7.egg/xlutils/filter.py", line 61, in __call__    filter.workbook(workbook,filename)  File "/usr/local/lib/python2.7/dist-packages/xlutils-1.6.0-py2.7.egg/xlutils/filter.py", line 287, in workbook    self.wtbook.dates_1904 = rdbook.datemode AttributeError: 'Workbook' object has no attribute 'datemode'
I am not very familiar with python , any suggestions will be appreciated. thank you
Reply
#2
you are using xlrd which only reads excel files.
you need a difference package to write to one.

Most people suggest pandas for both read and write,
I haven't had the need to use it.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Pandas keep existing format of Excel AlphaInc 2 883 Jan-11-2024, 03:44 AM
Last Post: plonkarchivist
  How to count total number of sheets in an excel workbook using polars sayyedkamran 0 630 Oct-27-2023, 09:54 PM
Last Post: sayyedkamran
  Errors if an xlsx file has blank rows in the beginning…. tester_V 1 790 Aug-28-2023, 06:22 PM
Last Post: deanhystad
  Write from dictionary to excel divon 3 3,473 Jun-11-2023, 10:37 AM
Last Post: Larz60+
  Data Sorting and filtering(From an Excel File) PY_ALM 0 1,012 Jan-09-2023, 08:14 PM
Last Post: PY_ALM
Smile How to further boost the data read write speed using pandas tjk9501 1 1,230 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  Split excel file and write output at specific row and set sheet position DSCA 0 1,960 May-12-2022, 07:29 PM
Last Post: DSCA
  Help with poorly formatted excel data armitron121 1 1,694 Jan-13-2022, 07:31 AM
Last Post: paul18fr
  Exporting data frame to excel dyerlee91 0 1,605 Oct-05-2021, 11:34 AM
Last Post: dyerlee91
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020