MathGroup Archive 2011

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

Search the Archive

Re: Mathematica to open an Excel spreadsheet and inject output into designated cells

  • To: mathgroup at smc.vnet.net
  • Subject: [mg118659] Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • From: Albert Retey <awnl at gmx-topmail.de>
  • Date: Fri, 6 May 2011 07:23:18 -0400 (EDT)

Hi,
>
> (1.) Imagine that you have an Excel spreadsheet workbook in your
> C:\Temp drive. The following data is contained in a worksheet called
> "Data" of the workbook. Cell D1 contains the word "Year", and cell E1
> contains the label "Region 1". Cells D2 to D39 (i.e.; the year
> column) contain the years 1972 to 2009. Cells E2 to E39 (i.e.; the
> Region 1 column) contain data values.
>
> The data values are (in Mathematica format):
...

>
> (2.) Mathematica reads the "Region 1" heading in cell (1,5) of the
> "Data" worksheet via:
>
> Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data", 1, 5}]
>
> (3.) Mathematica then skips the column heading and reads the data
> values incells (2, 5) up to (39, 5) via:
>
> values = Table[Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data",
> i, 5}], {i, 2, 39}];
>
> L = Length[values];
>
> data = Table[{1971 + i, values[[i]]}, {i, 1, L}];
>
> (4.) We plot the data:
>
> dataplt = ListPlot[data, PlotRange ->  {{1970, 2010}, {4000,
> 15000}},PlotStyle ->  {Red}]
>
> (5.) We get a polynomial fit:
>
> nlm = NonlinearModelFit[data, a + b x^2 + c x^3 + d x^4 + e x^5, {a,
> b, c, d, e}, x]
>
> (6.) We plot our model:
>
> modelplt = Plot[nlm[x], {x, 1972, 2009}]
>
> (7.) We look at our data plot and model plot together:
>
> Show[dataplt, modelplt, Frame ->  True, PlotRange ->  {{1970, 2010},
> {4000, 15000}}]
>
> (8.) We build a forecast table:
>
> forecast = Table[{x, nlm[x]}, {x, 2010, 2019}]
>
> My question is; how can I convince Mathematica to open the original
> spreads heet.xls file; write the forecasted years in cells D40 to D50
> of the spreadsheet, and write the forecasted values in cells E40 to
> E50 of the spreadsheet?
>
> Please; I'm NOT talking about doing something like:
>
> Export["C:\\Temp\\Forecast.xls", {"Forecast" ->  forecast}]
>
> and then copying and pasting the values from Forecast.xls to
> Spreadsheet.xls.
>
> I want Mathematica to open Spreadsheet.xls and insert the forecasted
> years in cells D40 to D50 and the forcasted values in cells E40 to
> E50.  Thank you for your help!

I see 3 possibilities:

1) import all the data, manipulate it within Mathematica and export the 
manipulated data back to the file, overwriting the former file content. 
That should be relatively straightforward but might have some drawbacks, 
e.g. Mathematica will only export the data, all formatting will be lost.

2) use NETLink to open excel and then automate excel to do the 
manipulations by calling appropriate methods of COM-objects. This has 
also disadvantages: you might need to learn some new things, depending 
on what you know about NETLink and COM-Objects, and of course it works 
only on Windows with an Excel installed. But basically I think you 
should be able to do what you want with that approach.

3) get the package "Mathematica Link for Excel" and use its 
functionality. I have never used it, but to my understanding among other 
features it provides mathematica functions to basically do 2) with less 
own coding. You might want to contact the author whether you can do what 
you want with it.

hth,

albert


  • Prev by Date: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • Next by Date: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • Previous by thread: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • Next by thread: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells