NativeExcel2 for .NET Developer's Reference

Order sample

Example

This example creates worksheet with a simple order

Output file: console-order.xls

[C#]
using System;
using System.Drawing;
using NativeExcel;

namespace Console_Order
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-order.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();

            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();
            sheet.Name = "Order";

            //Left header of order
            IRange range = sheet.Range["A2:D3"];
            range.Merge();
            range.Font.Name   = "Times New Roman";
            range.Font.Size   = 26;
            range.Font.Italic = true;
            range.Font.Bold   = true;
            range.Value = "Olson`s";
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment   = XlVAlign.xlVAlignBottom;

            range = sheet.Range["A4:D5"];
            range.Merge();
            range.Font.Name   = "Times New Roman";
            range.Font.Size   = 16;
            range.Font.Italic = true;
            range.Value = "Equipment Store";
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment   = XlVAlign.xlVAlignTop;
   
            //Right header of order
            range = sheet.Range["F2:F4"];
            range.Font.Name = "Arial";
            range.Font.Size = 10;
            range.Font.Bold = true;
            range.HorizontalAlignment = XlHAlign.xlHAlignRight;
            range[1,1].Value = "2297 Front Street";
            range[2,1].Value = "Phoenix, Arizona 99065";
            range[3,1].Value = "Phone: (861) 930-1754";


            range = sheet.Range["A7:F7"];
            range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDouble;
            range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = 16;

            //Columns and rows settings
            sheet.Cells.Columns[1].ColumnWidth = 13.86;
            sheet.Cells.Columns[5].ColumnWidth = 15.29;
            sheet.Cells.Rows[2].RowHeight = 24.25;
            sheet.Cells.Rows[5].RowHeight = 24.25;

            //Order body
            //Sold to
            range = sheet.Range["A9:C12"];
            range[1, 1].Font.Bold = true;
            //Values
            range[1, 1].Value = "Sold to:";
            range[2, 1].Value = "Mr. Bruce Kretchmer";
            range[3, 1].Value = "1135 State Street";
            range[4, 1].Value = "Chicago, Illinois 60688";
            //Borders
            range.Borders[XlBordersIndex.xlAround].Weight = XlBorderWeight.xlThick;
            range.Borders[XlBordersIndex.xlAround].ColorIndex = 16;

            //Shipped to
            range = sheet.Range["A14:C17"];
            range[1, 1].Font.Bold = true;
            //Values
            range[1, 1].Value = "Shipped to:";
            range[2, 1].Value = "Kretchmer`s Appliance Store";
            range[3, 1].Value = "811 Regent Street";
            range[4, 1].Value = "Chicago, Illinois 60688";
            //Borders
            range.Borders[XlBordersIndex.xlAround].Weight = XlBorderWeight.xlThick;
            range.Borders[XlBordersIndex.xlAround].ColorIndex = 16;


            //Order information
            range = sheet.Range["E9:E12"];
            range.Font.Bold = true;
            range.HorizontalAlignment = XlHAlign.xlHAlignRight; 
            //Values
            range[1, 1].Value = "Date ";
            range[2, 1].Value = "Order Number ";
            range[3, 1].Value = "Shipped Via ";
            range[4, 1].Value = "Prepaid or Collect ";

            range = sheet.Range["F9:F12"];
            range.HorizontalAlignment = XlHAlign.xlHAlignLeft; 
            //Values
            range[1, 1].Value = new DateTime(2004, 2, 3);
            range[1, 1].NumberFormat = "DD/MM/YY";
            range[2, 1].Value = 452;
            range[3, 1].Value = "UPS";
            range[4, 1].Value = "Prepaid";
            //Borders
            range.Borders[XlBordersIndex.xlAround].Weight = XlBorderWeight.xlThick;
            range.Borders[XlBordersIndex.xlAround].ColorIndex = 16;

            range = sheet.Range["A20:F20"];
            range.Font.Bold = true;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter; 

            sheet.Range["A21:A23"].HorizontalAlignment = XlHAlign.xlHAlignCenter;
            sheet.Range["E21:F23"].NumberFormat = "$#,##0.00";

            range = sheet.Range["E25:E27"];
            range.Font.Bold = true;
            range.HorizontalAlignment = XlHAlign.xlHAlignRight; 

            //background color
            sheet.Range["F27"].Interior.Color = Color.Silver;
            //Data for the table
            object[,] Arr = 
               {
                 {"QUANTITY",  "ITEM", null, null, "UNIT PRICE", "AMOUNT"        },
                 {        15, "Item1", null, null,        32.50, "=A21*E21"      }, 
                 {        17, "Item2", null, null,        40.30, "=A22*E22"      }, 
                 {         8, "Item3", null, null,        15.20, "=A23*E23"      }, 
                 {      null,    null, null, null,         null,  null           }, 
                 {      null,    null, null, null,   "SUBTOTAL", "=SUM(F21:F23)" }, 
                 {      null,    null, null, null,        "TAX", "=F25*20/100"   }, 
                 {      null,    null, null, null,      "TOTAL", "=A25+F26"      } 
               };  

            //Table
            range = sheet.Range["A20:F27"];
            range.Value = Arr;

            //Borders
            range.Borders[XlBordersIndex.xlAround].Weight = XlBorderWeight.xlThick;
            range.Borders[XlBordersIndex.xlAround].ColorIndex = 16;

            sheet.Range["F25:F27"].NumberFormat = "$#,##0.00";

            //Footer
            range = sheet.Range["B29"];
            range.Value = "Thank you for your business!";
            range.Font.Italic = true;

            //Printing settings
            sheet.PageSetup.PrintGridLines = false;
            sheet.PageSetup.CenterHorizontally = true;

            //Save workbook
            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports System.Drawing
imports NativeExcel

Module Console_Order

    Sub Main()
        Dim FileName As String = "console-order.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create a new workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()

        'Add worksheet
        Dim sheet As IWorksheet = book.Worksheets.Add()
        sheet.Name = "Order"

        'Left header of order
        Dim range As IRange = sheet.Range("A2:D3")
        range.Merge()
        range.Font.Name   = "Times New Roman"
        range.Font.Size   = 26
        range.Font.Italic = True
        range.Font.Bold   = True
        range.Value = "Olson`s"
        range.HorizontalAlignment = XlHAlign.xlHAlignCenter
        range.VerticalAlignment   = XlVAlign.xlVAlignBottom

        range = sheet.Range("A4:D5")
        range.Merge()
        range.Font.Name   = "Times New Roman"
        range.Font.Size   = 16
        range.Font.Italic = True
        range.Value = "Equipment Store"
        range.HorizontalAlignment = XlHAlign.xlHAlignCenter
        range.VerticalAlignment   = XlVAlign.xlVAlignTop

        'Right header of order
        range = sheet.Range("F2:F4")
        range.Font.Name = "Arial"
        range.Font.Size = 10
        range.Font.Bold = True
        range.HorizontalAlignment = XlHAlign.xlHAlignRight
        range(1,1).Value = "2297 Front Street"
        range(2,1).Value = "Phoenix, Arizona 99065"
        range(3,1).Value = "Phone: (861) 930-1754"


        range = sheet.Range("A7:F7")
        range.Borders(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlDouble
        range.Borders(XlBordersIndex.xlEdgeTop).ColorIndex = 16

        'Columns and rows settings
        sheet.Cells.Columns(1).ColumnWidth = 13.86
        sheet.Cells.Columns(5).ColumnWidth = 15.29
        sheet.Cells.Rows(2).RowHeight = 24.25
        sheet.Cells.Rows(5).RowHeight = 24.25

        'Order body
        'Sold to
        range = sheet.Range("A9:C12")
        range(1, 1).Font.Bold = True
        'Values
        range(1, 1).Value = "Sold to:"
        range(2, 1).Value = "Mr. Bruce Kretchmer"
        range(3, 1).Value = "1135 State Street"
        range(4, 1).Value = "Chicago, Illinois 60688"
        'Borders
        range.Borders(XlBordersIndex.xlAround).Weight = XlBorderWeight.xlThick
        range.Borders(XlBordersIndex.xlAround).ColorIndex = 16

        'Shipped to
        range = sheet.Range("A14:C17")
        range(1, 1).Font.Bold = True
        'Values
        range(1, 1).Value = "Shipped to:"
        range(2, 1).Value = "Kretchmer`s Appliance Store"
        range(3, 1).Value = "811 Regent Street"
        range(4, 1).Value = "Chicago, Illinois 60688"
        'Borders
        range.Borders(XlBordersIndex.xlAround).Weight = XlBorderWeight.xlThick
        range.Borders(XlBordersIndex.xlAround).ColorIndex = 16


        'Order information
        range = sheet.Range("E9:E12")
        range.Font.Bold = True
        range.HorizontalAlignment = XlHAlign.xlHAlignRight 
        'Values
        range(1, 1).Value = "Date "
        range(2, 1).Value = "Order Number "
        range(3, 1).Value = "Shipped Via "
        range(4, 1).Value = "Prepaid or Collect "

        range = sheet.Range("F9:F12")
        range.HorizontalAlignment = XlHAlign.xlHAlignLeft 
        'Values
        range(1, 1).Value = new DateTime(2004, 2, 3)
        range(1, 1).NumberFormat = "DD/MM/YY"
        range(2, 1).Value = 452
        range(3, 1).Value = "UPS"
        range(4, 1).Value = "Prepaid"
        'Borders
        range.Borders(XlBordersIndex.xlAround).Weight = XlBorderWeight.xlThick
        range.Borders(XlBordersIndex.xlAround).ColorIndex = 16

        range = sheet.Range("A20:F20")
        range.Font.Bold = True
        range.HorizontalAlignment = XlHAlign.xlHAlignCenter 

        sheet.Range("A21:A23").HorizontalAlignment = XlHAlign.xlHAlignCenter
        sheet.Range("E21:F23").NumberFormat = "$#,##0.00"

        range = sheet.Range("E25:E27")
        range.Font.Bold = True
        range.HorizontalAlignment = XlHAlign.xlHAlignRight 

        'background color
        sheet.Range("F27").Interior.Color = Color.Silver
        'Data for the table
        Dim NullVal As Object = System.DBNull.Value

        Dim Arr(,) As Object = New Object(7,5) {}

        Arr(0,0) = "QUANTITY"
        Arr(0,1) = "ITEM"
        Arr(0,4) = "UNIT PRICE"
        Arr(0,5) = "AMOUNT"

        Arr(1,0) = 15
        Arr(1,1) = "Item1"
        Arr(1,4) = 32.50
        Arr(1,5) = "=A21*E21"

        Arr(2,0) = 17
        Arr(2,1) = "Item2"
        Arr(2,4) = 40.30
        Arr(2,5) = "=A22*E22"

        Arr(3,0) = 8
        Arr(3,1) = "Item3"
        Arr(3,4) = 15.20
        Arr(3,5) = "=A23*E23"

        Arr(5,4) = "SUBTOTAL" 
        Arr(5,5) = "=SUM(F21:F23)"

        Arr(6,4) = "TAX"
        Arr(6,5) = "=F25*20/100"

        Arr(7,4) = "TOTAL"
        Arr(7,5) = "=A25+F26"

        'Table
        range = sheet.Range("A20:F27")
        range.Value = Arr

        'Borders
        range.Borders(XlBordersIndex.xlAround).Weight = XlBorderWeight.xlThick
        range.Borders(XlBordersIndex.xlAround).ColorIndex = 16

        sheet.Range("F25:F27").NumberFormat = "$#,##0.00"

        'Footer
        range = sheet.Range("B29")
        range.Value = "Thank you for your business!"
        range.Font.Italic = True

        'Printing settings
        sheet.PageSetup.PrintGridLines = False
        sheet.PageSetup.CenterHorizontally = True

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module