Copy xls files manipulate it and save it without loosing referenced cells

Rhino Developer
1

hi all,
i use xlwt xlrd and xlutils to read and write xls files and feed it with data in between but unfortunatly xlutils.copy dont copy all formula references instead only the value is copied.
Is there another option to copy and manipulate xls data without loosing reference paths in the formula?
openpyxl give me all time the error (works not so good with ironpython?).
Any tip is very welcome

2

Hi,
Are you looking specifically for a Python solution? There must be multiple options through .Net

3

i would prefer python because i a m programmer beginner and never used another language.
Maybe win32com is a option?
Do you know any net library that i can use and can i install net librarys via pip?

4

Hmm… any ideas @ivelin.peychev?

5

Might have to use Rhinoscript (VBA) : there are 3 reading examples here:

6

yep vba is the king for excel, my solution at the moment in python with the net

if EXECUTE == True:
import clr
clr.AddReference(“Microsoft.Office.Interop.Excel”)
from Microsoft.Office.Interop import Excel
from System import Array

ex = Excel.ApplicationClass()   
ex.Visible = False
ex.DisplayAlerts = False   

workbook = ex.Workbooks.Open(PATHOPEN)
ws = workbook.Worksheets[1]

for i in range(ROW_COL[0],len(LIST)):
    ws.Cells(i+ROW_COL[0],ROW_COL[1]).Value = LIST[i]


workbook.Close(SaveChanges =True,Filename = PATHSAVE)

and thanks for help as always

2 Likes
7

You know, maybe this is not the brightest solutions but you could serialize your data with cpickle then from CPython3 de-serialize it and use Openpyxl. I haven’t tried many modules for xls manipulation but among the ones I’ve used Openpyxl is by far the best. You can add a bit of Tkinter and the result is beatiful :wink: