Re: Export to .xlsx or .ods ?
- To: mathgroup at smc.vnet.net
- Subject: [mg110635] Re: Export to .xlsx or .ods ?
- From: telefunkenvf14 <rgorka at gmail.com>
- Date: Tue, 29 Jun 2010 08:40:14 -0400 (EDT)
- References: <i0cjju$885$1@smc.vnet.net>
On Jun 29, 5:58 am, Thomas Melehan <tpmele... at 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