Write data into existing Excel (xlsx) file with multiple sheets

[Nandini Badarinarayan]

Hello,

I have an excel (xlsx) file with 11 worksheets and I need to insert the contents of a text file (roughly 26 columns and 100 rows) from Row 3 onwards. Currently, this is the code I am trying

#!/usr/bin/env python

import csv
import xlwt
import xlrd
from xlutils import copy as xl_copy

#open text file
with open('S12_final.txt') as tab_file:
    tab_reader = csv.reader(tab_file, delimiter='\t')
    xls_readable_book = xlrd.open_workbook('S12.xlsx') #open main excel sheet
    xls_writeable_book = xl_copy.copy(xls_readable_book)
    xls_writeable_sheet = xls_writeable_book.get_sheet_by_name('Filtered') #2nd sheet on which data needs to be copied to
    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 the workbook

I end up with the following errors

Traceback (most recent call last):
  File "./tab2excel_a.py", line 12, 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 66, in __call__
    filter.row(row_x,row_x)
  File "/usr/local/lib/python2.7/dist-packages/xlutils-1.6.0-py2.7.egg/xlutils/filter.py", line 503, in row
    wtrow = self.wtsheet.row(wtrowx)
  File "/usr/local/lib/python2.7/dist-packages/xlwt-0.7.5-py2.7.egg/xlwt/Worksheet.py", line 1078, in row
    self.__rows[indx] = self.Row(indx, self)
  File "/usr/local/lib/python2.7/dist-packages/xlwt-0.7.5-py2.7.egg/xlwt/Row.py", line 42, in __init__
    raise ValueError("row index (%r) not an int in range(65536)" % rowx)
ValueError: row index (65536) not an int in range(65536)

Any suggestions to correct this or a better way to script it ?

Thank you

[Charlie Clark]

Am .06.2017, 12:51 Uhr, schrieb Nandini Badarinarayan
<nandini.ba...@gmail.com>:

> I have an excel (xlsx) file with 11 worksheets and I need to insert the
> contents of a text file (roughly 26 columns and 100 rows) from Row 3
> onwards. Currently, this is the code I am trying

As the documentation makes abundantly clear: xlwt does not support the
XLSX format.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Nandini]

I understand that I need to use openpyxl for my xlsx files. But does this library have a function to copy from text tab delim file into an existing excel sheet ?

[Charlie Clark]

Am .06.2017, 15:21 Uhr, schrieb Nandini <nandini.ba...@gmail.com>:

> I understand that I need to use openpyxl for my xlsx files. But does this
> library have a function to copy from text tab delim file into an existing
> excel sheet ?

No, but you can use the csv module for this. You just can't use xlrd /
xlwt for what you're doing, not least because the old XLS format has much
smaller column and row limits that you're hitting.

[John Yeung]

On Thu, Jun 1, 2017 at 9:21 AM, Nandini <nandini.ba...@gmail.com> wrote:
> I understand that I need to use openpyxl for my xlsx files. But does this
> library have a function to copy from text tab delim file into an existing
> excel sheet ?

You are already taking the right approach in terms of reading the
tab-delimited file using the csv module. What openpyxl does (roughly
speaking) is similar to what the whole bundle of xlrd, xlwt, and
xlutils.copy does, except for .xlsx instead of .xls.

So, the simplest modification to what you're doing now is to open the
existing workbook in openpyxl, and write to it using openpyxl. In
openpyxl, the same workbook is both readable and writable.

John Y.

[Nandini]

Hello,

So I have managed to write up a code that works alright for my needs. But when I save my new excel sheet, I end up losing the images/fonts/colors from the original one on all worksheets. Is there a way to resolve this ? Alternatively, when I try to insert the Image in python 2.7 (i have installed pillow) I get the following error

from openpyxl.drawing.image import Image
ImportError: No module named image

#!/usr/bin/python

import os
from openpyxl.reader.excel import load_workbook
# from openpyxl.drawing.image import Image
import csv

directoryPath = r'/REPORTS'
os.chdir(directoryPath)
folder_list = os.listdir(directoryPath)
for folders, sub_folders, file in os.walk(directoryPath):  
    for name in file:
        if name.endswith(".xlsx"):
            filename = os.path.join(folders, name)
            wb = load_workbook(filename, data_only=True)
            ws = wb.get_sheet_by_name('Filtered')
                  f = open("S12.txt")
                  reader = csv.reader(f, delimiter='\t')
                  for row in reader:
                      ws.append(row)
                      wb.save(filename)
# img=Image('logo.jpg')
# ws.add_image(img, 'A1')

# img = Image('logo.jpg')
# img.anchor('A1', anchortype='oneCell')
# ws.add_image(img)

[Adrian Klaver]

On 06/02/2017 02:37 AM, Nandini wrote:
> Hello,
>
> So I have managed to write up a code that works alright for my needs.
> But when I save my new excel sheet, I end up losing the
> images/fonts/colors from the original one on all worksheets. Is there a
> way to resolve this ?

Don't know about above.

>Alternatively, when I try to insert the Image in

> python 2.7|(|i have installed pillow) I get the following error

>
> |
>
> |
>
> |from openpyxl.drawing.image import Image |
> |ImportError: No module named image|
> ||

Works here:

openpyxl==2.4.7

In [1]: from openpyxl.drawing.image import Image

In [2]: Image?
Init signature: Image(img, coordinates=((0, 0), (1, 1)), size=(None,
None), nochangeaspect=True, nochangearrowheads=True)
Docstring: Raw Image class
File:
~/py_virt/py3/lib/python3.4/site-packages/openpyxl/drawing/image.py
Type: type


>
>
> |



--
Adrian Klaver
adrian...@aklaver.com

[gec...@gmail.com]

http://openpyxl.readthedocs.io/en/default/usage.html

"openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name."

[进陆]

don't know whether http://poi.apache.org/ can add new items (text, images) to existing excel file which has both text and images. If the answer is YES, Jython or jpype can help you. But, JVM is some a huge monster. 

The other answer is win32com as you may know, which always give the finally excel files as you expected. However you have to install msword.