| Author |
 Topic  |
|
|
Thomas83
24 Posts |
 Posted - 09/06/2013 : 03:24:36 AM
|
Origin 8 SR 6; Win 7
Hi.
How can I perform some simple mathematically operations between columns of different worksheets?
I have 2 Workbooks WBK1 and WBK2, each with one worksheet. I want to multiply the first column of WBK1 with each column (subsequentially) of WBK2 and have the result in a second worksheet of WBK2. There is a "roundabout" way by copying all columns in one worksheet and use "set.formula" and "execute.formula" but this is not what I really want. BTW is it right that with "execute.formula" calculations can only be performed within a single worksheet?
Now my ideas:
First I wanted to perform a vector calculation and use the "ocmath_simple_math" method. But I did not really understand how the calculation between the vectors is carried out. I did not understand the result.
Second I want to do the calculation cell-wise by using "GetCell()".
Both ways don´t work.
Can anybody help me? What is the fault in my codes? Im trying to understand what is wrong.
BTW: It would be nice to have a general code for performing calculations ("plus, minus , ..."). Furthermore not only calculations for the 1st column of WBK1, e.g. Column(i) of WBK1 * Column(i) of WBK2.
Thanks in advance. Here the 2 codes.
1)
#include <Origin.h>
#include <ocmath.h>
#include <wksheet.h>
void Calculations_II()
{
WorksheetPage WBK1("Book1"), WBK2("Book2"); // Set the specific Workbooks
Worksheet wks1("[Book1]Sheet1"), wks2("[Book2]Sheet1"), wks3; //wks1, wks2 input data, wks3 results
int iNumRows = wks1.Columns(0).GetNumRows(); //Get the number of Rows
int iNumCols = wks1.GetNumCols();
int aa = WBK2.AddLayer();
wks3 = WBK2.Layers(aa);
for (int i=1; i<=iNumCols; i++)
{
Dataset dsS3(wks3,0);
Dataset dsS4(wks3,i);
vector vS1(iNumRows); //number of elements in the vector =number of rows
vector vS2(iNumRows);
vector vS3(iNumRows);
vector vS4(iNumRows);
for (int k = 0; k< iNumRows; k++)
{
string value1, value2;
int Result1 = (int) wks1.GetCell(k,0,value1);
int Result2 = (int) wks2.GetCell(k,i,value2);
vS1[k] = Result1; //value of each elements of the vector
vS2[k] = Result2; //value of each elements of the vector
ocmath_simple_math(iNumRows, vS3, vS4, iNumRows, vS1, vS1, iNumRows, vS2, vS2, MATHTOOL_OPT_MUL);
dsS3 = vS3;
dsS4 = vS4;
}
}
}
2)
#include <Origin.h>
#include <ocmath.h>
#include <wksheet.h>
void Calculations_II()
{
WorksheetPage WBK1("Book1"), WBK2("Book2"); // Set the specific Workbooks
Worksheet wks1("[Book1]Sheet1"), wks2("[Book2]Sheet1"), wks3; //wks1, wks2 input data, wks3 results
int iNumRows = wks1.Columns(0).GetNumRows();
int iNumCols = wks1.GetNumCols();
if(!WBK2)
return;
int aa = WBK2.AddLayer();
wks3 = WBK2.Layers(aa);
for (int k = 0; k<= iNumCols; k++)
{
for (int i = 0; i<= iNumRows; i++)
{
string InputA, InputB;
double FactorA = (int) wks1.GetCell(i,0,InputA);
double FactorB = (int) wks2.GetCell(i,k,InputB);
double Result = FactorA * FactorB;
wks3.SetCell(i,k,Result)
}
}
}
|
Edited by - Thomas83 on 09/06/2013 03:37:41 AM |
|
|
greg
USA
1378 Posts |
Posted - 09/06/2013 : 11:15:42 AM
|
One nice thing about a Dataset is that Origin already knows how to do basic math on vectors:
void Calculations_II()
{
WorksheetPage WBK1("Book1"), WBK2("Book2");
Worksheet wks1("[Book1]Sheet1"), wks2("[Book2]Sheet1"),wks3;
int iNumRows = wks1.Columns(0).GetNumRows();
// Need this since vector multiplication expects same-size vectors
if( iNumRows != wks2.Columns(0).GetNumRows())
{
printf("Size of two datasets do not match.\n");
}
else
{
// This should be wks2 since that's what we loop over
int iNumCols = wks2.GetNumCols();
int aa = WBK2.AddLayer();
wks3 = WBK2.Layers(aa);
// We don't care about number of rows in destination
// since Origin will handle it
// But we will have to set the number of columns to match wks2
wks3.SetSize(1,iNumCols);
Dataset ds1(wks1,0); // This is the first column of [book1]sheet1
// Column indeces are from zero
// and last column is one less than column count
for (int i=0; i < iNumCols; i++)
{
Dataset ds2(wks2,i);
Dataset ds3(wks3,i);
ds3 = ds1 * ds2; // Yes, it's this simple
}
}
}
Also, I have to point out that GetCell is a function that copies the displayed string value of the cell to a string variable and returns a BOOL so something like
double FactorA = (int) wks1.GetCell(i,0,InputA);
makes no sense. The boolean gets cast to an int and assigned to a double?
Proper use would be:
string str;
BOOL bRet = wks1.GetCell(0,0,str);
double dVal1 = atof(str);
printf("%.15f\n",dVal1);
but you should be aware that the value you get is the displayed string and not the full precision. Full value can be gotten from the dataset:
double dVal2 = ds1[0];
printf("%.15f\n",dVal2);
|
Edited by - greg on 09/06/2013 11:19:42 AM |
 |
|
|
Thomas83
24 Posts |
Posted - 09/09/2013 : 02:12:35 AM
|
Hi greg.
Thanks for your help. I thought I already tried the way you suggested, but it didn´t work. Maybe I did some different mistakes. However, your code works and it is what I need. Thanks again for your fast reply.
|
|
|
| |
Topic |
|
|
|
Calculation between columns II