The One With

July 2013 - Posts

  • WinForms Spreadsheet Control – New API

    In the recent release v2013.1 of the DevExpress WinForms Subscription, we introduced a brand new Spreadsheet Control. We are hard at work adding features to it and in v13.1.5 we’ve added new API to make working with the spreadsheet even easier.

    New Cells API

     

    v  The CellValue.IsDateTime property indicates whether a cell contains a DateTime value.

    v  The Cell.IsDisplayedAsDateTime property indicates whether a number contained in a cell is displayed as a DateTime. This eliminates the need for checking cell value and format.

    v  Assigning a DateTime to a Cell.Value results in an automatic formatting of the value as a DateTime.

    v  To clear cell value, you can now assign it to null, e.g. Cell.Value = null

    v  The Cell.Value property can be assigned various object  types. Built-in converters are responsible for correct interpretation and type conversion. The following methods have now been implemented: CellValue.ToObject, CellValue.FromObject, CellValue.TryCreateFromObject.

    New Workbooks API

    v  The Evaluate method gets a string and evaluates it as if it were a cell formula. It can be used to convert a worksheet name to an object or a value.  In the following example, the mass value is calculated by multiplying the value contained in the A1 cell by density (a defined name value), and rounding the result to two decimal places. 

    spreadsheetDocument.DefinedNames.Add("density", "13.534");

    double mass = spreadsheetControl1.Document.Evaluate("=ROUND(A1*density,2)").NumericValue;

    v  The Calculate method recalculates the cells in all worksheets for the current workbook.

    v  The following events have been implemented and occur when worksheets, rows or columns are inserted or deleted.

    ü  event SheetInserted

    ü  event SheetRemoved

    ü  event RowsRemoved

    ü  event RowsInserted

    ü  event ColumnsRemoved

    ü  event ColumnsInserted

    clip_image001[6]

     

    v  The HasMacros property indicates the presence of macros in a workbook (XLSM file format)

    v  The IWorkbook.Range method provides access to the IRangeProvider interface that can parse a string in order to obtain a worksheet’s range:

    Range range = spreadsheetControl1.Document.Range["Sheet1!A1:B2"];

    or

    Range range = spreadsheetControl1.Document.Range.Parse("Sheet1!A1:B2");

     

    New Worksheet API

    v  Worksheet.MaxColumnCount allows you to limit the number of columns in a worksheet

    v  Worksheet.MaxRowCount allows  you to limit the number of rows in a worksheet

    New DefinedName API

    ·         DefinedName.Range property gets or sets a worksheet cell or a range marked by the defined name.

     

    Build v13.1.5 is available for immediate download from the Download Manager.

    Cheers,

    Azret

  • WinForms Spreadsheet Control – Custom Functions

    In the recent release v2013.1 of the DevExpress WinForms Subscription, we introduced a brand new Spreadsheet Control. We are hard at work adding features to it and in v13.1.5 we’ve added support for Custom Functions

    To register a Custom Function, we simply need to implement a CustomFunction interface and add the object to workbook’s CustomFunctions collection.

    Here is a sample implementation a custom function SPHEREMASS(radius, density). The function calculates the mass of a sphere given it’s radius and density. If parameter density is not specified, the density of water (1000 kg/m3) is assumed.

    Spreadsheet Control - Custom Function

    if (!workbook.CustomFunctions.Contains(customFunction.Name))

        workbook.CustomFunctions.Add(customFunction);

     

    public class SphereMassFunction : ICustomFunction

    {

        const string functionName = "SPHEREMASS";

        readonly ParameterInfo[] functionParameters;

     

        public SphereMassFunction()

        {  

            // Missing optional parameters do not result in error message.

            this.functionParameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Value, ParameterAttributes.Required),

                new ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)};

        }

        public string Name { get { return functionName; } }

        ParameterInfo[] CustomFunction.Parameters { get { return functionParameters; } }

        ParameterType CustomFunction.ReturnType { get { return ParameterType.Value; } }

        // Reevaluate cells on every recalculation.

        bool CustomFunction.Volatile { get { return false; } }

     

        ParameterValue CustomFunction.Evaluate(IList<ParameterValue> parameters, EvaluationContext context)

        {

            double radius;

            double density = 1000;

            ParameterValue radiusParameter;

            ParameterValue densityParameter;

     

            if (parameters.Count == 2)

            {

                densityParameter = parameters[1];

                if (densityParameter.IsError)

                    return densityParameter;

                else

                    density = densityParameter.NumericValue;               

            }

                       

            radiusParameter = parameters[0];

            if (radiusParameter.IsError)

                return radiusParameter;

            else

                radius = radiusParameter.NumericValue;

     

            return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density;

     

        }

        string CustomFunction.GetName(CultureInfo culture)

        {

            return functionName;

        }

    }

    Build v13.1.5 is available for immediate download from the Download Manager.

    Cheers,

    Azret

  • WinForms Spreadsheet Control – Custom Draw

    In the recent release v2013.1 of the DevExpress WinForms Subscription, we introduced a brand new Spreadsheet Control. We are hard at work adding features to it and in v13.1.5 we’ve added support for Custom Draw

    Two new events CustomDrawCell and CustomDrawCellBackground are introduced so that you can take control over the painting of each cell.

    Spreadsheet Control - Custom Draw

    The usage is very simple:

    spreadsheetControl1.CustomDrawCell += HighlightCells;

    void HighlightCells(object sender, DevExpress.XtraSpreadsheet.CustomDrawCellEventArgs e)

    {

        string textToFind = "Waters";

        string cellText = e.Cell.DisplayText;

        int index = cellText.IndexOf(textToFind, StringComparison.InvariantCultureIgnoreCase);

        if (index >= 0)

        {

            StringFormat format = (StringFormat)StringFormat.GenericTypographic.Clone();

            format.SetMeasurableCharacterRanges(new CharacterRange[] { new CharacterRange(index, textToFind.Length) });

            Region[] regions = e.Graphics.MeasureCharacterRanges(cellText, e.Font, e.Bounds, format);

            RectangleF firstChar = regions[0].GetBounds(e.Graphics);

            RectangleF lastChar = regions[regions.Length - 1].GetBounds(e.Graphics);

            RectangleF highlightBounds = RectangleF.FromLTRB(

                Math.Min(firstChar.Left, lastChar.Left),

                Math.Min(firstChar.Top, lastChar.Top),

                Math.Max(firstChar.Right, lastChar.Right) + 5,

                Math.Max(firstChar.Bottom, lastChar.Bottom));

            e.Cache.FillRectangle(e.Cache.GetSolidBrush(Color.Yellow), highlightBounds);

        }

    }

    Build v13.1.5 is available for immediate download from the Download Manager.

    Cheers,

    Azret

LIVE CHAT

Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

FOLLOW US

DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2017 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners