Outputing a tabular function

[Clifford Martin]

In some code I've written I output a table. In
Mathematica I use TableForm in the usual way then use
columns of data to fill the table. It looks nice in
Mathematica. But my customer wants to be able to pull
the file up in Excel. I've tried various export
formats but since the tables are made up of columns of
data that are just lists it writes the lists as rows
Any suggestions about exporting the data so it can be
read in an external program and look like a table? (I
should add that I iterate over many sets of data and
so there are multiple tables that print out. I capture
the data in a function that I try to Export). Any
suggestions appreciated.

Cliff

[Chris Chiasson]

I don't get it. If one exports to say, .csv, the data comes out in
Excel as a table. Are you talking about table headings?


On Mon, 7 Mar 2005 02:10:29 -0500 (EST), Clifford Martin

Post by Clifford Martin
In some code I've written I output a table. In
Mathematica I use TableForm in the usual way then use
columns of data to fill the table. It looks nice in
Mathematica. But my customer wants to be able to pull
the file up in Excel. I've tried various export
formats but since the tables are made up of columns of
data that are just lists it writes the lists as rows
Any suggestions about exporting the data so it can be
read in an external program and look like a table? (I
should add that I iterate over many sets of data and
so there are multiple tables that print out. I capture
the data in a function that I try to Export). Any
suggestions appreciated.
Cliff

[Peter Pein]

Post by Clifford Martin
In some code I've written I output a table. In
Mathematica I use TableForm in the usual way then use
columns of data to fill the table. It looks nice in
Mathematica. But my customer wants to be able to pull
the file up in Excel. I've tried various export
formats but since the tables are made up of columns of
data that are just lists it writes the lists as rows
Any suggestions about exporting the data so it can be
read in an external program and look like a table? (I
should add that I iterate over many sets of data and
so there are multiple tables that print out. I capture
the data in a function that I try to Export). Any
suggestions appreciated.
Cliff

Hi Cliff,

if all columns have got the same number of rows, simply
Transpose[yourTable] before Export[]ing it. Else for instance:
In[1]:=
t=Table["col."<>ToString[i]<>" row "<>ToString[j],{i,3},
{j,1,5+Random[Integer,{-2,3}]}]
Out[1]=
{{col.1 row 1,col.1 row 2,col.1 row 3},
{col.2 row 1,col.2 row 2,col.2 row 3, col.2 row 4,col.2 row 5,col.2
row6,col.2 row 7,col.2 row 8},
{col.3 row 1, col.3 row 2,col.3 row 3,col.3 row 4,col.3 row 5}}

Fill each list of the Table with the empty string (or what you want) and
_then_ Transpose[] it:

In[2]:=
With[{m=Max[Length/@t]},
Transpose[PadRight[#,m,""]&/@t]]
Out[2]=
{{col.1 row 1,col.2 row 1,col.3 row 1},
{col.1 row 2,col.2 row 2,col.3 row 2},
{col.1 row 3,col.2 row 3,col.3 row 3},
{,col.2 row 4,col.3 row 4},
{,col.2 row 5,col.3 row 5},
{,col.2 row 6,},
{,col.2 row 7,},
{,col.2 row 8,}}

Now you can export it and the columns and rows appear as expected (at
least in Open Office 1.1.4):
In[3]:=
Export["t.xls",%,"XLS"];

[Bill Rowe]

In some code I've written I output a table. In Mathematica I use
TableForm in the usual way then use columns of data to fill the table.

Your wording here implies you create something with TableForm and then change it. I can think of no simple obvious way to change the output of TableForm. So, it is a bit unclear to me what your end table looks like.

It looks nice in Mathematica. But my customer wants to be able to pull
the file up in Excel. I've tried various export formats but since the
tables are made up of columns of data that are just lists it writes
the lists as rows Any suggestions about exporting the data so it can
be read in an external program and look like a table?

It would have been useful had you said specfically what you've tried and what you found wanting. If you are usign version 5.1, Export can create a native Excel file. That is

Export["test.xls", Table[i + 10 j, {i,5},{j,5}], "XLS"]

will create a spread sheet with a 5 x 5 array. If the data structure has more than 2 dimensions, i.e.,Table[i + 10 j, {i,5},{j,5}, {k,3}], the resulting Excel file will have multiple sheets with data.

For versions before 5.1 that also had the Export function, I've found the best way to get data into an Excel spread sheet is to to export to either "CSV" or "TSV" formats. If your customer is using Windows, I think you can do

Export["test.xls", Table[i + 10 j, {i,5},{j,5}], "TSV"]

and you will get a file that will open as you want in Excel when double clicked.

(I should add that I iterate over many sets of data and so there are
multiple tables that print out. I capture the data in a function that
I try to Export). Any suggestions appreciated.

--
To reply via email subtract one hundred and four

[Adam Getchell]

Post by Clifford Martin
In some code I've written I output a table. In
Mathematica I use TableForm in the usual way then use
columns of data to fill the table. It looks nice in
Mathematica. But my customer wants to be able to pull
the file up in Excel. I've tried various export
formats but since the tables are made up of columns of
data that are just lists it writes the lists as rows
Any suggestions about exporting the data so it can be
read in an external program and look like a table? (I
should add that I iterate over many sets of data and
so there are multiple tables that print out. I capture
the data in a function that I try to Export). Any
suggestions appreciated.
Cliff

I've been exporting into comma separated values, which, for a matrix,
actually separates by tabs, then importing into Excel just fine.

For example:

In[25]:=
Export["djia-results.txt",
results2, "csv",
ConversionOptions ->
{"TableHeadings" ->
{None, Range[minYear,
maxYear]}}];

You can leave off the ConversionOptions; I added them because I had
columns of stock data which I wanted to decorate with the year for
inspection (although doing actual number crunching the year is
unimportant as long as all the values for a given year are in the same
column).

Then import from Excel.

Adam Getchell