Styjun

Where should I draw the line on follow up questions from previous employer

Are spot colors limited and why CMYK mix is not treated same as spot color mix?

Small RAM 4 KB on the early Apple II?

Can copper pour be used as an alternative to large traces?

Eliminate key lookup in execution plan

Can I lend a small amount of my own money to a bank at the federal funds rate?

Is this homebrew "Faerie Fire Grenade" unbalanced?

How can I portray a character with no fear of death, without them sounding utterly bored?

Was a six-engine 747 ever seriously considered by Boeing?

Can authors email you PDFs of their textbook for free?

Break down the phrase "shitsurei shinakereba naranaindesu"

Why does Sauron not permit his followers to use his name?

What's the origin of the concept of alternate dimensions/realities?

Why do presidential pardons exist in a country having a clear separation of powers?

Is it good practice to speed up and slow down where not written in a song?

Can two aircraft be allowed to stay on the same runway at the same time?

Group riding etiquette

What am I looking at here at Google Sky?

Can I leave a large suitcase at TPE during a 4-hour layover, and pick it up 4.5 days later when I come back to TPE on my way to Taipei downtown?

Are sweatpants frowned upon on flights?

“all of who” or “all of whom”?

What is a "hashed transaction" in SQL Server Replication terminology?

What is this "opened" cube called?

LWC: Is it safe to rely on window.location.href to get the page url?

How to prevent closing parenthesis from being appended to my formula in openpyxl

How to randomly select an item from a list?How to remove an element from a list by index?How do you read from stdin?How do you append to a file in Python?How to remove a key from a Python dictionary?How to write values of a dict to an empty (new) column in openpyxl?Referencing cells in the same rowUnhide new cells with openpyxl?How to reference cells in another sheet based on multiple conditionsOpenpyxl number_format not modifying cell values derived from formula

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I am trying to provide a link that will download an autogenerated spreadsheet, and am attempting to use openpyxl to create the spreadsheet. It is almost working, except for one small detail: when I try to use a formula as the value of the cell, there is a closing parenthesis appended to the end in the saved spreadsheet. This results in a #NAME? error.

Namely, I use something like this as the value

"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

and the resulting value in the spreadsheet is something like (I open it with LibreOffice Calc)

=VLOOKUP(A1, $'lookup_table'.A1:B3, 2, 0) - B1)

Notice the extra parenthesis after the B1.

I have dug around the internet for a solution, but have kept coming up empty. Any guidance on how to make this format correctly?

minimal code example

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet(title="lookup_table")
lookup_table = "one": 1, "two": 2, "three": 3
for row, (key, value) in enumerate(lookup_table.items()):
 ws1.cell(column=1, row=row + 1, value=key)
 ws1.cell(column=2, row=row + 1, value=value)

for i, value in enumerate(["one", "two", "three"]):
 ws.cell(column=1, row=i + 1, value=value)
 ws.cell(column=2, row=i + 1, value=lookup_table[value])
 ws.cell(
 column=3,
 row=i + 1,
 value="=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1),
 )


wb.save("test.xlsx")

python openpyxl libreoffice-calc

share|improve this question

edited Mar 27 at 23:54

Joseph Gilgen

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  When writing formulae it's always worth looking at the XML source because the various programs employ lots of magic such as translations in the GUI.
  
  – Charlie Clark
  Mar 28 at 11:16
  

  
  
  
  

add a comment | 

0

I am trying to provide a link that will download an autogenerated spreadsheet, and am attempting to use openpyxl to create the spreadsheet. It is almost working, except for one small detail: when I try to use a formula as the value of the cell, there is a closing parenthesis appended to the end in the saved spreadsheet. This results in a #NAME? error.

Namely, I use something like this as the value

"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

and the resulting value in the spreadsheet is something like (I open it with LibreOffice Calc)

=VLOOKUP(A1, $'lookup_table'.A1:B3, 2, 0) - B1)

Notice the extra parenthesis after the B1.

I have dug around the internet for a solution, but have kept coming up empty. Any guidance on how to make this format correctly?

minimal code example

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet(title="lookup_table")
lookup_table = "one": 1, "two": 2, "three": 3
for row, (key, value) in enumerate(lookup_table.items()):
 ws1.cell(column=1, row=row + 1, value=key)
 ws1.cell(column=2, row=row + 1, value=value)

for i, value in enumerate(["one", "two", "three"]):
 ws.cell(column=1, row=i + 1, value=value)
 ws.cell(column=2, row=i + 1, value=lookup_table[value])
 ws.cell(
 column=3,
 row=i + 1,
 value="=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1),
 )


wb.save("test.xlsx")

python openpyxl libreoffice-calc

share|improve this question

edited Mar 27 at 23:54

Joseph Gilgen

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  When writing formulae it's always worth looking at the XML source because the various programs employ lots of magic such as translations in the GUI.
  
  – Charlie Clark
  Mar 28 at 11:16
  

  
  
  
  

add a comment | 

I am trying to provide a link that will download an autogenerated spreadsheet, and am attempting to use openpyxl to create the spreadsheet. It is almost working, except for one small detail: when I try to use a formula as the value of the cell, there is a closing parenthesis appended to the end in the saved spreadsheet. This results in a #NAME? error.

Namely, I use something like this as the value

"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

and the resulting value in the spreadsheet is something like (I open it with LibreOffice Calc)

=VLOOKUP(A1, $'lookup_table'.A1:B3, 2, 0) - B1)

Notice the extra parenthesis after the B1.

I have dug around the internet for a solution, but have kept coming up empty. Any guidance on how to make this format correctly?

minimal code example

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet(title="lookup_table")
lookup_table = "one": 1, "two": 2, "three": 3
for row, (key, value) in enumerate(lookup_table.items()):
 ws1.cell(column=1, row=row + 1, value=key)
 ws1.cell(column=2, row=row + 1, value=value)

for i, value in enumerate(["one", "two", "three"]):
 ws.cell(column=1, row=i + 1, value=value)
 ws.cell(column=2, row=i + 1, value=lookup_table[value])
 ws.cell(
 column=3,
 row=i + 1,
 value="=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1),
 )


wb.save("test.xlsx")

python openpyxl libreoffice-calc

share|improve this question

edited Mar 27 at 23:54

Joseph Gilgen

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet(title="lookup_table")
lookup_table = "one": 1, "two": 2, "three": 3
for row, (key, value) in enumerate(lookup_table.items()):
 ws1.cell(column=1, row=row + 1, value=key)
 ws1.cell(column=2, row=row + 1, value=value)

for i, value in enumerate(["one", "two", "three"]):
 ws.cell(column=1, row=i + 1, value=value)
 ws.cell(column=2, row=i + 1, value=lookup_table[value])
 ws.cell(
 column=3,
 row=i + 1,
 value="=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1),
 )


wb.save("test.xlsx")

share|improve this question

edited Mar 27 at 23:54

Joseph Gilgen

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

share|improve this question

edited Mar 27 at 23:54

Joseph Gilgen

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

asked Mar 27 at 22:43

Joseph GilgenJoseph Gilgen

2644 bronze badges

1 Answer
1

active

oldest

votes

0

So the issue actually had nothing to do with openpyxl. But was with LibreOffice.

I looked into the generated xml files, and found that value was exactly as expected. The issue is that LibreOffice Calc references another sheet as sheet2.A1 and Microsoft Excel references it as sheet2!A1. I suppose that since the sheet was being saved as a .xlsx it makes sense to use the Excel format.

The file now opens correctly in Microsoft Excel and LibreOffice Calc after updating the string from
"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

to

"=VLOOKUP(A, 'lookup_table'!A1:B3, 2, 0) - B".format(i + 1, i + 1)

share|improve this answer

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are mixing display formula representation and OOXML file format representation. According to section 18.17.2.3 of the OOXML spec the separator between a sheet name and the A1 reference part has to be '!'. Apparently, Excel also handles the non-standard '.' separator character. There are a few cases where the display formula and the file format formulas differ and you need to handle them when you create files yourself.
  
  – moggi
  Mar 28 at 19:16
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55387587%2fhow-to-prevent-closing-parenthesis-from-being-appended-to-my-formula-in-openpyxl%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

So the issue actually had nothing to do with openpyxl. But was with LibreOffice.

I looked into the generated xml files, and found that value was exactly as expected. The issue is that LibreOffice Calc references another sheet as sheet2.A1 and Microsoft Excel references it as sheet2!A1. I suppose that since the sheet was being saved as a .xlsx it makes sense to use the Excel format.

The file now opens correctly in Microsoft Excel and LibreOffice Calc after updating the string from
"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

to

"=VLOOKUP(A, 'lookup_table'!A1:B3, 2, 0) - B".format(i + 1, i + 1)

share|improve this answer

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are mixing display formula representation and OOXML file format representation. According to section 18.17.2.3 of the OOXML spec the separator between a sheet name and the A1 reference part has to be '!'. Apparently, Excel also handles the non-standard '.' separator character. There are a few cases where the display formula and the file format formulas differ and you need to handle them when you create files yourself.
  
  – moggi
  Mar 28 at 19:16
  

  
  
  
  

add a comment | 

0

So the issue actually had nothing to do with openpyxl. But was with LibreOffice.

I looked into the generated xml files, and found that value was exactly as expected. The issue is that LibreOffice Calc references another sheet as sheet2.A1 and Microsoft Excel references it as sheet2!A1. I suppose that since the sheet was being saved as a .xlsx it makes sense to use the Excel format.

The file now opens correctly in Microsoft Excel and LibreOffice Calc after updating the string from
"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

to

"=VLOOKUP(A, 'lookup_table'!A1:B3, 2, 0) - B".format(i + 1, i + 1)

share|improve this answer

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are mixing display formula representation and OOXML file format representation. According to section 18.17.2.3 of the OOXML spec the separator between a sheet name and the A1 reference part has to be '!'. Apparently, Excel also handles the non-standard '.' separator character. There are a few cases where the display formula and the file format formulas differ and you need to handle them when you create files yourself.
  
  – moggi
  Mar 28 at 19:16
  

  
  
  
  

add a comment | 

So the issue actually had nothing to do with openpyxl. But was with LibreOffice.

I looked into the generated xml files, and found that value was exactly as expected. The issue is that LibreOffice Calc references another sheet as sheet2.A1 and Microsoft Excel references it as sheet2!A1. I suppose that since the sheet was being saved as a .xlsx it makes sense to use the Excel format.

The file now opens correctly in Microsoft Excel and LibreOffice Calc after updating the string from
"=VLOOKUP(A, $'lookup_table'.A1:B3, 2, 0) - B".format(i + 1, i + 1)

to

"=VLOOKUP(A, 'lookup_table'!A1:B3, 2, 0) - B".format(i + 1, i + 1)

share|improve this answer

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

share|improve this answer

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges

answered Mar 27 at 23:52

Joseph GilgenJoseph Gilgen

2644 bronze badges