Services & Resources / Wolfram Forums
-----
 /
MathGroup Archive
2005
*January
*February
*March
*April
*May
*June
*July
*August
*September
*October
*November
*December
*Archive Index
*Ask about this page
*Print this page
*Give us feedback
*Sign up for the Wolfram Insider

MathGroup Archive 2005

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

Search the Archive

Mathematica and Excel

  • To: mathgroup at smc.vnet.net
  • Subject: [mg61885] Mathematica and Excel
  • From: "nma" <marxer at mec.li>
  • Date: Thu, 3 Nov 2005 04:58:57 -0500 (EST)
  • Sender: owner-wri-mathgroup at wolfram.com

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 documentation - 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:

- data exchange via clipboard
- data exchange via files
- data reading and writing using .NETLink

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;

Norbert Marxer
www.mec.li


  • Prev by Date: Re: issues with integrating Boole
  • Next by Date: ExportString[exp, "MathML"]
  • Previous by thread: Re: Re: Question about "Reduce"
  • Next by thread: ExportString[exp, "MathML"]