Cell Formula Value

[Vidya Jayakumar]

Hi,

I want to find the last cell without any values in column A. Suppose we say there are values
which was returned by the formula in column A from 1 st row to 15th row (like 1.1,1.3 etc returned by formula).
There are still more rows left in column A without values but the formula in place (lets say this kind of rows ends at row 20).
It is a lookup formula like this VLOOKUP(B16,FCE_2,FALSE)

When i do
Dim range1 As IRange = sheet1.UsedRange(1, 1, 20, 1)
Dim counter As Int32 =0
For Each cell As IRange In
range1.Rows()
counter += 1
If cell.DisplayText = "" Then
Exit For
End If
Next
When i am looping through each cell in this "range1.rows " row 1 to row 15 will return 1.1,1.3 and so on.
What method should i use to find in each cell when it will not return this value
i.e.When i am trying to read 16th row
cell.FormulaStringValue
or
cell.FormulaNumberValue should return " " or whatever method that is?
only then i will know that row in that column does not have any value in it.

I know when i want to export the rows to a datatable i have an option to use
ExcelExportDataTableOptions.ComputedFormulaValues in Exportdatatable()

I hope you can understand the above example.

Thanks,
Vidya

[Yavanaarasi G]

Hi Vidhya,

If you want to find the cell that contains formula you have to use the "HasFormula" property. Please refer the below code:

[VB]
For i As Integer = 0 To sheet.UsedRange.Rows.Length - 1
For j As Integer = 0 To sheet.UsedRange.Columns.Length - 1
Dim cell As IRange = sheet.Range(i + 1, j + 1)
If cell.HasFormula = True Then
MessageBox.Show(cell.DisplayText.ToString())
End If
Next j
Next i

Also to export the datatable with the options "ComputedFormulaValues" you need Essential Calculate along with Essential XlsIO for retrieving the computed value.

Here is the code snippet:

[VB]
'Refresh the calculation engine. The second parameter is true since this is the first time we are calling this.RefreshCalcEngine

Me.RefreshCalcEngine(workbook, True)
'Export
Dim export As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues)

Please try this and let me know if this helps.

Regards,
G.Yavana

[Aymiee Lee]

what if I want to detect column types and ComputedFormulaValues, how can I do that for the exportdatatable?