The Syncfusion native Blazor components library offers 70+ UI and Data Viz web controls that are responsive and lightweight for building modern web apps.
.NET PDF framework is a high-performance and comprehensive library used to create, read, merge, split, secure, edit, view, and review PDF files in C#/VB.NET.
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()
YGYavanaarasi G Syncfusion Team 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
ALAymiee LeeSeptember 15, 2009 07:04 PM UTC
what if I want to detect column types and ComputedFormulaValues, how can I do that for the exportdatatable?