1-- This test procedure for Excel_Out is in the Ada 95 syntax,
2-- for compatibility with a larger number of development systems.
3-- With Ada 2005 and later, you can also write "xl.Write(...)" etc. everywhere.
4--
5
6with Excel_Out;                         use Excel_Out;
7
8with Ada.Calendar;                      use Ada.Calendar;
9with Ada.Numerics.Float_Random;         use Ada.Numerics.Float_Random;
10with Ada.Streams.Stream_IO, Ada.Text_IO;
11
12procedure Excel_Out_Demo is
13
14  procedure Small_demo is
15    xl: Excel_Out_File;
16  begin
17    Create(xl, "/tmp/Small.xls");
18    Put_Line(xl, "This is a small demo for Excel_Out");
19    for row in 3 .. 8 loop
20      for column in 1 .. 8 loop
21        Write(xl, row, column, row * 1000 + column);
22      end loop;
23    end loop;
24    Close(xl);
25  end Small_demo;
26
27  procedure Big_demo(ef: Excel_type) is
28    xl: Excel_Out_File;
29    font_1, font_2, font_3, font_title, font_5, font_6: Font_type;
30    fmt_1, fmt_decimal_2, fmt_decimal_0, fmt_title, fmt_5, fmt_boxed, fmt_cust_num, fmt_8,
31    fmt_date_1, fmt_date_2, fmt_date_3, fmt_vertical: Format_type;
32    custom_num, custom_date_num: Number_format_type;
33    some_time: constant Time:= Time_Of(2014, 03, 16, (11.0*60.0 + 55.0)* 60.0 + 17.0);
34    damier: Natural;
35  begin
36    Create(xl, "/tmp/Big [" & Excel_type'Image(ef) & "].xls", ef, Windows_CP_1253);
37    Zoom_level(xl, 85, 100);  --  Zoom level 85% (Excel: Ctrl + one bump down with the mouse wheel)
38    -- Some page layout for printing...
39    Header(xl, "Big demo");
40    Footer(xl, "&D");
41    Margins(xl, 1.2, 1.1, 0.9, 0.8);
42    Print_Row_Column_Headers(xl);
43    Print_Gridlines(xl);
44    Page_Setup(xl, fit_height_with_n_pages => 0, orientation => landscape, scale_or_fit => fit);
45    --
46    Write_default_column_width(xl, 7);
47    Write_column_width(xl, 1, 17); -- set to width of n times '0'
48    Write_column_width(xl, 2, 11);
49    Write_column_width(xl, 5, 11);
50    Write_column_width(xl, 14, 0); -- hide this column
51    --
52    Write_default_row_height(xl, 14);
53    Write_row_height(xl, 1, 23);   -- header row 1
54    Write_row_height(xl, 2, 23);   -- header row 2
55    Write_row_height(xl, 9, 23);
56    Write_row_height(xl, 11, 43);
57    Write_row_height(xl, 13, 0);   -- hide this row
58    --
59    Define_font(xl, "Arial", 9, font_1, regular, blue);
60    Define_font(xl, "Courier New", 11, font_2, bold & italic, red);
61    Define_font(xl, "Times New Roman", 13, font_3, bold, teal);
62    Define_font(xl, "Arial Narrow", 15, font_title, bold);
63    Define_font(xl, "Calibri", 15, font_5, bold, dark_red);
64    Define_font(xl, "Calibri", 9, font_6);
65    --
66    Define_number_format(xl, custom_num, "0.000000"); -- 6 decimals
67    Define_number_format(xl, custom_date_num, "yyyy\-mm\-dd\ hh:mm:ss"); -- ISO date
68    --
69    Define_format(xl,
70      font_title, general,
71      fmt_title,
72      border => top & bottom, vertical_align => centred
73    );
74    Define_format(xl, font_1, percent_0, fmt_1, centred, right);
75    Define_format(xl, font_2, decimal_2, fmt_decimal_2);
76    Define_format(xl, font_3, decimal_0_thousands_separator, fmt_decimal_0, centred);
77    Define_format(xl, font_1, percent_2_plus, fmt_5, centred, right);
78    Define_format(xl, font_5, general,   fmt_boxed, border => box, vertical_align => centred);
79    Define_format(xl, font_1, custom_num,  fmt_cust_num, centred);
80    Define_format(xl, font_6, general, fmt_8);
81    Define_format(xl, font_6, dd_mm_yyyy,       fmt_date_1, shaded => True, background_color => yellow);
82    Define_format(xl, font_6, dd_mm_yyyy_hh_mm, fmt_date_2, background_color => yellow);
83    Define_format(xl, font_6, hh_mm_ss,         fmt_date_3, shaded => True); -- custom_date_num
84    Define_format(xl, font_6, general, fmt_vertical, wrap_text => True, text_orient => rotated_90);
85    --
86    Use_format(xl, fmt_title);
87    Put(xl, "This is a big demo for Excel Writer / Excel_Out");
88    Merge(xl, 6);
89    Next(xl);
90    Put(xl, "Excel format: " & Excel_type'Image(ef));
91    Merge(xl, 1);
92    New_Line(xl);
93    Freeze_Top_Row(xl);
94    Put(xl, "Version: " & version);
95    Merge(xl, 3);
96    Next(xl, 4);
97    Put(xl, "Ref.: " & reference);
98
99    Use_format(xl, fmt_decimal_2);
100    for column in 1 .. 9 loop
101      Write(xl, 3, column, Long_Float(column) + 0.5);
102    end loop;
103    Use_format(xl, fmt_8);
104    Put(xl, "  <- = column + 0.5");
105
106    Use_format(xl, fmt_decimal_0);
107    for row in 4 .. 7 loop
108      for column in 1 .. 9 loop
109        damier:= 10 + 990 * ((row + column) mod 2);
110        Write(xl, row, column, row * damier + column);
111      end loop;
112    end loop;
113    Use_format(xl, fmt_8);
114    Put(xl, "  <- = row * (1000 or 10) + column");
115
116    Use_format(xl, fmt_title);
117    for column in 1 .. 20 loop
118      Write(xl, 9, column, Character'Val(64 + column) & "");
119    end loop;
120
121    Use_format(xl, fmt_boxed);
122    Write(xl, 11, 1, "Calibri font");
123    Use_format(xl, fmt_vertical);
124    Put(xl, "Wrapped text, rotated 90�");
125    Use_format(xl, fmt_8);
126    Write(xl, 11, 4, "First number:");
127    Write(xl, 11, 6, Long_Float'First);
128    Write(xl, 11, 8, "Last number:");
129    Write(xl, 11, 10, Long_Float'Last);
130    Write(xl, 11, 12, "Smallest number:");
131    Write(xl, 11, 15, (1.0+Long_Float'Model_Epsilon) * Long_Float'Model_Small);
132    Next(xl);
133    --  Testing a specific code page (Windows_CP_1253), which was set upon the Create call above.
134    Put_Line(xl, "A few Greek letters (alpha, beta, gamma): " &
135      Character'Val(16#E1#) & ", " & Character'Val(16#E2#) & ", " & Character'Val(16#E3#)
136    );
137    -- Date: 2014-03-16 11:55:15
138    Use_format(xl, fmt_date_2);
139    Put(xl, some_time);
140    Use_format(xl, fmt_date_1);
141    Put(xl, some_time);
142    Use_format(xl, fmt_date_3);
143    Put(xl, some_time);
144    Use_default_format(xl);
145    Put(xl, 0.0);
146    Write_cell_comment_at_cursor(xl, "This is a comment." & ASCII.LF & "Nice, isn't it ?");
147    Put(xl, " <- default fmt (general)");
148    New_Line(xl);
149
150    for row in 15 .. 300 loop
151      Use_format(xl, fmt_1);
152      Write(xl, row, 3, Long_Float(row) * 0.01);
153      Use_format(xl, fmt_5);
154      Put(xl, Long_Float(row-100) * 0.001);
155      Use_format(xl, fmt_cust_num);
156      Put(xl, Long_Float(row - 15) + 0.123456);
157    end loop;
158    Close(xl);
159  end Big_demo;
160
161  procedure Fancy is
162    xl: Excel_Out_File;
163    font_title, font_normal, font_normal_grey: Font_type;
164    fmt_title, fmt_subtitle, fmt_date, fmt_percent, fmt_amount: Format_type;
165    first_day: constant Time:= Time_Of(2014, 03, 28, 9.0*3600.0);
166    price, last_price: Long_Float;
167    gen: Generator;
168  begin
169    Create(xl, "/tmp/Fancy.xls");
170    -- Some page layout for printing...
171    Header(xl, "Fancy sheet");
172    Footer(xl, "&D");
173    Margins(xl, 1.2, 1.1, 0.9, 0.8);
174    Print_Gridlines(xl);
175    Page_Setup(xl, fit_height_with_n_pages => 0, orientation => portrait, scale_or_fit => fit);
176    --
177    Write_column_width(xl, 1, 15); -- set to width of n times '0'
178    Write_column_width(xl, 3, 10); -- set to width of n times '0'
179    Define_font(xl, "Calibri", 15, font_title, bold, white);
180    Define_font(xl, "Calibri", 10, font_normal);
181    Define_font(xl, "Calibri", 10, font_normal_grey, color => grey);
182    Define_format(xl, font_title, general, fmt_title,
183      border => bottom, background_color => dark_blue,
184      vertical_align => centred
185    );
186    Define_format(xl, font_normal, general, fmt_subtitle, border => bottom);
187    Define_format(xl, font_normal, dd_mm_yyyy, fmt_date, background_color => silver);
188    Define_format(xl, font_normal, decimal_0_thousands_separator, fmt_amount);
189    Define_format(xl, font_normal_grey, percent_2_plus, fmt_percent);
190    Use_format(xl, fmt_title);
191    Write_row_height(xl, 1, 25);
192    Put(xl, "Daily Excel Writer stock prices");
193    Merge(xl, 3);
194    New_Line(xl);
195    Use_format(xl, fmt_subtitle);
196    Put(xl,"Date");
197    Put(xl,"Price");
198    Put_Line(xl,"Variation %");
199    Freeze_Panes_at_cursor(xl);
200    Reset(gen);
201    price:= 950.0 + Long_Float(Random(gen)) * 200.0;
202    for i in 1..3650 loop
203      Use_format(xl, fmt_date);
204      Put(xl, first_day + i * Day_Duration'Last);
205      Use_format(xl, fmt_amount);
206      last_price:= price;
207      price:= price * (1.0 + 0.1 * (Long_Float(Random(gen)) - 0.49));
208      Put(xl, price);
209      Use_format(xl, fmt_percent);
210      Put_Line(xl, price / last_price - 1.0);
211    end loop;
212    Close(xl);
213  end Fancy;
214
215  function My_nice_sheet(size: Positive) return String is
216    xl: Excel_Out_String;
217  begin
218    Create(xl);
219    Put_Line(xl, "This Excel file is fully created in memory.");
220    Put_Line(xl, "It can be stuffed directly into a zip stream,");
221    Put_Line(xl, "or sent from a server!");
222    Put_Line(xl, "- see ZipTest @ project Zip-Ada (search ""unzip-ada"" or ""zip-ada""");
223    for row in 1 .. size loop
224      for column in 1 .. size loop
225        Write(xl, row + 5, column, 0.01 + Long_Float(row * column));
226      end loop;
227    end loop;
228    Close(xl);
229    return Contents(xl);
230  end My_nice_sheet;
231
232  procedure String_demo is
233    use Ada.Streams.Stream_IO;
234    f: File_Type;
235  begin
236    Create(f, Out_File, "/tmp/From_string.xls");
237    String'Write(Stream(f), My_nice_sheet(200));
238    Close(f);
239  end String_demo;
240
241  procedure Speed_test is
242    xl: Excel_Out_File;
243    t0, t1: Time;
244    iter: constant:= 1000;
245    size: constant:= 150;
246    secs: Long_Float;
247    dummy_int: Integer:= 0;
248  begin
249    Create(xl, "/tmp/Speed_test.xls");
250    t0:= Clock;
251    for i in 1..iter loop
252      declare
253        dummy: constant String:= My_nice_sheet(size);
254      begin
255        dummy_int:= 0 * dummy_int + dummy'Length;
256      end;
257    end loop;
258    t1:= Clock;
259    secs:= Long_Float(t1-t0);
260    Put_Line(xl,
261      "Time (seconds) for creating" &
262      Integer'Image(iter) & " sheets with" &
263      Integer'Image(size) & " x" &
264      Integer'Image(size) & " =" &
265      Integer'Image(size**2) & " cells"
266    );
267    Put_Line(xl, secs);
268    Put_Line(xl, "Sheets per second");
269    Put_Line(xl, Long_Float(iter) / secs);
270    Close(xl);
271  end Speed_test;
272
273  use Ada.Text_IO;
274
275begin
276  Put_Line("/tmp/Small demo -> Small.xls");
277  Small_demo;
278  Put_Line("/tmp/Big demo -> Big [...].xls");
279  for ef in Excel_type loop
280    Big_demo(ef);
281  end loop;
282  Put_Line("/tmp/Fancy sheet -> Fancy.xls");
283  Fancy;
284  Put_Line("/tmp/Excel sheet in a string demo -> From_string.xls");
285  String_demo;
286  Put_Line("/tmp/Speed test -> Speed_test.xls");
287  Speed_test;
288end Excel_Out_Demo;
289