SpreadsheetGear The Performance Spreadsheet Component Company 
IValues Interface
See Also  Members   Example
SpreadsheetGear.Advanced.Cells Namespace : IValues Interface
Provides a high performance, but potentially destructive API for getting and setting the cell values and formulas of a worksheet.

Syntax

C# 
[DefaultMemberAttribute("Item")]
public interface IValues

Example

C#Copy Code
using System; 
using System.Diagnostics; 
using SpreadsheetGear; 
using SpreadsheetGear.Advanced.Cells; 
  
namespace SpreadsheetGear.AdvancedCellValueSample 
{ 
    class Program 
    { 
        // Demonstrate the use of advanced cell values APIs. 
        static void Main() 
        { 
            // Create a new workbook and get it's worksheet. 
            IWorkbook workbook = Factory.GetWorkbook(); 
            IWorksheet worksheet = workbook.Worksheets[0]; 
  
            // Get the SpreadsheetGear.Advanced.Cells.IValues 
            // interface from the worksheet. 
            IValues values = (IValues)worksheet; 
  
            // Set short month names in B1, C1 and D1 and verify 
            // using SpreadsheetGear.Advanced.Cells.IValue. 
            values.SetText(0, 1, "Jan"); 
            Debug.Assert(values[0, 1].Text.Equals("Jan")); 
            values.SetText(0, 2, "Feb"); 
            Debug.Assert(values[0, 2].Text.Equals("Feb")); 
            values.SetText(0, 3, "Mar"); 
            Debug.Assert(values[0, 3].Text.Equals("Mar")); 
  
            // Set names in A2, A3 and A4 and verify. 
            values.SetText(1, 0, "Mary"); 
            Debug.Assert(values[1, 0].Text.Equals("Mary")); 
            values.SetText(2, 0, "Mike"); 
            Debug.Assert(values[2, 0].Text.Equals("Mike")); 
            values.SetText(3, 0, "Miles"); 
            Debug.Assert(values[3, 0].Text.Equals("Miles")); 
  
            // Set some numeric values in B2:D4 and verify. 
            for (int column = 1; column <= 3; column++) 
            { 
                double columnSum = 0.0; 
                for (int row = 1; row <= 3; row++) 
                { 
                    double number = row * 10 + column; 
                    values.SetNumber(row, column, number); 
                    Debug.Assert(values[row, column].Number == number); 
                    columnSum += number; 
                } 
                // Set formulas which sum the month, verify the 
                // formula was set and verify the formula result 
                // is correct. 
                string formula = "=SUM(" 
                    + workbook.WorkbookSet.GetAddress(1, column) 
                    + ":" 
                    + workbook.WorkbookSet.GetAddress(3, column) 
                    + ")"; 
                values.SetFormula(4, column, formula); 
                IValue val = values[4, column]; 
                Debug.Assert(val != null); 
                Debug.Assert(val.HasFormula); 
                Debug.Assert(val.Formula.Equals(formula)); 
                Debug.Assert(val.Type == SpreadsheetGear.Advanced.Cells.ValueType.Number); 
                Debug.Assert(val.Number == columnSum); 
            } 
        } 
    } 
} 
    
Visual BasicCopy Code
Imports System
Imports System.Diagnostics
Imports SpreadsheetGear
Imports SpreadsheetGear.Advanced.Cells

Namespace SpreadsheetGear.AdvancedCellValueSample
_
    Class Program

        ' Demonstrate the use of advanced cell values APIs.
        Shared Sub Main()
            ' Create a new workbook and get it's worksheet.
            Dim workbook As IWorkbook = Factory.GetWorkbook()
            Dim worksheet As IWorksheet = workbook.Worksheets(0)

            ' Get the SpreadsheetGear.Advanced.Cells.IValues
            ' interface from the worksheet.
            Dim values As IValues = CType(worksheet, IValues)

            ' Set short month names in B1, C1 and D1 and verify
            ' using SpreadsheetGear.Advanced.Cells.IValue.
            values.SetText(0, 1, "Jan")
            Debug.Assert(values(0, 1).Text.Equals("Jan"))
            values.SetText(0, 2, "Feb")
            Debug.Assert(values(0, 2).Text.Equals("Feb"))
            values.SetText(0, 3, "Mar")
            Debug.Assert(values(0, 3).Text.Equals("Mar"))

            ' Set names in A2, A3 and A4 and verify.
            values.SetText(1, 0, "Mary")
            Debug.Assert(values(1, 0).Text.Equals("Mary"))
            values.SetText(2, 0, "Mike")
            Debug.Assert(values(2, 0).Text.Equals("Mike"))
            values.SetText(3, 0, "Miles")
            Debug.Assert(values(3, 0).Text.Equals("Miles"))

            ' Set some numeric values in B2:D4 and verify.
            Dim column As Integer
            For column = 1 To 3
                Dim columnSum As Double = 0.0
                Dim row As Integer
                For row = 1 To 3
                    Dim number As Double = row * 10 + column
                    values.SetNumber(row, column, number)
                    Debug.Assert((values(row, column).Number = number))
                    columnSum += number
                Next row
                ' Set formulas which sum the month, verify the
                ' formula was set and verify the formula result
                ' is correct.
                Dim formula As String = "=SUM(" + workbook.WorkbookSet.GetAddress(1, column) + ":" + workbook.WorkbookSet.GetAddress(3, column) + ")"
                values.SetFormula(4, column, formula)
                Dim val As IValue = values(4, column)
                Debug.Assert((Not (val Is Nothing)))
                Debug.Assert(val.HasFormula)
                Debug.Assert(val.Formula.Equals(formula))
                Debug.Assert((val.Type = Advanced.Cells.ValueType.Number))
                Debug.Assert((val.Number = columnSum))
            Next column
        End Sub 'Main
    End Class 'Program
End Namespace 'SpreadsheetGear.AdvancedCellValueSample

Remarks

This interface provides high performance access to the values of a worksheet. Unlike IRange, IValue and IValues do very little checking of arguments, worksheet/cell protection, formatting, or other important properties and attributes of a worksheet.

For example, SetNumber will allow you to place numbers in every cell which is occupied by a merged cell, while Value will throw an exception if you attempt to place a number in any cell except the top-left cell of a merged cell.

Using this API to simply read the values of cells in a workbook can be done without fear of destructive side affects, although unexpected exceptions are likely to be thrown if you provide invalid row or column indexes. Formula cells will be calculated normally, as needed, before a cell type or cell value is returned.

When using this API to set or clear values in a worksheet, you must be certain that what you are doing makes sense or you are likely to create a corrupt workbook. Don't replace part of an array formula with a value, or clear part of an array formula. Don't place a value into a cell which is part of a merged cell, unless you place the value in the top-left cell of the merged cell.

Formats are not considered in any way and are never modified by this API. This API will always bypass any data validation or protection.

Setting values with this API will clear pre-existing formulas or values as needed with the exceptions of the previously noted issues with array formulas and merged cells.

Setting or clearing values with this API will update the calculation state of the workbook set.

The row and column indexes passed to this API are assumed to be valid row and column indexes. Unexpected exceptions may be thrown if they are not valid. Valid indexes are greater than or equal to zero and less than MaxRows / MaxColumns.

WorkbookViews associated with the workbook set containing this worksheet will be updated as needed only if a lock is acquired before using the API and released after using the API. To improve performance when using this API in conjunction with a workbook view, use BeginUpdate and EndUpdate.

This API will not invoke WorkbookView.RangeChanged events.

If you are in doubt about whether you should use this API, use IRange instead.

Requirements

Namespace: SpreadsheetGear.Advanced.Cells

Platforms: Windows Vista, Windows XP, Windows Server 2008, Windows Server 2003, Windows 2000, Windows Me and Windows 98, including 32 bit and 64 bit editions where applicable. SpreadsheetGear for .NET 1.x requires the Microsoft .NET Framework 1.1 or .NET 2.0 (works with .NET 3.x). SpreadsheetGear for .NET 2007 requires the Microsoft .NET Framework 2.0 (works with .NET 3.x).

Assembly: SpreadsheetGear (in SpreadsheetGear.dll)

See Also