Re: Adding data to excel sheet (v7.0)
- To: mathgroup at smc.vnet.net
- Subject: [mg108810] Re: Adding data to excel sheet (v7.0)
- From: telefunkenvf14 <rgorka at gmail.com>
- Date: Thu, 1 Apr 2010 06:01:24 -0500 (EST)
- References: <hokle2$seu$1@smc.vnet.net>
On Mar 27, 5:08 am, kristoph <kristophs.p... at web.de> wrote:
> Hi,
>
> suppose I have a prepared Excel sheet which already contains numbers
> and some writing. However, the sheet is not complete because I would
> like to add some results from Mathematica.
>
> How do I just add the results and not overwrite the whole file.
>
> Example:
> Suppose the prepared excel file contains in cell A1 the text
> "results". Now I would like to export from my Mathematica file the
> results of some calculations. Say the result is 100. How do I add the
> result 100 to the existing file such that the resulting excel file has
> still "results" written in cell A1 and now 100 in cell B1.
>
> Thanks for answer.
> Kris
Here you go...
Start by creating two new .xls documents in MS Excel. Name one of the
files test.xls and put some data in the first column. Name the other
file OutputFromMMA.xls.
With both files open, you now need to create an Excel formula in
test.xls that references cells in OutputFromMMA.xls.
After accepting this formula, you can drag the formula down to create
correspondences to a range of cells, if you like. In order for this to
work as intended, you'll probably want to remove one (or both) of the
"$" symbols from the formula, if Excel automatically put some in.
Close both Excel files. Now we can import the desired data. (I
reflexively check "Elements" available for Import[])
In[1]:= Import["C:\Users\x61Tablet\Documents\\test.xls", "Elements"]
Out[1]= {"Data", "Formulas", "Sheets"}
"Sheets" is an option, so let's try that; specifically "Sheet1", rows
1\[Ellipsis]10, of column 1. Note that Range[] just generates the list
{1,2,3...10}, and while you're at it, name the imported data.
In[2]:= data = Import[
"C:\Users\x61Tablet\Documents\\test.xls", {"Sheets", "Sheet1",
Range[10],
1}]
Out[2]= {100., 200., 300., 400., 500., 600., 700., 800., 900., 1000.}
Do what ever calculations you wanted to do\[Ellipsis]
In[3]:= someCalculation = Plus @@ ToCharacterCode["ryan is super
cool"]/data
Out[3]= {17.46, 8.73, 5.82, 4.365, 3.492, 2.91, 2.494286, 2.1825,
1.94, 1.746}
Now export this data to OutputFromMMA.xls. (Warning: This overwrites
the original file!! This is OK for us\[LongDash]the file was really
just a placeholder.)
In[4]:= Export["C:\Users\x61Tablet\Documents\\OutputFromMMA.xls",
someCalculation]
Out[4]= "C:\\Users\\x61Tablet\\Documents\\OutputFromMMA.xls"
Check OutputFromMMA.xls to make sure the data exported as desired. If
it looks OK, close the OutputFromMMA.xls file completely and open up
test.xls in Excel. You'll likely be prompted with a security warning
or something about updating data links.
Update the links (your data should then show up) and save the file. If
you're using Excel 2007 or 2010 you may get a 'minor loss of fidelity'
warning; 95% of the time these are meaningless, so you can tell Excel
not to warn you again.
After having done this, I think it would actually be better to
Export[] to .txt or .csv. Why? Excel has better features for
automatically linking to these types of files and updating at periodic
intervals (at least in Excel 2007 and 2010). Check the 'Data' tab in
Excel for more info.
-RG