Excel formula with double quote with C#

Question

I would like to write a formula in a Excel cell with SpreadSightLight in C#. I have a following formula in excel

=COUNTIFS(Sheet2!E:E, D2, Shet2!A:A, ">0")

In the code it looks

"=COUNTIFS({0}!E:E,{1},{0}!A:A ,\">0\")";

I save the file from the code, but when i try to open the file Excel (2013) gives error message and the formula is removed

Removed Records: Formula from /xl/worksheets/sheet3.xml part

and the sheet3.xml contains the following line

COUNTIFS(Sheet2!E:E, D2, Sheet2!A:A ,&quot;&gt;0&quot;)

I tried the verbatim string and the char(34) solutions also but causes the same error.

How can I solve this problem? Thanks in advance.

I have never heard of SpreadSightLight and so I googled it... I only got 5 results all pointing to only this question... — chancea
– chancea, Commented Aug 2, 2013 at 15:14
Hi, @KazJaw. The complete line of code is: string formula = "=COUNTIFS({0}!E:E,{1},{0}!A:A ,\">0\")"; and the code to write: Write(string.Format(formula, sheetName, SLConvert.ToCellReference(StartRow, StartColumn + 2)), style2); (where 'Write' is a own method to use 'SetCellValue' and 'SetCellStyle' together) — eran
– eran, Commented Aug 5, 2013 at 6:53

eran · Accepted Answer · 2013-08-05 10:29:29Z

1

I have not succeed to find the reason why there can be no double quotes written into an excel formula. However there is a solution to avoid double quotes:

string formula = "=COUNTIFS({0}!E:E, {1}, {0}!A:A , CONCATENATE(CHAR(62), CHAR(48)))";

answered Aug 5, 2013 at 10:29

eran

214 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

COG · Accepted Answer · 2016-01-07 02:56:52Z

0

In case anybody subsequently comes across this thread, I believe the problem is the greater than sign. It's getting confused with the end tag in XML. So it needs to be replaced by &gt (as hinted at by what you said your sheet3.xml contains). I think the double quotes are fine as you have them - escaped with a preceding backslash.

answered Jan 7, 2016 at 2:56

COG

3212 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel formula with double quote with C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related