Trouble reading from an excel file

[albie0803]

last line throws error

import os
Win = Windows()
MyAss = CurrentAssembly()

# get XLSC reading functions
from openpyxl import load_workbook

# Get math extensions
import math
# open a workbook, replace with your own path
wb = load_workbook(filename = 'C:\\Users\\allan\\Desktop\\Gleason Bevels\\Straight_Bevel_TS_TCA_10_07_16-Buttons Chordals-1.xlsm', data_only=True)

Traceback (most recent call last):
File "<string>", line 13, in <module>
File "C:\PROGRAM FILES\ALIBRE DESIGN\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\reader\excel.py", line 118, in load_workbook
File "C:\PROGRAM FILES\ALIBRE DESIGN\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\reader\excel.py", line 66, in repair_central_directory
UnicodeDecodeError: ('unknown', u'\xd0', -1, 0, '')

File has macros and protected cells.

The same line works fine on a basic excel file in a different script

# Open XLSX File for usage
wb = load_workbook(filename = 'C:\\Users\\Allan\\Desktop\\shaft sizes.xlsx', data_only=True)

Looking at it is it because its an xlsm file and not an xlsx one?

 

[idslk]

Hello albie,

which Excel Version do you use ?



Regards
Stefan

 

[albie0803]

Excel 2007

 

[albie0803]

It's a Unicode Translation Error. There is something in the file that can't be read properly. The file is a program we bought that calculates bevel tooth cutouts with most of the working locked away. I was going to read values out from it to set global parameters in my bevel gearset assembly.
It was going to be: calculate variables in excel, open drawing, open model, run script, update drawing, Done!

Oh well

 

[NateLiquidGravity]

If the excel file does all the work then you might try using the excel add-in and link to the global parameters. No need to use alibre script for this.
Some how to can be found in this thread:
https://www.alibreforum.com/forum/index.php?threads/global-parameters-linked-to-excel.20351/

 

[albie0803]

I actually have another variant of the same calculations as an excel file and that works with scripting. My assembly file has a pinion and a crownwheel. The script is run from the assembly. It opens the excel file and reads values. Then it opens the GLP file and updates the values, saves and closes it. Then it regenerates the assembly and saves it.

Issue: The parts don't regenerate unless I open them first.

Am I going about this the wrong way or how do I get the parts to update?

...
Params.Save()
Params.Close()

Pinion = MyAssem.GetPart('Gleason Bevel Pinion Blank<1>')
Crown = MyAssem.GetPart('Gleason Bevel Crownwheel<1>')

Pinion.Regenerate()
Crown.Regenerate()

MyAssem.Regenerate()

 

[albie0803]

you might try using the excel add-in

Still stuck with Office 2007 on Windows 10 so addin doesn't work.

I get a Can't find project or library window and then a Password window.

 

[ajayre]

It's a Unicode Translation Error. There is something in the file that can't be read properly. The file is a program we bought that calculates bevel tooth cutouts with most of the working locked away. I was going to read values out from it to set global parameters in my bevel gearset assembly.
It was going to be: calculate variables in excel, open drawing, open model, run script, update drawing, Done!

Oh well

Have you tried asking the openpyxl community for help? Perhaps this is a known issue and they have a workaround?

What about the first answer given here?: https://stackoverflow.com/questions...i-codec-cant-encode-character-at-special-name

Andy

 

[albie0803]

Thanks Andy, I will come back to that as opening that particular file is not essential. It would have been nice to be able to use it but my alternative file gives me the information for the gear blank shape that I am after to create drawings, I don't need the iges surfaces that it also creates.

As I said above, the assembly doesn't update unless the parts are individually opened and closed. Can this be done via code or does it have to be a manual step?

A search shows that I have asked this sort of question before without getting a real answer. Maybe there isn't one?

 

[idslk]

Hello albie0803,

have you tried something like:

ca=CurrentAssembly()
for i in ca.Parts:
  i.Regenerate()

There is something in the file that can't be read properly.

Can you provide the file?

Regards
Stefan

 

[albie0803]

@idslk the regenerate didn't make any difference.
Here is the file in question. I am trying to read values from column B (it was too large to post here)

 

[idslk]

the regenerate didn't make any difference.

Hello albie0803, how do you apply the value changes to your parts?
If you apply changes like:
ca.Parts[1].Parameters[0].Value = 8
they update here immediatly. (see package...test script should be included)

Regards
Stefan

 

[idslk]

used LibreOffice to "convert"...
When calculations are done, i assume you do not need the macros for this input data again...
Loaded with deactivated Macros and saved as *_07.xlsx (MSO 2007 format)
should work with MS Office also...

Regards
Stefan

 

[albie0803]

Thanks Stefan, you are amazing!

Here is my clunky effort

import os
Win = Windows()
MyAssem = CurrentAssembly()

# get XLSC reading functions
from openpyxl import load_workbook

# Get math extensions
import math

Bore = 110
BossL = 50

Options = []
Options.append(["Pinion Bore Length", WindowsInputTypes.Real, Bore])
Options.append(["Crownwheel Bore Length", WindowsInputTypes.Real, BossL])
Values = Win.OptionsDialog("Gleason Bevels", Options,40)
if Values == None:
  sys.exit()

print 'Opening spreadsheet'

# open a workbook, replace with your own path
wb = load_workbook(filename = 'C:\\Users\\Allan\\Desktop\\Gleason Bevels\\Gleason_Straight.xlsx', data_only=True)
 
# get access to the sheet
Sheet1 = wb['Sheet1']

Params=GlobalParameters(r'C:\Users\Allan\Desktop\Gleason Bevels', 'GleasonParameters')

print 'Reading Crownwheel Values from spreadsheet'

# Read parameter file, assign items and change values
G_Addendum = Params.GetParameter("G_Addendum")
G_Addendum.Value = Sheet1['H14'].value * 25.4

G_Dedendum = Params.GetParameter("G_Dedendum")
G_Dedendum.Value = Sheet1['H15'].value * 25.4

G_FaceAngle = Params.GetParameter("G_FaceAngle")
G_FaceAngle.Value = Sheet1['Q10'].value

G_FaceLength = Params.GetParameter("G_FaceLength")
G_FaceLength.Value = Sheet1['C5'].value * 25.4

G_HeelOD = Params.GetParameter("G_HeelOD")
G_HeelOD.Value = Sheet1['H20'].value * 25.4

G_PitchAngle = Params.GetParameter("G_PitchAngle")
G_PitchAngle.Value = Sheet1['H11'].value

G_RootAngle = Params.GetParameter("G_RootAngle")
G_RootAngle.Value = Sheet1['Q13'].value

G_Width = Params.GetParameter("G_Width")
G_Width.Value = Values[1]

print 'Reading Pinion Values from spreadsheet'

P_Addendum = Params.GetParameter("P_Addendum")
P_Addendum.Value = Sheet1['C14'].value * 25.4

P_Dedendum = Params.GetParameter("P_Dedendum")
P_Dedendum.Value = Sheet1['C15'].value * 25.4

P_FaceAngle = Params.GetParameter("P_FaceAngle")
P_FaceAngle.Value = Sheet1['Q11'].value

P_FaceLength = Params.GetParameter("P_FaceLength")
P_FaceLength.Value = Sheet1['C5'].value * 25.4

P_HeelOD = Params.GetParameter("P_HeelOD")
P_HeelOD.Value = Sheet1['C20'].value * 25.4

P_PitchAngle = Params.GetParameter("P_PitchAngle")
P_PitchAngle.Value = Sheet1['C11'].value

P_RootAngle = Params.GetParameter("P_RootAngle")
P_RootAngle.Value = Sheet1['Q12'].value

P_Width = Params.GetParameter("P_Width")
P_Width.Value = Values[0]

Params.Save()
Params.Close()

Pinion = MyAssem.GetPart('Gleason Bevel Pinion Blank<1>')
Crown = MyAssem.GetPart('Gleason Bevel Crownwheel<1>')

Options = []
Options.append([None, WindowsInputTypes.Label, 'Open and Close each Part'])
Options.append([None, WindowsInputTypes.Label, 'Regenerate Assembly'])
Values = Win.OptionsDialog("Gleason Bevels", Options,200)
if Values == None:
  sys.exit()
 

#for i in MyAssem.Parts:
#  i.Regenerate()

#Pinion.Regenerate()
#Crown.Regenerate()

#MyAssem.Regenerate()
#MyAssem.Save()

print 'Done!'

I don't have time right now to try it but I assume this is acceptable

ca.Parts[1].Parameters[0].Value = Sheet1['H14'].value

I have Libre Office at home so I will do a conversion there.

 

[Lew_Merrick]

Albie -- Have you thought of "exporting" the data into a CSV and then "importing" that into Wizo/AlibreScript??? -- Lew

 

[albie0803]

No Lew I hadn't. I saw a while ago someone posted about reading an excel file and I went "I can use that" and wrote a small script. This bevel gear job came along and I went "Yes, read from the excel file and update parameters, I'm sure I can do that" and here we are.

 

[Lew_Merrick]

No Lew I hadn't. I saw a while ago someone posted about reading an excel file and I went "I can use that" and wrote a small script. This bevel gear job came along and I went "Yes, read from the excel file and update parameters, I'm sure I can do that" and here we are.

Hi Albie -- For many years I "Saved" my OpenOffice Calc spreadsheets in Exce; format until so many "versions" were (supposed to be) "current" that I gave up the entire effort. A friend of min who works at Microsoft on the "Office build team" has problems determining which "version" an Excel file is saved in. Imagine what it is like for somebody without this "insider's knowledge?" -- Lew

 

[albie0803]

@idslk Thanks again Stefan. I was able to adapt your code for what I wanted.

I set up a column in the spreadsheet with the values in the same order as the parameters are listed.
I then read them into a list. Your code gets implemented and as each parameter is referenced the correct value is assigned to it and it all updates nicely.

import time
import os
import math
from openpyxl import load_workbook
Win = Windows()
ca=CurrentAssembly()

print 'Opening spreadsheet - Be patient!'

# open a workbook
wb = load_workbook(filename = 'C:\\Users\\allan\\Desktop\\Gleason Bevels\\Gleason_Straight.xlsx', data_only=True)

# get access to the sheet
Sh1 = wb['Sheet1']

def set(index): # reads a row of data
  num=str(x) # row number
  Acel="O"+num
  #  Get spreadsheet values
  A = Sh1[Acel].value            # parameter
  return A

x=1  # first useful row
y=0  # parameter index

A = set(x)

print 'Reading Values from spreadsheet'

EParams = []

while A!=None:
  EParams.append(A)
  x=x+1
  A = set(x)

for i in range(len(ca.Parts)):
  print 'Part Nr.:',i, 'named',ca.Parts[i].Name
  for j in range(len(ca.Parts[i].Parameters)):
    print 'Part',i, 'Param:', j, 'Name:', ca.Parts[i].Parameters[j], 'Value:', ca.Parts[i].Parameters[j].Value
    ca.Parts[i].Parameters[j].Value = EParams[y]
    y=y+1

 

[idslk]

Hello albie0803,

@idslk Thanks again Stefan. I was able to adapt your code for what I wanted.

I'm happy about that.

I set up a column in the spreadsheet with the values in the same order as the parameters are listed.

You know that you can read out your Editorvariables and write them into an excel sheet? You can use some kind of:

wb = load_workbook(FileName)
ws = wb.active
mypart = CurrentPart()
ws['A1'] = mypart.Parameters[0].Name
ws['B1'] = mypart.Parameters[0].Value

Also possible variables in Parameters are (.Comment; .Equation; .Units; .Type)
With a loop, it maybe reduces your typing to setup an excel-table...
And last but not least: the import time module in the sample was only used to have the time.sleep(1) function for a delay to give the viewer a chance to view...
Regards
Stefan

 

[albie0803]

Thanks for the tip, but the excel file I am reading from does all the initial calculations for the bevel gearset so I am only reading from it. I am able to show others the variable set to cross check if they want to and then feed the info to Alibre to configure my models and then update drawings.