1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: t; c-basic-offset: 4 -*- */
2 /* libwps
3 * Version: MPL 2.0 / LGPLv2.1+
4 *
5 * This Source Code Form is subject to the terms of the Mozilla Public
6 * License, v. 2.0. If a copy of the MPL was not distributed with this
7 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 *
9 * Major Contributor(s):
10 * Copyright (C) 2006, 2007 Andrew Ziem
11 * Copyright (C) 2004 Marc Maurer (uwog@uwog.net)
12 * Copyright (C) 2004-2006 Fridrich Strba (fridrich.strba@bluewin.ch)
13 *
14 * For minor contributions see the git repository.
15 *
16 * Alternatively, the contents of this file may be used under the terms
17 * of the GNU Lesser General Public License Version 2.1 or later
18 * (LGPLv2.1+), in which case the provisions of the LGPLv2.1+ are
19 * applicable instead of those above.
20 */
21
22 #include <map>
23 #include <sstream>
24
25 #include "WPSStream.h"
26
27 #include "QuattroFormula.h"
28
29 /** namespace to regroup data used to read QuattroPro .wb1-3, .qpw formula */
30 namespace QuattroFormulaInternal
31 {
32 struct Functions
33 {
34 char const *m_name;
35 int m_arity;
36 };
37
38 struct State
39 {
40 /** constructor */
StateQuattroFormulaInternal::State41 State(QuattroFormulaManager::CellReferenceFunction const &readCellReference, int version)
42 : m_readCellReferenceFunction(readCellReference)
43 , m_version(version)
44 , m_idFunctionsMap()
45 , m_idToDLLName1Map()
46 , m_actDLLName1Id(-1)
47 , m_idToDLLName2Map()
48 {
49 if (m_version>=2)
50 {
51 // in .qpw, H/VLookUp have four arguments
52 m_idFunctionsMap =
53 {
54 {0x55, {"VLookUp", 4}},
55 {0x5a, {"HLookup", 4}}
56 };
57 }
58 }
59
60 /** function to call to read a cell reference*/
61 QuattroFormulaManager::CellReferenceFunction m_readCellReferenceFunction;
62 /** the file version: 1: .wb1-3, 2: .qpw*/
63 int m_version;
64 /** the function which differs from default */
65 std::map<int, Functions> m_idFunctionsMap;
66 //! map id to DLL name 1
67 std::map<int, librevenge::RVNGString> m_idToDLLName1Map;
68 //! the current id DLL name 1
69 int m_actDLLName1Id;
70 //! map id to DLL name2
71 std::map<Vec2i, librevenge::RVNGString> m_idToDLLName2Map;
72 };
73 }
74
75 // constructor
QuattroFormulaManager(QuattroFormulaManager::CellReferenceFunction const & readCellReference,int version)76 QuattroFormulaManager::QuattroFormulaManager(QuattroFormulaManager::CellReferenceFunction const &readCellReference, int version)
77 : m_state(new QuattroFormulaInternal::State(readCellReference, version))
78 {
79 }
80
addDLLIdName(int id,librevenge::RVNGString const & name,bool func1)81 void QuattroFormulaManager::addDLLIdName(int id, librevenge::RVNGString const &name, bool func1)
82 {
83 if (name.empty())
84 {
85 WPS_DEBUG_MSG(("QuattroFormulaManager::addDLLIdName: called with empty name for id=%d\n", id));
86 return;
87 }
88 if (func1)
89 {
90 m_state->m_actDLLName1Id=id;
91 auto &map = m_state->m_idToDLLName1Map;
92 if (map.find(id) != map.end())
93 {
94 WPS_DEBUG_MSG(("QuattroFormulaManager::addDLLIdName: called with dupplicated id=%d\n", id));
95 }
96 else
97 map[id]=name;
98 return;
99 }
100 if (m_state->m_actDLLName1Id<0)
101 {
102 WPS_DEBUG_MSG(("QuattroFormulaManager::addDLLIdName: oops, unknown name1 id for %d\n", id));
103 return;
104 }
105 auto &map = m_state->m_idToDLLName2Map;
106 Vec2i fId(m_state->m_actDLLName1Id, id);
107 if (map.find(fId) != map.end())
108 {
109 WPS_DEBUG_MSG(("QuattroFormulaManager::addDLLIdName: called with dupplicated id=%d,%d\n", m_state->m_actDLLName1Id, id));
110 }
111 else
112 map[fId]=name;
113 return;
114 }
115
116 //------------------------------------------------------------
117 // read a formula
118 //------------------------------------------------------------
119 namespace QuattroFormulaInternal
120 {
121 static Functions const s_listFunctions[] =
122 {
123 // 0
124 { "", 0} /*SPEC: double*/, {"", 0}/*SPEC: cell*/, {"", 0}/*SPEC: cells*/, {"=", 1} /*SPEC: end of formula*/,
125 { "(", 1} /* SPEC: () */, {"", 0}/*SPEC: int*/, { "", -2} /*SPEC: text*/, {"", -2} /*SPEC: default argument*/,
126 { "-", 1}, {"+", 2}, {"-", 2}, {"*", 2},
127 { "/", 2}, { "^", 2}, {"=", 2}, {"<>", 2},
128
129 // 1
130 { "<=", 2},{ ">=", 2},{ "<", 2},{ ">", 2},
131 { "And", 2},{ "Or", 2}, { "Not", 1}, { "+", 1},
132 { "&", 2}, { "", -2} /*halt*/, { "DLL", 0} /*DLL*/,{ "", -2} /*extended noop: 1b00011c020400020000000 means A*/,
133 { "", -2} /*extended op*/,{ "", -2} /*reserved*/,{ "", -2} /*reserved*/,{ "NA", 0} /*checkme*/,
134
135 // 2
136 { "NA", 0} /* Error*/,{ "Abs", 1},{ "Int", 1},{ "Sqrt", 1},
137 { "Log10", 1},{ "Ln", 1},{ "Pi", 0},{ "Sin", 1},
138 { "Cos", 1},{ "Tan", 1},{ "Atan2", 2},{ "Atan", 1},
139 { "Asin", 1},{ "Acos", 1},{ "Exp", 1},{ "Mod", 2},
140
141 // 3
142 { "Choose", -1},{ "IsNa", 1},{ "IsError", 1},{ "False", 0},
143 { "True", 0},{ "Rand", 0},{ "Date", 3},{ "Now", 0},
144 { "PMT", 3} /*BAD*/,{ "QPRO_PV", 3} /*BAD*/,{ "QPRO_FV", 3} /*BAD*/,{ "IF", 3},
145 { "Day", 1},{ "Month", 1},{ "Year", 1},{ "Round", 2},
146
147 // 4
148 { "Time", 3},{ "Hour", 1},{ "Minute", 1},{ "Second", 1},
149 { "IsNumber", 1},{ "IsText", 1},{ "Len", 1},{ "Value", 1},
150 { "Fixed", 2}, { "Mid", 3}, { "Char", 1},{ "Ascii", 1},
151 { "Find", 3},{ "DateValue", 1} /*checkme*/,{ "TimeValue", 1} /*checkme*/,{ "CellPointer", 1} /*checkme*/,
152
153 // 5
154 { "Sum", -1},{ "Average", -1},{ "COUNT", -1},{ "Min", -1},
155 { "Max", -1},{ "VLookUp", 3},{ "NPV", 2}, { "Var", -1},
156 { "StDev", -1},{ "IRR", 2} /*BAD*/, { "HLookup", 3},{ "DSum", 3},
157 { "DAverage", 3},{ "DCount", 3},{ "DMin", 3},{ "DMax", 3},
158
159 // 6
160 { "DVar", 3},{ "DStd", 3},{ "Index", 3} /* index2d*/, { "Columns", 1},
161 { "Rows", 1},{ "Rept", 2},{ "Upper", 1},{ "Lower", 1},
162 { "Left", 2},{ "Right", 2},{ "Replace", 4}, { "Proper", 1},
163 { "Cell", 2},{ "Trim", 1},{ "Clean", 1},{ "IsText", 1},
164
165 // 7
166 { "IsNonText", 1},{ "Exact", 2},{ "QPRO_Call", -2} /*UNKN*/,{ "Indirect", 1},
167 { "RRI", 3}, { "TERM", 3}, { "CTERM", 3}, { "SLN", 3},
168 { "SYD", 4},{ "DDB", 4}, { "StDevP", -1}, { "VarP", -1},
169 { "DBStdDevP", 3}, { "DBVarP", 3}, { "PV", 5}, { "PMT", 5},
170
171 // 8
172 { "FV", 5}, { "Nper", 5}, { "Rate", 5}/*IRate*/, { "Ipmt", 6},
173 { "Ppmt", 6}, { "SumProduct", 2}, { "QPRO_MemAvail", 0}, { "QPRO_MememsAvail", 0},
174 { "QPRO_FileExist", 1}, { "QPRO_CurValue", 2}, { "Degrees", 1},{ "Radians", 1},
175 { "QPRO_Hex", 1},{ "QPRO_Num", 1},{ "Today", 0},{ "NPV", 2},
176
177 // 9
178 { "QPRO_CellIndex", 4}, { "QPRO_Version", 0}, { "", -2} /*UNKN*/,{ "", -2} /*UNKN*/,
179 { "QPRO_Dhol", 3} /* fixme name: DHOL ?*/, { "", -2} /*UNKN*/, { "", -2} /*UNKN*/,{ "", -2} /*UNKN*/,
180 { "", -2} /*UNKN*/,{ "", -2} /*UNKN*/, { "Sheet", 1}, { "", -2} /*UNKN*/,
181 { "", -2} /*UNKN*/,{ "Index", 4}, { "QPRO_CellIndex3d", -2} /*UNKN*/,{ "QPRO_property", 1},
182
183 // a
184 {"QPRO_DDE", 4}, {"QPRO_Command", 1}, {"QPRO_Gerlinie", 3} /* fixme: name GERLINIE? */
185 };
186
187 }
188
readFormula(std::shared_ptr<WPSStream> const & stream,long endPos,Vec2i const & position,int sheetId,std::vector<WKSContentListener::FormulaInstruction> & formula,std::string & error) const189 bool QuattroFormulaManager::readFormula(std::shared_ptr<WPSStream> const &stream, long endPos,
190 Vec2i const &position, int sheetId,
191 std::vector<WKSContentListener::FormulaInstruction> &formula, std::string &error) const
192 {
193 RVNGInputStreamPtr input = stream->m_input;
194 libwps::DebugFile &ascFile=stream->m_ascii;
195 formula.resize(0);
196 error = "";
197 long pos = input->tell();
198 if (endPos - pos < 4) return false;
199 auto sz = int(libwps::readU16(input)); // max 1024
200 if (endPos-pos-4 != sz) return false;
201
202 std::vector<QuattroFormulaInternal::CellReference> listCellsPos;
203 auto fieldPos= int(libwps::readU16(input)); // ref begin
204 if (fieldPos<0||fieldPos>sz)
205 {
206 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: can not find the field header\n"));
207 error="###fieldPos";
208 return false;
209 }
210 if (fieldPos!=sz)
211 {
212 input->seek(pos+4+fieldPos, librevenge::RVNG_SEEK_SET);
213 ascFile.addDelimiter(pos+4+fieldPos,'|');
214 while (!input->isEnd())
215 {
216 long actPos=input->tell();
217 if (actPos+4>endPos) break;
218 QuattroFormulaInternal::CellReference cell;
219 if (!m_state->m_readCellReferenceFunction(stream, endPos, cell, position, sheetId) || input->tell()<actPos+2)
220 {
221 input->seek(actPos, librevenge::RVNG_SEEK_SET);
222 break;
223 }
224 if (cell.empty())
225 {
226 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: find some deleted cells\n"));
227 }
228 else
229 listCellsPos.push_back(cell);
230 continue;
231 }
232 if (input->tell() !=endPos)
233 {
234 ascFile.addDelimiter(input->tell(),'@');
235 static bool first=true;
236 if (first)
237 {
238 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: potential formula codes\n"));
239 first=false;
240 }
241 error="###codes,";
242 }
243 input->seek(pos+4, librevenge::RVNG_SEEK_SET);
244 endPos=pos+4+fieldPos;
245 }
246 std::stringstream f;
247 std::vector<std::vector<WKSContentListener::FormulaInstruction> > stack;
248 bool ok = true;
249 size_t actCellId=0;
250 int numDefault=0;
251 while (long(input->tell()) != endPos)
252 {
253 double val;
254 bool isNaN;
255 pos = input->tell();
256 if (pos > endPos) return false;
257 auto wh = int(libwps::readU8(input));
258 int arity = 0;
259 WKSContentListener::FormulaInstruction instr;
260 bool noInstr=false;
261 switch (wh)
262 {
263 case 0x0:
264 if (endPos-pos<9 || !libwps::readDouble8(input, val, isNaN))
265 {
266 f.str("");
267 f << "###number";
268 error=f.str();
269 ok = false;
270 break;
271 }
272 instr.m_type=WKSContentListener::FormulaInstruction::F_Double;
273 instr.m_doubleValue=val;
274 break;
275 case 0x1:
276 if (actCellId>=listCellsPos.size())
277 {
278 f.str("");
279 f << "###unknCell" << actCellId;
280 error=f.str();
281 ok = false;
282 break;
283 }
284 stack.push_back(listCellsPos[actCellId++].m_cells);
285 noInstr=true;
286 break;
287 case 0x2:
288 if (actCellId>=listCellsPos.size())
289 {
290 f.str("");
291 f << "###unknListCell" << actCellId;
292 error=f.str();
293 ok = false;
294 break;
295 }
296 stack.push_back(listCellsPos[actCellId++].m_cells);
297 noInstr=true;
298 break;
299 case 0x5:
300 instr.m_type=WKSContentListener::FormulaInstruction::F_Long;
301 instr.m_longValue=long(libwps::read16(input));
302 break;
303 case 0x6:
304 instr.m_type=WKSContentListener::FormulaInstruction::F_Text;
305 while (!input->isEnd())
306 {
307 if (input->tell() >= endPos)
308 {
309 ok=false;
310 break;
311 }
312 auto c = char(libwps::readU8(input));
313 if (c==0) break;
314 instr.m_content += c;
315 }
316 break;
317 case 0x7: // maybe default parameter
318 ++numDefault;
319 noInstr=true;
320 break;
321 case 0x1a:
322 {
323 if (input->tell()+4 >= endPos)
324 {
325 ok=false;
326 break;
327 }
328 static bool first=true;
329 if (first)
330 {
331 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: this file contains some DLL functions, the result can be bad\n"));
332 first=false;
333 }
334 arity= int(libwps::read8(input));
335 std::stringstream s;
336 s << "DLL";
337 int ids[2];
338 for (auto &id : ids) id=int(libwps::readU16(input));
339 s << "_";
340 auto it1 = m_state->m_idToDLLName1Map.find(ids[0]);
341 if (it1!= m_state->m_idToDLLName1Map.end())
342 s << it1->second.cstr();
343 else
344 {
345 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: can not find DLL function0 name for id=%d\n", ids[0]));
346 s << "F" << ids[0];
347 f << "##DLLFunc0=" << ids[0] << ",";
348 }
349 s << "_";
350 auto it2 = m_state->m_idToDLLName2Map.find(Vec2i(ids[0],ids[1]));
351 if (it2!= m_state->m_idToDLLName2Map.end())
352 s << it2->second.cstr();
353 else
354 {
355 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: can not find DLL function1 name for id=%d\n", ids[1]));
356 s << "F" << ids[1];
357 f << "##DLLFunc1=" << ids[1] << ",";
358 }
359 instr.m_type=WKSContentListener::FormulaInstruction::F_Function;
360 instr.m_content=s.str();
361 break;
362 }
363 default:
364 {
365 auto fIt=m_state->m_idFunctionsMap.find(wh);
366 if (fIt!=m_state->m_idFunctionsMap.end())
367 {
368 instr.m_type=WKSContentListener::FormulaInstruction::F_Function;
369 instr.m_content=fIt->second.m_name;
370 arity = fIt->second.m_arity;
371 }
372 else if (unsigned(wh) >= WPS_N_ELEMENTS(QuattroFormulaInternal::s_listFunctions) || QuattroFormulaInternal::s_listFunctions[wh].m_arity == -2)
373 {
374 f.str("");
375 f << "##Funct" << std::hex << wh;
376 error=f.str();
377 ok = false;
378 break;
379 }
380 else
381 {
382 instr.m_type=WKSContentListener::FormulaInstruction::F_Function;
383 instr.m_content=QuattroFormulaInternal::s_listFunctions[wh].m_name;
384 arity = QuattroFormulaInternal::s_listFunctions[wh].m_arity;
385 }
386 ok=!instr.m_content.empty();
387 if (arity == -1) arity = int(libwps::read8(input));
388 break;
389 }
390 }
391
392 if (!ok) break;
393 if (noInstr) continue;
394 std::vector<WKSContentListener::FormulaInstruction> child;
395 if (instr.m_type!=WKSContentListener::FormulaInstruction::F_Function)
396 {
397 child.push_back(instr);
398 stack.push_back(child);
399 continue;
400 }
401 size_t numElt = stack.size();
402 arity-=numDefault;
403 numDefault=0;
404 if (arity<0 || int(numElt) < arity)
405 {
406 f.str("");
407 f << instr.m_content << "[##" << arity << "]";
408 error=f.str();
409 ok = false;
410 break;
411 }
412 //
413 // first treat the special cases
414 //
415 if (arity==3 && instr.m_type==WKSContentListener::FormulaInstruction::F_Function && instr.m_content=="TERM")
416 {
417 // @TERM(pmt,pint,fv) -> NPER(pint,-pmt,pv=0,fv)
418 auto pmt=stack[size_t(int(numElt)-3)];
419 auto pint=stack[size_t(int(numElt)-2)];
420 auto fv=stack[size_t(int(numElt)-1)];
421
422 stack.resize(size_t(++numElt));
423 // pint
424 stack[size_t(int(numElt)-4)]=pint;
425 //-pmt
426 auto &node=stack[size_t(int(numElt)-3)];
427 instr.m_type=WKSContentListener::FormulaInstruction::F_Operator;
428 instr.m_content="-";
429 node.resize(0);
430 node.push_back(instr);
431 instr.m_content="(";
432 node.push_back(instr);
433 node.insert(node.end(), pmt.begin(), pmt.end());
434 instr.m_content=")";
435 node.push_back(instr);
436 //pv=zero
437 instr.m_type=WKSContentListener::FormulaInstruction::F_Long;
438 instr.m_longValue=0;
439 stack[size_t(int(numElt)-2)].resize(0);
440 stack[size_t(int(numElt)-2)].push_back(instr);
441 //fv
442 stack[size_t(int(numElt)-1)]=fv;
443 arity=4;
444 instr.m_type=WKSContentListener::FormulaInstruction::F_Function;
445 instr.m_content="NPER";
446 }
447 else if (arity==3 && instr.m_type==WKSContentListener::FormulaInstruction::F_Function && instr.m_content=="CTERM")
448 {
449 // @CTERM(pint,fv,pv) -> NPER(pint,pmt=0,-pv,fv)
450 auto pint=stack[size_t(int(numElt)-3)];
451 auto fv=stack[size_t(int(numElt)-2)];
452 auto pv=stack[size_t(int(numElt)-1)];
453 stack.resize(size_t(++numElt));
454 // pint
455 stack[size_t(int(numElt)-4)]=pint;
456 // pmt=0
457 instr.m_type=WKSContentListener::FormulaInstruction::F_Long;
458 instr.m_longValue=0;
459 stack[size_t(int(numElt)-3)].resize(0);
460 stack[size_t(int(numElt)-3)].push_back(instr);
461 // -pv
462 auto &node=stack[size_t(int(numElt)-2)];
463 instr.m_type=WKSContentListener::FormulaInstruction::F_Operator;
464 instr.m_content="-";
465 node.resize(0);
466 node.push_back(instr);
467 instr.m_content="(";
468 node.push_back(instr);
469 node.insert(node.end(), pv.begin(), pv.end());
470 instr.m_content=")";
471 node.push_back(instr);
472
473 //fv
474 stack[size_t(int(numElt)-1)]=fv;
475 arity=4;
476 instr.m_type=WKSContentListener::FormulaInstruction::F_Function;
477 instr.m_content="NPER";
478 }
479
480 if ((instr.m_content[0] >= 'A' && instr.m_content[0] <= 'Z') || instr.m_content[0] == '(')
481 {
482 if (instr.m_content[0] != '(')
483 child.push_back(instr);
484
485 instr.m_type=WKSContentListener::FormulaInstruction::F_Operator;
486 instr.m_content="(";
487 child.push_back(instr);
488 for (int i = 0; i < arity; i++)
489 {
490 if (i)
491 {
492 instr.m_content=";";
493 child.push_back(instr);
494 }
495 auto const &node=stack[size_t(int(numElt)-arity+i)];
496 child.insert(child.end(), node.begin(), node.end());
497 }
498 instr.m_content=")";
499 child.push_back(instr);
500
501 stack.resize(size_t(int(numElt)-arity+1));
502 stack[size_t(int(numElt)-arity)] = child;
503 continue;
504 }
505 if (arity==1)
506 {
507 instr.m_type=WKSContentListener::FormulaInstruction::F_Operator;
508 stack[numElt-1].insert(stack[numElt-1].begin(), instr);
509 if (wh==3)
510 break;
511 continue;
512 }
513 if (arity==2)
514 {
515 instr.m_type=WKSContentListener::FormulaInstruction::F_Operator;
516 stack[numElt-2].push_back(instr);
517 stack[numElt-2].insert(stack[numElt-2].end(), stack[numElt-1].begin(), stack[numElt-1].end());
518 stack.resize(numElt-1);
519 continue;
520 }
521 ok=false;
522 error = "### unexpected arity";
523 break;
524 }
525
526 if (!ok) ;
527 else if (stack.size()==1 && stack[0].size()>1 && stack[0][0].m_content=="=")
528 {
529 formula.insert(formula.begin(),stack[0].begin()+1,stack[0].end());
530 if (input->tell()!=endPos)
531 {
532 // unsure, find some text here, maybe some note
533 static bool first=true;
534 if (first)
535 {
536 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: find some extra data\n"));
537 first=false;
538 }
539 error="##extra data";
540 ascFile.addDelimiter(input->tell(),'#');
541 }
542 return true;
543 }
544 else
545 error = "###stack problem";
546
547 static bool first = true;
548 if (first)
549 {
550 WPS_DEBUG_MSG(("QuattroFormulaManager::readFormula: I can not read some formula\n"));
551 first = false;
552 }
553
554 f.str("");
555 for (auto const &i : stack)
556 {
557 for (auto const &j : i)
558 f << j << ",";
559 f << "@";
560 }
561 f << error << "###";
562 error = f.str();
563 return false;
564 }
565 ////////////////////////////////////////////////////////////
566 // cell reference
567 ////////////////////////////////////////////////////////////
568 namespace QuattroFormulaInternal
569 {
operator <<(std::ostream & o,CellReference const & ref)570 std::ostream &operator<<(std::ostream &o, CellReference const &ref)
571 {
572 if (ref.m_cells.size()==1)
573 {
574 o << ref.m_cells[0];
575 return o;
576 }
577 o << "[";
578 for (auto const &r: ref.m_cells) o << r;
579 o << "]";
580 return o;
581 }
582 }
583
584 /* vim:set shiftwidth=4 softtabstop=4 noexpandtab: */
585
586