When you export large text to excel (Office 2007), no more than 8203 characters are exported to an excel cell

Question

When you export large text to excel, no more than 8203 characters are exported to an excel cell. I think problem to VBA or win32com.client, if i use openpyxl lib - export correct.

Error: (-2147352567, 'Ошибка.', (0, None, None, None, 0, -2146827284), None)

I have the problem only in office 2007, export so use 2007 office only.

In office 2016 export is working fine.

import win32com.client
import os


path_excell = 'c:\wa\Excel2.xlsx'
excel = win32com.client.Dispatch("Excel.Application")
if os.path.isfile(path_excell):
    wb = excel.Workbooks.Open(path_excell)
else:
    wb = excel.Workbooks.add
sheet = wb.ActiveSheet
new_str = '1|2|3|4|5|6|7|8|9'
new_str = new_str.split('|')
k = 8200
i = 1
try:
    while k<8400:
        s = 'v'*k
        new_str[7] = s
        sheet.Range(sheet.Cells(i, 1), sheet.Cells(i, 9)).Value = new_str
        k += 1
        i += 1
except Exception as err:
    print(f'Error: {str(err)}: max {k} char')
finally:
    if os.path.isfile(path_excell):
        wb.Save()
    else:
        wb.SaveAs(path_excell)
    wb.Close()
    excel.Quit()

*DON'T* use Office Automation to create an Excel file. `xlsx` is a zip package containing XML files which means you don't need Excel installed, you can just create the files yourself. It's far easier obviously to use a library like `XlsxWriter` or `openpyxl`. — Panagiotis Kanavos, Sep 02 '19 at 10:13

Jainil Patel · Answer 1 · 2019-09-02T09:23:08.423

-1

IT IS WORKING FINE FOR ME!

import win32com.client
import os


path_excell = 'C:\\Users\jainil\Documents\\book1.xlsx'
excel = win32com.client.Dispatch("Excel.Application")
if os.path.isfile(path_excell):
    wb = excel.Workbooks.Open(path_excell)
else:
    wb = excel.Workbooks.add
sheet = wb.ActiveSheet
new_str = '1|2|3|4|5|6|7|8|9'
new_str = new_str.split('|')
k = 0
i = 1

while k<8400:
    s = 'v'*k
    new_str[7] = s
    sheet.Range(sheet.Cells(i, 1), sheet.Cells(i, 9)).Value = new_str
    k += 1
    i += 1
if os.path.isfile(path_excell):
    wb.Save()
else:
    wb.SaveAs(path_excell)
wb.Close()
excel.Quit()

edited Sep 02 '19 at 09:23

answered Sep 02 '19 at 09:18

Jainil Patel

1,284
7
16

Your version Microsoft Office - not 2007 – Vitaliy Utkin Sep 02 '19 at 09:28
it is for just showing output in .xlsx file. i use office 2016 just to show output, is it related to python? – Jainil Patel Sep 02 '19 at 09:30
yeah now i get your question , you mean office 2016 is working fine but office 2007 is not, so i reccommend you to simply UPGRADE! – Jainil Patel Sep 02 '19 at 09:34
some users may have a lower version of office so we take from 2007 the beginning – Vitaliy Utkin Sep 02 '19 at 12:52

When you export large text to excel (Office 2007), no more than 8203 characters are exported to an excel cell

1 Answers1