How can I open an Excel file and modify it in Mathematica?

Question

I wanted to ask how can I view an Excel file from a Mathematica notebook and modify it.

I have seen a similar question, Open Excel file with Mathematica, but the answer involves .NET. Is there any other way to open an Excel file and modify it?

You could Import[] a spreadsheet, but I don't believe there's a native way to have a spreadsheet interface within Mathematica... — J. M.'s missing motivation, Jul 05 '12 at 07:00
@J.M.: I dont want to import the values because I want that end user can open the Excel file and change its values if needed and then use it in application. — Jennifer, Jul 05 '12 at 07:06
Then I don't quite understand why you want to use Mathematica to open Excel files... something about "use the proper tool for the job"? — J. M.'s missing motivation, Jul 05 '12 at 07:07
@J.M. actually the undocumented TableView looks very spreadsheet-like. — Verbeia, Jul 05 '12 at 07:09
@Verbeia: yeah, I tried it out after Wizard brought it up. Looks like a good start, but its being undocumented is rather inconvenient... — J. M.'s missing motivation, Jul 05 '12 at 07:14
@J.M.: I am building an application in mathematica and it is using some large values. I wanted to give the control to users to change those values. — Jennifer, Jul 05 '12 at 07:17
Then I suppose TableView[Import["file.xls"]] or something like it would be useful? — J. M.'s missing motivation, Jul 05 '12 at 07:26
To visualize/edit a spreadsheet in Mathematica: http://mathematica.stackexchange.com/questions/3868/how-to-visualize-edit-a-big-matrix-as-a-table/3870#3870 — VLC, Jul 05 '12 at 09:15

score 8 · Answer 1 · answered Jul 05 '12 at 07:26

Amending the file directly would indeed require something like .Net. If you were willing to consider an import-export roundtrip but with a spreadsheet like interface in Mathematica, then something like this might work:

rawdata = (Import["myspreadsheet.xlsx"][[2]]);
 (* where 2 is the number of the worksheet you want to use *)
newdata = TableView[rawdata]

Now, change the data cells you want to change and then type Shift-Enter when the cursor is in that TableView.

Then in another cell, evaluate:

newnewdata = %[[1]]

This will give you an expression with the list of changed data, stripped of the enclosing TableView Head.

You can then Export that changed expression back to Excel if that is what you wish.

@Verbeia: Thanks...but I have tried it in another way. I have posted my answer. — Jennifer, Jul 06 '12 at 07:07

score 3 · Answer 2 · edited Jul 06 '12 at 07:14

3

Row[{
 Button["Browse", (g = SystemDialogInput["FileOpen"]; SystemOpen[g]), Method -> "Queued"],    
 Button["Import", y = Import[g, "Data"]]}]

I have made two Buttons, one for browsing theExcel file and other imports it.

edited Jul 06 '12 at 07:14

Vitaliy Kaurov

73,078
9
204
355

answered Jul 06 '12 at 07:06

Jennifer

953
4
16

How can I open an Excel file and modify it in Mathematica?

2 Answers2