上一篇文章跟大家介紹了DevExpress電子表格單元格和公式的基本設置，今天教大家如何用電子表格(Spreadsheet)制作發貨清單。

假設我們的數據模型是這樣的：

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; }
}

而我們要創建的電子表格清單是這樣的：

首先，大家應該還記得如何新建一個工作簿并設置默認值：

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

設置模擬的顧客信息：

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 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 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";

格式化整個標題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;

為每個產品設置屬性：

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++;
}

匯總：

// 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";

這樣一個簡單的發貨清單就完成了，現在我們將這個電子表格文檔保存為一個PDF文檔：

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

>>DevExpress最新版下載

慧都學院2017全新DevExpress線下研修班火熱報名中！