Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- To: mathgroup at smc.vnet.net
- Subject: [mg118689] Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
- From: Gilmar Rodriguez-pierluissi <peacenova at yahoo.com>
- Date: Sat, 7 May 2011 07:33:20 -0400 (EDT)
Thank you Ulf-Dietrich for your valuable help!
However, what I find incredible is that a user can access data from an Excel workbook (and worksheet) into Mathematica with surgical precision but, a user cannot transfer output from Mathematica to the same Excel workbook (and worksheet) without erasing the original
workbook!
--- On Fri, 5/6/11, Ulf-Dietrich Braumann <braumann at uni-leipzig.de> wrote:
From: Ulf-Dietrich Braumann <braumann at uni-leipzig.de>
Subject: [mg118689] [mg118648] Re: Mathematica to open an Excel spreadsheet and injectoutput into designated cells
To: mathgroup at smc.vnet.net
Date: Friday, May 6, 2011, 11:21 AM
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