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