Back to... Excel Writer

Source file : excel_out_demo.adb



-- This test procedure for Excel_Out is in the Ada 95 syntax,
-- for compatibility with a larger number of development systems.
-- With Ada 2005 and later, you can also write "xl.Write(...)" etc. everywhere.
--

with Excel_Out;                         use Excel_Out;

with Ada.Calendar;                      use Ada.Calendar;
with Ada.Numerics.Float_Random;         use Ada.Numerics.Float_Random;
with Ada.Streams.Stream_IO, Ada.Text_IO;

procedure Excel_Out_Demo is

  procedure Small_demo is
    xl: Excel_Out_File;
  begin
    Create(xl, "Small.xls");
    Put_Line(xl, "This is a small demo for Excel_Out");
    for row in 3 .. 8 loop
      for column in 1 .. 8 loop
        Write(xl, row, column, row * 1000 + column);
      end loop;
    end loop;
    Close(xl);
  end Small_demo;

  procedure Big_demo(ef: Excel_type) is
    xl: Excel_Out_File;
    font_1, font_2, font_3, font_title, font_5, font_6: Font_type;
    fmt_1, fmt_decimal_2, fmt_decimal_0, fmt_title, fmt_5, fmt_boxed, fmt_cust_num, fmt_8,
    fmt_date_1, fmt_date_2, fmt_date_3, fmt_vertical: Format_type;
    custom_num, custom_date_num: Number_format_type;
    some_time: constant Time:= Time_Of(2014, 03, 16, (11.0*60.0 + 55.0)* 60.0 + 17.0);
    damier: Natural;
  begin
    Create(xl, "Big [" & Excel_type'Image(ef) & "].xls", ef, Windows_CP_1253);
    Zoom_level(xl, 85, 100);  --  Zoom level 85% (Excel: Ctrl + one bump down with the mouse wheel)
    -- Some page layout for printing...
    Header(xl, "Big demo");
    Footer(xl, "&D");
    Margins(xl, 1.2, 1.1, 0.9, 0.8);
    Print_Row_Column_Headers(xl);
    Print_Gridlines(xl);
    Page_Setup(xl, fit_height_with_n_pages => 0, orientation => landscape, scale_or_fit => fit);
    --
    Write_default_column_width(xl, 7);
    Write_column_width(xl, 1, 17); -- set to width of n times '0'
    Write_column_width(xl, 2, 11);
    Write_column_width(xl, 5, 11);
    Write_column_width(xl, 14, 0); -- hide this column
    --
    Write_default_row_height(xl, 14);
    Write_row_height(xl, 1, 23);   -- header row 1
    Write_row_height(xl, 2, 23);   -- header row 2
    Write_row_height(xl, 9, 23);
    Write_row_height(xl, 11, 43);
    Write_row_height(xl, 13, 0);   -- hide this row
    --
    Define_font(xl, "Arial", 9, font_1, regular, blue);
    Define_font(xl, "Courier New", 11, font_2, bold & italic, red);
    Define_font(xl, "Times New Roman", 13, font_3, bold, teal);
    Define_font(xl, "Arial Narrow", 15, font_title, bold);
    Define_font(xl, "Calibri", 15, font_5, bold, dark_red);
    Define_font(xl, "Calibri", 9, font_6);
    --
    Define_number_format(xl, custom_num, "0.000000"); -- 6 decimals
    Define_number_format(xl, custom_date_num, "yyyy\-mm\-dd\ hh:mm:ss"); -- ISO date
    --
    Define_format(xl,
      font_title, general,
      fmt_title,
      border => top & bottom, vertical_align => centred
    );
    Define_format(xl, font_1, percent_0, fmt_1, centred, right);
    Define_format(xl, font_2, decimal_2, fmt_decimal_2);
    Define_format(xl, font_3, decimal_0_thousands_separator, fmt_decimal_0, centred);
    Define_format(xl, font_1, percent_2_plus, fmt_5, centred, right);
    Define_format(xl, font_5, general,   fmt_boxed, border => box, vertical_align => centred);
    Define_format(xl, font_1, custom_num,  fmt_cust_num, centred);
    Define_format(xl, font_6, general, fmt_8);
    Define_format(xl, font_6, dd_mm_yyyy,       fmt_date_1, shaded => True, background_color => yellow);
    Define_format(xl, font_6, dd_mm_yyyy_hh_mm, fmt_date_2, background_color => yellow);
    Define_format(xl, font_6, hh_mm_ss,         fmt_date_3, shaded => True); -- custom_date_num
    Define_format(xl, font_6, general, fmt_vertical, wrap_text => True, text_orient => rotated_90);
    --
    Use_format(xl, fmt_title);
    Put(xl, "This is a big demo for Excel Writer / Excel_Out");
    Merge(xl, 6);
    Next(xl);
    Put(xl, "Excel format: " & Excel_type'Image(ef));
    Merge(xl, 1);
    New_Line(xl);
    Freeze_Top_Row(xl);
    Put(xl, "Version: " & version);
    Merge(xl, 3);
    Next(xl, 4);
    Put(xl, "Ref.: " & reference);

    Use_format(xl, fmt_decimal_2);
    for column in 1 .. 9 loop
      Write(xl, 3, column, Long_Float(column) + 0.5);
    end loop;
    Use_format(xl, fmt_8);
    Put(xl, "  <- = column + 0.5");

    Use_format(xl, fmt_decimal_0);
    for row in 4 .. 7 loop
      for column in 1 .. 9 loop
        damier:= 10 + 990 * ((row + column) mod 2);
        Write(xl, row, column, row * damier + column);
      end loop;
    end loop;
    Use_format(xl, fmt_8);
    Put(xl, "  <- = row * (1000 or 10) + column");

    Use_format(xl, fmt_title);
    for column in 1 .. 20 loop
      Write(xl, 9, column, Character'Val(64 + column) & "");
    end loop;

    Use_format(xl, fmt_boxed);
    Write(xl, 11, 1, "Calibri font");
    Use_format(xl, fmt_vertical);
    Put(xl, "Wrapped text, rotated 90");
    Use_format(xl, fmt_8);
    Write(xl, 11, 4, "First number:");
    Write(xl, 11, 6, Long_Float'First);
    Write(xl, 11, 8, "Last number:");
    Write(xl, 11, 10, Long_Float'Last);
    Write(xl, 11, 12, "Smallest number:");
    Write(xl, 11, 15, (1.0+Long_Float'Model_Epsilon) * Long_Float'Model_Small);
    Next(xl);
    --  Testing a specific code page (Windows_CP_1253), which was set upon the Create call above.
    Put_Line(xl, "A few Greek letters (alpha, beta, gamma): " &
      Character'Val(16#E1#) & ", " & Character'Val(16#E2#) & ", " & Character'Val(16#E3#)
    );
    -- Date: 2014-03-16 11:55:15
    Use_format(xl, fmt_date_2);
    Put(xl, some_time);
    Use_format(xl, fmt_date_1);
    Put(xl, some_time);
    Use_format(xl, fmt_date_3);
    Put(xl, some_time);
    Use_default_format(xl);
    Put(xl, 0.0);
    Write_cell_comment_at_cursor(xl, "This is a comment." & ASCII.LF & "Nice, isn't it ?");
    Put(xl, " <- default fmt (general)");
    New_Line(xl);

    for row in 15 .. 300 loop
      Use_format(xl, fmt_1);
      Write(xl, row, 3, Long_Float(row) * 0.01);
      Use_format(xl, fmt_5);
      Put(xl, Long_Float(row-100) * 0.001);
      Use_format(xl, fmt_cust_num);
      Put(xl, Long_Float(row - 15) + 0.123456);
    end loop;
    Close(xl);
  end Big_demo;

  procedure Fancy is
    xl: Excel_Out_File;
    font_title, font_normal, font_normal_grey: Font_type;
    fmt_title, fmt_subtitle, fmt_date, fmt_percent, fmt_amount: Format_type;
    first_day: constant Time:= Time_Of(2014, 03, 28, 9.0*3600.0);
    price, last_price: Long_Float;
    gen: Generator;
  begin
    Create(xl, "Fancy.xls");
    -- Some page layout for printing...
    Header(xl, "Fancy sheet");
    Footer(xl, "&D");
    Margins(xl, 1.2, 1.1, 0.9, 0.8);
    Print_Gridlines(xl);
    Page_Setup(xl, fit_height_with_n_pages => 0, orientation => portrait, scale_or_fit => fit);
    --
    Write_column_width(xl, 1, 15); -- set to width of n times '0'
    Write_column_width(xl, 3, 10); -- set to width of n times '0'
    Define_font(xl, "Calibri", 15, font_title, bold, white);
    Define_font(xl, "Calibri", 10, font_normal);
    Define_font(xl, "Calibri", 10, font_normal_grey, color => grey);
    Define_format(xl, font_title, general, fmt_title,
      border => bottom, background_color => dark_blue,
      vertical_align => centred
    );
    Define_format(xl, font_normal, general, fmt_subtitle, border => bottom);
    Define_format(xl, font_normal, dd_mm_yyyy, fmt_date, background_color => silver);
    Define_format(xl, font_normal, decimal_0_thousands_separator, fmt_amount);
    Define_format(xl, font_normal_grey, percent_2_plus, fmt_percent);
    Use_format(xl, fmt_title);
    Write_row_height(xl, 1, 25);
    Put(xl, "Daily Excel Writer stock prices");
    Merge(xl, 3);
    New_Line(xl);
    Use_format(xl, fmt_subtitle);
    Put(xl,"Date");
    Put(xl,"Price");
    Put_Line(xl,"Variation %");
    Freeze_Panes_at_cursor(xl);
    Reset(gen);
    price:= 950.0 + Long_Float(Random(gen)) * 200.0;
    for i in 1..3650 loop
      Use_format(xl, fmt_date);
      Put(xl, first_day + i * Day_Duration'Last);
      Use_format(xl, fmt_amount);
      last_price:= price;
      price:= price * (1.0 + 0.1 * (Long_Float(Random(gen)) - 0.49));
      Put(xl, price);
      Use_format(xl, fmt_percent);
      Put_Line(xl, price / last_price - 1.0);
    end loop;
    Close(xl);
  end Fancy;

  function My_nice_sheet(size: Positive) return String is
    xl: Excel_Out_String;
  begin
    Create(xl);
    Put_Line(xl, "This Excel file is fully created in memory.");
    Put_Line(xl, "It can be stuffed directly into a zip stream,");
    Put_Line(xl, "or sent from a server!");
    Put_Line(xl, "- see ZipTest @ project Zip-Ada (search ""unzip-ada"" or ""zip-ada""");
    for row in 1 .. size loop
      for column in 1 .. size loop
        Write(xl, row + 5, column, 0.01 + Long_Float(row * column));
      end loop;
    end loop;
    Close(xl);
    return Contents(xl);
  end My_nice_sheet;

  procedure String_demo is
    use Ada.Streams.Stream_IO;
    f: File_Type;
  begin
    Create(f, Out_File, "From_string.xls");
    String'Write(Stream(f), My_nice_sheet(200));
    Close(f);
  end String_demo;

  procedure Speed_test is
    xl: Excel_Out_File;
    t0, t1: Time;
    iter: constant:= 1000;
    size: constant:= 150;
    secs: Long_Float;
    dummy_int: Integer:= 0;
  begin
    Create(xl, "Speed_test.xls");
    t0:= Clock;
    for i in 1..iter loop
      declare
        dummy: constant String:= My_nice_sheet(size);
      begin
        dummy_int:= 0 * dummy_int + dummy'Length;
      end;
    end loop;
    t1:= Clock;
    secs:= Long_Float(t1-t0);
    Put_Line(xl,
      "Time (seconds) for creating" &
      Integer'Image(iter) & " sheets with" &
      Integer'Image(size) & " x" &
      Integer'Image(size) & " =" &
      Integer'Image(size**2) & " cells"
    );
    Put_Line(xl, secs);
    Put_Line(xl, "Sheets per second");
    Put_Line(xl, Long_Float(iter) / secs);
    Close(xl);
  end Speed_test;

  use Ada.Text_IO;

begin
  Put_Line("Small demo -> Small.xls");
  Small_demo;
  Put_Line("Big demo -> Big [...].xls");
  for ef in Excel_type loop
    Big_demo(ef);
  end loop;
  Put_Line("Fancy sheet -> Fancy.xls");
  Fancy;
  Put_Line("Excel sheet in a string demo -> From_string.xls");
  String_demo;
  Put_Line("Speed test -> Speed_test.xls");
  Speed_test;
end Excel_Out_Demo;


Excel Writer: Ada package writing Excel files (.xls). Ada programming.
Some news about Excel Writer and other Ada projects on Gautier's blog.