Message Boards Message Boards

GROUPS:

Export in For Loop

Posted 1 month ago
341 Views
|
5 Replies
|
2 Total Likes
|

Hi, I need to export as .xls tables create in a for loop. In particular, I would like to save the table generated in each cycle in a different worksheet of the same file. Can you help me?

5 Replies

Say, you bring your data in the form of a list consisting of an index number (from a loop or whatever) and the respective worksheet content:

data = Table[{n, RandomReal[1, {5, 5}]}, {n, 1, 10}];

then the whole can be exported like so:

allContent = Rule["Worksheet_" ~~ ToString[#1], #2] & @@@ data;
Export["MyExcelFile.xls", allContent, "XLS"]
Posted 1 month ago

Thank you for your answer, but maybe I need to explain better. From a loop like: For [i = 1, i <= n, i++, I have n tables and I want to export each table in a different worksheet of the same file.

Posted 1 month ago

I don't think there is a way to append a new sheet to an existing file. Follow @Henrik Schachner's suggestion and collect the index and tables in data. Something like

data = {};
For[...
table = ...
AppendTo[data, {i, table}]
]

Rather than For, consider using Table or Map.

I found the following example in the Basic Examples section of this page of the documentation: https://reference.wolfram.com/language/ref/format/XLS.html. It exports 2 data sets, data1 and data2, into 2 different sheets named Experiment 1 and Experiment 2, of the same spreadsheet, datasets.xls. If you're not able to append new sheets to a spreadsheet after it has been created, you might need to create the spreadsheet with all the sheets outside of your for loop and then add the data to each sheet.

data1 = Table[{t, 9.8*t^2}, {t, 0, 3, .1}];
data2 = Table[{t, 9.8*t^2}, {t, 0, 5, .2}];
Export["datasets.xls", 
 "Sheets" -> {"Experiment 1" -> data1, 
   "Experiment 2" -> data2}, "Rules"]

Edit: I understand that your issue arises from the Export function overwriting the file in question instead of appending to it. For some file types, adding OverwriteTarget->"Append" to the Export function helps, although it appears not for Excel sheets. For more basic file types like CSV, you can also use AppendTo, or OpenAppend and then Write. You can try doing this into a CSV and then converting to .xls, or use Henrik's suggestion of keeping all the data and then exporting once at the end.

Ok, here comes annother simple suggestion: For appending a new worksheet to an existing file you import its content, append the new worksheet within Mathematica and finally export the whole thing, e.g. like so (using "MyExcelFile.xls" from above):

oldSheets = Import["MyExcelFile.xls", "Sheets"];
oldData = Import["MyExcelFile.xls", "Data"];
oldContent = Thread[oldSheets -> oldData];
(* now append the new worksheet in that format: *)
newContent = Append[oldContent, "New Worksheet" -> RandomInteger[10, {5, 5}]];
Export["MyExcelFile.xls", newContent, "XLS"]
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract