Prev: numerical integration
Next: hearing a plot
From: Thomas Melehan on 29 Jun 2010 06:58
Trying to export a file that has too many columns for .xls (but not too many for .xlsx or .ods).

Mathematica is not recognizing Export file format .xlsx or .ods.

Any help?




From: telefunkenvf14 on 29 Jun 2010 08:40
On Jun 29, 5:58 am, Thomas Melehan <tpmele...(a)gmail.com> wrote:
> Trying to export a file that has too many columns for .xls (but not too many for .xlsx or .ods).
>
> Mathematica is not recognizing Export file format .xlsx or .ods.
>
> Any help?

I've read that .xlsx is based on XML... so I generated a new test.xlsx
file to play with, containing:

1 4 7
2 5 8
3 6 9

In[1]:= Import["C:\Users\x61Tablet\Desktop\\test.xlsx", "Elements"]
Out[1]= {"FileNames"}

In[2]:= Import["C:\Users\x61Tablet\Desktop\\test.xlsx", "FileNames"]
Out[2]= {"[Content_Types].xml", "_rels\\.rels", \
"xl\\_rels\\workbook.xml.rels", "xl\\workbook.xml", "xl\\styles.xml",
\
"xl\\theme\\theme1.xml", "xl\\worksheets\\sheet1.xml", \
"docProps\\core.xml", "docProps\\app.xml"}

And now for the XML mess...

In[3]:= Import["C:\Users\x61Tablet\Desktop\\test.xlsx", {"FileNames",
"xl\\worksheets\\sheet1.xml"}]
Out[3]= XMLObject[
"Document"][{XMLObject["Declaration"]["Version" -> "1.0",
"Encoding" -> "UTF-8", "Standalone" -> "yes"]},
XMLElement[
"worksheet", {{"http://www.w3.org/2000/xmlns/", "xmlns"} ->
"http://schemas.openxmlformats.org/spreadsheetml/2006/main", \
{"http://www.w3.org/2000/xmlns/", "r"} ->
"http://schemas.openxmlformats.org/officeDocument/2006/\
relationships", {"http://www.w3.org/2000/xmlns/", "mc"} ->
"http://schemas.openxmlformats.org/markup-compatibility/2006", \
{"http://schemas.openxmlformats.org/markup-compatibility/2006",
"Ignorable"} ->
"x14ac", {"http://www.w3.org/2000/xmlns/", "x14ac"} ->
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"}, \
{XMLElement["dimension", {"ref" -> "A1:C3"}, {}],
XMLElement[
"sheetViews", {}, {XMLElement[
"sheetView", {"tabSelected" -> "1",
"workbookViewId" -> "0"}, {XMLElement[
"selection", {"activeCell" -> "C4", "sqref" -> "C4"}, {}]}]}],
XMLElement[
"sheetFormatPr", {"defaultRowHeight" ->
"12.75", \
{"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac",
"dyDescent"} -> "0.2"}, {}],
XMLElement[
"sheetData", {}, {XMLElement[
"row", {"r" -> "1",
"spans" ->
"1:3", {"http://schemas.microsoft.com/office/spreadsheetml/\
2009/9/ac", "dyDescent"} -> "0.2"}, {XMLElement[
"c", {"r" -> "A1"}, {XMLElement["v", {}, {"1"}]}],
XMLElement["c", {"r" -> "B1"}, {XMLElement["v", {}, {"4"}]}],
XMLElement["c", {"r" -> "C1"}, {XMLElement["v", {}, {"7"}]}]}],
XMLElement[
"row", {"r" -> "2",
"spans" ->
"1:3", {"http://schemas.microsoft.com/office/spreadsheetml/\
2009/9/ac", "dyDescent"} -> "0.2"}, {XMLElement[
"c", {"r" -> "A2"}, {XMLElement["v", {}, {"2"}]}],
XMLElement["c", {"r" -> "B2"}, {XMLElement["v", {}, {"5"}]}],
XMLElement["c", {"r" -> "C2"}, {XMLElement["v", {}, {"8"}]}]}],
XMLElement[
"row", {"r" -> "3",
"spans" ->
"1:3", {"http://schemas.microsoft.com/office/spreadsheetml/\
2009/9/ac", "dyDescent"} -> "0.2"}, {XMLElement[
"c", {"r" -> "A3"}, {XMLElement["v", {}, {"3"}]}],
XMLElement["c", {"r" -> "B3"}, {XMLElement["v", {}, {"6"}]}],
XMLElement[
"c", {"r" -> "C3"}, {XMLElement["v", {}, {"9"}]}]}]}],
XMLElement[
"pageMargins", {"left" -> "0.7", "right" -> "0.7",
"top" -> "0.75", "bottom" -> "0.75", "header" -> "0.3",
"footer" -> "0.3"}, {}]}], {}]

I have a hunch that XML can do what you want. (Although I don't have
personal experience with exporting XML from Mathematica, only
importing and hacking for what I want.) And it looks like Excel 2010
is capable of opening plain XML, so then you can save in whatever
format you want. Dunno if Excel 2007 has the same features or not.

I'm pretty sure other file formats, such as .csv, will have similar
'width' limitations.

Couldn't you just export half of the columns at a time?

-RG


From: John Jowett on 30 Jun 2010 01:49
You might try exporting to CSV and then opening that with Excel. I
haven't tried to see if there is a limit on the number of columns.
JMJ

On Jun 29, 12:58 pm, Thomas Melehan <tpmele...(a)gmail.com> wrote:
> Trying to export a file that has too many columns for .xls (but not too many for .xlsx or .ods).
>
> Mathematica is not recognizing Export file format .xlsx or .ods.
>
> Any help?


From: telefunkenvf14 on 1 Jul 2010 08:25
On Jun 30, 12:49 am, John Jowett <john.m.jow...(a)gmail.com> wrote:
> You might try exporting to CSV and then opening that with Excel. I
> haven't tried to see if there is a limit on the number of columns.
> JMJ
>
> On Jun 29, 12:58 pm, Thomas Melehan <tpmele...(a)gmail.com> wrote:
>
> > Trying to export a file that has too many columns for .xls (but not too many for .xlsx or .ods).
>
> > Mathematica is not recognizing Export file format .xlsx or .ods.
>
> > Any help?

FYI, I read somewhere that CSV has a 1024 character width limit. If
the column names are especially long, you might want to shorten or
skip exporting them.

-RG

 | 
Pages: 1
Prev: numerical integration
Next: hearing a plot