f



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
0
peacenova (18)
5/4/2011 10:35:40 AM
comp.soft-sys.math.mathematica 28821 articles. 0 followers. Follow

4 Replies
641 Views

Similar Articles

[PageSpeed] 20

On May 4, 8:35 pm, 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


What you seem to be wanting to do can be accomplished using OpenAppend
however this doesn't work -- at least I cannot make it work -- with
Excel files.

Mike

0
5/5/2011 9:25:47 AM
It is possible to do all these things through COM interface using
NETLink:

Needs["NETLink`"];
excel = CreateCOMObject["excel.application"]

But I do not know much about it.

Alexey

Gilmar Rodriguez-pierluissi <peacenova@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

0
lehin.p (233)
5/6/2011 11:21:04 AM
On May 4, 8:35 pm, 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


What you seem to be wanting to do can be accomplished using OpenAppend
however this doesn't work -- at least I cannot make it work -- with
Excel files.

Mike

0
5/7/2011 10:22:29 AM
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

0
5/8/2011 11:36:22 AM
Reply: