Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.soft-sys.math.mathematica > #2092 > unrolled thread

Mathematica to open an Excel spreadsheet and inject output into designated cells

Back to article view | Back to comp.soft-sys.math.mathematica

Contents

  Mathematica to open an Excel spreadsheet and inject output into designated cells Gilmar Rodriguez-pierluissi <peacenova@yahoo.com> - 2011-05-04 10:35 +0000
    Re: Mathematica to open an Excel spreadsheet and inject output into ADL <alberto.dilullo@tiscali.it> - 2011-05-08 11:36 +0000

#2092 — Mathematica to open an Excel spreadsheet and inject output into designated cells

Mathgroup:
 
(1.) Imagine that you have an Excel spreadsheet workbook in your C:\Temp drive.
     The following data is contained in a worksheet called "Data" of the workbook.
     Cell D1 contains the word "Year", and cell E1 contains the label "Region 1".
     Cells D2 to D39 (i.e.; the year column) contain the years 1972 to 2009.
     Cells E2 to E39 (i.e.; the Region 1 column) contain data values.

     The data values are (in Mathematica format):
 
     {{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.}}
    
(2.) Mathematica reads the "Region 1" heading in cell (1,5) of the "Data" worksheet via:
 
     Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data", 1, 5}]
 
(3.) Mathematica then skips the column heading and reads the data values incells (2, 5)
     up to (39, 5) via:
 
     values = Table[Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data", i, 5}], {i, 2, 39}];
 
     L = Length[values];
 
     data = Table[{1971 + i, values[[i]]}, {i, 1, L}];
 
(4.) We plot the data:

     dataplt = ListPlot[data, PlotRange -> {{1970, 2010}, {4000, 15000}},PlotStyle -> {Red}]
 
(5.) We get a polynomial fit:

     nlm = NonlinearModelFit[data, a + b x^2 + c x^3 + d x^4 + e x^5, {a, b, c, d, e}, x]
 
(6.) We plot our model:

     modelplt = Plot[nlm[x], {x, 1972, 2009}]
 
(7.) We look at our data plot and model plot together:
 
     Show[dataplt, modelplt, Frame -> True, PlotRange -> {{1970, 2010}, {4000, 15000}}]
 
(8.) We build a forecast table:

     forecast = Table[{x, nlm[x]}, {x, 2010, 2019}]
 
My question is; how can I convince Mathematica to open the original spreads heet.xls file;
write the forecasted years in cells D40 to D50 of the spreadsheet, and write the forecasted
values in cells E40 to E50 of the spreadsheet?
 
Please; I'm NOT talking about doing something like:

Export["C:\\Temp\\Forecast.xls", {"Forecast" -> forecast}]

and then copying and pasting the values from Forecast.xls to Spreadsheet.xls.
 
I want Mathematica to open Spreadsheet.xls and insert the forecasted years in cells D40 to D50 and the forcasted values in cells E40 to E50.  Thank you for your help!
 
Gilmar Rodriguez Pierluissi

[toc] | [next] | [standalone]

#2209 — Re: Mathematica to open an Excel spreadsheet and inject output into

On 4 Mag, 12:35, Gilmar Rodriguez-pierluissi <peacen...@yahoo.com>
wrote:
> Mathgroup:
>
> (1.) Imagine that you have an Excel spreadsheet workbook in your C:\Temp drive.
>      The following data is contained in a worksheet called "Data" of the workbook.
>      Cell D1 contains the word "Year", and cell E1 contains the label "Region 1".
>      Cells D2 to D39 (i.e.; the year column) contain the years 1972 to 2009.
>      Cells E2 to E39 (i.e.; the Region 1 column) contain data values.
>
>      The data values are (in Mathematica format):
>
>      {{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.}}
>
> (2.) Mathematica reads the "Region 1" heading in cell (1,5) of the "Data" worksheet via:
>
>      Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data", 1, 5}]
>
> (3.) Mathematica then skips the column heading and reads the data values incells (2, 5)
>      up to (39, 5) via:
>
>      values = Table[Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data", i, 5}], {i, 2, 39}];
>
>      L = Length[values];
>
>      data = Table[{1971 + i, values[[i]]}, {i, 1, L}];
>
> (4.) We plot the data:
>
>      dataplt = ListPlot[data, PlotRange -> {{1970, 2010}, {4000, 15000}},PlotStyle -> {Red}]
>
> (5.) We get a polynomial fit:
>
>      nlm = NonlinearModelFit[data, a + b x^2 + c x^3 + d x^4 + e x^5, {a, b, c, d, e}, x]
>
> (6.) We plot our model:
>
>      modelplt = Plot[nlm[x], {x, 1972, 2009}]
>
> (7.) We look at our data plot and model plot together:
>
>      Show[dataplt, modelplt, Frame -> True, PlotRange -> {{1970, 2010}, {4000, 15000}}]
>
> (8.) We build a forecast table:
>
>      forecast = Table[{x, nlm[x]}, {x, 2010, 2019}]
>
> My question is; how can I convince Mathematica to open the original spreads heet.xls file;
> write the forecasted years in cells D40 to D50 of the spreadsheet, and write the forecasted
> values in cells E40 to E50 of the spreadsheet?
>
> Please; I'm NOT talking about doing something like:
>
> Export["C:\\Temp\\Forecast.xls", {"Forecast" -> forecast}]
>
> and then copying and pasting the values from Forecast.xls to Spreadsheet.  xls.
>
> I want Mathematica to open Spreadsheet.xls and insert the forecasted years in cells D40 to D50 and the forcasted values in cells E40 to E50.  Thank you for your help!
>
> Gilmar Rodriguez Pierluissi

You may search this group for "Norbert Marxer Excel" and find, I
believe, the detailed instructions to achieve what you look for.

ADL

[toc] | [prev] | [standalone]

Back to top | Article view | comp.soft-sys.math.mathematica

csiph-web