1
$\begingroup$

My general question is: How can I put each of my outputs (a number) in a special cell of a given sheet of an excel file? for example I have built an excel file (say results.xlsx) and I want to fill it by my outputs (say out[i]s). For example I want to put out[1] in B5 cell of first sheet of results.xls, out[2] in C10 cell of second sheet of results.xls and etc. How can this be done?

We can simplify the above problem: suppose I have built a table (say outs) using my outputs and export it as an excel file () as follows

out[1] = 1;
out[2] = 2;
out[3] = 3;
out[4] = 4;

outs1=Table[out[i], {i, 1, 4}];
Export["results.xlsx", outs1, "Data"]

this command gives me an excel file which has a row including values of out1.

I run another .nb file and do the same namely I build another table (outs2) as follows

out[5] = 5;
out[6] = 6;
out[7] = 7;
out[8] = 8;

outs2=Table[out[i], {i, 5, 8}];

Now I want to put outs2 as second row of results.xlsx file. How?

$\endgroup$

1 Answer 1

2
$\begingroup$

The common way is making a sparse array and putting there the result at the desired place.

s = SparseArray[{{11, 1} -> 1, {2, 2} -> 2, {3, 3} -> 3, {1, 13} -> 
     4}, {12, 15}, ""];
s // MatrixForm

enter image description here

Than you can export it to Excel

$\endgroup$
6
  • $\begingroup$ Thanks a lot this was a useful way. In fact it was answer of my general question. But what about my second one? namely if I want to construct an excel file using different .nb files where outputs of each file make a row of an excel file? $\endgroup$
    – Wisdom
    Apr 9, 2021 at 4:36
  • $\begingroup$ @Wisdom, you should compose an output array containing all the data at the desired places before exporting. The easiest way for this is making sparse array from one data-set and further correction of elements by something like s[[3;;5,4;;7]]=subarray $\endgroup$
    – Rom38
    Apr 9, 2021 at 5:13
  • $\begingroup$ Many thanks. Do you mean there is no way to fill my excel file gradually and by running each .nb file (as I explained above)? I think there should be a way, because it doesn't seem a hard work $\endgroup$
    – Wisdom
    Apr 9, 2021 at 5:18
  • $\begingroup$ @Wisdom, of course, you can do it gradually. All the evaluated data of a Mathematica session are stored in the kernel. You can run step-by-step any cells from the different notebooks to make the inclusions of subarrays into the output array that was created at the beginning as sparse. $\endgroup$
    – Rom38
    Apr 9, 2021 at 5:39
  • $\begingroup$ Thanks I got it, however your suggestion has an additional intermediate step (constructing one data-set) but my mean was an one-step command in the each nb file. $\endgroup$
    – Wisdom
    Apr 9, 2021 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.