1 /*
2 * pln.c: read sheets using a Plan Perfect encoding.
3 *
4 * Kevin Handy <kth@srv.net>
5 * Based on ff-csv code.
6 */
7
8 #include <gnumeric-config.h>
9 #include <glib/gi18n-lib.h>
10 #include <gnumeric.h>
11 #include <goffice/goffice.h>
12 #include <gnm-plugin.h>
13 #include <sheet.h>
14 #include <ranges.h>
15 #include <value.h>
16 #include <expr.h>
17 #include <cell.h>
18 #include <workbook.h>
19 #include <workbook-view.h>
20 #include <parse-util.h>
21 #include <sheet-style.h>
22 #include <style.h>
23 #include <mstyle.h>
24
25 #include <gsf/gsf-utils.h>
26 #include <gsf/gsf-input.h>
27 #include <string.h>
28 #include <math.h>
29
30 GNM_PLUGIN_MODULE_HEADER;
31
32 gboolean pln_file_probe (GOFileOpener const *fo, GsfInput *input,
33 GOFileProbeLevel pl);
34 void pln_file_open (GOFileOpener const *fo, GOIOContext *io_context,
35 WorkbookView *wb_view, GsfInput *input);
36
37 static char const *formula1[] = {
38 NULL, /* 0 */
39 "-(",
40 "ABS(",
41 "INT(",
42 "SIGN(",
43 "NOT(",
44 "TRUE(",
45 "FALSE(",
46 "AND(",
47 "OR(",
48 "AVERAGE(", /* 10 */
49 "COUNT(",
50 "MIN(",
51 "MAX(",
52 "NA(",
53 "ISNA(",
54 "NOW(",
55 "TODAY(",
56 "FACT(",
57 "ROW(",
58 "COLUMN(" /* 20 */
59 };
60
61 static char const * formula2[] =
62 {
63 "?bad1?(", /* 0 */
64 "POWER(",
65 "LN(",
66 "LOG(",
67 "SQRT(",
68 "PI(",
69 "EXP(",
70 "SIN(",
71 "COS(",
72 "TAN(",
73 "MOD(", /* 10 */
74 "ASIN(",
75 "ACOS(",
76 "ATAN(",
77 "TERM(",
78 "PV(",
79 "PMT(",
80 "FV(",
81 "NPV(",
82 "LOOKUP(",
83 "INDEX(", /* 20 */
84 "ROUND(",
85 "STDEV(",
86 "CONCAT(",
87 "MID(",
88 "LENGTH(",
89 "VALUE(",
90 "TEXT(",
91 "MDY(",
92 "MONTH(",
93 "DAY(", /* 30 */
94 "YEAR(",
95 "DATETEXT(",
96 "DATEVALUE(",
97 "VAR(",
98 "RANDOM(",
99 "CURRENCY(",
100 "ITERATION(",
101 "ISVALUE(",
102 "ISTEXT(",
103 "REPLACE(", /* 40 */
104 "RADIANS(",
105 "CELL(",
106 "SUBTRACT(",
107 "IRR(",
108 "FIND(",
109 "LEFT(",
110 "RIGHT(",
111 "UPPER(",
112 "LOWER(",
113 "PROPER(",
114 "CHAR(", /* 50 */
115 "CODE(",
116 "TRIM(",
117 "REPEAT(",
118 "BLOCK(",
119 "CURSOR(",
120 "DDB(",
121 "SLN(",
122 "SYD(",
123 "RATE(", /* 60 */
124 "STATUS(",
125 "FOREACH(",
126 "DEGREES(",
127 "HOUR(",
128 "MINUTE(",
129 "SECOND(",
130 "HMS(",
131 "TIMETEXT(",
132 "TIMEVALUE(",
133 "PRODUCT(", /* 70 */
134 "QUOTIENT(",
135 "VARP(",
136 "STDEVP(",
137 "ATAN2(",
138 "MATCH(",
139 "MATCH2(",
140 "LOOKUP2(",
141 "LINK(",
142 "ISERR(",
143 "ISERR2(", /* 80 */
144 "CHOOSE("
145 };
146
147 typedef struct {
148 Sheet *sheet;
149 GHashTable *styles;
150 } PlanPerfectImport;
151
152 static guint8 const signature[] =
153 { 0xff, 'W','P','C', 0x10, 0, 0, 0, 0x9, 0xa };
154
155 /* in charset.c. */
156 guint8 *pln_get_str (guint8 const *ch, unsigned len);
157
158 static char const *
pln_get_func_table1(unsigned i)159 pln_get_func_table1 (unsigned i)
160 {
161 g_return_val_if_fail (0 < i && i < G_N_ELEMENTS (formula1), "ERROR");
162 return formula1 [i];
163 }
164 static char const *
pln_get_func_table2(unsigned i)165 pln_get_func_table2 (unsigned i)
166 {
167 g_return_val_if_fail (0 < i && i < G_N_ELEMENTS (formula2), "ERROR");
168 return formula2 [i];
169 }
170
171 gboolean
pln_file_probe(GOFileOpener const * fo,GsfInput * input,GOFileProbeLevel pl)172 pln_file_probe (GOFileOpener const *fo, GsfInput *input,
173 GOFileProbeLevel pl)
174 {
175 /*
176 * a plan-perfect header
177 * 0 = -1
178 * 1-3 = "WPC"
179 * 4-7 = 16 (double word)
180 * 8 = 9 (plan perfect file)
181 * 9 = 10 (worksheet file)
182 * 10 = major version number
183 * 11 = minor version number
184 * 12-13 = encryption key
185 * 14-15 = unused
186 */
187 char const *header = NULL;
188 if (!gsf_input_seek (input, 0, G_SEEK_SET))
189 header = gsf_input_read (input, sizeof (signature), NULL);
190 return header != NULL &&
191 memcmp (header, signature, sizeof (signature)) == 0;
192 }
193
194 static GnmStyle *
pln_get_style(PlanPerfectImport * state,guint8 const * data,gboolean is_cell)195 pln_get_style (PlanPerfectImport *state, guint8 const* data, gboolean is_cell)
196 {
197 guint16 attr, fmt, font;
198 guint32 key;
199 GnmStyle *res;
200
201 attr = GSF_LE_GET_GUINT16 (data);
202 fmt = GSF_LE_GET_GUINT16 (data+2);
203 font = GSF_LE_GET_GUINT16 (data+4);
204
205 /* Check for use of sheet defaults */
206 if (is_cell) {
207 GnmStyle *def = sheet_style_default (state->sheet);
208 if ((attr & 0x0700) == 0x0400) {
209 attr &= 0xf8ff;
210 switch (gnm_style_get_align_h (def)) {
211 default :
212 case GNM_HALIGN_GENERAL:break;
213 case GNM_HALIGN_LEFT: attr |= 0x0100; break;
214 case GNM_HALIGN_RIGHT: attr |= 0x0200; break;
215 case GNM_HALIGN_DISTRIBUTED:
216 case GNM_HALIGN_CENTER_ACROSS_SELECTION :
217 case GNM_HALIGN_CENTER: attr |= 0x0300; break;
218 }
219 }
220 if ((attr & 0x8000)) {
221 gboolean is_locked = gnm_style_get_contents_locked (def);
222 attr = (attr & 0x3fff) | (is_locked ? 0x4000 : 0);
223 }
224 gnm_style_unref (def);
225 }
226
227 /* bit bash a key containing all relevant info */
228 key = fmt << 16;
229 key |= font & 0xf800;
230 key |= (attr >> 4) & 0x07ff; /* drop type, hide 0, and top lock bit */
231
232 res = g_hash_table_lookup (state->styles, GINT_TO_POINTER (key));
233 if (res == NULL) {
234 static GnmHAlign const haligns[] = {
235 GNM_HALIGN_GENERAL, GNM_HALIGN_LEFT, GNM_HALIGN_RIGHT, GNM_HALIGN_CENTER
236 };
237 res = gnm_style_new_default ();
238 gnm_style_set_font_italic (res, (attr & 0x0010) ? TRUE : FALSE);
239 gnm_style_set_contents_hidden (res, (attr & 0x0020) ? TRUE : FALSE);
240 gnm_style_set_font_uline (res,
241 (attr & 0x1000) ? UNDERLINE_DOUBLE :
242 ((attr & 0x0040) ? UNDERLINE_SINGLE : UNDERLINE_NONE));
243 gnm_style_set_font_bold (res, (attr & 0x0080) ? TRUE : FALSE);
244 gnm_style_set_align_h (res, haligns [(attr & 0x300) >> 8]);
245
246 g_hash_table_insert (state->styles, GINT_TO_POINTER (key), res);
247 #warning generate formats
248 }
249
250 gnm_style_ref (res);
251 return res;
252 }
253
254 static gnm_float
pln_get_number(guint8 const * ch)255 pln_get_number (guint8 const * ch)
256 {
257 int exp;
258 gnm_float dvalue, scale = 256.0;
259 int i;
260
261 dvalue = 0.0;
262 exp = *ch;
263 for (i = 1; i <= 7; i++) {
264 dvalue += ch[i] / scale;
265 scale *= 256;
266 }
267 if (exp & 128)
268 dvalue = -dvalue;
269 dvalue = gnm_ldexp (dvalue, ((exp & 127) - 64) * 4);
270
271 return dvalue;
272 }
273
274 static char *
pln_get_addr(GnmParsePos const * pp,guint8 const * ch)275 pln_get_addr (GnmParsePos const *pp, guint8 const *ch)
276 {
277 guint16 r = GSF_LE_GET_GUINT16 (ch);
278 guint16 c = GSF_LE_GET_GUINT16 (ch+2);
279 GnmCellRef ref;
280 GnmConventionsOut out;
281
282 ref.sheet = NULL;
283 ref.col_relative = ref.row_relative = FALSE;
284 ref.col = (c & 0x3fff);
285 ref.row = (r & 0x3fff);
286
287 switch (c & 0xc000) {
288 case 0xc000: ref.col = *((gint16 *)&c);
289 case 0x0000: ref.col_relative = TRUE;
290 break;
291 default :
292 break;
293 }
294 switch (r & 0xc000) {
295 case 0xc000: ref.row = *((gint16 *)&r);
296 case 0x0000: ref.row_relative = TRUE;
297 break;
298 default :
299 break;
300 }
301
302 out.accum = g_string_new (NULL);
303 out.pp = pp;
304 out.convs = gnm_conventions_default;
305 cellref_as_string (&out, &ref, TRUE);
306
307 return g_string_free (out.accum, FALSE);
308 }
309
310 static char *
pln_convert_expr(GnmParsePos const * pp,guint8 const * ch,size_t datalen)311 pln_convert_expr (GnmParsePos const *pp, guint8 const *ch, size_t datalen)
312 {
313 GString *expr = g_string_new (NULL);
314 guint8 *str;
315 guint8 const *end;
316 int len, code;
317 unsigned ui;
318
319 g_return_val_if_fail (datalen >= 2, g_string_free (expr, FALSE));
320
321 /* Expressions are stored INFIX so it is easier to just generate text */
322 ui = GSF_LE_GET_GUINT16 (ch);
323 g_return_val_if_fail (ui <= datalen - 2, g_string_free (expr, FALSE));
324
325 ch += 2;
326 #if DEBUG_EXPR
327 puts (cellpos_as_string (&pp->eval));
328 gsf_mem_dump (ch, ui);
329 #endif
330 for (end = ch + ui ; ch < end ; ) {
331 code = *ch++;
332 switch (code) {
333 case 1: g_string_append_c (expr, '+'); break;
334 case 5: /* Unary minus */
335 case 2: g_string_append_c (expr, '-'); break;
336 case 3: g_string_append_c (expr, '*'); break;
337 case 4: g_string_append_c (expr, '/'); break;
338 case 6: g_string_append_c (expr, '%'); break;
339 case 7: g_string_append (expr, "SUM("); break;
340 case 11: g_string_append_c (expr, '^'); break;
341
342 case 9: /* Text constant */
343 len = *ch;
344 str = pln_get_str (ch + 1, len);
345 g_string_append_c (expr, '\"');
346 go_strescape (expr, str);
347 g_string_append_c (expr, '\"');
348 ch += len + 1;
349 g_free (str);
350 break;
351
352 case 10: /* Named block */
353 len = *ch;
354 g_string_append_len (expr, ch + 1, len);
355 ch += len + 1;
356 break;
357
358 case 12: g_string_append (expr, pln_get_func_table1 (*ch++));
359 break;
360 case 13: g_string_append (expr, pln_get_func_table2 (*ch++));
361 break;
362 case 14: /* Special '+' which sums contiguous cells */
363 g_string_append (expr, "?+?");
364 break;
365 case 15: g_string_append (expr, "_MOD_"); break;
366 case 16: g_string_append (expr, "_NOT_"); break;
367 case 17: g_string_append (expr, "_AND_"); break;
368 case 18: g_string_append (expr, "_OR_"); break;
369 case 19: g_string_append (expr, "_XOR_"); break;
370 case 20: g_string_append (expr, "IF("); break;
371
372 case 21: /* Compare function */
373 switch (*ch) {
374 case 1: g_string_append (expr, "="); break;
375 case 2: g_string_append (expr, "<>"); break;
376 case 3: g_string_append (expr, ">"); break;
377 case 4: g_string_append (expr, ">="); break;
378 case 5: g_string_append (expr, "<"); break;
379 case 6: g_string_append (expr, "<="); break;
380 default:
381 g_warning ("unknown comparative operator %u", *ch);
382 }
383 ch++;
384 break;
385
386 case 22: g_string_append_c (expr, ','); break;
387 case 23: g_string_append_c (expr, '('); break;
388 case 24: g_string_append_c (expr, ')'); break;
389
390 case 25: {
391 unsigned sp = *ch++;
392 go_string_append_c_n (expr, ' ', sp);
393 break;
394 }
395
396 case 26: /* Special formula error code */
397 g_string_append (expr, "??ERROR??");
398 break;
399
400 case 27: /* Cell reference */
401 str = pln_get_addr (pp, ch);
402 g_string_append (expr, str);
403 g_free (str);
404 ch += 4;
405 break;
406
407 case 28: /* Block reference */
408 str = pln_get_addr (pp, ch);
409 g_string_append (expr, str);
410 g_free (str);
411 g_string_append_c (expr, ':');
412 str = pln_get_addr (pp, ch+4);
413 g_string_append (expr, str);
414 g_free (str);
415 ch += 8;
416 break;
417
418 case 29: g_string_append (expr, "<>1"); /* ?? is this right ?? */
419 break;
420
421 case 30: /* Floating point constant */
422 len = ch [8]; /* they store the ascii ?? will we be screwed by locale ? */
423 g_string_append_len (expr, ch+9, len);
424 ch += 9 + len;
425 break;
426
427 case 31: /* Reference to passed argument in user defined function */
428 g_string_append (expr, "_unknown31_");
429 ch++; /* ignore arg number */
430 break;
431
432 case 32: /* User function */
433 g_string_append (expr, "_unknown32_");
434 len = *ch;
435 ch += len + 1;
436 break;
437
438 case 33: /* Temporary variable (#:=) */
439 len = ch [1];
440 g_string_append (expr, "_unknown33_");
441 g_string_append_len (expr, ch+2, len);
442 ch += 2 + len;
443 break;
444
445 case 34: /* Temporary variable (#) */
446 len = ch [1];
447 g_string_append (expr, "_unknown34_");
448 g_string_append_len (expr, ch+2, len);
449 ch += 2 + len;
450 break;
451
452 case 35: g_string_append (expr, "0.");
453 break;
454
455 case 36: g_string_append_c (expr, '{'); break;
456 case 37: g_string_append_c (expr, ')'); break;
457 case 38: g_string_append (expr, "FACTORIAL"); break;
458 case 39: g_string_append (expr, "LOOKUP<"); break;
459 case 40: g_string_append (expr, "LOOKUP>"); break;
460
461 case 41: /* Attribute on */
462 case 42: /* Attribute off */
463 ch++; /* ignore */
464 break;
465
466 case 43: /* Total attributes for formula */
467 ch += 2;
468 break;
469
470 case 44: /* Conditional attribute */ break;
471 case 45: /* Assumed multiply - nop display */ break;
472
473 case 46: /* Date format */
474 ch++;
475 break;
476
477 default:
478 g_warning("PLN: Undefined formula code %d", code);
479 }
480 }
481
482 return g_string_free (expr, FALSE);
483 }
484
485 /* Font width should really be calculated, but it's too hard right now */
486 #define FONT_WIDTH 8
487 static double
pln_calc_font_width(guint16 cwidth,gboolean permit_default)488 pln_calc_font_width (guint16 cwidth, gboolean permit_default)
489 {
490 return (cwidth & 0xff) * FONT_WIDTH;
491 }
492
493 static GOErrorInfo *
pln_parse_sheet(GsfInput * input,PlanPerfectImport * state)494 pln_parse_sheet (GsfInput *input, PlanPerfectImport *state)
495 {
496 int max_col = gnm_sheet_get_max_cols (state->sheet);
497 int max_row = gnm_sheet_get_max_rows (state->sheet);
498 int i, rcode, rlength;
499 guint8 const *data;
500 GnmValue *v;
501 GnmStyle *style;
502 GnmParsePos pp;
503 GnmRange r;
504
505 range_init (&r, 0,0,0, gnm_sheet_get_max_rows (state->sheet));
506 parse_pos_init_sheet (&pp, state->sheet);
507
508 data = gsf_input_read (input, 16, NULL);
509 if (data == NULL || GSF_LE_GET_GUINT16 (data + 12) != 0)
510 return go_error_info_new_str (_("PLN : Spreadsheet is password encrypted"));
511
512 /* Process the record based sections
513 * Each record consists of a two-byte record type code,
514 * followed by a two byte length
515 */
516 do {
517 data = gsf_input_read (input, 4, NULL);
518 if (data == NULL)
519 break;
520
521 rcode = GSF_LE_GET_GUINT16 (data);
522 rlength = GSF_LE_GET_GUINT16 (data + 2);
523
524 data = gsf_input_read (input, rlength, NULL);
525 if (data == NULL)
526 break;
527
528 switch (rcode) {
529 case 0x01:
530 /* guint16 last row with data;
531 */
532 max_col = GSF_LE_GET_GUINT16 (data + 2);
533 break;
534
535 case 0x02:
536 /* char ascii char of decimal point 0
537 * char ascii char of thousands separator 1
538 * WPCHAR 1-6 bytes for currency symbol 2
539 * guint16 default worksheet attributs 8
540 * guint16 default worksheet format 10
541 * guint32 default font 12
542 * guint16 default col width 16
543 */
544 break;
545
546 case 0x03: /* Column format information */
547 for (i = 0; i < rlength / 6; i++, data += 6)
548 if (i <= max_col) {
549 double const width = pln_calc_font_width (
550 GSF_LE_GET_GUINT16 (data + 4), TRUE);
551 sheet_col_set_size_pts (state->sheet, i, width, FALSE);
552 r.start.col = r.end.col = i;
553 sheet_style_apply_range (state->sheet, &r,
554 pln_get_style (state, data, FALSE));
555 }
556 break;
557
558 default:
559 ;
560 /* g_warning("PLN : Record handling code for code %d not yet written", rcode); */
561 }
562 } while (rcode != 25);
563
564 /* process the CELL information */
565 while (NULL != (data = gsf_input_read (input, 20, NULL))) {
566 GnmExprTop const *texpr = NULL;
567 GnmCell *cell = NULL;
568 unsigned type = GSF_LE_GET_GUINT16 (data + 12);
569 unsigned length = GSF_LE_GET_GUINT16 (data + 18);
570
571 pp.eval.row = GSF_LE_GET_GUINT16 (data + 0);
572 pp.eval.col = GSF_LE_GET_GUINT16 (data + 2);
573 /* Special value indicating end of sheet */
574 if (pp.eval.row == 0xFFFF)
575 return NULL;
576
577 if (pp.eval.row > max_row)
578 return go_error_info_new_printf (
579 _("Ignoring data that claims to be in row %u which is > max row %u"),
580 pp.eval.row, max_row);
581 if (pp.eval.col > max_col)
582 return go_error_info_new_printf (
583 _("Ignoring data that claims to be in column %u which is > max column %u"),
584 pp.eval.col, max_col);
585
586 v = NULL;
587 if ((type & 0x7) != 0) {
588 style = pln_get_style (state, data, TRUE);
589 if (style != NULL)
590 sheet_style_set_pos (state->sheet, pp.eval.col, pp.eval.row, style);
591 if ((type & 0x7) != 6)
592 cell = sheet_cell_fetch (state->sheet, pp.eval.col, pp.eval.row);
593 } else {
594 style = NULL;
595 }
596
597 switch (type & 0x7) {
598 /* Empty Cell */
599 case 0:
600 if (length != 0) {
601 g_warning ("an empty unformated cell has an expression ?");
602 } else
603 continue;
604
605 /* Floating Point */
606 case 1: v = value_new_float (pln_get_number (data + 4));
607 break;
608 /* Short Text */
609 case 2:
610 v = value_new_string_nocopy (
611 pln_get_str (data + 5, data[4]));
612 break;
613 /* Long Text */
614 case 3: data = gsf_input_read (input, GSF_LE_GET_GUINT16 (data+4), NULL);
615 if (data != NULL)
616 v = value_new_string_nocopy (
617 pln_get_str (data + 2, GSF_LE_GET_GUINT16 (data)));
618 break;
619 /* Error Cell */
620 case 4: v = value_new_error_VALUE (NULL);
621 break;
622 /* na Cell */
623 case 5: v = value_new_error_NA (NULL);
624 break;
625 /* format only, no data in cell */
626 case 6: break;
627 }
628
629 if (length != 0) {
630 data = gsf_input_read (input, length, NULL);
631 if (cell != NULL && data != NULL) {
632 char *expr_txt = pln_convert_expr (&pp, data, length);
633
634 if (expr_txt != NULL) {
635 texpr = gnm_expr_parse_str (expr_txt, &pp,
636 GNM_EXPR_PARSE_DEFAULT,
637 gnm_conventions_default,
638 NULL);
639 if (texpr == NULL) {
640 value_release (v);
641 v = value_new_string_nocopy (expr_txt);
642 } else
643 g_free (expr_txt);
644 }
645 }
646 }
647
648 if (texpr != NULL) {
649 if (v != NULL)
650 gnm_cell_set_expr_and_value (cell, texpr, v, TRUE);
651 else
652 gnm_cell_set_expr (cell, texpr);
653 gnm_expr_top_unref (texpr);
654 } else if (v != NULL)
655 gnm_cell_set_value (cell, v);
656 }
657
658 return NULL;
659 }
660
661 void
pln_file_open(GOFileOpener const * fo,GOIOContext * io_context,WorkbookView * wb_view,GsfInput * input)662 pln_file_open (GOFileOpener const *fo, GOIOContext *io_context,
663 WorkbookView *wb_view, GsfInput *input)
664 {
665 Workbook *wb;
666 char *name;
667 Sheet *sheet;
668 GOErrorInfo *error;
669 PlanPerfectImport state;
670
671 wb = wb_view_get_workbook (wb_view);
672 name = workbook_sheet_get_free_name (wb, "PlanPerfect", FALSE, TRUE);
673 sheet = sheet_new (wb, name, 256, 65536);
674 g_free (name);
675 workbook_sheet_attach (wb, sheet);
676 sheet_flag_recompute_spans (sheet);
677
678 state.sheet = sheet;
679 state.styles = g_hash_table_new_full (
680 g_direct_hash, g_direct_equal,
681 NULL, (GDestroyNotify) gnm_style_unref);
682 error = pln_parse_sheet (input, &state);
683 g_hash_table_destroy (state.styles);
684 if (error != NULL) {
685 workbook_sheet_delete (sheet);
686 go_io_error_info_set (io_context, error);
687 }
688 }
689