CODEWITHSUNDEEP
xmlopenpyxlpycel
Iulia Gheorghita
Iulia Gheorghita

Reputation: 11

Writing both the value and formula using openpyxl?

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 <f> element and the <v> element in the XML file (like it's described here).

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 "value" attribute of a cell.

What's the best way to do this? Is it possible? Any ideas are welcome.

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.

Here's an example:

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)

I'm wondering if there's a straightforward way to set the v tags below for the formula cells A3 and A4.

{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

Upvotes: 1

Views: 145

Answers (0)

Related Questions