to XLS

[ggr...@sarj.ca]

I occasionally need to export data sets (LxNxM) to Excel. This
usually works extremely well using Export["filename",data,"XLS"]. By
default, Mathematica will create L worksheets, each with an NxM table.

My question is, is there a way to give a meaningful name to those
worksheets from Mathematica? Obviously the filename is specified, and
it is fairly trivial to add column and/or row headings to the tables
on each worksheet. I just haven't found the trick to naming each
worksheet automatically.

Thanks.

[Bob Hanlon]

One way would be to import the intended sheet name into the first cell of
each sheet and add the following macro to the code module of the workbook
or an add-on. Manually run the macro or put a call to it in an Auto_Open
macro.

Bob Hanlon

Public Sub reName_Sheets()
'
' Renames sheets from their default names (e.g., "Sheet1")
' to value in sheet's first cell (A1) except when that cell is
' blank
'
Dim fenSheet ' Index used in For Each ... Next loop

For Each fenSheet In Sheets

If Left(fenSheet.Name, 5) = "Sheet" And _
Not fenSheet.Range("A1") = "" Then

fenSheet.Name = fenSheet.Range("A1")

End If

Next fenSheet ' in Sheets

End Sub ' reName_Sheets

[ggr...@sarj.ca]

Hi Bob,

On Monday, November 21, 2005 at 6:49 GMT -0500, Bob Hanlon wrote:

> One way would be to import the intended sheet name into the first cell of
> each sheet and add the following macro to the code module of the workbook
> or an add-on. Manually run the macro or put a call to it in an Auto_Open
> macro.

Thanks for the suggestion. Unfortunately, the export routine appears
to be limited to handling very regular tables (like an L by M by N
table), so you need to play a few (albeit minor) tricks to get the M
by N tables to export correctly.

I was hoping for something more automated if it's reasonably easy and
from Mathematica*. Ultimately, I don't want to invest too much time
on this since I would prefer to spend the time finding ways of making
the current need for Excel redundant.

* For example, adding column headings for each table is as simple as:
Append[#, {"Heading 1", "Heading 2", ..., "Heading M"}]& /@ data

[Rolf....@gmail.com]

Hi,
just use the underlying JExcelApi (see www.andykhan.com ).
Then
ModifySheetName["mydata.xls", {"newsheetname1", "bla"}]
will do what you want.

(* ***************************************************** *)
Off[General::spell1];
ModifySheetNames[xlsfile_String,sheetnames:{__String}]:=ModifySheetNames[xlsfile,xlsfile,sheetnames];
ModifySheetNames[xlsfile_String, newxlsfile_String,
sheetnames:{__String}] := Module[{
ljc=ToExpression["JLink`LoadJavaClass"],jn =
ToExpression["JLink`JavaNew"],wb,sheets},
Needs["JLink`"]; ToExpression["JLink`InstallJava[]"];
ljc["jxl.Workbook"];
wb = Workbook`createWorkbook[FF=jn["java.io.File", newxlsfile],
Workbook`getWorkbook[jn["java.io.File", xlsfile ]]];
sheets = wb[getSheets[]];
Do[sheets[[i]][setName[sheetnames[[i]]]], {i,
Min[Length[sheets],Length[sheetnames]]}];
wb[write[]];
wb[close[]];
newxlsfile
];
(* ********************************************************* *)

Regards,
Rolf Mertig
GluonVision Gmbh, Berlin

[Norbert Marxer]

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