How to write data from Mathematica to an existing formatted Excel file?

Question

I have a sheet in an Excel file already formatted: 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 formatted template to create a new sheets, "copying" the stylings and data in the corresponding cells.

Answer 1

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.

Use ExcelLink

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.

Answer 2

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"]

Also you can automate this process by using Data ► Import from external source... ► Import data... Excel menu item and selecting a .dat file exported from Mathematica. Upon import the formatting of the Excel sheet will be retained. Also you can made a template Excel file with formatting and already tuned data import. This method even allows to add working hyperlinks as shown here.

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.

Answer 3

Here's a go at doing things from the extracted XML level. I only bothered to do this for the values, but if I ever use this I'll extend it to styles too probably.

One note before we start is that since CreateArchive can only take a directory, I had to use RunProcess and a system level zip compressor. On Mac (and I think Unix) that's zip. For your OS change that (it's under excelCompress).

So the basic idea is this:

Extract the file into a temp dir:

Clear[excelExtract];
excelExtract[file_] := 
 With[{eDir = 
    FileNameJoin@{$TemporaryDirectory, "_excel_extractor", 
      FileBaseName[file]}},
  Quiet@
   DeleteDirectory[
    eDir,
    DeleteContents -> True
    ];
  CreateDirectory[eDir,
   CreateIntermediateDirectories -> True
   ];
  CopyFile[file,
   FileNameJoin@{DirectoryName@eDir, FileBaseName[file] <> ".zip"},
   OverwriteTarget -> True
   ];
  ExtractArchive[
   FileNameJoin@{DirectoryName@eDir, FileBaseName[file] <> ".zip"},
   eDir
   ];
  DeleteFile[
   FileNameJoin@{DirectoryName@eDir, FileBaseName[file] <> ".zip"}];
  eDir
  ]

Create a worksheet-level update function:

Clear[excelValuesUpdate];
excelValuesUpdate[
   file_String?(FileExistsQ[#] && FileExtension[#] === "xml" &), 
   vals : _List | _Rule, overwrite : True | False : False] :=

  With[{xml = Import[file]},
   Export[file,
    excelValuesUpdate[xml, vals, overwrite],
    "XML",
    "ElementFormatting" -> False
    ]
   ];
excelValuesUpdate[
   xml : XMLObject[dec___][spec_, els_, r___],
   vals_,
   overwrite : True | False : False
   ] :=
  With[{vs =
     excelValuesFormat@
      Normal@
       excelValuesMerge[
        If[overwrite,
         vals,
         {excelValuesExtract[els], vals}
         ]
        ]
    },
   XMLObject[dec][spec,
    els /.
     XMLElement["sheetData", __] :>
      vs,
    r
    ]
   ];
excelValuesUpdate[dir_String?DirectoryQ, ws_String,
   vals_,
   overwrite : True | False : False
   ] :=

  With[{f = 
     FileNameJoin@{dir, "xl", "worksheets", 
       StringTrim[ws, ".xml"] <> ".xml"}},
   If[FileExistsQ[f],
    excelValuesUpdate[f, vals, overwrite],
    $Failed
    ]
   ];
excelValuesUpdate[dir_String?DirectoryQ, ws : _Integer : 1,
   vals_,
   overwrite : True | False : False
   ] :=

  With[{f = 
     FileNames["*.xml", FileNameJoin@{dir, "xl", "worksheets"}]},
   If[Length[f] >= ws,
    excelValuesUpdate[f[[ws]],
     vals,
     overwrite
     ],
    $Failed
    ]
   ];
excelValuesUpdate[file_, ws : _String | _Integer : 1,
  vals_,
  overwrite : True | False : False
  ] :=
 With[
  {eDir = 
    FileNameJoin@{$TemporaryDirectory, "_excel_extractor", 
      FileBaseName[file]}},
  If[! DirectoryQ[eDir], excelExtract[file]];
  excelValuesUpdate[eDir, ws, vals, overwrite]
  ]

This calls excelValuesFormat, excelValuesMerge, and excelValuesExtract which do the XML processing:

Here's the formatter:

Clear[excelValuesFormat];
excelValuesFormat[rules_] :=
  With[{coreData =
     GroupBy[
      With[{
          v = Lookup[#[[2]], "CellValue", Null],
          m = Lookup[#[[2]], "CellMeta", {}]
          },
         #[[1]] ->
          XMLElement["c",
           {"r" -> #[[1]], Sequence @@ m},
           {
            Replace[v, {
              Null -> Nothing,
              _ :>
               XMLElement["v", {},
                {ToString[v, InputForm]}
                ]
              }]}
           ]
         ] & /@ rules,
      (StringTrim[#[[1]], LetterCharacter ..] &) -> Last
      ]
    },
   XMLElement["sheetData", {},
    KeyValueMap[
     With[{
        keys =
         FromDigits[Flatten[{LetterNumber[#]}, 1], 26] & /@

          StringTrim[
           Lookup[List @@ #2[[All, 2]],
            "r"
            ],
           DigitCharacter ..
           ]
        },
       XMLElement["row",
        Flatten@
         {"r" -> ToString@#, "spans" ->
           StringRiffle[
            ToString /@
             MinMax[keys],
            ":"
            ]
          },
        #2[[Ordering[keys]]]
        ]
       ] &,
     coreData
     ]
    ]
   ];

The extractor:

Clear[excelValuesExtract];
excelValuesExtract[xml_] :=
  Cases[xml,
   XMLElement["c", {a___, "r" -> id_, b___}, v_] :>
    (id ->
      <|
       "CellValue" ->
        Replace[v,
         {
          {XMLElement["v", _, {s_String}]} :> ToExpression@s,
          _ -> Nothing
          }],
       "CellMeta" -> {a, b}
       |>),
   \[Infinity]
   ];

And the merger:

excelValueAssociationFormat[v_] :=

  If[AssociationQ[v] && 
    AnyTrue[{"CellValue"}, KeyMemberQ[v, #] &],
   v,
   <|
    "CellValue" -> v
    |>
   ];

Clear[excelValuesMerge];
excelValuesMerge[rules_] :=
 Merge[rules,
  <|

     "CellValue" -> 
      Replace[Lookup[#, "CellValue", 
        Nothing], {{___, l_} :> l, {} -> Null}],
     "CellMeta" -> 
      DeleteDuplicates@Apply[Join, Lookup[#, "CellMeta", {}]]
     |> &@*Map[excelValueAssociationFormat]
  ]

These store the cell data as an Association with style info in "CellMeta" and the value in "CellValue".

Then finally we need our compression function to repack the XML:

Clear[excelCompress];
excelCompress[file_, path_] :=

  With[{eDir = 
     FileNameJoin@{$TemporaryDirectory, "_excel_extractor", 
       FileBaseName[path]}},
   If[DirectoryQ[eDir],
    Quiet@DeleteFile[file];
    Switch[$OperatingSystem,
     "MacOSX",
     RunProcess[{"zip", "-r", ExpandFileName@file,
       Sequence @@
        Map[
         FileNameDrop[#, FileNameDepth[eDir]] &,
         FileNames["*", eDir, \[Infinity]]
         ]},
      ProcessDirectory -> eDir
      ],
     _,
     RunProcess[{"zip", "-r", ExpandFileName@file,
       Sequence @@
        Map[
         FileNameDrop[#, FileNameDepth[eDir]] &,
         FileNames["*", eDir, \[Infinity]]
         ]},
      ProcessDirectory -> eDir
      ]
     ];
    DeleteDirectory[eDir, DeleteContents -> True];
    file,
    $Failed
    ]
   ];
excelCompress[file_] :=
 excelCompress[file, file]

Now I made a dumb little spreadsheet to work with:

And we'll just add at value at "A10":

excelExtract[$workBook]

"/private/var/folders/9t/tqc70b7d61v753jkdbjkvd640000gp/T/_excel_\
extractor/bleh"

excelValuesUpdate[$workBook,
 "A10" -> 20
 ]

"/private/var/folders/9t/tqc70b7d61v753jkdbjkvd640000gp/T/_excel_\
extractor/bleh/xl/worksheets/sheet1.xml"

excelCompress[$workBook]

"~/Desktop/bleh.xlsx"

Note that the changes aren't merged until we re-compress. And here's the output:

One nice thing about this is that it lets us update with more precision. Everything else about it is worse.

Finally here's a function to do all of this in one go:

excelUpdate[file_, worksheet : _String | _Integer : 1,
  vals_, overwrite : True | False : False] :=
 (
  excelExtract[file];
  excelValuesUpdate[file,
   worksheet,
   vals,
   overwrite
   ];
  excelCompress[file]
  )

And we can use this like so:

excelUpdate[$workBook,
 {
  "B15" -> 10,
  "G22" -> 10,
  "C1" ->
   <|
    "CellValue" -> 1,
    "CellMeta" ->
     {
      "s" -> "1"
      }
    |>
  }
 ]

Note that odd Association spec. That's how I'm handling the formatting. The styles are numbered, I guess. So you use "s"->"n" to refer to the nth style. In my case yellow background is the first.

The style spec seems to be somewhat involved and not worth going in to, so I'd consider this a way to replace values without preserving styling.

Finally I've put this in a package here. You can use it like so:

Get["https://raw.githubusercontent.com/b3m2a1/mathematica-tools/master/ExcelUpdate.m"];
ExcelUpdate[file,vals]