summaryrefslogtreecommitdiff
Side-by-side diff
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--noncore/apps/opie-sheet/ChangeLog4
-rw-r--r--noncore/apps/opie-sheet/sheet.cpp10
-rw-r--r--noncore/apps/opie-sheet/sheet.h2
3 files changed, 11 insertions, 5 deletions
diff --git a/noncore/apps/opie-sheet/ChangeLog b/noncore/apps/opie-sheet/ChangeLog
index 56887c5..e8a31ee 100644
--- a/noncore/apps/opie-sheet/ChangeLog
+++ b/noncore/apps/opie-sheet/ChangeLog
@@ -1,64 +1,68 @@
+January 8, 2004
+ - fixed issue : "-if someone enters directly text as parameter to a string function the text renders as uppercase due to the calculation engine that uppercases all the parsing sentence."
+ now the text is handled correctly.
+
January 7, 2004
* Release by hayzel (koppermind@panafonet.gr)
This version has many valuable changes, though It may have some annoying bugs. Please if you are interested in opie-sheet try it hard, so I can fix some of them. Also If you want some other functions that must be here and are missing feel free to ask them. (no financial functions please. :) I really hate them )
-Fixed a bug with non closed parenthesis editing&recalculation infinite loop.
-Added support for functions that can parse parameters not ONLY as numbers but also as strings.
-Added many functions that cover many computational topics rendering opie-sheet a computational tool-spreadsheet at last. (total 90 functions!)
-Maintained compatibility with the opie-fileformat.
-New icons.
-Found that the DataParser was not a real RPN compiler of the expressions. In fact it was returning faulty results in calculations, in both binary or unary operations. A1-A2-A3 was parsed as A1-(A2-A3). -A1 was parsed as A1.
-Added new class "Expression" a general Parser for spreadsheet-expression. Imported from an old C# project of mine.
-Now can also parse <>=!%&^|"" in expressions.
-Added experimental Excel File format import!. The opie-sheet can import any excel file in BIFF7/BIFF8 format. These formats are used in Excel XP,2000,95. The Excel Importer class is in a good coding level.. BUT it is not complete. Only strings,numbers,formulas are imported. Not formatting rules. Not all the functions are converted in the functions of opie-sheet. Infact FEW functions are converted.
-Fixed a bug with Sheet Recalculation. Added ReCalc() function. Opie-sheet was calculating wrong the values of expression in opening/importing. if a value needed was not loaded yet in the time of calculation. Solved with ReCalc() each time the active sheet is changing.
*known issues:
-if someone enters directly text as parameter to a string function the text renders as uppercase due to the calculation engine that uppercases all the parsing sentence.
-randbetween return only integer part random... if both limit numbers are integers.
-skew and kurt function give different results compared to kspread-oofice equivalents.
-unstable parser Excel Class
-string vars and string functions are not correctly handled by excel importer.
-unicode strings are converted FINE in QString unicode format, but cannot be rendered fine if a suitable unicode font is not setuped as the default string. So the string is junked in the opie-sheet and may crash the parser.
*TODOs:
-surelly a much full-stable excel importer.
-Cell Manipulation of many Data is really slow.... must change the QList data type. To a structure more efficient.
-maybe some more functions.
-maybe some kind of charts drawing?
-maybe kspread or ooffice files import/export.
October 08, 2002
* Release 1.0.2 (by thufir)
- Fixed bug #250 (exhibiting re-calculation error in multi-level calculation). (by thufir)
October 06, 2002
- Fixed data edit box clear problem. (by thufir)
October 05, 2002
- Fixed bug #252 (starting up in 'mouse button' mode). (by thufir)
August 31, 2002
- Fixed and updated opie-sheet.pro file. (by cniehaus)
August 01, 2002
- Fixed gcc3 compile problems. (by harlekin)
July 06, 2002
- Fixed DocLnk management to save documents correctly. (by leseb)
July 04, 2002
* Release 1.0.1 (by thufir)
- Sheet/Qt is now a part of Opie, so its name is changed to Opie Sheet. (by thufir)
- Fixed sorting bug. (by thufir)
April 14, 2002
* Release 1.0.0 (by thufir)
diff --git a/noncore/apps/opie-sheet/sheet.cpp b/noncore/apps/opie-sheet/sheet.cpp
index 103b83b..e1e4744 100644
--- a/noncore/apps/opie-sheet/sheet.cpp
+++ b/noncore/apps/opie-sheet/sheet.cpp
@@ -656,521 +656,523 @@ double Sheet::functionSum(const QString &param1, const QString &param2)
bool ok;
if (findRange(param1, param2, &row1, &col1, &row2, &col2))
{
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok) result+=tempResult;
}
return result;
}else
{
double d1=0,d2=0;
d1=calculateVariable(param1).toDouble(&ok);
d2=calculateVariable(param2).toDouble(&ok);
return(d1+d2);
};
return 0;
}
QString Sheet::functionIndex(const QString &param1, const QString &param2, int indx)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
int ii=1;
for (col=col1; col<=col2; ++col)
for (row=row1; row<=row2; ++row)
{
if(ii==indx) return text(row,col);
ii++;
}
return QString("");
}
double Sheet::functionVariancePopulation(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
double avg1=functionAvg(param1,param2);
double result=0, tempResult;
int count1=0;
bool ok;
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok) { result=result + (tempResult - avg1)*(tempResult - avg1); count1++;};
}
if(count1>0) result=result/double(count1); else result=0.0;
return result;
};
double Sheet::functionVariance(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
double avg1=functionAvg(param1,param2);
double result=0, tempResult;
int count1=0;
bool ok;
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok) { result=result + (tempResult - avg1)*(tempResult - avg1); count1++;};
}
if(count1>1) result=result/double(count1-1); else result=0.0;
return result;
};
double Sheet::functionSkew(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
double avg1=functionAvg(param1,param2);
double var1=sqrt(functionVariancePopulation(param1,param2));
if(var1==0.0) return 0.0;
double result=0, tempResult;
int count1=0;
bool ok;
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok)
{
result=result + (tempResult - avg1)*(tempResult - avg1)*(tempResult - avg1)/(var1*var1*var1);
count1++;
};
}
if(count1>0) result=result/double(count1); else result=0.0;
return result;
};
double Sheet::functionKurt(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
double avg1=functionAvg(param1,param2);
double var1=sqrt(functionVariancePopulation(param1,param2));
if(var1==0.0) return 0.0;
double result=0, tempResult;
int count1=0;
bool ok;
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok)
{
result=result + (tempResult - avg1)*(tempResult - avg1)*
(tempResult - avg1)*(tempResult - avg1)/(var1*var1*var1*var1);
count1++;
};
}
if(count1>0) result=result/double(count1)-3.0; else result=0.0;
return result;
};
double Sheet::functionSumSQ(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
double result=0, tempResult;
bool ok;
if (findRange(param1, param2, &row1, &col1, &row2, &col2))
{
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempResult=text(row, col).toDouble(&ok);
if (ok) result+=tempResult*tempResult;
}
return result;
}else
{
double d1=0,d2=0;
d1=calculateVariable(param1).toDouble(&ok);
d2=calculateVariable(param2).toDouble(&ok);
return(d1*d1+d2*d2);
};
return 0;
}
double Sheet::functionMin(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
double min=0, tempMin;
bool ok, init=FALSE;
if (findRange(param1, param2, &row1, &col1, &row2, &col2))
{
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempMin=text(row, col).toDouble(&ok);
if (ok && (!init || tempMin<min))
{
min=tempMin;
init=TRUE;
}
}
return min;
}else
{
double d1=0,d2=0;
d1=calculateVariable(param1).toDouble(&ok);
d2=calculateVariable(param2).toDouble(&ok);
if(d1<d2) return(d1); else return(d2);
};
return 0;
}
double Sheet::functionMax(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
double max=0, tempMax;
bool ok, init=FALSE;
if (findRange(param1, param2, &row1, &col1, &row2, &col2))
{
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
tempMax=text(row, col).toDouble(&ok);
if (ok && (!init || tempMax>max))
{
max=tempMax;
init=TRUE;
}
};
return max;
}else
{
double d1=0,d2=0;
d1=calculateVariable(param1).toDouble(&ok);
d2=calculateVariable(param2).toDouble(&ok);
if(d1>d2) return(d1); else return(d2);
};
return 0;
}
double Sheet::functionAvg(const QString &param1, const QString &param2)
{
double resultSum=functionSum(param1, param2), resultCount=functionCount(param1, param2);
return (resultCount>0 ? resultSum/resultCount : 0);
}
double Sheet::functionCount(const QString &param1, const QString &param2)
{
int row1, col1, row2, col2, row, col;
int divider=0;
bool ok;
if (findRange(param1, param2, &row1, &col1, &row2, &col2))
{
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
text(row, col).toDouble(&ok);
if (ok) ++divider;
};
return divider;
}else
{
double d1=0,d2=0;int ii=0;
d1=calculateVariable(param1).toDouble(&ok);
if (ok) ii++;
d2=calculateVariable(param2).toDouble(&ok);
if (ok) ii++;
return(ii);
};
return 0;
}
double Sheet::functionCountIf(const QString &param1, const QString &param2, const QString &param3)
{
int row1, col1, row2, col2, row, col;
if (!findRange(param1, param2, &row1, &col1, &row2, &col2)) return 0;
//same as count except check if each field is equal to param3
int divider=0;
QString s2;
bool ok;
s2=calculateVariable(param3);
for (row=row1; row<=row2; ++row)
for (col=col1; col<=col2; ++col)
{
text(row, col).toDouble(&ok);
if (ok && (s2==text(row,col)) ) ++divider;
}
return divider;
}
-QString Sheet::calculateFunction(const QString &function, const QString &parameters, int NumOfParams)
+QString Sheet::calculateFunction(const QString &func, const QString &parameters, int NumOfParams)
{
bool ok;
double val1=0.0,val2=0.0,val3=0.0;
long int vali=0;
int w1,w2;
int row,col;
QString s1,s2;
//basic functions
+ QString function;
+ function=func.upper();
if (function=="+")
{
s1=calculateVariable(getParameter(parameters, 0));
s2=calculateVariable(getParameter(parameters, 1));
val1=s1.toDouble(&ok)+s2.toDouble(&ok);
return QString::number(val1);
};
if (function=="-")
{
s1=calculateVariable(getParameter(parameters, 0));
s2=calculateVariable(getParameter(parameters, 1));
val1=s1.toDouble(&ok)-s2.toDouble(&ok);
return QString::number(val1);
};
if (function=="*")
{
val1=calculateVariable(
getParameter(parameters, 0)).toDouble(&ok)
*calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
return QString::number(val1);
};
if (function=="/")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val2==0.0) return QString("Err101");
val1=val1/val2;
return QString::number(val1);
};
if (function==">")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1>val2) return QString::number(1); else return QString::number(0);
};
if (function=="<")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1<val2) return QString::number(1); else return QString::number(0);
};
if (function==">=")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1>=val2) return QString::number(1); else return QString::number(0);
};
if (function=="<=")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1<=val2) return QString::number(1); else return QString::number(0);
};
if (function=="!=")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1!=val2) return QString::number(1); else return QString::number(0);
};
if (function=="=="||function=="=")
{
val1=calculateVariable(getParameter(parameters, 0)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1)).toDouble(&ok);
if(val1==val2) return QString::number(1); else return QString::number(0);
};
//LOGICAL / INFO
if (function=="ISBLANK")
{
if(findRowColumn(getParameter(parameters, 0), &row, &col, FALSE))
{
if(text(row,col).length()==0) val1=1; else val1=0;
}else
{
if(findRowColumn(calculateVariable(getParameter(parameters, 0)), &row,&col, FALSE))
{
if(text(row,col).length()==0) val1=1; else val1=0;
}else
{
val1=0;
};
};
return QString::number(val1);
};
if (function=="ISNUMBER")
{
if(findRowColumn(getParameter(parameters, 0, TRUE, function), &row, &col, FALSE))
{
val1=text(row,col).toDouble(&ok);
if(ok) val1=1; else val1=0;
}else
{
if(findRowColumn(calculateVariable(getParameter(parameters, 0, TRUE, function)), &row,&col, FALSE))
{
val1=text(row,col).toDouble(&ok);
if(ok) val1=1; else val1=0;
}else
{
val1=0;
};
};
return QString::number(val1);
};
if (function=="AND")
{
vali=calculateVariable(getParameter(parameters, 0, TRUE, function)).toInt(&ok)
& calculateVariable(getParameter(parameters, 1, TRUE, function)).toInt(&ok);
return QString::number(vali);
};
if (function=="OR")
{
vali=calculateVariable(getParameter(parameters, 0, TRUE, function)).toInt(&ok)
| calculateVariable(getParameter(parameters, 1, TRUE, function)).toInt(&ok);
return QString::number(vali);
};
if (function=="NOT")
{
vali=!calculateVariable(getParameter(parameters, 0, TRUE, function)).toInt(&ok);
return QString::number(vali);
};
// MATHEMATICAL FUNCTIONS
if (function=="ABS")
{
val1=fabs(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ACOS")
{
val1=acos(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ACOSH")
{
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
if(val1<1.0) return QString::number(0);
val1=acosh(val1);
return QString::number(val1);
};
if (function=="ASIN")
{
val1=asin(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ASINH")
{
val1=asinh(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ATAN")
{
val1=atan(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ATAN2")
{
val1=atan2(calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok),
calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="ATANH")
{
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
if(val1<=-1.0 || val1>=1.0) return QString("Err101");
val1=atanh(val1);
return QString::number(val1);
};
if (function=="CEILING")
{
// rounds up param1 to specified accuracy param2
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
if(val2==0.0) return QString::number(val1);
val1=ceil(val1/val2)*val2;
return QString::number(val1);
};
if (function=="COS")
{
val1=cos(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="COSH")
{
val1=cosh(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="DEGREES")
{
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok)*180.0/M_PI;
return QString::number(val1);
};
if (function=="EXP")
{
val1=exp(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="FACT")
{
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=Factorial(val1);
return QString::number(val2);
};
if (function=="FLOOR")
{
// rounds down param1 to specified accuracy param2
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
if(val2==0.0) return QString::number(val1);
val1=floor(val1/val2)*val2;
return QString::number(val1);
};
if (function=="INT")
{
// rounds down param1
val1=int(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
return QString::number(val1);
};
if (function=="EVEN")
{
//converts param1 to even
vali=int(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
if(vali % 2 !=0) val1=vali+1; else val1=vali;
return QString::number(val1);
};
if (function=="ODD")
{
//converts param1 to odd
vali=int(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
if(vali % 2 !=0) val1=vali; else val1=vali+1;
return QString::number(val1);
};
if (function=="ISEVEN")
{
//Is Even param1?
vali=int(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
if(vali % 2 == 0) val1=1; else val1=0;
return QString::number(val1);
};
if (function=="ISODD")
{
//Is odd param1?
vali=int(calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok));
if(vali % 2 == 0) val1=0; else val1=1;
return QString::number(val1);
};
if (function=="LN")
{
// returns the natural logarithm of param1
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
if(val1<=0.0) return QString("Err101");
val1=log(val1);
return QString::number(val1);
};
@@ -1524,561 +1526,561 @@ QString Sheet::calculateFunction(const QString &function, const QString &paramet
};
if(function=="GAMMALN")
{
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=GammaLn(val1);
return QString::number(val2);
};
if(function=="ERF")
{
// ERF (a,b)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
return QString::number(ErrorFunction(val2)-ErrorFunction(val1));
};
if(function=="ERFC")
{
// ERFC (a,b)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
return QString::number(ErrorFunctionComplementary(val2)-ErrorFunctionComplementary(val1));
};
if(function=="POISSON")
{
// POISSON DISTR(x,n,distr/desnt)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 2, TRUE, function)).toInt(&ok);
if(vali==1)
{
return QString::number(GammaQ(floor(val1)+1, val2));
}else
{
return QString::number(exp(-val2)*pow(val2,val1)/exp(GammaLn(val1+1.0)));
};
};
if(function=="CHIDIST")
{
// POISSON CHIDIST(x,n,distr/density)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 2, TRUE, function)).toInt(&ok);
if(vali==1)
{
return QString::number(GammaP(val2/2.0,val1*val1/2.0));
} else
{
return QString::number(
pow(val1,val2-1.0)*exp(-val1*val1/2)/ ( pow(2,val2/2.0-1.0)*exp(GammaLn(val2/2.0)))
);
};
};
if(function=="CHI2DIST")
{
// POISSON CHISQUAREDIST(x,n,distr/density)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 2, TRUE, function)).toInt(&ok);
if(vali==1)
{
return QString::number(GammaP(val2/2.0,val1/2.0));
} else
{
return QString::number(
pow(val1,val2/2.0-1.0)/(exp(val1/2.0)*pow(sqrt(2.0),val2)*exp(GammaLn(val2/2.0)))
);
};
};
if(function=="BETAI")
{
// BETA INCOMPLETE BETA(x,a,b)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
val3=calculateVariable(getParameter(parameters, 2, TRUE, function)).toDouble(&ok);
return QString::number(BetaIncomplete(val2,val3,val1));
};
if(function=="GAMMAP")
{
// GammaP (x,a)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
return QString::number(GammaP(val2,val1));
};
if(function=="GAMMAQ")
{
// GammaQ (x,a)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
return QString::number(GammaQ(val2,val1));
};
if (function=="VAR")
{
val1=functionVariance(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
return QString::number(val1);
};
if (function=="VARP")
{
val1=functionVariancePopulation(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
return QString::number(val1);
};
if (function=="STDEV")
{
val1=functionVariance(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
if(val1<=0.0) return QString::number(0.0);
return QString::number(sqrt(val1));
};
if (function=="STDEVP")
{
val1=functionVariancePopulation(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
if(val1<=0.0) return QString::number(0.0);
return QString::number(sqrt(val1));
};
if (function=="SKEW")
{
val1=functionSkew(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
return QString::number(val1);
};
if (function=="KURT")
{
val1=functionKurt(getParameter(parameters, 0, TRUE, function), getParameter(parameters, 1, TRUE, function));
return QString::number(val1);
};
if(function=="GAMMADIST")
{
// GAMMADIST (x,alpha,beta,distribution?density1:0)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
val3=calculateVariable(getParameter(parameters, 2, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 3, TRUE, function)).toInt(&ok);
if(vali==1)//distribution
{
if(val3==0.0) return QString::number(0.0);
else
return QString::number(GammaP(val2,val1/val3));
}else //density
{
return QString::number(
pow(val1,val2-1.0)*exp(-val1/val3) / (pow(val3,val2)*exp(GammaLn(val2)))
);
};
};
if(function=="BETADIST")
{
// BETADIST (z,alpha,beta,distribution?density1:0)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
val3=calculateVariable(getParameter(parameters, 2, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 3, TRUE, function)).toInt(&ok);
if(vali==1)//distribution
{
return QString::number(BetaIncomplete(val2,val3,val1));
}else //density
{
return QString::number(
pow(val1,val2-1.0)*pow(1.0-val1,val3-1.0) / Beta(val2,val3)
);
};
};
if(function=="FDIST")
{
// FDIST (z,d1,d2,distribution?density1:0)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
val3=calculateVariable(getParameter(parameters, 2, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 3, TRUE, function)).toInt(&ok);
if(vali==1)//distribution
{
return QString::number(
-BetaIncomplete(val3/2,val2/2,val3/(val3+val2*val1))
+BetaIncomplete(val3/2,val2/2,1)
);
}else //density
{
return QString::number(
pow(val2,val2/2)*pow(val3,val3/2)*pow(val1,val2/2-1)/
(pow(val3+val2*val1,(val2+val3)/2)*Beta(val2/2,val3/2))
);
};
};
if(function=="NORMALDIST")
{
// NORMALDIST (x,m,s,distribution?density1:0)
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
val2=calculateVariable(getParameter(parameters, 1, TRUE, function)).toDouble(&ok);
val3=calculateVariable(getParameter(parameters, 2, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 3, TRUE, function)).toInt(&ok);
if(vali==1)//distribution
{
return QString::number(
(ErrorFunction((val1-val2)/(sqrt(2)*val3))+1)/2.0
);
}else //density
{
return QString::number(
exp(-pow(((val1-val2)/val3),2)/2)/(val3*sqrt(2*M_PI))
);
};
};
if(function=="PHI")
{
// NORMALDIST (x,distribution?density1:0) with mean=0 s=1.0
val1=calculateVariable(getParameter(parameters, 0, TRUE, function)).toDouble(&ok);
vali=calculateVariable(getParameter(parameters, 1, TRUE, function)).toInt(&ok);
if(vali==1)//distribution
{
return QString::number(
(ErrorFunction(val1/(sqrt(2)))+1)/2.0
);
}else //density
{
return QString::number(
exp(-pow(val1,2)/2)/(sqrt(2*M_PI))
);
};
};
/*
StudentTDistribution/: PDF[StudentTDistribution[n_], x_] :=
1/(Sqrt[n] Beta[n/2, 1/2]) Sqrt[n/(n+x^2)]^(n+1) /;
ParameterQ[StudentTDistribution[n]]
StudentTDistribution/: CDF[StudentTDistribution[n_], x_] :=
(1 + Sign[x] BetaRegularized[n/(n+x^2), 1, n/2, 1/2])/2 /;
ParameterQ[StudentTDistribution[n]]
*/
return 0;
};
QString Sheet::dataParserHelper(const QString &data)
{
if(data.left(1)=="""" && data.right(1)=="""") return QString(data);
Expression exp1(data);
exp1.Parse();
QStack<QString> stack1;
stack1.setAutoDelete(TRUE);
int i=0;
QString* s1;
QString* s2=NULL;
int* i1;
int args,tokentype;
QString tempval;
s1=exp1.CompiledBody.first();i1=exp1.CompiledBodyType.first();
while(i<=(int)exp1.CompiledBody.count()-1)
{
args= ((*i1) & 0xFF00)>>8; tokentype=(*i1) & 0x00FF;
if(tokentype==NUMBER_TOKEN)
{
stack1.push(new QString(*s1));
//printf("Parse:Number=%s\r\n",s1->latin1());
}
else if(tokentype==VARIABLE_TOKEN)
{
- stack1.push(new QString(*s1));
+ stack1.push(new QString(QString(*s1).upper()));
//printf("Parse:Var=%s\r\n",s1->latin1());
//here to put implementation of other types of variables except cell.
//for example names
}
else if(tokentype==STRING_TOKEN)
{
stack1.push(new QString(*s1));
//printf("Parse:String=%s\r\n",s1->ascii());
}
else if(tokentype==FUNCTION_TOKEN)
{
QString params="";
for(int w1=1;w1<=args;w1++)
{
if((int)stack1.count()!=0) s2=stack1.pop();
params=*s2+params;//args in reverse order
params=","+params;
};
params=params.mid(1);
if(params==NULL) params="0";
//printf("Parse:Func=%s, params=%s, stackcount=%d,args=%d\r\n"
// ,s1->latin1(),params.latin1(),stack1.count(),args);
tempval=calculateFunction(*s1,params,args);
- tempval=tempval.upper();
+ tempval=tempval;
stack1.push(new QString(tempval));
};
//loops to next token
if(exp1.CompiledBody.next()!=NULL) s1=exp1.CompiledBody.current(); else break;
if(exp1.CompiledBodyType.next()!=NULL) i1=exp1.CompiledBodyType.current(); else break;
i++;
};
if((int)stack1.count()!=0)s2=stack1.pop(); else s2=new QString("!ERROR");
tempval=*s2;
return(tempval);
};
QString Sheet::dataParser(const QString &cell, const QString &data)
{
QString strippedData(data);
strippedData.replace(QRegExp("\\s"), "");
if (strippedData.isEmpty() || strippedData[0]!='=') return data;
if (listDataParser.find(cell)!=listDataParser.end()) return "0";
listDataParser.append(cell);
// printf("DATAPARSER: data=%s, cell=%s\r\n",data.ascii(),cell.ascii());
- strippedData=dataParserHelper(strippedData.remove(0, 1).upper().replace(QRegExp(":"), ","));
+ strippedData=dataParserHelper(strippedData.remove(0, 1).replace(QRegExp(":"), ","));
int i=0;
QString tempParameter(getParameter(strippedData, i)), result="";
do
{
result+=","+calculateVariable(tempParameter);
tempParameter=getParameter(strippedData, ++i);
}
while (!tempParameter.isNull());
listDataParser.remove(cell);
return result.mid(1);
}
void Sheet::setData(const QString &data)
{
setText(currentRow(), currentColumn(), data);
slotCellChanged(currentRow(), currentColumn());
activateNextCell();
}
QString Sheet::getData()
{
typeCellData *cellData=findCellData(currentRow(), currentColumn());
if (cellData)
return cellData->data;
return "";
}
void Sheet::lockClicks(bool lock)
{
clicksLocked=lock;
}
void Sheet::paintCell(QPainter *p, int row, int col, const QRect & cr, bool selected)
{
if (selected && row==currentRow() && col==currentColumn()) selected=FALSE;
int sheetDataCurrent=sheetData.at();
typeCellData *cellData=findCellData(row, col);
if (sheetDataCurrent>=0) sheetData.at(sheetDataCurrent);
if (!cellData) cellData=&defaultCellData;
if (selected)
p->fillRect(0, 0, cr.width(), cr.height(), colorGroup().highlight());
else
{
p->fillRect(0, 0, cr.width(), cr.height(), colorGroup().base());
p->fillRect(0, 0, cr.width(), cr.height(), cellData->background);
}
QTableItem *cellItem=item(row, col);
if (cellItem)
{
p->setPen(selected ? colorGroup().highlightedText() : cellData->fontColor);
p->setFont(cellData->font);
QString str=cellItem->text();
p->drawText(2, 2, cr.width()-4, cr.height()-4, cellData->alignment, cellItem->text());
}
int rx=cr.width()-1, ry=cr.height()-1;
QPen pen(p->pen());
p->setPen(cellData->borders.right);
p->drawLine(rx, 0, rx, ry);
p->setPen(cellData->borders.bottom);
p->drawLine(0, ry, rx, ry);
p->setPen(pen);
}
void Sheet::viewportMousePressEvent(QMouseEvent *e)
{
QMouseEvent ce(e->type(), viewportToContents(e->pos()), e->globalPos(), e->button(), e->state());
if (clicksLocked)
{
if (selectionNo<0)
{
clearSelection();
QTableSelection newSelection;
newSelection.init(rowAt(ce.pos().y()), columnAt(ce.pos().x()));
newSelection.expandTo(newSelection.anchorRow(), newSelection.anchorCol());
selectionNo=addSelection(newSelection);
}
}
else
QTable::contentsMousePressEvent(&ce);
}
void Sheet::viewportMouseMoveEvent(QMouseEvent *e)
{
QMouseEvent ce(e->type(), viewportToContents(e->pos()), e->globalPos(), e->button(), e->state());
if (clicksLocked)
{
if (selectionNo>=0)
{
QTableSelection oldSelection(selection(selectionNo));
oldSelection.expandTo(rowAt(ce.pos().y()), columnAt(ce.pos().x()));
if (!(oldSelection==selection(selectionNo)))
{
removeSelection(selectionNo);
selectionNo=addSelection(oldSelection);
}
}
}
else
QTable::contentsMouseMoveEvent(&ce);
}
void Sheet::viewportMouseReleaseEvent(QMouseEvent *e)
{
QMouseEvent ce(e->type(), viewportToContents(e->pos()), e->globalPos(), e->button(), e->state());
if (clicksLocked && selectionNo>=0)
{
QTableSelection oldSelection(selection(selectionNo));
oldSelection.expandTo(rowAt(ce.pos().y()), columnAt(ce.pos().x()));
removeSelection(selectionNo);
selectionNo=-1;
if (oldSelection.topRow()==oldSelection.bottomRow() && oldSelection.leftCol()==oldSelection.rightCol())
emit cellClicked(findCellName(oldSelection.topRow(), oldSelection.leftCol()));
else
emit cellClicked(findCellName(oldSelection.topRow(), oldSelection.leftCol())+','+findCellName(oldSelection.bottomRow(), oldSelection.rightCol()));
}
else
QTable::contentsMouseReleaseEvent(&ce);
}
QString Sheet::findCellName(int row, int col)
{
return (getHeaderString(col+1)+QString::number(row+1));
}
void Sheet::copySheetData(QList<typeCellData> *destSheetData)
{
typeCellData *tempCellData, *newCellData;
destSheetData->clear();
for (tempCellData=sheetData.first(); tempCellData; tempCellData=sheetData.next())
{
newCellData=new typeCellData;
*newCellData=*tempCellData;
destSheetData->append(newCellData);
}
}
void Sheet::setSheetData(QList<typeCellData> *srcSheetData)
{
typeCellData *tempCellData, *newCellData;
for (tempCellData=sheetData.first(); tempCellData; tempCellData=sheetData.next())
{
clearCell(tempCellData->row, tempCellData->col);
updateCell(tempCellData->row, tempCellData->col);
}
sheetData.clear();
for (tempCellData=srcSheetData->first(); tempCellData; tempCellData=srcSheetData->next())
{
newCellData=new typeCellData;
*newCellData=*tempCellData;
sheetData.append(newCellData);
setText(newCellData->row, newCellData->col, dataParser(findCellName(newCellData->row, newCellData->col), newCellData->data));
}
emit sheetModified();
}
void Sheet::setName(const QString &name)
{
sheetName=name;
emit sheetModified();
}
QString Sheet::getName()
{
return sheetName;
}
void Sheet::setBrush(int row, int col, const QBrush &brush)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=createCellData(row, col);
if (cellData)
{
cellData->background=brush;
emit sheetModified();
}
}
QBrush Sheet::getBrush(int row, int col)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=&defaultCellData;
return cellData->background;
}
void Sheet::setTextAlign(int row, int col, Qt::AlignmentFlags flags)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=createCellData(row, col);
if (cellData)
{
cellData->alignment=flags;
emit sheetModified();
}
}
Qt::AlignmentFlags Sheet::getAlignment(int row, int col)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=&defaultCellData;
return cellData->alignment;
}
void Sheet::setTextFont(int row, int col, const QFont &font, const QColor &color)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=createCellData(row, col);
if (cellData)
{
cellData->font=font;
cellData->fontColor=color;
emit sheetModified();
}
}
QFont Sheet::getFont(int row, int col)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=&defaultCellData;
return cellData->font;
}
QColor Sheet::getFontColor(int row, int col)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=&defaultCellData;
return cellData->fontColor;
}
void Sheet::setPen(int row, int col, int vertical, const QPen &pen)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=createCellData(row, col);
if (cellData)
{
if (vertical)
cellData->borders.right=pen;
else
cellData->borders.bottom=pen;
emit sheetModified();
}
}
QPen Sheet::getPen(int row, int col, int vertical)
{
typeCellData *cellData=findCellData(row, col);
if (!cellData) cellData=&defaultCellData;
return (vertical ? cellData->borders.right : cellData->borders.bottom);
}
diff --git a/noncore/apps/opie-sheet/sheet.h b/noncore/apps/opie-sheet/sheet.h
index 41f1b86..f705cd0 100644
--- a/noncore/apps/opie-sheet/sheet.h
+++ b/noncore/apps/opie-sheet/sheet.h
@@ -1,211 +1,211 @@
/***************************************************************************
* *
* This program is free software; you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation; either version 2 of the License, or *
* (at your option) any later version. *
* *
***************************************************************************/
/*
* Opie Sheet (formerly Sheet/Qt)
* by Serdar Ozler <sozler@sitebest.com>
*/
#ifndef SHEET_H
#define SHEET_H
#include <qtable.h>
#include <qstack.h>
typedef struct typeCellBorders
{
QPen right, bottom;
};
typedef struct typeCellData
{
int col, row;
typeCellBorders borders;
QBrush background;
Qt::AlignmentFlags alignment;
QColor fontColor;
QFont font;
QString data;
};
#define NONE_TOKEN 0
#define NUMBER_TOKEN 1
#define VARIABLE_TOKEN 2
#define FUNCTION_TOKEN 3
#define SYMBOL_TOKEN 4
#define STRING_TOKEN 5
class Expression
{
public:
QString Body;
QList<QString> CompiledBody;
QList<int> CompiledBodyType;
QString SYMBOL;
QString MATHSYMBOL;
QArray<int> ArgsOfFunc;
int FuncDepth;
bool ErrorFound;
int n; // holds the current parser position
QString chunk; // the piece in the parser we are on
int SymbGroup; // the current type
QString InExpr;
QChar chunk0(void); // retunrs the first char of expression;
Expression(QString expr1);// constructor
bool isSymbol(QChar ch);
bool isMathSymbol(QChar ch);
void GetNext();
void First();
void Third();
void Fourth();
void Fifth();
void Sixth();
void Seventh();
void Eighth();
void Ninth();
bool Expression::Parse(); //parses the expression in RPN format;
};
class Sheet: public QTable
{
Q_OBJECT
// Variables
bool clicksLocked;
int selectionNo;
typeCellBorders defaultBorders;
typeCellData defaultCellData;
// QT objects
QList<typeCellData> sheetData, clipboardData;
QString pressedCell, releasedCell, sheetName;
QStringList listDataParser;
// Private functions
bool findRowColumn(const QString &variable, int *row, int *col, bool giveError=FALSE);
QString findCellName(int row, int col);
bool findRange(const QString &variable1, const QString &variable2, int *row1, int *col1, int *row2, int *col2);
QString calculateVariable(const QString &variable);
- QString calculateFunction(const QString &function, const QString &parameters, int NumOfParams);
+ QString calculateFunction(const QString &func, const QString &parameters, int NumOfParams);
QString getParameter(const QString &parameters, int paramNo, bool giveError=FALSE, const QString funcName="");
QString dataParser(const QString &cell, const QString &data);
QString dataParserHelper(const QString &data);
typeCellData *createCellData(int row, int col);
typeCellData *findCellData(int row, int col);
//LOGICAL / INFO
double functionCountIf(const QString &param1, const QString &param2, const QString &param3);
double functionSumSQ(const QString &param1, const QString &param2); //sum of squares
QString functionIndex(const QString &param1, const QString &param2, int indx);
//math functions computations
double BesselI0(double x);
double BesselI(int n, double x);
double BesselK0(double x);
double BesselI1(double x);
double BesselK1(double x);
double BesselK(int n, double x);
double BesselJ0(double x);
double BesselY0(double x);
double BesselJ1(double x);
double BesselY1(double x);
double BesselY(int n, double x);
double BesselJ(int n, double x);
double GammaLn(double xx);
double Factorial(double n);
double GammaP(double a, double x);
double GammaQ(double a,double x);
void GammaSeries(double *gamser, double a, double x, double *gln);
void GammaContinuedFraction(double *gammcf, double a, double x, double *gln);
double ErrorFunction(double x);
double ErrorFunctionComplementary(double x);
double Beta(double z, double w);
double BetaContinuedFraction(double a, double b, double x);
double BetaIncomplete(double a, double b, double x);
double functionVariance(const QString &param1, const QString &param2);
double functionVariancePopulation(const QString &param1, const QString &param2);
double functionSkew(const QString &param1, const QString &param2);
double functionKurt(const QString &param1, const QString &param2);
// Sheet/Qt parser functions
double functionSum(const QString &param1, const QString &param2);
double functionAvg(const QString &param1, const QString &param2);
double functionMax(const QString &param1, const QString &param2);
double functionMin(const QString &param1, const QString &param2);
double functionCount(const QString &param1, const QString &param2);
// Reimplemented QTable functions
void paintCell(QPainter *p, int row, int col, const QRect & cr, bool selected);
void viewportMousePressEvent(QMouseEvent *e);
void viewportMouseMoveEvent(QMouseEvent *e);
void viewportMouseReleaseEvent(QMouseEvent *e);
public slots:
void slotCellSelected(int row, int col);
void slotCellChanged(int row, int col);
public:
Sheet(int numRows, int numCols, QWidget *parent);
~Sheet();
void ReCalc(void);
void setData(const QString &data);
QString getData();
void setName(const QString &data);
QString getName();
void setPen(int row, int col, int vertical, const QPen &pen);
QPen getPen(int row, int col, int vertical);
void setBrush(int row, int col, const QBrush &brush);
QBrush getBrush(int row, int col);
void setTextAlign(int row, int col, Qt::AlignmentFlags flags);
Qt::AlignmentFlags getAlignment(int row, int col);
void setTextFont(int row, int col, const QFont &font, const QColor &color);
QFont getFont(int row, int col);
QColor getFontColor(int row, int col);
void lockClicks(bool lock=TRUE);
void copySheetData(QList<typeCellData> *destSheetData);
void setSheetData(QList<typeCellData> *srcSheetData);
void getSelection(int *row1, int *col1, int *row2, int *col2);
void insertRows(int no=1, bool allColumns=TRUE);
void insertColumns(int no=1, bool allRows=TRUE);
void dataFindReplace(const QString &find, const QString &replace, bool matchCase=TRUE, bool allCells=TRUE, bool entireCell=FALSE, bool replace=FALSE, bool replaceAll=FALSE);
// Static functions
static int getHeaderColumn(const QString &section);
static QString getHeaderString(int section);
public slots:
void editCut();
void editCopy();
void editPaste(bool onlyContents=FALSE);
void editClear();
void swapCells(int row1, int col1, int row2, int col2);
signals:
void currentDataChanged(const QString &data);
void cellClicked(const QString &cell);
void sheetModified();
};
#endif