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")

mystring = "=name(parameter)"

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

from openpyxl import Workbook

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

(Oct-28-2022, 09:39 PM)Pedroski55 Wrote: 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.

(Oct-28-2022, 10:22 PM)rob101 Wrote: 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.

Quote: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.

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")

(Oct-30-2022, 05:46 PM)deanhystad Wrote: 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.

Quote: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.

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")

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

(Oct-31-2022, 03:55 PM)deanhystad Wrote: 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.

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