Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- To: mathgroup at smc.vnet.net
- Subject: [mg118648] Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- From: Ulf-Dietrich Braumann <braumann at uni-leipzig.de>
- Date: Fri, 6 May 2011 07:21:18 -0400 (EDT)
Hi,
there is no Export routine in Mathematica capable to manipulate single
numbers or to add something without touching the rest. However, it is easy
to do that what you want combining Import and Export "on foot":
First you definitely should load your complete "Data" worksheet:
In[1]:= tableau =
Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data"}]
Out[1]= {{"", "", "", "Year", "Region 1"}, {"", "", "", 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.}}
Your two columns are simply extracted:
In[2]:= data = tableau[[2 ;; 39, 4 ;; 5]]
Out[2]= {{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.}}
Once you have fitted using:
In[3]:= nlm =
NonlinearModelFit[data,
a + b x^2 + c x^3 + d x^4 + e x^5, {a, b, c, d, e}, x];
... and did the extrapolation using:
In[4]:= forecast = Table[{x, nlm[x]}, {x, 2010, 2019}]
Out[4]= {{2010, 12103.4}, {2011, 11899.7}, {2012, 11662.5}, {2013,
11392.3}, {2014, 11089.9}, {2015, 10755.9}, {2016, 10391.2}, {2017,
9997.06}, {2018, 9574.57}, {2019, 9125.17}}
... you than append a number of lines according to your forcast horizon to
your tableau variable:
In[5]:= tableau =
Join[tableau,
Table[Table["", {Last[Dimensions[tableau]]}], {Length[forecast]}]];
However, I do not have any idea what you carry in the columns A, B, and C
...
You than insert the forcasted data at that tableau position you want:
In[6]:= tableau[[39 + 1 ;; 39 + Length[forecast], 4 ;; 5]] = forecast;
Now you export:
In[7]:= Export["C:\\Temp\\Forecast.xls", {"Forecast" -> tableau}];
I sometimes prefer keeping a worksheet as is and just add the extended
data in a new worksheet:
In[8]:= Export["C:\\Temp\\Forecast.xls",
"Sheets" -> {"Data" -> tableau[[1 ;; 39]],
"Forecast" -> tableau}, "Rules"];
Wasn't that easy?
Regards - Ulf-Dietrich