[Date Index] [Thread Index] [Author Index]

Re: to XLS

• To: mathgroup at smc.vnet.net
• Subject: [mg62408] Re: to XLS
• From: "Norbert Marxer" <marxer at mec.li>
• Date: Wed, 23 Nov 2005 01:12:42 -0500 (EST)
• References: <20051121114855.JTPP8508.eastrmmtao02.cox.net@[172.18.52.8]> <dluqgr$nlb$1@smc.vnet.net>
• Sender: owner-wri-mathgroup at wolfram.com

Hello

I will describe below two methods to give a name to an Excel worksheet
from Mathematica. The first exports an XMLSS string into an XML file
which can be opened inExcel, the second opens and writes into an Excel
workbook (you must have Excel installed on your system to do this).

If you need more information on this please consult the links at
"http://www.mec.li/MathematicaAndExcel/MathematicaAndExcel.html"; or
"http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_frm/thread/606ea83ce6296ec2/9a6dd43ffeb07de0#9a6dd43ffeb07de0";.

Method 1 using Export as "Text" into an "XML" file

Step 1: define the following functions:

xmlCell[data_] := Block[{apo = "\"", tab = "\t"},
      StringJoin[tab, tab, "<Cell><Data ss:Type=", apo, If[
      NumberQ[data], "Number", "String"], apo, ">",
        ToString[data], "</Data></Cell>"]];

xmlRow[rows_] := Block[{nL = "\n", tab = "\t"},
    StringJoin[tab, "<Row>", nL, Map[(xmlCell[#] <> nL) &, rows],
        tab, "</Row>"]]

xmlTable[tbl_] := Block[{nL = "\n"},
      StringJoin["<Table>", nL, Map[(xmlRow[#] <> nL) &, tbl],
"</Table>"]];

xmlWorksheet[name_, tbl_] := Block[{apo = "\"", nL = "\n"},
      StringJoin["<Worksheet ss:Name=", apo,
      ToString[name], apo, ">", nL, xmlTable[tbl], nL,
        "</Worksheet>"]];

xmlWorkbook[tbl_] := Block[{apo = "\"", nL = "\n"},
      StringJoin["<?xml version='1.0'?>", nL, "<Workbook
      xmlns=", apo, "urn:schemas-microsoft-com:office:spreadsheet",
apo,
" xmlns:ss=", apo, "urn:schemas-
            microsoft-com:office:spreadsheet", apo, ">", nL,
        Switch[tbl,
          {{__, {__ ..}} ..}, Map[xmlWorksheet[#[[1]], #[[2]]] &, tbl],
          {__, {{__} ..}}, xmlWorksheet[tbl[[1]], tbl[[2]]],
          {__, {__}}, xmlWorksheet[tbl[[1]], {tbl[[2]]}],
          _, xmlWorksheet["Incorrect Input", {""}]
          ],
        nL, "</Workbook>"]];

Step 2: create (test) tables (3-fold nested list, matrix, list which
satisfy the expression matching test in the xmlWorkbook function
above):

tblDim3 = Table[{"Name " <> ToString[k], Table[i + 10j + 100k, {i, 1,
2}, {j, \
1, 2}]}, {k, 1, 2}]
tblDim2 = {"Name 1", Table[i + 10j, {i, 1, 2}, {j, 1, 2}]}
tblDim1 = {"Name 1", Table[i, {i, 1, 2}]}

Step 3: export the tables into an XML file.

dirName=... as you like ...
Export[dirName <> "ForumContributionDim0.xml", xmlWorkbook[{}],
"Text"];
Export[dirName <> "ForumContributionDim1.xml", xmlWorkbook[tblDim1],
"Text"];
Export[dirName <> "ForumContributionDim2.xml", xmlWorkbook[tblDim2],
"Text"];
Export[dirName <> "ForumContributionDim3.xml", xmlWorkbook[tblDim3],
"Text"];

Step 4: open the XML file in Excel

Method 2 using NETLink

An alternative would be to use NETLink Programming. The following
command installs NETLink, creates an Excel application object, opens
Excel, adds a workbook, adds a worksheet, sets the name of the
worksheet and writes some values into a range object.

Needs["NETLink`"];
InstallNET[];
excel=CreateCOMObject["Excel.Application"];
excel@Visible=True;
excel@Workbooks@Add[];
excel@Workbooks@Item[1]@Sheets@Add[];
excel@Workbooks[]@Item[1]@Sheets@Item[1]@Name="My Sheet Name";
excel@Workbooks[]@Item[1]@Sheets@Item[1]@Range["A4:B5"]@Value2={{"r1",
      "r2"},{18,"r3"}};

I hope this information will help you.

Best regards
Norbert Marxer