Message Boards Message Boards

0
|
4924 Views
|
5 Replies
|
2 Total Likes
View groups...
Share
Share this post:

Export in For Loop

Posted 3 years ago

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?

POSTED BY: Giada P.
5 Replies

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"]
POSTED BY: Henrik Schachner

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.

POSTED BY: Kevin Reiss
Posted 3 years 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.

POSTED BY: Rohit Namjoshi
Posted 3 years 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 BY: Giada P.

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 BY: Henrik Schachner
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