Mathematica and Excel Documentation
Contribution to the MathForum
2.11.2005
The following Content has been sent to the MathForum and is also available on
the WEB (http://www.mec.li/MathematicaAndExcel/MathForum.html).
Once in a while there is some discussion in this forum about how to get Excel
tables into Mathematica, how to exchange tables between Mathematica and Excel
using the clipboard or files and how Mathematica and Excel can communicate with
each other. These are important questions, because both Mathematica and Excel
are widely used programs.
If you are using both Mathematica and Excel and if you want answers to these
questions above, you might be interested in reading the documentation given at
the link (
http://www.mec.li/MathematicaAndExcel/MathematicaAndExcel.html ), which
gives (for a small fee) a systematic overview and answers to many questions
related to the topics mentioned above.
I am convinced that - by using this document - you can save many hours (probably
days). Instead of searching the WEB, the Mathematica HelpBrowser, the .NETLink,
JLink, Visual Basic for Applications, Excel etc. documentation you will get a
quick start and can study and learn from many examples.
The documentation (containing a more than 150 page .pdf file, also given as .nb
file, a clipboard palette, style sheet, approximatley 40 .xls / .csv / .txt / .xml
... example files) is divided into three main areas:
You will find the
following methods using the clipboard:
-
a palette which
makes it possible to write a selected Mathematica table (list, or list of
lists) to the clipboard with a button click in such a format that it can be
pasted in Excel using Ctrl+V;
-
a palette which
makes it possible to read a selected Excel table (Range object) by using
Ctrl+C in Excel and pressing a button in Mathematica which will insert the
table in the Mathematica format (with curly brackets);
-
the construction
of this standalone palette and all its functions are discussed thouroughly;
it will not only work with Excel but also with other spreadsheet programs
like "Calc" from OpenOffice.org;
-
the behaviour of
the ClipboardNotebook[] and the different Copy As and Paste As commands are
investigated using FrontEnd programming;
-
you will also
use JLink and Java commands to inspect the clipboard content and see the
many different formats in which the copied data are available; you can also
access the HTML or Rich Text Format (and other formats) if you know how to
interpret it.
You will find the
following methods using file reading / writing:
-
in Excel: we
will discuss which file format (.xls, .csv, .txt, .prn, .xml ...) you should
choose to import / export tables, text, numbers, formulas, charts, ...
-
in Excel: a
short Excel macro is given to export a chart into a file which can be
imported in Mathematica;
-
in Mathematica:
we will discuss which file format for the function Export (CSV, Table, Text,
...) you should choose to import / export tables, text, numbers, formulas,
charts, ...
-
in Mathematica:
you should not (as is usually recommended in the MathForum) read Excel
tables using the Import function with the format "Table" because empty rows
are lost (which makes all the formula cell references obsolete); the
solution to this is given;
-
in Mathematica:
we give the format you have to choose to write more than one Worksheet into
a file;
-
in Mathematica:
we explain how to write an .xml file in the XMLSS specification;
-
String matching
and Expression matching techniques show how to extract the information from
imported data and convert it to Mathematica expressions;
You will find the
following methods using .NETLink to work with Excel:
-
a short
introduction to .NETLink programming and the use of (Primary) Interop
Assemblies is given;
-
you will find
many links for downloading the PIA's, the (Excel) language references and
more and how to get information on the available Excel functions;
-
Mathematica code
is supplied which starts Excel, adds Workbooks and Worksheets, sets cell
content, sets tabular data, makes charts ... and also quits Excel;
-
you can call
these functions interactively or programmatically;
-
in this way you
can use Mathematica as a development environment and manage your Excel
applications (formulas, formatting) completely from Mathematica;
|