BUG: XlsxWriter ignoring formats on numpy types if merged cells by riptusk331 · Pull Request #27006 · pandas-dev/pandas (original) (raw)

The XlsxWriter write_cells method takes a cells parameter that is itself derived from a generator of the various cells in the DataFrame. It calls the _value_with_format() to convert any numpy types to Python types for the Excel writer.

In the section of code that deals with writing merged cells, the original passed cell.val parameter was being passed into the writer, rather than the val returned from the _value_with_format() function. This
caused incompatible numpy or Pandas types to get passed into the writer when the condition of being in a 'merged' (grouped) DataFrame cell was met.

In my case, I had a Pandas Period object in the MultiIndex of a grouped DataFrame . All that needed to be done to fix this was simply removing the cell. from cell.val that was being passed into wks.merge_range() function, and replacing it with the correct val, which is 'type safe' for the Excel writer.