1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291 | -- 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;
-- 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;
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;
quotation_day : 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, quotation_day);
quotation_day := quotation_day + Day_Duration'Last;
Use_format (xl, 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));
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;
|