Mathematica to open an Excel spreadsheet and inject output into designated cells
- To: mathgroup at smc.vnet.net
- Subject: [mg118613] Mathematica to open an Excel spreadsheet and inject output into designated cells
- From: Gilmar Rodriguez-pierluissi <peacenova at yahoo.com>
- Date: Wed, 4 May 2011 06:35:39 -0400 (EDT)
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