Services & ResourcesWolfram ForumsMathGroup Archive

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

Re: How to modify an Excel file

• To: mathgroup at smc.vnet.net
• Subject: [mg95400] Re: How to modify an Excel file
• From: Norbert Marxer <marxer at mec.li>
• Date: Sat, 17 Jan 2009 05:32:00 -0500 (EST)
• References: <gkkf19$gn9$1@smc.vnet.net>

On 14 Jan., 11:33, ADL <alberto.dilu... at tiscali.it> wrote:
> Dear group, I am using Mathematica 6/7 export/import capabilities of
> Excel files, and they work fine. The only problem is that, as far as I
> understood, they cannot work with cell formats. This means that I
> could not find a way to import an Excel file, modify a cell and then
> export it in a new file with the same formatting as the original.
>
> Is there, to your knowledge, a way to let Mathematica write a value in
> a cell of an existing Excel file without using "Excel Link" and
> without modifying the cell's format?
>
> I have both Mathematica and Excel installed on my PC.
>
> Hope some of you has a solution!
>
> Thank you in advance

Hello

You can write directly in an existing Excel file using NETLink. For
details see the documentation in the Help Browser at "NETLink/tutorial/
Overview". I recommend to run the example "ExcelPieChart.nb".

If everything works OK you are ready to attack your problem.

The following commands will load the package, install NET, start
Excel, make it visible and start a dialog Window to open an existing
Excel file:

Needs["NETLink`"]
InstallNET[]
excel = CreateCOMObject["Excel.Application"]
If[ ! NETObjectQ[excel], Return[$Failed]]
excel[Visible] = True
excel[FindFile[]]

This selects the Excel Workbook and Excel Worksheet (here the first
worksheet):

workbook = excel[Workbooks[1]]
worksheet = excel[Workbooks[1][Worksheets[1]]]

This writes a title into the "A1" Excel cell and sets the font:

worksheet[Range["A1"][Value]] = "Primzahlen";
worksheet[Range["A1"][Font[Bold]]] = True;

This specifies a range:

start = "B3"; cols = 2; rows = 10;
srcRange = worksheet@Range[start]@Resize[rows, cols]

This defines a table of numbers and writes it into Excel :

values = Table[{i^2, Prime[i]}, {i, rows}];
srcRange@Value = values;

You can even create a chart:

chartCastSep =
 CastNETObject[workbook[Charts[][Add[]]],
  "Microsoft.Office.Interop.Excel.ChartClass"]
chartCastSep@ChartWizard[srcRange];

If you prefer a XY scatter plot then:

LoadNETType["Microsoft.Office.Interop.Excel.XlChartType"]
chartCastSep[
 ChartWizard[srcRange, XlChartType`xlXYScatter, format = 1,
  plotBy = 2, catLab = 1, serLab = 0, hasLegend = True, "Title",
  "CategoryTitle", "ValueTitle", "ExtraTitle"]]

With some Excel knowledge you can even read and write the formula in
the Excel cells or read and write Excel Macro code.

I hope this helps and good luck.

Best Regards
Norbert Marxer

• Follow-Ups:
  • Re: Re: How to modify an Excel file
    • From: "Tugrul Temel" <temelt@xs4all.nl>