title: Writing both the value and formula using openpyxl?
body: <p>I'm wondering whether it's possible to write both the value and the formula for a cell using openpyxl or some other method. For example, I'd like to store both something like '=SUM(A1:A5)' as well as its value, say 100. More specifically, I'd like to fill out both the <code>&lt;f&gt;</code> element and the <code>&lt;v&gt;</code> element in the XML file (like it's described <a href="https://docs.microsoft.com/en-us/office/open-xml/working-with-formulas" rel="nofollow noreferrer">here</a>).</p>
  <p>I'm guessing it's not possible using just openpyxl since for reading in an Excel file one needs to specify if it's value only or not, and one writes both values and formulas to the &quot;value&quot; attribute of a cell.</p>
  <p>What's the best way to do this? Is it possible? Any ideas are welcome.</p>
  <p>As a side note, I'm not particularly worried that the value is correct, i.e., matches Excel's computation of the formula. I'm using pycel to evaluate the cell.</p>
  <p>Here's an example:</p>
  <pre class="lang-py prettyprint-override"><code>from openpyxl import Workbook
  import xml.etree.ElementTree as ET
  from zipfile import ZipFile
  
  wb = Workbook()
  ws = wb.active
  ws['A1'], ws['A2'] = 1, 2
  ws['A3'] = '=SUM(A1:A2)'
  ws['A4'] = '=SWITCH(A1:A2, 1, 2, 2, 1)'
  
  filename = 'testing_xml.xlsx'
  wb.save(filename)
  
  with ZipFile(filename, 'r') as zip_obj:
     zip_obj.extractall()
     tree = ET.parse('xl/worksheets/sheet1.xml')
  
  for elem in tree.iter():
      print(elem.tag, elem.attrib, elem.text)
  </code></pre>
  <p>I'm wondering if there's a straightforward way to set the <code>v</code> tags below for the formula cells <code>A3</code> and <code>A4</code>.</p>
  <pre><code>{http://schemas.openxmlformats.org/spreadsheetml/2006/main}worksheet {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}outlinePr {'summaryBelow': '1', 'summaryRight': '1'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}pageSetUpPr {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}dimension {'ref': 'A1:A4'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetViews {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetView {'workbookViewId': '0'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}selection {'activeCell': 'A1', 'sqref': 'A1'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetFormatPr {'baseColWidth': '8', 'defaultRowHeight': '15'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}row {'r': '1'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}c {'r': 'A1', 't': 'n'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}v {} 1
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}row {'r': '2'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}c {'r': 'A2', 't': 'n'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}v {} 2
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}row {'r': '3'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}c {'r': 'A3'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}f {} SUM(A1:A2)
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}v {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}row {'r': '4'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}c {'r': 'A4'} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}f {} SWITCH(A1:A2, 1, 2, 2, 1)
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}v {} None
  {http://schemas.openxmlformats.org/spreadsheetml/2006/main}pageMargins {'left': '0.75', 'right': '0.75', 'top': '1', 'bottom': '1', 'header': '0.5', 'footer': '0.5'} None
  </code></pre>