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