f



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

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@uni-leipzig.de> wrote:

From: Ulf-Dietrich Braumann <braumann@uni-leipzig.de>
Subject:  Re: Mathematica to open an Excel spreadsheet and injectoutput into designated cells
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
0
peacenova (18)
5/7/2011 11:33:14 AM
comp.soft-sys.math.mathematica 28821 articles. 0 followers. Follow

0 Replies
603 Views

Similar Articles

[PageSpeed] 58

Reply: