IValues Interface

Provides a high performance, but potentially destructive API for getting and setting the cell values and formulas of a worksheet.

Object Model

Syntax

Visual Basic
C#

'Declaration
 
<System.Reflection.DefaultMemberAttribute("Item")>
Public Interface IValues

'Usage
 
Dim instance As IValues

[System.Reflection.DefaultMember("Item")]
public interface IValues

Remarks

This interface provides high performance access to the values of a worksheet. Unlike SpreadsheetGear.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, IValues.SetNumber will allow you to place numbers in every cell which is occupied by a merged cell, while SpreadsheetGear.IRange.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 SpreadsheetGear.IWorkbookSet.MaxRows / SpreadsheetGear.IWorkbookSet.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 SpreadsheetGear.IWorkbookSet.BeginUpdate and SpreadsheetGear.IWorkbookSet.EndUpdate.

This API will not invoke WorkbookView.RangeChanged events.

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

Example

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 Basic

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

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

IValues Members
SpreadsheetGear.Advanced.Cells Namespace
IValues Interface
ValueType Enumeration