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?

Answer 1

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.

Answer 2

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.

Answer 3

While this doesn't allow you to edit the spreadsheet in Mathematica directly per se, I did make a resource function a few months ago which allows you to edit any 2D arrays in your system's native spreadsheet editor. The resource function is called SpreadsheetEdit and it can be accessed on the Wolfram Function Repository https://resources.wolframcloud.com/FunctionRepository/resources/SpreadsheetEdit/

And used in Mathematica as ResourceFunction["SpreadsheetEdit"][data]