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 can use the OpenXML SDK to read Xlsx files.

To do this you need to add a reference to the OpenXML library which can be done via the nuget package (you'll also need a reference to WindowsBase). You then need to load the spreadsheet, find the sheet you're interested in and iterate the cells.

Each Cell has a CellFormula property which will be non-null if there is a formula in that cell.

As an example, the following code will iterate each cell and output a line for any cell that has a formula. it will return true if any cell has a formula in it; otherwise it will return false:

public static bool OutputFormulae(string filename, string sheetName)
{
    bool hasFormula = false;

    //open the document
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        //get the workbookpart
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //get the correct sheet
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
        if (sheet != null)
        {
            //get the corresponding worksheetpart
            WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

            //iterate the child Cells
            foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
            {
                //check for a formula
                if (cell.CellFormula != null && !string.IsNullOrEmpty(cell.CellFormula.Text))
                {
                    hasFormula = true;
                    Console.WriteLine("Cell {0} has the formula {1}", cell.CellReference, cell.CellFormula.Text);
                }
            }
        }
    }

    return hasFormula;
}

This can be called with the name of the file and the name of the sheet you're interested in although it would be trivial to update the code to iterate all sheets. An example call:

bool formulaExistsInSheet = OutputFormulae(@"d:\test.xlsx", "Sheet1");
Console.WriteLine("Formula exists? {0}", formulaExistsInSheet);

An example output from the above:

Cell C1 has the formula A1+B1
Cell B3 has the formula C1*20
Formula exists? True

If you're only interested if there are any cells in the sheet that have a formula you can simplify the above by using the Any extension method:

public static bool HasFormula(string filename, string sheetName)
{
    bool hasFormula = false;
    //open the document
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        //get the workbookpart
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //get the correct sheet
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
        if (sheet != null)
        {
            //get the corresponding worksheetpart
            WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

            hasFormula = worksheetPart.Worksheet.Descendants<Cell>().Any(c =>
                c.CellFormula != null && !string.IsNullOrEmpty(c.CellFormula.Text));
        }
    }

    return hasFormula;
}

Answer 2

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 3

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

Answer 4

If your excel file is .xlsx, than, since .xlsx is really a zip archive, you can read xl\calcChain.xml inside it. This file contains entries like this:

<c r="G3" i="1" l="1"/><c r="A3" i="1" l="1"/>

In this example there are formulas in cells G3 and A3. So you can do something like this:

    // Add references for
    // System.IO.Compression
    // System.IO.Compression.FileSystem

    private static List<string> GetCellsWithFormulaInSheet(string xlsxFileName, int sheetNumber)
    {
        using (var zip = System.IO.Compression.ZipFile.OpenRead(xlsxFileName))
        {
            var list = new List<string>();

            var entry = zip.Entries.FirstOrDefault(e => e.FullName == "xl/calcChain.xml");
            if (entry == null)
                return list;

            var xdoc = XDocument.Load(entry.Open());
            XNamespace ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";

            return xdoc.Root.Elements(ns + "c")
                .Select(x => new { Cell = x.Attribute("r").Value, Sheet = int.Parse(x.Attribute("i").Value) })
                .Where(x => x.Sheet == sheetNumber)
                .Select(x => x.Cell)
                .ToList();
        }
    }

And then use this method like this:

var cellsWithFormula = GetCellsWithFormulaInSheet(@"c:\Book.xlsx", 1);
bool hasFormulaInSheet = cellsWithFormula.Any();

Answer 5

i used Apache Poi Library... which has below relevant method

if(cell.getCellType()==CellType.CELL_TYPE_FORMULA)
{
// this cell contains formula......
}