Exporting data to a new excel sheet of a pre-existing excel file

Question

m = Table[i - j, {i, 5}, {j, 6}]
n = Table[i - j, {i, 3}, {j, 7}]
o = Table[i^j, {i, 3}, {j, 7}]
Export["mfile2.xls", {"m Data" -> Transpose[m],"n Data" -> Transpose[n]}]

The above code creates a spreadsheet with two sheets named "m-data" and "n-data". Now I would like to be able to add more data to the existing spreadsheet. But when I do this:

Export["mfile2.xls", {"o Data" -> Transpose[o]}]

The earlier data is deleted and now my mfile2.xls just has the one sheet names "o-data" containing o-data.

Is it possible for me to Export data into some particular existing sheet and maybe even a specific cell of some pre-existing excel file and/or maybe get MMA to create a new sheet in addition to the existing ones.

Answer 1

Maybe this approach is not sophisticated enough, but it may come handy if you want to add only a few new sheets to your table or you want to update it regularly. Given you have created your data "m, n" and have exported it to "mfile2.xls".

You import the data of your XLS-file

data = Import[NotebookDirectory[] <> "mfile2.xls"];

and import the names of your sheets

sheets = Import[NotebookDirectory[] <> "mfile2.xls", "Sheets"]

Now you create your new data "o".

o = Table[i^j, {i, 3}, {j, 7}]

Then you rebuild the structure that you have exported earlier

olddata = sheets[[#]] -> data[[#]] & /@ Range[Length[sheets]]

and you add the data of your new sheet to it

newdata = Append[olddata, "o Data" -> Transpose[o]]

Finally you export the updated data to the original file

Export[NotebookDirectory[] <> "mfile2.xls", newdata]

which gives you an XLS containing all previous sheets along with the new one.

For convenience you can build yourself a function to which you only pass the filename of the XLS you want to append a new sheet to and the new data.

Answer 2

Hope it's not too late... Suppose you want to dump new data (called here "datanew" and generated somehow) into a new sheet of an existing Excel file named "oldfile.xlsx" containing (in sheet 1) data ("dataold"). What I do is:

SetDirectory[NotebookDirectory[]]
dataold=Import["oldfile.xlsx"]
Export["oldfile.xlsx",{dataold,datanew}]

What you are doing is importing the data already existing and then dumping it back again in sheet 1 but, you also dump "datanew" in sheet 2. You can name each new sheet:

Export["oldfile.xlsx",{"Old Data"->dataold,"New Data"->datanew}]

(The new sheets have names: Old Data, New Data)

Answer 3

Here is a simple function which does the job for me. Just make sure that your file is in the same notebook directory.

SetDirectory[NotebookDirectory[]];
ExportExcel[FileName_, NewData_] := Module[{OldData},
 If[FileExistsQ[FileName] == True,
    OldData = Import[FileName];
    Export[FileName, AppendTo[OldData, NewData]],
    Export[FileName, NewData]
   ]
  ]