C# – How to check a Cell contains formula or not in Excel through oledb reader or excel library, excel datareader or NPOI etc (Except Interop)

asp.netc++exceloledbwinforms

How to check a Cell contains formula or not in Excel through oledb reader ?

enter image description here

System.Data.OleDb.OleDbConnection conn2 = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=NO;IMEX=1\";");
conn2.Open();
string strQuery2 = "SELECT * FROM [" + Table + "]";

System.Data.OleDb.OleDbDataAdapter adapter2 = new System.Data.OleDb.OleDbDataAdapter(strQuery2, conn2);

System.Data.DataTable DT2 = new System.Data.DataTable();

adapter2.Fill(DT2);

Best Solution

You may explore this : Range.HasFormula under com-interop.

I also noticed there's a post that can be improvised to cater your needs.

Here's a skeleton - not the exact syntax.

Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);
Excel.WorkSheet WS = workBooks.WorkSheets("Sheet1");

Range rangeData = WS.Range["A1:C3"];    

foreach (Excel.Range c in rangeData.Cells)
{
    if (c.HasFormula)
    {
       MessageBox.Show(Convert.ToString(c.Value));
    }        
}

Not sure how you can achieve such with OLEDB, since your query just seems to just grab cell data (texts, numbers, without formulas) into the query.

If you must use OLEDB, this post can be helpful to start. If you still need assistance, feel free to comment.

Related Question