f



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

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

0
braumann (7)
5/6/2011 11:21:15 AM
comp.soft-sys.math.mathematica 28821 articles. 0 followers. Follow

0 Replies
626 Views

Similar Articles

[PageSpeed] 36

Reply: