MathGroup Archive 2011

[Date Index] [Thread Index] [Author Index]

Search the Archive

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


  • Prev by Date: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • Next by Date: Re: Stiff ODE: Modified Poisson-Boltzmann
  • Previous by thread: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
  • Next by thread: Re: Mathematica to open an Excel spreadsheet and inject output into designated cells