Search code examples
export-to-excelmaple

Exporting data into excel using iterative loop

I am doing an iterative calculation on maple and I want to store the resulting data (which comes in a column matrix) from each iteration into a specific column of an Excel file. For example, my data is

mydat||1:= <<11,12,13,14>>:

mydat||2:= <<21,22,23,24>>:

mydat||3:= <<31,32,33,34>>:

and so on. I am trying to export each of them into an excel file and I want each data to be stored in consecutive columns of the same excel file. For example, mydat||1 goes to column A, mydat||2 goes to column B and so on. I tried something like following. 

with(ExcelTools):
for k from 1 to 3 do
Export(mydat||k, "data.xlsx", "Sheet1", "A:C"): #The problem is selecting the range.
end do:

How do I select the range appropriately here? Is there any other method to export the data and store in the way that I explained above?

Solution

  • There are couple of ways to do this. The easiest is certainly to put all of your data into one data structure and then export that. For example:

    mydat1:= <<11,12,13,14>>:
mydat2:= <<21,22,23,24>>:
mydat3:= <<31,32,33,34>>:
mydata := Matrix( < mydat1 | mydat2 | mydat3 > );

    This stores your data in a Matrix where mydat1 is the first column, mydat2 is the second column, etc. With the data in this form, either ExcelTools:-Export or the more generic Export command will work:

    ExcelTools:-Export( data, "data.xlsx" );
Export( "data.xlsx", data );

    Now since you mention that you are doing an iterative calculation, you may want to write the results out column by column. Here's another method that doesn't involve the creation of another data structure to house the results. This does assume that the data in mydat"i" has been created before the loop. 

    for i to 3 do
    ExcelTools:-Export( cat(`mydat`,i), "data.xlsx", 1, ["A1","B1","C1"][i] );
end do;

    If you want to write the data out to a file as you are building it, then just do the Export call after the creation of each of the columns, i.e.

    ExcelTools:-Export( mydat1, "data.xlsx", 1, "A1" );

    Note that I removed the "||" characters. These are used in Maple for concatenation and caused some issues with the second method.