Write both formula and value in openpyxl

formula.py

from zipfile import ZipFile, ZIP_DEFLATED

from openpyxl import LXML, Workbook, load_workbook
from openpyxl.cell._writer import _set_attributes
from openpyxl.comments.comment_sheet import CommentRecord
from openpyxl.compat import safe_string
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
from openpyxl.worksheet._writer import WorksheetWriter
from openpyxl.writer.excel import ExcelWriter
from openpyxl.xml.functions import Element, SubElement, whitespace, XML_NS, REL_NS


def etree_write_cell(xf, worksheet, cell, styled=None):
    value, attributes = _set_attributes(cell, styled)

    el = Element("c", attributes)
    if value is None or value == "":
        xf.write(el)
        return

    if cell.data_type == 'f':
        shared_formula = worksheet.formula_attributes.get(cell.coordinate, {})
        formula = SubElement(el, 'f', shared_formula)

        actual_value = None
        if isinstance(value, str):
            parts = value.rsplit(sep=";", maxsplit=1)
            if len(parts) > 1:
                # The string contains both a formula and value
                value, actual_value = parts

        if value is not None:
            formula.text = value[1:]
            value = None

        if actual_value:
            cell_content = SubElement(el, 'v')
            cell_content.text = safe_string(actual_value)

    if cell.data_type == 's':
        inline_string = SubElement(el, 'is')
        text = SubElement(inline_string, 't')
        text.text = value
        whitespace(text)


    else:
        cell_content = SubElement(el, 'v')
        if value is not None:
            cell_content.text = safe_string(value)

    xf.write(el)


def lxml_write_cell(xf, worksheet, cell, styled=False):
    value, attributes = _set_attributes(cell, styled)

    if value == '' or value is None:
        with xf.element("c", attributes):
            return

    with xf.element('c', attributes):
        if cell.data_type == 'f':
            shared_formula = worksheet.formula_attributes.get(cell.coordinate, {})
            actual_value = None

            if isinstance(value, str):
                parts = value.rsplit(sep=";", maxsplit=1)
                if len(parts) > 1:
                    # The string contains both a formula and value
                    value, actual_value = parts

            with xf.element('f', shared_formula):
                if value is not None:
                    xf.write(value[1:])
                    value = None

            if actual_value:
                with xf.element("v"):
                    xf.write(safe_string(actual_value))

        if cell.data_type == 's':
            with xf.element("is"):
                attrs = {}
                if value != value.strip():
                    attrs["{%s}space" % XML_NS] = "preserve"
                el = Element("t", attrs) # lxml can't handle xml-ns
                el.text = value
                xf.write(el)
        else:
            with xf.element("v"):
                if value is not None:
                    xf.write(safe_string(value))


if LXML:
    write_cell = lxml_write_cell
else:
    write_cell = etree_write_cell


class WorksheetFormulaWriter(WorksheetWriter):
    def write_row(self, xf, row, row_idx):
        attrs = {'r': f"{row_idx}"}
        dims = self.ws.row_dimensions
        attrs.update(dims.get(row_idx, {}))

        with xf.element("row", attrs):

            for cell in row:
                if cell._comment is not None:
                    comment = CommentRecord.from_cell(cell)
                    self.ws._comments.append(comment)
                if (
                    cell._value is None
                    and not cell.has_style
                    and not cell._comment
                    ):
                    continue
                write_cell(xf, self.ws, cell, cell.has_style)


class ExcelFormulaWriter(ExcelWriter):
    def write_worksheet(self, ws):
        ws._drawing = SpreadsheetDrawing()
        ws._drawing.charts = ws._charts
        ws._drawing.images = ws._images
        if self.workbook.write_only:
            if not ws.closed:
                ws.close()
            writer = ws._writer
        else:
            writer = WorksheetFormulaWriter(ws)
            writer.write()

        ws._rels = writer._rels
        self._archive.write(writer.out, ws.path[1:])
        self.manifest.append(ws)
        writer.cleanup()


def save_formula_workbook(workbook, fp):
    archive = ZipFile(fp, 'w', ZIP_DEFLATED, allowZip64=True)
    writer = ExcelFormulaWriter(workbook, archive)
    writer.save()
    return True


if __name__ == '__main__':
    # Test
    wb = Workbook()
    ws = wb.active

    ws['A1'] = 42

    cell = ws.cell(row=2, column=1)
    cell.value = "=A1;42"

    # Save the file
    filename = "formula.xlsx"
    save_formula_workbook(wb, filename)

    # Reload the file and check if the value has been set
    wb = load_workbook(filename, data_only=True)
    ws = wb.active
    print('A1', ws['A1'].value)
    print('A2', ws['A2'].value)