We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Cell Formula Value

Thread ID:

Created:

Updated:

Platform:

Replies:

75357 Jul 23,2008 02:33 AM UTC Sep 15,2009 07:04 PM UTC ASP.NET Web Forms (Classic) 2
loading
Tags: XlsIO
Vidya Jayakumar
Asked On July 23, 2008 02:33 AM UTC

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 [Syncfusion]
Replied On July 28, 2008 11:46 AM UTC

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
Replied On September 15, 2009 07:04 PM UTC

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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;