Services & ResourcesWolfram ForumsMathGroup Archive

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

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

• To: mathgroup at smc.vnet.net
• Subject: [mg118662] Re: Mathematica to open an Excel spreadsheet and inject output into designated cells
• From: "Hans Michel" <hmichel at cox.net>
• Date: Fri, 6 May 2011 07:23:51 -0400 (EDT)

You need to treat Excel as a database not an entire file that needs to be
consumed.

First have an ODBC or any other kind of (JDBC) Database connector Driver for
Excel available on your system.

Second Use Mathematica's SQL features

http://www.connectionstrings.com/excel

Look around for proper connection string, also be concerned with permissions
issue on your system.

Use the distinct SQL statements special to Excel. Use [SheetNames] and
R[1]C[1] for ranges.

Use SQL SELECT for retrieval
Use SQL Insert for insertion/update.

Focus on the data not the charts as if your scope is to also insert M plots
into Excel it is possible but not worth the effort required.

I have done this before but can't find the code. 

There is enough info here to try a new paradigm. 
The Mathematica SQL stuff is in the documentation
The ODBC or JDBC driver stuff is freely available on the Net
The connection string properties are available at connectionstrings.com
The specialize SQL statements to hook into Excel are documented on
Microsoft's site
Permission issue you should control


Hans

-----Original Message-----
From: Gilmar Rodriguez-pierluissi [mailto:peacenova at yahoo.com] 
Sent: Wednesday, May 04, 2011 5:36 AM
To: mathgroup at smc.vnet.net
Subject: [mg118662] [mg118613] 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