How to get cell(x,y).value = "\'=name(p)" to show correctly ??

[ozlevia]

Creating an excel file that is designed to be processed by another package (OpenL).
I am trying to enter into a call the string:
=name(parameter)
With the intent of preventing excel from evaluation name. I do that by writing
'=name(parameter).

However, when the excel file is written, saved and opened, what I expect is to see:
=name(parameter)
and if the cell is selected to see:
'=name(parameter)

Instead even without clicking on the cell I see:
'=name(parameter)

What do I have to do while updating the value of the cell to get this to work?

This is the code (simplified):

def write_to_file(): # write the xlsx file.
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.cell(1,1).value = "\'" + "=" + "name" + "(parameter)"
    wb.save("test.xlsx")

#-----------------------------------------------------

For some reason excel 'ignores' the ' I add before the =. and treats the whole thing as a string.
As soon as I click on the cell, it snaps to the right form i.e. =name(parameter).

I spend hours trying to add \n; \r; and \t. Nothing worked.

Larz60+ write Oct-28-2022, 01:28 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Attempted to do this for you once. Please use BBCode tags on future posts.

[Pedroski55]

Not sure what you are trying to do. Insert a formula??

openpyxl does not have 100% support for formulas I read.

Maybe you just need this

mystring = "=name(parameter)"

I believe Excel will interpret a single apostrophe you place before the = sign as indicating the beginning of a number.

Or maybe the apostrophe needs a partner? They usually come in pairs!

If I just put this in Excel, =name(parameter) that cell shows: #NAME? presumably because XL doesn't know what the function name() is.

[rob101]

I've not used the openpyxl module, but I have been doing some reading on it, as I plan to use it, but from what I understand (and I could be wrong) I'd try something like:

from openpyxl import Workbook  
wb = Workbook()
sheet = wp.active
sheet['A1'] = f"{=name(parameter)}"

... or something along those lines.

I may even install the module and try it, so that I can see for myself what the issue is.

---

Okay, I've tried this:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet['A1'] = "'\=name(parameter)"
wb.save("test.xlsx")

... and A1 shows as '\=name(parameter)

Is that what you wanted?

---

Update:
Thinking about your code some more, the issue you have, is that in Python, the backslash is a 'special' character, as in \t for a tab. or \n for a newline. If you want a literal backslash, you need to escape it, which is yet another use for backslash, so your code would need to be sheet.cell(1, 1).value = "\\'" + "=" + "name" + "(parameter)".

As you can see, that looks a little clumsy (as does all string concatenation), which is one reason not to use such code.

[ozlevia]

Not sure what you are trying to do. Insert a formula??

openpyxl does not have 100% support for formulas I read.

Maybe you just need this

mystring = "=name(parameter)"

I believe Excel will interpret a single apostrophe you place before the = sign as indicating the beginning of a number.

Or maybe the apostrophe needs a partner? They usually come in pairs!

If I just put this in Excel, =name(parameter) that cell shows: #NAME? presumably because XL doesn't know what the function name() is.

The Excel file I am writing, is intended to be read by an Open Source Rules System called OpenL. As such it is not intended to be manipulated by a person. You observation is correct on two counts. Using the format you suggested DOES create the right entry in the right cell. BUT as soon as you 'click' on the cell, it turns into "#NAME?" For my needs, this good enough. I will continue to look into more robust solutions.

[ozlevia]

I've not used the openpyxl module, but I have been doing some reading on it, as I plan to use it, but from what I understand (and I could be wrong) I'd try something like:

from openpyxl import Workbook  
wb = Workbook()
sheet = wp.active
sheet['A1'] = f"{=name(parameter)}"

... or something along those lines.

I may even install the module and try it, so that I can see for myself what the issue is.

---

Okay, I've tried this:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet['A1'] = "'\=name(parameter)"
wb.save("test.xlsx")

... and A1 shows as '\=name(parameter)

Is that what you wanted?

---

Update:
Thinking about your code some more, the issue you have, is that in Python, the backslash is a 'special' character, as in \t for a tab. or \n for a newline. If you want a literal backslash, you need to escape it, which is yet another use for backslash, so your code would need to be sheet.cell(1, 1).value = "\\'" + "=" + "name" + "(parameter)".

As you can see, that looks a little clumsy (as does all string concatenation), which is one reason not to use such code.

This is useful. thank you. I must have not explained my problem well. I need to create the construct =name() WITHOUT having excel evaluate it as a function. Typically, when one writes excel files manually the construct used is '=name(). Since my excel file is an input to another package (OpenL) I can use just =name().

[deanhystad]

You need to set the data_type for the cell.

I am not an excel user, so I had to look up what the single quote does.

A single quote (or an apostrophe) is used to signify Text in Excel. A number entered with the leading apostrophe is treated as Text and (generally) cannot be used in calculations. Often used for ZIP codes, phone numbers, Invoice or Product code numbers.

Then it was just a matter of finding what cell attribute is changed when you enter a formula with a leading single quote. I made a spreadsheet that contained a formula, and the same formula as text. Then I ran a Python program to open the worksheet and looked at all the attributes for each of the cells.

Using what I learned I modified your example to set the value and type.

import openpyxl

wb = openpyxl.Workbook()
cell = wb.active["A1"]
cell.value = "=name(parameter)"   # single quote is not needed because
cell.data_type = "s"              # entering single quote sets data type to Text
wb.save("test.xlsx")

When I open the spreadsheet I see =name(parameter) displayed in the cell, and when I click on the cell, it is displayed '=name(parameter) in the editor.

This is your example with a tweak to set the data type.

[ozlevia]

You need to set the data_type for the cell.

I am not an excel user, so I had to look up what the single quote does.

A single quote (or an apostrophe) is used to signify Text in Excel. A number entered with the leading apostrophe is treated as Text and (generally) cannot be used in calculations. Often used for ZIP codes, phone numbers, Invoice or Product code numbers.

Then it was just a matter of finding what cell attribute is changed when you enter a formula with a leading single quote. I made a spreadsheet that contained a formula, and the same formula as text. Then I ran a Python program to open the worksheet and looked at all the attributes for each of the cells.

Using what I learned I modified your example to set the value and type.

import openpyxl

wb = openpyxl.Workbook()
cell = wb.active["A1"]
cell.value = "=name(parameter)"   # single quote is not needed because
cell.data_type = "s"              # entering single quote sets data type to Text
wb.save("test.xlsx")

When I open the spreadsheet I see =name(parameter) displayed in the cell, and when I click on the cell, it is displayed '=name(parameter) in the editor.

This is your example with a tweak to set the data type.

Thank you! This does work. I do NOT see a '=, only =. BUT since this file is is processed by another compiler, it works just fine.

[deanhystad]

I think this might be what you want.

import openpyxl

def set_cell_text(cell, text):
    cell.value = text
    cell.data_type = "s"
    cell.quotePrefix = True

wb = openpyxl.load_workbook("data.xlsx")
set_cell_text(wb.active["B3"], "=A1+A2")
wb.active["B4"] = "=A1+A2"
wb.save("data2.xlsx")

For testing I made a small spreadsheet named "data.xlsx".

Output:
A1: 3, B1: =A1+A2
A2: 4, B2: '=A1+A2

Running the program creates a new spreadsheet named "data2.xlsx" with new cells A3 which acts just like A2 (text) and A4 which acts just like A1 (formula), all the way down to showing the leading quote for text cells.

[ozlevia]

I think this might be what you want.

import openpyxl

def set_cell_text(cell, text):
    cell.value = text
    cell.data_type = "s"
    cell.quotePrefix = True

wb = openpyxl.load_workbook("data.xlsx")
set_cell_text(wb.active["B3"], "=A1+A2")
wb.active["B4"] = "=A1+A2"
wb.save("data2.xlsx")

For testing I made a small spreadsheet named "data.xlsx".

Output:
A1: 3, B1: =A1+A2
A2: 4, B2: '=A1+A2

Running the program creates a new spreadsheet named "data2.xlsx" with new cells A3 which acts just like A2 (text) and A4 which acts just like A1 (formula), all the way down to showing the leading quote for text cells.

This IS the right solution. I did not know about "quotePrefix". But that is EXACTLY what I intended. Thank you!!!!
I hope others will find it useful too.
https://python-forum.io/images/smilies/smile.png

[deanhystad]

If you are interested, this is the program I used to peek around in the workbook.

import openpyxl
import pandas as pd

def set_cell_text(cell, text):
    cell.value = text
    cell.data_type = "s"
    cell.quotePrefix = True

wb = openpyxl.load_workbook("data.xlsx")
set_cell_text(wb.active["B3"], "=A1+A2")
wb.active["B4"] = "=A1+A2"
wb.save("data2.xlsx")

cell_attributes = (
    "coordinate",
    "comment",
    "encoding",
    "data_type",
    "encoding",
    "has_style",
    "hyperlink",
    "internal_value",
    "is_date",
    "number_format",
    "pivotButton",
    "quotePrefix",
    "style",
    "style_id",
    "value")

cells = {}
for address, title in zip(("B1", "B4", "B2", "B3"), ("Formula", "My Formula", "Text", "My Text")):
    cell = wb.active[address]
    cells[title] = {attr:getattr(cell, attr) for attr in cell_attributes}

df = pd.DataFrame(cells)
print(df)

Output:
                Formula My Formula     Text  My Text
coordinate           B1         B4       B2       B3
comment            None       None     None     None
encoding          utf-8      utf-8    utf-8    utf-8
data_type             f          f        s        s
has_style         False      False     True     True
hyperlink          None       None     None     None
internal_value   =A1+A2     =A1+A2   =a1+a2   =A1+A2
is_date           False      False    False    False
number_format   General    General  General  General
pivotButton       False      False    False    False
quotePrefix       False      False     True     True
style            Normal     Normal   Normal   Normal
style_id              0          0        1        1
value            =A1+A2     =A1+A2   =a1+a2   =A1+A2