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.
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:
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
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):
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:
this will import the content of the first sheet:
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(
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.
You can copy a tabulated data to clipboard and then paste it in a sheet with formatted cells without loss of formatting:
Alternatively, you could save your Excel file as XML file,
|show 6 more comments|