Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- To: mathgroup at smc.vnet.net
- Subject: [mg118647] Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- From: Alexey <lehin.p at gmail.com>
- Date: Fri, 6 May 2011 07:21:08 -0400 (EDT)
It is possible to do all these things through COM interface using
NETLink:
Needs["NETLink`"];
excel = CreateCOMObject["excel.application"]
But I do not know much about it.
Alexey
Gilmar Rodriguez-pierluissi <peacenova at yahoo.com> wrote:
> Mathgroup:
>
> (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):
>
> {{1972, 5331.}, {1973, 5932.}, {1974, 5656.}, {1975, 5382.}, {1976, 5357.},
> {1977, 5680.}, {1978, 5859.}, {1979, 6132.}, {1980,6002.}, {1981, 6208.},
> {1982, 6298.}, {1983, 7007.}, {1984, 7729.}, {1985, 8534.}, {1986, 9199.},
> {1987, 9696.}, {1988, 10114.}, {1989, 10237.}, {1990, 9754.}, {1991, 9354.},
> {1992, 9493.}, {1993, 10056.}, {1994, 10965.}, {1995, 11285.}, {1996, 11740.},
> {1997, 11800.}, {1998, 11633.}, {1999, 11893.}, {2000, 12214.} , {2001, 12153.},
> {2002, 12243.}, {2003, 12017.}, {2004, 12370.}, {2005, 13524.} , {2006, 13837.},
> {2007, 13672.}, {2008, 12524.}, {2009, 10645.}}
>
> (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!
>
> Gilmar Rodriguez Pierluissi