Working with Data

Return Formatted Data | Return Unformatted Data | Copy, Move, Swap, Delete Data | Sort Data | Add Formulas to a Spreadsheet | Allow Users to Enter Formulas | Create and Use Custom Functions | Search for Data

Return Formatted Data
You can return formatted data using different properties or methods, depending on the data you want to return. Clip: Use to return a block of data Text: Use to return data for a cell GetText: Similiar to the Text property, but a much faster method The following example returns the text from cell B2 fpSpread1.GetText 1, 2, varText

Return Unformatted Data
You can return unformatted data using different properties or methods, depending on the data you want to return. ClipValue: Use to return a block of data Value: Use to return data for a cell GetFloat, GetInteger: Use to return data for numeric cells The following example returns the value of cell B2. fpSpread1.Col = 1 fpSpread1.Row = 2 strText = fpSpread1.Value

Copy, Move, Swap, Delete Data
Your user can copy, move, swap and delete data in the Spread or you can programatically do this through code. This example copies the first 2 rows and columns to column 1 beginning on row 4. Spread also supports the clipboard so you can copy data using the shortcut keys. fpSpread1.CopyRange 1, 1, 5, 5, 1, 10

Sort Data
You can sort spreadsheet data using up to 256 sort keys. To sort data, first specify the data you want to sort, then specify which column or row you want to sort by, the priority of that column or row in the sort (sort by that column or row first, second, third, and so on), and whether you want to sort in ascending or descending order. The following example sorts the data on column 2 fpSpread1.SortKey(1) = 2 fpSpread1.SortKeyOrder(1) = SS_SORT_ORDER_ASCENDING fpSpread1.Sort -1, -1, -1, -1, SortByRow

Add Formulas to a Spreadsheet
You can define formulas (mathematical expressions) for currency, number, and percent cells in a spreadsheet. The fpSpread control includes over 100 mathematical, logical, and statistical functions. The following example sums the range of cells A1 through A4 and places the total in cell B2. fpSpread1.Row = 2 fpSpread1.Col = 2 fpSpread1.Formula = "SUM(A1:A4)"

Allow Users to Enter Formulas
By default, users cannot enter formulas in cells in the spreadsheet. If you prefer, you can let users enter formulas in currency, number, and percent cells. fpSpread1.AllowUserFormulas = True

Search for Data
You can search the spreadsheet for data by searching a specified column or row. You can also specify search options, such as whether to search for partial matches or to ignore text case. The following example searches column 2 for "FarPoint" and displays the found row number in a message box. ret = fpSpread1.SearchCol(2, 1, fpSpread1.MaxRows, "FarPoint", SearchFlagsNone) MsgBox "FarPoint was found in row " & ret, 0, "Search"

Working with Data