0
$\begingroup$

When, I want to export a lot of my results to excel, how can I make sure that the Sheet name is also listed according to the results?

For example, There is a following the result.

result[1] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}
result[2] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}
result[3] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}
result[4] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}
・・・
result[100] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}

If I don't care about the sheet name, then the output formula would look like this:

answer = Table[result[i], {i, n}]
Export["newfiles.xlsx", answer, "XLSX"] 

However, I want the names of the output Excel sheets to be result1 and result2 ... instead of the default sheet1 and sheet2 ... respectively.

Export["newfile.xlsx",{"result 1" -> result[1],"result 2" -> result[2],"result 3" -> result[3]}......."result 100" -> result[100], "XLSX"] 

Instead of doing this manually, how can it be automated?

$\endgroup$
1
  • $\begingroup$ Thank you for your help! $\endgroup$
    – hare
    Oct 27, 2023 at 9:52

2 Answers 2

0
$\begingroup$
Clear["Global`*"];
nres = 4;
result[1] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}; 
result[2] = {{d, e, f}, {g, h, i}, {j, k, l}, {m, n, o}, {p, q, r}}; 
result[3] = {{1, 2, 3}, {4, 5, 6}, {7, 8, 9}, {a, b, c}}; 
result[4] = {{11, 22, 33}, {44, 55, 66}, {77, 88, 99}};

names = "result" <> IntegerString[#, 10, 3] & /@ Range[nres];

res = MapThread[Rule[#1, #2] &, { names, result[#] & /@ Range[nres]}];

Export["C:/test2.xls", res, "XLS"];
$\endgroup$
1
$\begingroup$

A little code golf...

(*example copied from Syed*) 
result[1] = {{a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}, {a, b, c}}; 
result[2] = {{d, e, f}, {g, h, i}, {j, k, l}, {m, n, o}, {p, q, r}}; 
result[3] = {{1, 2, 3}, {4, 5, 6}, {7, 8, 9}, {a, b, c}}; 
result[4] = {{11, 22, 33}, {44, 55, 66}, {77, 88, 99}};

(* proposed solution *)

output[data_, i_] := With[
  {name = StringTemplate["result``"]@IntegerString[First@i, 10, 3]},
  Rule[name, data]]

With[
 {sheets = MapIndexed[output, Values@DownValues[result]]},
 Export["test2.xlsx", sheets]]

Benefits:

  • No new symbols are permanently defined (except for an output function;
  • no need to predefine the number number of symbols
  • no need to specify export as "XLSX", as this is inferred from the filename

Comments:

  • You are making your life hard with result[1], etc. instead of just making these lists; the Values@DownValues[result] essentially does this conversion (assuming these have sequential values)
  • If you were a fan of one-liners, you could get rid of the With[] construct and just put the code in place, but I find it is a bit clearer to read this way
$\endgroup$

Your Answer

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

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