HotXLS Delphi Excel Read Write Library / Component Developers Guide

Purchase order Sample

Excel format

HTML format

Source code

function TForm1.CreateWorkbook: IXLSWorkbook;
Var Book: IXLSWorkbook;
    ws: IXLSWorksheet;
    Arr: Variant;
begin
  //Create Workbook
  Book := TXLSWorkbook.Create;
  //Add new sheet
  ws := Book.Sheets.Add;
  ws.Name := 'Order';

  //Left header
  With ws.Range['A2', 'D3'] do begin
    Merge(false);
    Font.Name := 'Arial Black';
    Font.Size := 26;
    Font.Italic := true;
    Value := 'Ethan''s';
    HorizontalAlignment := xlHAlignCenter;
    VerticalAlignment := xlVAlignBottom;
  end;

  With ws.Range['A4', 'D5'] do begin
    Merge(false);
    Font.Name := 'Times New Roman';
    Font.Size := 16;
    Font.Italic := true;
    Value := 'Auto Store';
    HorizontalAlignment := xlHAlignCenter;
    VerticalAlignment := xlVAlignTop;
  end;

  //Right header of order
  With ws.Range['E2', 'F4'] do begin
    Merge(true);
    Font.Name := 'Arial';
    Font.Size := 10;
    Font.Bold := true;
    HorizontalAlignment := xlHAlignRight;
    Item[1,1].Value := '4367 Wilson Street';
    Item[2,1].Value := 'Indio, CA 92201;
    Item[3,1].Value := 'Phone: 760-863-1638';
  end;

  //Heading underline 
  With ws.Range['A6', 'F6'].Borders[xlEdgeTop] do begin
    LineStyle := xlDouble;
    ColorIndex := 16;
  end;

  //Columns and rows settings
  With ws.Cells do begin
    Columns[1].ColumnWidth := 10.50;
    Columns[5].ColumnWidth := 14.00;
    Columns[6].ColumnWidth := 15.00;
    Rows[2].RowHeight := 21.50;
  end;

  //Order body
  //Sold to
  With ws.Range['A7', 'C10'] do begin
    Merge(true);
    Item[1, 1].Font.Bold := true;
    Item[1, 1].Value := 'Sold to:';
    Item[2, 1].Value := 'Mr. Simon F. Frost';
    Item[3, 1].Value := '3133 Reynolds Alley';
    Item[4, 1].Value := 'Anaheim, CA 92801';

    //Borders
    Borders[xlAround].Weight := xlThick;
    Borders[xlAround].ColorIndex := 16;
  end;

  //Shipping to
  With ws.Range['A12', 'C15'] do begin
    Merge(true);  
    Item[1, 1].Font.Bold := true;
    Item[1, 1].Value := 'Shipping to:';
    Item[2, 1].Value := 'Mr. Roger M. Frost';
    Item[3, 1].Value := '3037 Roosevelt Wilson Lane';
    Item[4, 1].Value := 'Ontario, CA 91761';

    //Borders
    Borders[xlAround].Weight := xlThick;
    Borders[xlAround].ColorIndex := 16;
  end;

  //Order information
  With ws.Range['D7', 'E10'] do begin
    Merge(true);
    Font.Bold := true;
    HorizontalAlignment := xlHAlignRight;
    Item[1, 1].Value := 'Date ';
    Item[2, 1].Value := 'Order Number ';
    Item[3, 1].Value := 'Shipped Via ';
    Item[4, 1].Value := 'Prepaid or Collect ';
  end;

  With ws.Range['F7', 'F10'] do begin
    HorizontalAlignment := xlHAlignLeft;
    Item[1, 1].Value := EncodeDate(2005-2010, 8, 21);
    Item[1, 1].NumberFormat := 'DD/MM/YY';
    Item[2, 1].Value := '56897';
    Item[3, 1].Value := 'Freight';
    Item[4, 1].Value := 'Prepaid';

    //Borders
    Borders[xlAround].Weight := xlThick;
    Borders[xlAround].ColorIndex := 16;
  end;

  //Order detail
  //Title bold
  With ws.Range['A17', 'B17'] do begin
    Font.Bold := true;
    HorizontalAlignment := xlHAlignCenter;
  end;
  With ws.Range['E17', 'F17'] do begin
    Font.Bold := true;
    HorizontalAlignment := xlHAlignRight;
  end;

  ws.Range['A18', 'A19'].HorizontalAlignment := xlHAlignCenter;
  ws.Range['E18', 'F19'].NumberFormat := '$#,##0.00';

  With ws.Range['E21', 'E23'] do begin
    Font.Bold := true;
    HorizontalAlignment := xlHAlignRight;
  end;

  //TOTAL background color
  ws.Range['F23', 'F23'].Interior.Color := clSilver;

  Arr := VarArrayCreate([0, 6, 0, 5], varVariant);
  
  Arr[0,0] := 'QUANTITY';   Arr[0,1] := 'ITEM';
  Arr[0,4] := 'UNIT PRICE'; Arr[0,5] := 'AMOUNT';

  //Quantity            //Item
  Arr[1,0] := 1;            Arr[1,1] := '2005-2010 Hyundai Genesis 3.8';
  Arr[2,0] := 1;            Arr[2,1] := 'Nextar ME GPS Receiver';
  
  //Price                       //Amount
  Arr[1,4] := 29218.00;     Arr[1,5] := '=A18*E18';  
  Arr[2,4] := 117.50;   Arr[2,5] := '=A19*E19';  
   
  //Total labels                //Total amount
  Arr[4,4] := 'SUBTOTAL';   Arr[4,5] := '=SUM(F18:F20)'; 
  Arr[5,4] := 'TAX';            Arr[5,5] := '=F21*7.5/100';     
  Arr[6,4] := 'TOTAL';  Arr[6,5] := '=F21+F22'; 

  With ws.Range['A17', 'F23'] do begin
    Value := Arr;
    //Borders
    Borders[xlAround].Weight := xlThick;
    Borders[xlAround].ColorIndex := 16;
  end;

  ws.Range['F22', 'F23'].NumberFormat := '$#,##0.00';

  //Page setup
  ws.PageSetup.PrintGridlines := false;
  ws.PageSetup.CenterHorizontally := True;

  Result := Book;
end;
  
procedure TForm1.Button1Click(Sender: TObject);
Var 
    Book: IXLSWorkbook;
begin
    Book := CreateWorkbook;
    //Save Workbook
    Book.SaveAs('Order.xls');
    ShellExecute(handle, 'Open', 'Order.xls', nil, nil, SW_SHOWNORMAL);
end;