1

Using Python to enter text into an Excel file, I have a little problem that occurs with Excel 2010. It is however working with Excel 2013. I've tested on 2 different PC's. On one Pc with Excel 2010, Excel crashes, on the other, it enters these kind of bubbles (they behave like images - you can even resize them):

enter image description here

The code works like this: I create a string, indicating line changes by \r\n, and pasting with Excel.Dispatchand PasteSpecial.

What can be the reason for not inserting correctly? 

def pasteToExcel(excelfile, sheet, data, startcell):
    if type(data) == list:
        rows = 1
        cols = len(data)
    elif type(data) == pd.DataFrame:
        rows = data.shape[0]
        cols = data.shape[1]
    elif type(data) == int or float:
        rows = 1
        cols = 1
    elif type(data) == str:
        rows = 1
        cols = 1

    cellrange = startcell
    text = ""

    cellrange = calculateCellRange(startcell,rowcount=rows,colcount=cols)

    if type(data) == pd.DataFrame:
        print "  Erkannter Datentyp vom Input: pd.DataFrame"
        line_strings = []
        for row in range(rows):
            for col in range(cols-1): 
                line_strings.append(str(data.ix[row,col])+"\t") #cell change: \t
            line_strings.append(str(data.ix[row,data.shape[1]-1])+"\r\n")

        for item in line_strings:
            item = item.replace(".",",")
            text += item

    elif type(data) == str:
        text = data

    elif type(data) == list:
        line_strings = []
        for index in range(len(data)): # 0 1 2           len(data) =3 ; range(2) = 0,1
            if index < len(data)-1:
                line_strings.append(str(data[index])+"\t") #cell change: \t
            elif index == len(data)-1:
                line_strings.append(str(data[index])+"\r\n")

        for item in line_strings:
            item = item.replace(".",",")
            text += item

    clipboard.OpenClipboard()
    clipboard.EmptyClipboard()
    clipboard.SetClipboardText(text)
    clipboard.CloseClipboard()

    excel = Dispatch("Excel.Application")
    excel.Visible = 0
    wb = excel.Workbooks.Open(excelfile) 
    ws = wb.Worksheets(sheet)
    ws.Activate()
    ws.Range(cellrange).Select()
    wb.ActiveSheet.PasteSpecial()
    excel.DisplayAlerts = False
    excel.ActiveWorkbook.Save()
    excel.Quit()
user2366975
  • 4,350
  • 9
  • 47
  • 87
  • What exactly is present in the excel cell, after the copy-paste op? Could you provide the raw text? – arvindch Jun 27 '14 at 07:35
  • The raw text is what you see. The numbers and values seen in the table are coming from a template. Instead of the bubbles, the real numbers should be placed there. – user2366975 Jun 27 '14 at 07:50
  • I think my library http://xlwings.org will do what you are trying to do in an easier way. It should be compatible with all recent versions of Excel and Python. – Felix Zumstein Jun 30 '14 at 21:54
  • May be this could help! http://stackoverflow.com/questions/10423593/unwanted-character-in-excel-cell-in-python – Ravi Yenugu Jul 09 '14 at 14:22

0 Answers0