Transforming XML into MS Excel XML - formula syntax

Question

I am transforming some XML to view in MS Excel. There is a very good reference on how to create the schema - http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx. This blog is also useful.

I am having trouble inserting a formula into a cell, so: <Cell ss:Index="2" ss:Formula="=SUM(R[-3]C[0]:R[-1]C[0])"> <Data ss:Type="Number"></Data> </Cell> is pretty straightforward. This will sum the values in the row 3 rows above to the row 1 above.

However, the formula I need (in A1 style format, rather than R1C1) is =SUMIF(A:A,"Assignments",B:B).

I have tried many options, including

• ss:Formula="=SUMIF(C[-1]:C[-1],Assignments,C[0]:C[0])" but the definition of the cell equals =SUMIF(A:A,Assignments,B:B) in this case.

• ss:Formula="=SUMIF(C[-1]:C[-1],"Assignments",C[0]:C[0])" but the workbook becomes corrupt and won't open.

• ss:Formula="=SUMIF(C[-1]:C[-1],'Assignments',C[0]:C[0])" but the definition of the cell equals 0 in this case.

The quotation marks are causing a problem. Does anyone know the correct syntax to get the required formula insert into a cell?

Answer 1

The answer is to be aware of XML document escape characters. What characters do I need to escape in XML documents?

The line in the code needed was:

ss:Formula="=SUMIF(C1:C1, "Assignments",C[0]:C[0])"