The One With

Introducing Spreadsheet Document API – Part 3

Now that we know how to work with the Spreadsheet API, how to write to Cells and how to make Formulas, let’s build an invoice document.

Imagine our data model to be as follows:

public class Customer { 
    public string Name { get; set; } 
    public string Address1 { get; set; } 
    public string Address2 { get; set; } 
    public string City { get; set; } 
    public string State { get; set; } 
    public string Zip { get; set; } 
    public string Phone { get; set; } 
    public List<Product> Products { get; set; } 
}

public class Product { 
    public string Name { get; set; } 
    public int Quantity { get; set; } 
    public double Discount { get; set; } 
    public double Price { get; set; } 
}

Our goal is to create a Spreadsheet Invoice like this:

Spreadsheet Invoice

First thing, as you remember let’s new up a Workbook and set the defaults.

Workbook book = new Workbook();

// Setup document defaults... 
book.Styles.DefaultStyle.Font.Name = "Segoe UI"; 
book.Styles.DefaultStyle.Font.Size = 14;

This will be our mock customer:

Customer customer = new Customer() { 
    Name = "Alcorn Mickey", 
    Address1 = "Mickeys World of Fun", 
    Address2 = "436 1st Ave.", 
    City = "Cleveland", 
    State = "OH", 
    Zip = "37288", 
    Phone = "(203)290-8902" 
};

customer.Products = new List<Product> { 
    new Product() { Name = "Chai", Price = 100, Quantity = 3, Discount = 0.1 }, 
    new Product() { Name = "Chang", Price = 120, Quantity = 6, Discount = 0.15 }, 
};

Add the billing info:

// Add Billing info... 
book.Worksheets[0].Cells["B2"].Value = "BILL TO:"; 
book.Worksheets[0].Cells["B2"].Font.Bold = true; 
book.Worksheets[0].Cells["B3"].Value = customer.Name; 
book.Worksheets[0].Cells["B4"].Value = customer.Address1; 
book.Worksheets[0].Cells["B5"].Value = customer.Address2; 
book.Worksheets[0].Cells["B6"].Value = String.Format("{0} {1}, {2}", customer.City, customer.State, customer.Zip); 
book.Worksheets[0].Cells["B7"].Value = String.Format("Phone: {0}", customer.Phone);

Add the header:

// Add Header... 
book.Worksheets[0].Cells["B9"].Value = "Product"; 
book.Worksheets[0].Cells["C9"].Value = "Quantity"; 
book.Worksheets[0].Cells["D9"].Value = "Price"; 
book.Worksheets[0].Cells["E9"].Value = "Discount"; 
book.Worksheets[0].Cells["F9"].Value = "Due";

Format the entire header Range [B9:F9]:

// Format header... 
book.Worksheets[0].Range["B9:F9"].ColumnWidthInCharacters = 16; 
Formatting header = book.Worksheets[0].Range["B9:F9"].BeginUpdateFormatting(); 
header.Alignment.Horizontal = HorizontalAlignment.Right; 
header.Borders.BottomBorder.Color = Color.Black; 
header.Borders.BottomBorder.LineStyle = BorderLineStyle.Medium; 
header.Font.Bold = true; 
book.Worksheets[0].Range["B9:F9"].EndUpdateFormatting(header);

// Product is left aligned... 
book.Worksheets[0].Cells["B9"].Alignment.Horizontal = HorizontalAlignment.Left;

For every Product in the list, create the line items:

const int start = 10;

// Add line items... 
int row = start; 
foreach(Product product in customer.Products) { 
    book.Worksheets[0].Cells["B" + row].Value = product.Name; 
    book.Worksheets[0].Cells["C" + row].Value = product.Quantity; 
    book.Worksheets[0].Cells["D" + row].Value = (double)product.Price; 
    book.Worksheets[0].Cells["D" + row].NumberFormat = "$#,##0.00"; 
    book.Worksheets[0].Cells["E" + row].Value = (double)product.Discount; 
    book.Worksheets[0].Cells["E" + row].NumberFormat = "0%"; 
    book.Worksheets[0].Cells["F" + row].Formula = String.Format("=C{0}*D{0}*(1-E{0})", row); 
    book.Worksheets[0].Cells["F" + row].NumberFormat = "$#,##0.00"; 
    row++; 
}

Sum up the totals:

// Total... 
book.Worksheets[0].Cells["E" + (row + 1)].Value = "Total:"; 
book.Worksheets[0].Cells["E" + (row + 1)].Alignment.Horizontal = HorizontalAlignment.Right; 
book.Worksheets[0].Cells["E" + (row + 1)].Font.Bold = true; 
book.Worksheets[0].Cells["F" + (row + 1)].Formula = String.Format("=SUM(F{0}:F{1})", start, row - 1); 
book.Worksheets[0].Cells["F" + (row + 1)].NumberFormat = "$#,##0.00";


And we are done. We are now ready to save to a Spreadsheet document file or export it to a PDF.

book.ExportToPdf(@"Invoice.pdf");

Exporeted document:


Cheers

Azret

Published May 30 2013, 01:20 PM by
Bookmark and Share

Comments

tom thorne

Does the document server work against documents that are already open in excel?  For instance, can I use it to read unsaved changes from a document that is open in excel?

May 31, 2013 5:03 AM

E Brito

What can i say, that is just amazing.

Are we getting similar library for word documents?

May 31, 2013 9:56 AM

Azret Botash (DevExpress)

@tom thorne: No, I am afraid it's not possible to access the unsaved changes.

@E Brito: You have this capability even now, take a look here http://dxpr.es/11tMWA2

in v2013.1, we've made it even better and easier :) Stay tuned :)

May 31, 2013 2:55 PM

Hans Nieuwenhuis

Can you add TextBoxes to Excel sheets?

I prefer TextBoxes for entering text (like a customer name and address) above entering that data in cells.

June 1, 2013 2:13 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