How we can find "formula" cells in excel using spire xls using c#?

Question

0.00/5 (No votes)

See more:

How we can find "formula" cells in excel using spire xls using c#?

How we can find "formula" cells in excel using spire xls using c#?

What I have tried:

object test = sheet.Rows[0]["A1"].FormulaValue;
object test1 = sheet.Rows[0]["A1"].Formula;
object test2 = sheet.Rows[0]["A1"].FormulaStringValue;

object test3 = sheet.Rows[0]["A1"].FormulaR1C1;
object test14 = sheet.Rows[0]["A1"].FormulaErrorValue;
object test25 = sheet.Rows[0]["A1"].FormulaNumberValue;

Posted 29-Feb-16 19:16pm

Indukanth

Updated 1-Mar-16 21:17pm

Add a Solution

Comments

Herman<T>.Instance 1-Mar-16 3:30am

Have you tried EPPLUS?

Indukanth 1-Mar-16 3:48am

Hi sir,
actually my entire project is working with this Spire.XLS, now i got a new requirement for find the formula cells.
thats why im using this.

Herman<T>.Instance 1-Mar-16 4:12am

Can you detect the Range used in the sheet?

Indukanth 1-Mar-16 5:13am

i tried the below snippet but didn't get any results..

var temp= sheet.Range["A1"].Formula;
if (sheet.Range[1, 1].FormulaBoolValue)
{
MessageBox.Show("test");
}
im not too familiar range.
how can go through this?

Herman<T>.Instance 1-Mar-16 5:15am

A Range is an area that contains multiple Columns and or ROWS. So try to detect how c# can select that Range (A1 is only 1 cell) like A1:Z25.
Then iterate over Rows and Columns

Indukanth 1-Mar-16 5:23am

ok sure.. i will try and let you know. :)
thankyou.

2 solutions

Solution 2

Try this code snippets:

C#

foreach (CellRange cr in sheet.Range["A1:C4"].Cells)
{
    if(cr.HasFormula)
    {
       //..
    }
}

Posted 1-Mar-16 21:17pm

Greg Barbados

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-03-01T04:45:00

Solution 1

A statement sheet.Rows[0]["A1"].Formula; returns variant (type of string). If if starts with "=", this means that cell contains formula.
See: Range.Formula Property (Excel)[^]

Posted 1-Mar-16 4:45am

Maciej Los

Comments

Indukanth 1-Mar-16 23:06pm

Hi Maciej Los im using Spire.XLS.
I think i tried this code. but it doesn't return any result.

Maciej Los 2-Mar-16 12:16pm

string s = sheet.Rows[0]["A1"].Formula; if(s.StartsWith("=")) {Console.WriteLine("Contains formula!");}