# How to write data from Mathematica to an existing formated Excel file?

I have a sheet in an Excel file already formated: different colors, fonts, columns width and cells styles. I want to fill-out values from Mathematica into specific cells of that sheets, or use that sheet as a formated template to create a new sheets, "copying" the stylings and data in the corresponding cells.

-
@Mr.Wizard I edited the question so as to explicitly state the bearing with Mathematica –  jss Dec 5 '12 at 20:38
Okay, thank you. This now looks like it may be a duplicate of one of these. Please take a look and let me know. mathematica.stackexchange.com/q/11759/121 or mathematica.stackexchange.com/q/8725/121 –  Mr.Wizard Dec 5 '12 at 20:57
The first post seems discontinued. I am just learning from the second one (q/8725) and let you know later. –  jss Dec 5 '12 at 21:29
jss, sorry I was not here sooner to reply. I shall reopen this question for you. Please add sufficient detail of the operation you wish to perform, and include as much of an example as is practical. Please also make it clear in the question how it is distinct from those to prior ones (which, if I understand, it is) or others may close your question again; this is because even if an old question is unanswered ("discontinued" as you put it) a new but identical question should not be started. –  Mr.Wizard Dec 6 '12 at 7:55
I am unable, from the little information supplied, to form a coherent idea of exactly what asker expects to accomplish in Mathematica. –  m_goldberg Dec 6 '12 at 18:36

I see several ways how you could achieve what you want. Any of them either needs extra "non-mathematica" software, efforts or knowledge. I think the best way is to learn how to interact with excel via .Net/COM as is described e.g. in the documentation, but you have mentioned that you want to avoid that. Here are the alternatives that I can think of:

# Use Import/Export with FormattedData (broken?)

In newer versions there is the possibility to use "FormattedData" as the optional element argument for excel files. While import seems to work I couldn't get the export to work even for a very simple example. If you get the export to work for your specific files, you could import with something like:

data = Import[xlsfilename,"FormattedData"]


then manipulate the data (needs some extra care as the formatting need to be taken into account), e.g. this will change the value of a formatted number entry of the cell b2 in the first sheet, the content as imported is wrapped with Style and the first argument of Style is the actual data:

data[[1, 2, 2, 1]] = 4;


once you have replaced the data, you should be able to reexport (this is the part that doesn't seem to work, at least for me):

Export[xlsfilename, data,"FormattedData"]


# Use .NET/COM

I've read that you don't want to delve into this, but I think it is the one possibility that definitely would work. It needs some effort and learning of course, especially if you haven't done anything like that before. On the other hand it isn't very difficult and it's the one method that I did succesfully use before. I'm sure you'll get answers to get you started if you ask for this in a more specific way.

# Import/Export as xml

the current XLSX format is actually a zipped archive containing some XML files which you could import, manipulate within mathematica and reexport. I have no real experience in how well that works but the following might get you started. This will show the various filenames:

Import[xlsxfilename, "ZIP"]


this will import the content of the first sheet:

Import[xlsxfilename, {"ZIP","xl\\worksheets\\sheet1.xml"}]


manipulating these files might be cumbersome though, as the data and styling is spread over several files. I think getting this to work will be a lot more effort than learning how to manipulate excel files via .NET. Using the information that Alexey provides in his answer it looks like working with files saved as XML from Excel might be less effort than working with the xlsx-files directly.

# Find and Use other libraries to manipulate XLS files

you might be able to find 3rd party libraries which let you read/write xls files, written e.g. in Java. You could try to use these from Mathematica. I doubt that this would be any easier than using .NET/COM which is readily available, but it could be a solution if there is no excel/.NET/COM for the platform you are planning to run this on. Actually there is such a java library coming with Mathematica which seems to be used for the current import/export functionality(\$InstallationDirectory/SystemFiles/Converters/Java/jxl.jar, looks like that is jexcelapi, but I'm not sure). I have no experience what exactly it can do and if/how you can make use of it directly.

There is a commercial add-on to Mathematica called ExcelLink. I also have no experience in using this myself and I don't know if it supports the very recent versions of Mathematica, but it seems to provide the functionality you want. See the link for more information about it.

-
I did it in .NET with Visual Studio and wanted to migrate to Mathematics fully, but without wraping .NET. XML choice seems quite flexible but as you said will be a lot more effort. –  jss Dec 7 '12 at 17:52
ExcelLink seems to me the better choice but not free. –  jss Dec 7 '12 at 17:53
There are a lot of things that have influence on what "better" would mean for your specific use case and situation -- and that we can't know. This is why I tried to show the alternatives, at least as known to me. ExcelLink might well be the best choice. I'm not sure but think that it relies on .Net or COM under the hood, though. So it is, AFAIK, only available on Windows... –  Albert Retey Dec 8 '12 at 10:58

You can copy a tabulated data to clipboard and then paste it in a sheet with formatted cells without loss of formatting:

CopyToClipboard@
Cell[StringReplace[
ExportString[myTable, "TSV"],
"\r\n" -> "\n"], TextClipboardType -> "PlainText"]


Alternatively, you could save your Excel file as XML file, Import this file in Mathematica and manipulate it as you wish. Excel works with such files exactly as with its native format.

-
And for version 7, TableForm[myTable] and copy as Plain Text ... –  Chris Degnen Dec 6 '12 at 13:57
@Chris It can be automatized too (via FrontEndTokenExecute[nb, "Copy"]), see here. –  Alexey Popkov Dec 6 '12 at 14:43
@ Alexey - Yes, something like this maybe: stackoverflow.com/a/7608554/879601 –  Chris Degnen Dec 6 '12 at 14:47
@AlexeyPopkov: can you automatize the paste into the correct excel sheet also? I doubt that this can be done from within Mathematica (without using .NET/COM which probably would make possible more straightforward solutions). Am I unaware of something obvious?? –  Albert Retey Dec 6 '12 at 14:54
@Albert Yes, there is a workaround which allows automatic import of the textual data into appropriate place in an Excel worksheet. In Excel, go to the "Data" menu, select "Import..." submenu and then "Import data...". I recommend to use the .dat format for intermediate file since Excel does not lock this file for writing as opposed to the .txt files. –  Alexey Popkov Dec 6 '12 at 15:12