1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Trouble reading from an excel file

Discussion in 'Alibre Script' started by albie0803, Jul 21, 2019.

  1. albie0803

    albie0803 Alibre Super User

    last line throws error

    Code:
    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

    Code:
    # 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?
     
  2. idslk

    idslk Alibre Super User

    Hello albie,

    which Excel Version do you use ?

    upload_2019-7-21_14-30-44.png

    Regards
    Stefan
     
  3. albie0803

    albie0803 Alibre Super User

    Excel 2007
     
  4. albie0803

    albie0803 Alibre Super User

    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 :(
     
  5. NateLiqGrav

    NateLiqGrav Alibre Super User

  6. albie0803

    albie0803 Alibre Super User

    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?

    Code:
    ...
    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()
    
     
    Last edited: Jul 28, 2019
  7. albie0803

    albie0803 Alibre Super User

    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.
     
  8. ajayre

    ajayre Alibre Super User

    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
     
    Last edited: Jul 29, 2019
  9. albie0803

    albie0803 Alibre Super User

    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?
     
  10. idslk

    idslk Alibre Super User

    Hello albie0803,

    have you tried something like:
    Code:
    ca=CurrentAssembly()
    for i in ca.Parts:
      i.Regenerate()
    Can you provide the file?

    Regards
    Stefan
     
  11. albie0803

    albie0803 Alibre Super User

    @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)
     
  12. idslk

    idslk Alibre Super User

    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
     

    Attached Files:

  13. idslk

    idslk Alibre Super User

    upload_2019-7-30_0-12-18.png

    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
     
  14. albie0803

    albie0803 Alibre Super User

    Thanks Stefan, you are amazing!

    Here is my clunky effort

    Code:
    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

    Code:
    ca.Parts[1].Parameters[0].Value = Sheet1['H14'].value
    I have Libre Office at home so I will do a conversion there.
     
  15. Lew_Merrick

    Lew_Merrick Alibre Super User

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

    albie0803 Alibre Super User

    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. :)
     
  17. Lew_Merrick

    Lew_Merrick Alibre Super User

    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
     
  18. albie0803

    albie0803 Alibre Super User

    @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.

    Code:
    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
     
  19. idslk

    idslk Alibre Super User

    Hello albie0803,
    :) I'm happy about that.
    You know that you can read out your Editorvariables and write them into an excel sheet? You can use some kind of:
    Code:
    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
     
  20. albie0803

    albie0803 Alibre Super User

    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.
     

Share This Page