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

Question

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

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);

Answer 1

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.

Answer 2

I got solution but only in Interop Services!!

public bool IsFormulaExistInExcel(string excelpath)
     {
        bool IsFormulaExist = false;
        try
        {
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            Microsoft.Office.Interop.Excel.Workbooks workBooks = null;
            Microsoft.Office.Interop.Excel.Workbook workBook = null;
            Microsoft.Office.Interop.Excel.Worksheet workSheet;
            excelApp = new Microsoft.Office.Interop.Excel.Application();
            workBooks = excelApp.Workbooks;
            workBook = workBooks.Open(excelpath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            workSheet = workBook.Worksheets.get_Item(1);
            Microsoft.Office.Interop.Excel.Range rng = workSheet.UsedRange;


            dynamic FormulaExist = rng.HasFormula;
            Type unknown = FormulaExist.GetType();

            if (unknown.Name == "DBNull")
                IsFormulaExist = true;
            else if (unknown.Name == "Boolean")
            {
                if (FormulaExist == false)
                    IsFormulaExist = false;
                else if (FormulaExist == true)
                    IsFormulaExist = true;
            }
        }
        catch (Exception E)
        {
        }
    return IsFormulaExist;
  }