efficiently creating/populating a 20x20 cell matrix

[jdulberg]

Hello,

I have a 20x20 matrix set up in Excel that I'd like to recreate in Flash. Essentially the values are either 0 or a calculated formula of -Fo or 1+2(Fo).

My question is with a matrix of this size, is there an efficient way to create and populate it besides hard coding the address for each data cell? Can I make this using a loop?

I've attached a snippet of what the Excel sheet looks like. Notice the data trend.

Any suggestions would be really helpful! Thanks.
Jason

[IQAndreas]

A few suggestions, but if you want the SWF to keep opening the XLS file dynamically to populate values, this is very complicated, and I cannot help you with this. However, if you want to copy over the data manually, read on.

First of all (and this would be easiest), most Excel calculations can be done in Flash, so unless you have a pretty huge getup with references to other cells in your spreadsheet, you could copy over the functions behind each cell and translate them into AS3, which wouldn't require major changes.

Otherwise, if you can copy over the actual results (not functions) into a new spreadsheet, you can save that spreadsheet as a CSV (comma separated values) file. (Should be in "Save as...")

Then you can load the CSV file into Flash like you would any other text file and use String.split(","); to convert each field into an Array. Do the same for lineBreaks to create rows.

[jdulberg]

Hi IqAndreas,

Thanks for your input! We are looking to make this into a graphical calculator that is independent of Excel, unfortunately I'm not sure if the csv method would work in that case. The matrix values are change based on user input.

There are quite a few formulas involved however I was thinking of doing the calculations and storing them as variables to then be called from the matrix.

My problem that I'm running into now is dynamically creating and populating the matrix. I can create the 20x20 with 0 value, but how do I programatically populate the trend fields as shown in the screenshot? This would be a big deal to do manually.

Thanks again for your ideas!!
Jason

[IQAndreas]

Sorry. I was under the impression you were trying to export data from Excel into your SWF. Again and again I learn I need to read the post. :sigh:

Could you clarify exactly what it is you are doing? Then I could better assist you.

Do you mean you want to display the results as a grid similar to Excel? Then you might want to look into the DataGrid component.

Or do you want some way to store the data in code so it is easily retrievable? You could use 2D arrays, or "Arrays inside of Arrays", or nested arrays, many names, all the same purpose. Basically, if you have 20 columns, you may have an Array with 20 items. Each item (column) has twenty rows, and is therefor another array with twenty items. Each item of that array contains whatever number you want to store.

Is this what you mean?


var rows:uint = 20;
var columns:uint = 20;

//First, make the Array of Arrays
var grid:Array = new Array();

for (var c:int = 0; c < columns; c++)
{
//Here, I am setting each column in the grid to a new array,
//each of which will later contain a cell with a number in it
grid[c] = new Array();

//This part is not necessary, but a good idea
for (var r:int = 0; r < rows; r++)
{
//Make every cell a value of zero to begin with
grid[c][r] = 0.0;
}
}

//Here are a few examples, this one sets the first cell to a number
grid[0][0] = 35.2;

//This will set and return the number at 3, 5 (remember, Arrays start at zero and not one)
grid[2][4] = 345356.4763567;
trace(grid[2][4]);

//ETC

Do you understand what I am doing here, or do you wish me to elaborate further?

[IQAndreas]

If you want to avoid having to start at zero and end at 19, and instead want the column and row numbers to range from 1 to 20, use the following code:

var rows:uint = 20;
var columns:uint = 20;

//First, make the Array of Arrays
var grid:Array = new Array();

for (var c:int = 1; c <= columns; c++)
{
//Here, I am setting each column in the grid to a new array,
//each of which will later contain a cell with a number in it
grid[c] = new Array();

//This part is not necessary, but a good idea
for (var r:int = 1; r <= rows; r++)
{
//Make every cell a value of zero to begin with
grid[c][r] = 0.0;
}
}

//This will set and return the number at 3, 5
grid[3][5] = 345356.4763567;
trace(grid[3][5]);

Instead of copying and pasting what I have provided, look at the code and notice the differences.

Also note that now you really have 21 rows and columns, but you are just ignoring the first one of each.

[jdulberg]

Hi IqAndreas,

Your code to create the grid makes sense to me. You're right, there are actually 21 rows/columns. Essentially, the data in the matrix is being used to calculate a secondary formula used to create a graph of temperature over time (with a whole bunch of other variables at play). The user will never see the actual data, just their own input fields.

I'm away from my work computer however I'll post back with my progress tomorrow.

Thanks again!
Jason

[jdulberg]

Hi IqAndreas,

Your code has worked great so far, the matrix is now working. It's just a matter of adding in the trend data now. Is there a way to trace the entire matrix to the screen in a similar fashion as an Excel sheet? It would really help to be able to see it for debugging purposes.

Thanks again for all your input, it helps a lot!
Jason

[IQAndreas]

I haven't used it myself, but the DataGrid component should be able to display data VERY similar to Excel. All you need is some way to loop through all your data (do you need code for this? It's simply several for loops) and apply that cell to a matching cell in the grid.

[jdulberg]

I've managed to set up a DataGrid and dynamically insert the column headers in your code however I'm not sure how to insert the data into appropriate columns/rows to look like the same Excel chart.

So based on your code, I'm inserting the column:


var grid:Array = new Array();
for (var c:int = 0; c <= columns; c++) {
grid[c] = new Array();
dataGrid.addColumn("c "+c);
for (var r:int = 1; r <= rows; r++) {
grid[c][r] = 0.0; // set all cells to 0
}
}


Then me populating the trend, which is where I can't figure out how to insert this into the DataGrid:


for (var q:int = 1; q <= rows-1; q++) {
grid[q-1][q] = Fo*-1;
grid[q][q] = 1+(2*Fo);
grid[q+1][q] = Fo*-1;
}


If you could point me in the right direction, that would be great!

Jason

[IQAndreas]

"grid" in the code I gave you is an Array with that name, and not a DataGrid component.

Also, could you clarify what you are doing? What is "Fo"? That code right there might throw errors, notably if you are working on the last column, if you try setting column "currentColumn + 1", that won't work.

If you could clarify a bit I can help you out again some time tomorrow.

[jdulberg]

Hi IqAndreas,

No worries about that grid array, everything seems to work great when I trace test cells. I dropped a DataGrid component onto the stage and called it as dataGrid. Now it's just a matter of getting the grid array to appear properly in the DataGrid that is giving the headache.

"Fo" is a variable that I defined at the top of the script, it's actually just a short form for "Fourier's formula" - this project is going to be a heat transfer calculator of sorts. Basically once this matrix is complete, the data is sucked into another 1x20 matrix, multiplied by user data and inverted. The result is a line graph of heat transfer over time.

Hope that clears up my goal a bit more :) I know, it's a little convoluted.

Thanks again,
Jason