Back to... Excel Writer

Source file : excel_out_demo.adb



with Excel_Out;

with Ada.Calendar,
     Ada.Characters.Handling,
     Ada.Numerics.Float_Random,
     Ada.Streams.Stream_IO,
     Ada.Text_IO;

procedure Excel_Out_Demo is

  use Excel_Out, Ada.Calendar;

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

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

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

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

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

    xl.Use_format (fmt_boxed);
    xl.Write (11, 1, "Calibri font");
    xl.Use_format (fmt_vertical);
    xl.Put ("Wrapped text, rotated 90°");
    xl.Use_format (fmt_8);
    xl.Write (11, 4, "First number:");
    xl.Write (11, 6, Long_Float'First);
    xl.Write (11, 8, "Last number:");
    xl.Write (11, 10, Long_Float'Last);
    xl.Write (11, 12, "Smallest number:");
    xl.Write (11, 15, (1.0 + Long_Float'Model_Epsilon) * Long_Float'Model_Small);
    xl.Next;
    --  Testing a specific code page (Windows_CP_1253), which was set upon the Create call above.
    xl.Put_Line ("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
    xl.Use_format (fmt_date_2);
    xl.Put (some_time);
    xl.Use_format (fmt_date_1);
    xl.Put (some_time);
    xl.Use_format (fmt_date_3);
    xl.Put (some_time);
    xl.Use_default_format;
    xl.Put (0.0);
    xl.Write_cell_comment_at_cursor ("This is a comment." & ASCII.LF & "Nice, isn't it ?");
    xl.Put (" <- default fmt (general)");
    xl.New_Line;

    for row in 15 .. 300 loop
      xl.Use_format (fmt_1);
      xl.Write (row, 3, Long_Float (row) * 0.01);
      xl.Use_format (fmt_5);
      xl.Put (Long_Float (row - 100) * 0.001);
      xl.Use_format (fmt_cust_num);
      xl.Put (Long_Float (row - 15) + 0.123456);
    end loop;
    xl.Close;
  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;
    quotation_day : Time := Time_Of (2023, 03, 28, 9.0 * 3600.0);
    price, last_price : Long_Float;
    use Ada.Numerics.Float_Random;
    gen : Generator;
  begin
    xl.Create ("fancy.xls");
    --  Some page layout for printing...
    xl.Header ("Fancy sheet");
    xl.Footer ("&D");
    xl.Margins (1.2, 1.1, 0.9, 0.8);
    xl.Print_Gridlines;
    xl.Page_Setup (fit_height_with_n_pages => 0, orientation => portrait, scale_or_fit => fit);
    --
    xl.Write_column_width (1, 15);  --  set to width of n times '0'
    xl.Write_column_width (3, 10);  --  set to width of n times '0'
    xl.Define_Font ("Calibri", 15, font_title, bold, white);
    xl.Define_Font ("Calibri", 10, font_normal);
    xl.Define_Font ("Calibri", 10, font_normal_grey, color => grey);
    xl.Define_Format (font_title, general, fmt_title,
      border => bottom, background_color => dark_blue,
      vertical_align => centred
    );
    xl.Define_Format (font_normal, general, fmt_subtitle, border => bottom);
    xl.Define_Format (font_normal, dd_mm_yyyy, fmt_date, background_color => silver);
    xl.Define_Format (font_normal, decimal_0_thousands_separator, fmt_amount);
    xl.Define_Format (font_normal_grey, percent_2_plus, fmt_percent);
    xl.Use_format (fmt_title);
    xl.Write_row_height (1, 25);
    xl.Put ("Daily Excel Writer stock prices");
    xl.Merge (3);
    xl.New_Line;
    xl.Use_format (fmt_subtitle);
    xl.Put ("Date");
    xl.Put ("Price");
    xl.Put_Line ("Variation %");
    xl.Freeze_Panes_at_cursor;
    Reset (gen);
    price := 950.0 + Long_Float (Random (gen)) * 200.0;
    for i in 1 .. 3650 loop
      xl.Use_format (fmt_date);
      xl.Put (quotation_day);
      quotation_day := quotation_day + Day_Duration'Last;
      xl.Use_format (fmt_amount);
      last_price := price;
      --  Subtract 0.5 after Random for zero growth / inflation / ...
      price := price * (1.0 + 0.1 * (Long_Float (Random (gen)) - 0.489));
      xl.Put (price);
      xl.Use_format (fmt_percent);
      xl.Put_Line (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
    xl.Create;
    xl.Put_Line ("This Excel file is fully created in memory.");
    xl.Put_Line ("It can be stuffed directly into a zip stream,");
    xl.Put_Line ("or sent from a server!");
    xl.Put_Line ("- see ZipTest @ project Zip-Ada (search ""unzip-ada"" or ""zip-ada""");
    for row in 1 .. size loop
      for column in 1 .. size loop
        xl.Write (row + 5, column, 0.01 + Long_Float (row * column));
      end loop;
    end loop;
    xl.Close;
    return xl.Contents;
  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
    xl.Create ("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);
    xl.Put_Line (
      "Time (seconds) for creating" &
      iter'Image & " sheets with" &
      size'Image & " x" &
      size'Image & " =" &
      Integer'Image (size**2) & " cells"
    );
    xl.Put_Line (secs);
    xl.Put_Line ("Sheets per second");
    xl.Put_Line (Long_Float (iter) / secs);
    xl.Close;
  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 BIFF3 .. BIFF4 loop
    Big_Demo (ef);
  end loop;
  Put_Line ("Fancy sheet -> sancy.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.