How to modify an Excel file

[ADL]

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

[dh]

Hi,

assume that you have an Excell sheet with 1,2 in the first row and 3,4

in the second row saved to a file "d:/tmp/t.xls". Read it in Mathematica by:

d = Import["d:/tmp/t.xls"]

this gives: {{{1., 2.}, {3., 4.}}}

now we add 10 to every cell and save it in "d:/tmp/t1.xls"by:

d=d+10;

Export["d:/tmp/t1.xls", d]

If you open this file, you will see that all the cells are changed.

hope this helps, Daniel

[Norbert Marxer]

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

[Tugrul Temel]

Dear Norbert Marxer

I tried to work out the example you have given in your earlier e-mail. It
works fine until the following point... but then I am not able to get the
Charts properly as I receive "$Failed" commands and it seems that I face
with "exceptions" but I do not know how to deal with these exceptions. Could
you tell me if you know..

Tugrul

*************************************************

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"]]

*************************************************