4
$\begingroup$

I have searched and have not found a solution.

n = Table[i^2 + j*3, {i, 3}, {j, 7}] // TableForm
Export["stuck.xls", n]

4  7  10  13  16  19  22
7  10 13  16  19  22  25
12 15 18  21  24  27  30

What I want is to have the cell columns and rows give me exactly what i and j were, like this:

    j   1   2   3   4   5   6   7
i                               
1       4   7   10  13  16  19  22
2       7   10  13  16  19  22  25
3       12  15  18  21  24  27  30

From this I can see that the result of i=3 and j=7 is 30.

Can anyone help with this task?

Improve this question
$\endgroup$
8
  • $\begingroup$ this did not paste like I expected. The columns should have been 7 wide (as shown) but the rows should have been 3 deep. The place where you see a number 2 should have been the start of a new row and where the number 3 shows should have been the start of the third row . . . .sorry for the confussion $\endgroup$
    – John
    Commented Jan 9, 2015 at 5:35
  • 1
    $\begingroup$ I fixed your post for you. Welcome to the site. Please have a look at the faq, and you may also want to see the revised source code of your question that produced this formatting. $\endgroup$
    – Oleksandr R.
    Commented Jan 9, 2015 at 5:40
  • $\begingroup$ Hi Oleksandr,That was a speedy reply . . .thank you much $\endgroup$
    – John
    Commented Jan 9, 2015 at 5:46
  • $\begingroup$ sorry, hit return too soon . . .where exactly can i see the "revised code" you referenced . . .thx again $\endgroup$
    – John
    Commented Jan 9, 2015 at 5:46
  • $\begingroup$ Click on the "edited <> ago" text to see that: here. $\endgroup$
    – Oleksandr R.
    Commented Jan 9, 2015 at 5:50

2 Answers 2

Reset to default
3
$\begingroup$

You can pad n with required column and row headers before exporting to Excel:

padF = With[{d = Dimensions[#], ap = ArrayPad[#, {{1, 0}, {1, 0}}, ""]}, 
    Transpose@Prepend[Transpose[Join[{Prepend[Range@d[[2]], "j"]}, ap]], 
                      Join[{"", "i"}, Range@d[[1]]]]] &;

n = Table[i^2 + j*3, {i, 3}, {j, 7}];
nn = padF@n;
Export["stuck2.xlsx", nn]

enter image description here

Improve this answer
$\endgroup$
5
$\begingroup$

You are looking for the TableHeadings option of TableForm.

table = Table[i^2 + j*3, {i, 1, 3, .5}, {j, 1, 7, .5}];
rowHeaders = Range[1, 3, .5];
columnHeaders = Range[1, 7, .5];
{rowVar, columnVar} = {"i", "j"};

TableForm[table, TableHeadings -> {rowHeaders, columnHeaders}]

If you want to show the row and column variable names then you have to pad the data with empty strings to make room for the extra labels.

TableForm[
  ArrayPad[table, {{1, 0}, {1, 0}}, ""],
  TableHeadings -> {{rowVar}~Join~rowHeaders,
                    {columnVar}~Join~columnHeaders}
]

Note: Data should be left as data (Table or List); separate from presentation (TableForm). That way you can choose to display it as a TableForm, MatrixForm, or easily work with the data the later on. In particular

(* Bad practice *)
n = Table[i^2 + j*3, {i, 3}, {j, 7}] // TableForm;
(* Fail *)
TableForm[n, TableHeadings -> {Range@3, Range@7}]

Edit: There used to be an answer by @kguler which addresses exporting the headers to Excel by prepending the headers to the data. This uses the same method, perhaps in a more easily readable format.

formatTable[table_, {rowHeaders_, columnHeaders_}, {rowVar_, columnVar_}] :=
  table //
  ArrayPad[#, {{1, 0}, {1, 0}}, ""] & //
  Prepend[#, {columnVar}~Join~columnHeaders] & //
  Transpose // 
  Prepend[#, {"", rowVar}~Join~rowHeaders] & //
  Transpose;

Export["stuck.xls", formatTable[table, {rowHeaders, columnHeaders}, {rowVar, columnVar}]]
Improve this answer
$\endgroup$
5
  • $\begingroup$ Thanks Tim, This works as long as my increments are integers. But what about when my formula looks like this n = Table[i^2 + j*3, {i, 1, 3, .1}, {j, 1, 7, .1}]; $\endgroup$
    – John
    Commented Jan 9, 2015 at 6:22
  • $\begingroup$ TableHeadings->{Range[1,3,.1],Range[1,7,.1]} $\endgroup$
    – Timothy Wofford
    Commented Jan 9, 2015 at 6:25
  • $\begingroup$ Bingo, thank you very much and thank you as well kguler ! $\endgroup$
    – John
    Commented Jan 9, 2015 at 6:34
  • $\begingroup$ Ok, sorry to bring this thread up again, i got the table to contain headers in Mathematica just fine but when I export to excel they were missing once again :-( I am new to all of this so i apologize for what are probably painfully mundane questions. Thanks in advance for the help all $\endgroup$
    – John
    Commented Jan 9, 2015 at 23:43
  • $\begingroup$ i realize now that i did not mention the word "excel" in the question, i will be more clear in the future all $\endgroup$
    – John
    Commented Jan 10, 2015 at 0:35

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.