Prev: Mathematica needs better support for automatic
Next: A general problem with numerical functions defined in a messy way
From: kristoph on 27 Mar 2010 06:08
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

From: David Bailey on 28 Mar 2010 05:06
kristoph 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
>
Without delving into the Java API's that can manipulate spreadsheets,
your best bet would be to use Import to get the whole spreadsheet into
Mathematica, add the new information, and then re-export it. You want to
work with a simple spreadsheet - not one with extra font information and
images, etc.

Beware that Export to spreadsheets seems rather slow if the amount of
data is large, so test early with some data that is as big as you expect
to need.

I think it is possible to incrementally change an XL spreadsheet via
Java (and hence via Mathematica), so if the above is not adequate, you
might want to contact me.

David Bailey
http://www.dbaileyconsultancy.co.uk

From: telefunkenvf14 on 1 Apr 2010 07:01
On Mar 27, 5:08 am, kristoph <kristophs.p...(a)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

 | 
Pages: 1
Prev: Mathematica needs better support for automatic
Next: A general problem with numerical functions defined in a messy way