What's new
By:
Advanced search…

saving using openpyxl

E

eboves

Member
Hi all,

I am trying to save a xlsx file using openpyxl, and every time I tried to save it these errors come out. When I run the code locally (no alibre) it works fine, but as soon as I import it to alibre I get those errors below. Any idea why? Thanks a lot for all the help in advance!!!


File "C:\PROGRAM FILES\ALIBRE DESIGN 27.0.0.27038\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 169, in startElementNS
File "C:\PROGRAM FILES\ALIBRE DESIGN 27.0.0.27038\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 108, in write
TypeError: expected long, got NoneType
 
idslk

idslk

Alibre Super User
as it shows...
in the call you are using a element which is of a NoneType, but you should handover an Value with a LongType...

and if you post the "surrounding" code, the answer will maybe more meaningful ;)

Regards
Stefan
 
E

eboves

Member
Thank you idslk, for the response. I had checked all the values that I am getting from user input, but still, it says that. The code I wrote is below.

url_bom_file = Values[0]
work_order = Values[2]
run_prchse = Values[3]
customer_name = Values[12]
created_date = date.today()
remarks = []
thk = []
description = []
material = []

elvis_url_folder_xlsx = r'C:\Users\EBOVES\Desktop\python\projects\purchase_req\prchse_awj.xlsx'
wb = load_workbook(elvis_url_folder_xlsx)
ws = wb.active
bom_file = url_bom_file
prchase = ['prchase', 'purchase', 'prchse', 'pchse', 'pchase', 'purchased', 'prchased', 'pchse']
def load_bom_data(bom_file):
bom_list = []
with open(bom_file) as bom_lst:
bom_components = csv.reader(bom_lst, delimiter=",")
next(bom_components) # this line will skip the 1 row or headers
for row in bom_components:
bom_dict = {}
if row[6].lower() in prchase:
bom_dict['material'] = row[1]
bom_dict['description'] = row[7]
bom_dict['thk'] = row[8]
bom_dict['remarks'] = row[9]
bom_list.append(bom_dict)
print('DIRECTORY', bom_dict)
return bom_list

def create_prchse_req(bom_list):
item_description = []
for list_index in range(len(bom_list)):
material.append(bom_list[list_index]['material'])
description.append(bom_list[list_index]['description'])
thk.append(bom_list[list_index]['thk'])
remarks.append(bom_list[list_index]['remarks'])
item_des = remarks[list_index] + ' ' + thk[list_index] + 'GA ' + material[list_index] + ' (' + description[list_index].split(' ', 1)[0] + ')'
item_description.append(item_des)
return item_description
bom_list_appended = load_bom_data(bom_file)
item_desc = create_prchse_req(bom_list_appended)
print(item_desc)
##### CREATE A NAME/ url when you save data
elvis_url_folder = r'C:\Users\EBOVES\Desktop\python\projects\purchase_req'
prchse_req_name = str(work_order) + '-' + str(customer_name).upper() + '-' + 'AWJ'
prchse_req_url = elvis_url_folder + '\\' + prchse_req_name + '.xlsx'
new_url = prchse_req_url
#wb = Workbook()
ws['B2'].value = prchse_req_name
ws['H17'].value = created_date
for i in range(5, 5+len(item_desc)):
ws.cell(row=i, column=6).value = item_desc[i - 5]
ws.cell(row=i, column=10).value = run_prchse
print("this is the new URL", new_url)
wb.save(new_url)
wb.close()
os.startfile(new_url)
 
B

BobSchaefer

Senior Member
So, I'm just making guesses here, but my best guess is that one of the cells is getting a null value, and its only being detected on the save of the workbook. First question that popped out to me is the run_prchse variable. You define it as an array of 3, but never assign it a value and reference it as a single variable instead of as an array.

Again, this is just a quick gess.
 
E

eboves

Member
BobSchaefer said:
So, I'm just making guesses here, but my best guess is that one of the cells is getting a null value, and its only being detected on the save of the workbook. First question that popped out to me is the run_prchse variable. You define it as an array of 3, but never assign it a value and reference it as a single variable instead of as an array.

Again, this is just a quick gess.
Click to expand...
I am getting that value from user input using the Options.append method in alibre. I checked all input and I am getting everything I need from them no problem. I think is something else. thanks a lot BobSchaefer.
 
E

eboves

Member
NateLiqGrav said:
By chance are you testing your code in a newer Python version? Alibre Script is based on IronPython 2.7
Click to expand...
Yeah I am running python 3.11 and I just noticed that alibre uses 2.7 iron instead. Do you know how I can bypass this? I am new to programming. Thanks a lot NateLiqGrav.
 
idslk

idslk

Alibre Super User
eboves said:
The code I wrote is below.
Click to expand...
can you please use the code insert function of the forum:

1692814332445.png

this will keep alls the format of the code including indents. Tis makes it more possible to test the code...

Regards
Stefan
 
E

eboves

Member
Python: 
url_bom_file = Values[0]
work_order = Values[2]
run_prchse = Values[3]
customer_name = Values[12]
created_date = date.today()
remarks = []
thk = []
description = []
material = []

elvis_url_folder_xlsx = r'C:\Users\EBOVES\Desktop\python\projects\purchase_req\prchse_awj.xlsx'
wb = load_workbook(elvis_url_folder_xlsx)
ws = wb.active
bom_file = url_bom_file
prchase = ['prchase', 'purchase', 'prchse', 'pchse', 'pchase', 'purchased', 'prchased', 'pchse']
def load_bom_data(bom_file):
    bom_list = []
    with open(bom_file) as bom_lst:
        bom_components = csv.reader(bom_lst, delimiter=",")
        next(bom_components) # this line will skip the 1 row or headers
        for row in bom_components:
            bom_dict = {}
            if row[6].lower() in prchase:
            bom_dict['material'] = row[1]
            bom_dict['description'] = row[7]
            bom_dict['thk'] = row[8]
            bom_dict['remarks'] = row[9]
            bom_list.append(bom_dict)
    return bom_list

def create_prchse_req(bom_list):
    item_description = []
    for list_index in range(len(bom_list)):
        material.append(bom_list[list_index]['material'])
        description.append(bom_list[list_index]['description'])
        thk.append(bom_list[list_index]['thk'])
        remarks.append(bom_list[list_index]['remarks'])
        item_des = remarks[list_index] + ' ' + thk[list_index] + 'GA ' + material[list_index] + ' (' + description[list_index].split(' ', 1)[0] + ')'
        item_description.append(item_des)
    return item_description
bom_list_appended = load_bom_data(bom_file)
item_desc = create_prchse_req(bom_list_appended)

elvis_url_folder = r'C:\Users\EBOVES\Desktop\python\projects\purchase_req'
prchse_req_name = str(work_order) + '-' + str(customer_name).upper() + '-' + 'AWJ'
prchse_req_url = elvis_url_folder + '\\' + prchse_req_name + '.xlsx'
new_url = prchse_req_url
#wb = Workbook()
ws['B2'].value = prchse_req_name
ws['H17'].value = created_date
for i in range(5, 5+len(item_desc)):
ws.cell(row=i, column=6).value = item_desc[i - 5]
ws.cell(row=i, column=10).value = run_prchse
print("this is the new URL", new_url)
wb.save(new_url)
wb.close()
os.startfile(new_url)
 
E

eboves

Member
NateLiqGrav said:
By chance are you testing your code in a newer Python version? Alibre Script is based on IronPython 2.7
Click to expand...
Hey NateLiqGrav. quick question, do you know a way around this? I've been looking for ways to do it online but havent found anything helpful.
 
idslk

idslk

Alibre Super User
is this the whole code you posted?
From where is the array Values[] getting the values? ist it filled from an Win.OptionsDialog?
Have you run the posted code by yourself? it stumbles at some missing indents...
Can you post a sample BOM also?

Regards
Stefan
 
E

eboves

Member
idslk said:
is this the whole code you posted?
From where is the array Values[] getting the values? ist it filled from an Win.OptionsDialog?
Have you run the posted code by yourself? it stumbles at some missing indents...
Can you post a sample BOM also?

Regards
Stefan
Click to expand...
The problem is with openpyxl I can modify any cell on excel with it but every time I do the wb.save(file_name) it says that it got a NONETYPE. I tried creating a new workbook from scratch, but it does the same thing. for some reason the wb.save(file_name) is breaking when it reaches that line even if is an empty file.
 
idslk

idslk

Alibre Super User
OK, have written a short test script...:
Code: 
from openpyxl import load_workbook
from openpyxl import workbook
win = Windows()
filename = win.OpenFileDialog('Select spreadsheet-file containing Coordinates', 'ExcelFiles|*.xlsx',' *.xlsx')
try:
  wb = load_workbook(filename,data_only= True)
except:
  sys.exit('Canceled by user. No or false filename/file type choosen.')
ws = wb.active
ws['B2'].value = "Test"
wb.save(filename)

Tried in V26 and V27 - Result:
Code: 
>>>
Traceback (most recent call last):
  File "<string>", line 11, in <module>
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\workbook.py", line 265, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 187, in save_workbook
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 170, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 76, in write_data
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 105, in _write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\strings.py", line 47, in write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\shared\xmltools.py", line 170, in start_tag
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 169, in startElementNS
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 108, in write
TypeError: expected long, got NoneType
>>>

Had not looked deeper into yet...

Regards
Stefan
 
E

eboves

Member
idslk said:
OK, have written a short test script...:
Code: 
from openpyxl import load_workbook
from openpyxl import workbook
win = Windows()
filename = win.OpenFileDialog('Select spreadsheet-file containing Coordinates', 'ExcelFiles|*.xlsx',' *.xlsx')
try:
  wb = load_workbook(filename,data_only= True)
except:
  sys.exit('Canceled by user. No or false filename/file type choosen.')
ws = wb.active
ws['B2'].value = "Test"
wb.save(filename)

Tried in V26 and V27 - Result:
Code: 
>>>
Traceback (most recent call last):
  File "<string>", line 11, in <module>
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\workbook.py", line 265, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 187, in save_workbook
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 170, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 76, in write_data
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 105, in _write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\strings.py", line 47, in write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\shared\xmltools.py", line 170, in start_tag
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 169, in startElementNS
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 108, in write
TypeError: expected long, got NoneType
>>>

Had not looked deeper into yet...

Regards
Stefan
Click to expand...
That's the same error I am getting when I try to save it. I wonder why it is happening. you can open an existing file and actually change the value of any cell inside with the ws['B2'].value = "something" and if you print it you get the new value, but when you try to save it breaks.

Thanks a lot Stefan. btw enjoy the weekend.
 
E

eboves

Member
idslk said:
OK, have written a short test script...:
Code: 
from openpyxl import load_workbook
from openpyxl import workbook
win = Windows()
filename = win.OpenFileDialog('Select spreadsheet-file containing Coordinates', 'ExcelFiles|*.xlsx',' *.xlsx')
try:
  wb = load_workbook(filename,data_only= True)
except:
  sys.exit('Canceled by user. No or false filename/file type choosen.')
ws = wb.active
ws['B2'].value = "Test"
wb.save(filename)

Tried in V26 and V27 - Result:
Code: 
>>>
Traceback (most recent call last):
  File "<string>", line 11, in <module>
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\workbook.py", line 265, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 187, in save_workbook
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 170, in save
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 76, in write_data
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\excel.py", line 105, in _write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\writer\strings.py", line 47, in write_string_table
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\site-packages\openpyxl\shared\xmltools.py", line 170, in start_tag
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 169, in startElementNS
  File "C:\PROGRAM FILES\ALIBRE DESIGN 26.0.0.26040\PROGRAM\ADDONS\ALIBRESCRIPT\PythonLib\xml\sax\saxutils.py", line 108, in write
TypeError: expected long, got NoneType
>>>

Had not looked deeper into yet...

Regards
Stefan
Click to expand...
Hey Stefan any luck with this?
 
NateLiquidGravity

NateLiquidGravity

Alibre Super User
My Google searching shows that there were problems discovered trying to use openpyxl on regular ironpython years ago and at the time it was not fixed. There may be other methods you can use though since all the standard .net can be used with ironpython.
 
E

eboves

Member
NateLiqGrav said:
My Google searching shows that there were problems discovered trying to use openpyxl on regular ironpython years ago and at the time it was not fixed. There may be other methods you can use though since all the standard .net can be used with ironpython.
Click to expand...
Thanks a lot for the info NateLiqGrav!!!!
 
idslk

idslk

Alibre Super User
eboves said:
Hey Stefan any luck with this?
Click to expand...

I've found a script in an old assembly where parameter have been written into a workbook with openpyxl. The assembly was made with a version from 2019...
This time it was possible to use Openpyxl from AlibreScript. I tested the same assembly today withV25, V26 and V27 and the actual error occured. So something between the "old" Alibre and the newer versions must have change...

Regards
Stefan
 
E

eboves

Member
idslk said:
I've found a script in an old assembly where parameter have been written into a workbook with openpyxl. The assembly was made with a version from 2019...
This time it was possible to use Openpyxl from AlibreScript. I tested the same assembly today withV25, V26 and V27 and the actual error occured. So something between the "old" Alibre and the newer versions must have change...

Regards
Stefan
Click to expand...
I see, thanks a lot Stefan for all the help. I will use another library instead.
 
You must log in or register to reply here.
Top