The One With

Introducing Spreadsheet Document API – Part 2

In the last post, we’ve looked at how easy and intuitive the Spreadsheet API is. Let’s explore other handy features.

Ranges

A Worksheet Range is a collection of sequential cells that can be updated in one shot, instead of iterating thru individual cells one by one. A Range, is referenced using the named coordinates from top-left cell to the bottom-right:

Cell Range

Fig 1: Cell Range

The following example demonstrates how to set a value to a cell range and update it’s formatting.

Workbook book = new Workbook();

// Set values and size... 
book.Worksheets[0].Range["B2:D4"].Value = "W"; 
book.Worksheets[0].Range["B2:D4"].ColumnWidth = 128; 
book.Worksheets[0].Range["B2:D4"].RowHeight = 128;

// Apply custom formatting... 
Formatting b2d4 = book.Worksheets[0].Range["B2:D4"].BeginUpdateFormatting(); 
            
b2d4.Alignment.Horizontal = HorizontalAlignment.Center; 
b2d4.Borders.BottomBorder.Color = Color.Orange; 
b2d4.Borders.BottomBorder.LineStyle = BorderLineStyle.Medium; 
b2d4.Borders.TopBorder.Color = Color.Orange; 
b2d4.Borders.TopBorder.LineStyle = BorderLineStyle.Medium; 
b2d4.Borders.LeftBorder.Color = Color.Orange; 
b2d4.Borders.LeftBorder.LineStyle = BorderLineStyle.Medium; 
b2d4.Borders.RightBorder.Color = Color.Orange; 
b2d4.Borders.RightBorder.LineStyle = BorderLineStyle.Medium; 
b2d4.Fill.BackgroundColor = Color.WhiteSmoke; 
b2d4.Font.Name = "Forte"; 
b2d4.Font.Color = Color.Navy;

book.Worksheets[0].Range["B2:D4"].EndUpdateFormatting(b2d4);

image

Fig 2: Cell Range

Formulas

The ability to calculate totals, averages, set current dates and to perform various calculations within the document, is of course one of the major reasons why Spreadsheets are an amazingly very power tool. We will ship countless of functions, ranging from simple Summations to Boolean logic operators, from Financial to a complete Trigonometry routines.

image

Formulas and expressions are set with the help of the Formula property on a cell or a range. For example, the code bellow sets a 3 values for cells B1:B3 and sums them up in B4

Workbook book = new Workbook();

book.Worksheets[0].Range["B1:B3"].Value = 43; 
book.Worksheets[0].Range["B1:B3"].NumberFormat = "$#,##0.00";

book.Worksheets[0].Cells["B4"].Formula = "= SUM(B1:B3)"; 
book.Worksheets[0].Cells["B4"].NumberFormat = "$#,##0.00"; 
book.Worksheets[0].Cells["B4"].Borders.TopBorder.Color = Color.Orange; 
book.Worksheets[0].Cells["B4"].Borders.TopBorder.LineStyle = BorderLineStyle.Medium;

Document with a Formula Previewed in Microsoft Excel

Now that we know the basics, let's create an invoice.

Cheers

Azret

Published May 30 2013, 10:13 AM by
Bookmark and Share

Comments

Alexander Krakhotko

is there a formula "Cube"?

More / Cube / ...

June 5, 2013 6:05 AM
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-2016 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners