BUG: XlsxWriter ignoring formats on numpy types if merged cells
State: closed

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.

- [x] closes #26999 
- [x] tests added / passed
- [x] whatsnew entry


Labels:
name: IO Excel
description: read_excel, to_excel
name: Period
description: Period data type

Comments:
@riptusk331 Thanks for the PR.

can you add tests and a whatsnew.
Nice catch! Yea as mentioned I think just need a test and a whatsnew for v0.25 and can get this in
Never written a proper test before, so giving the docs a read and will try to push something this week.
can you add a test here
yes trying to get to this tomorrow
Hello @riptusk331! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:





There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-06-28 00:43:56 UTC
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=h1) Report
> Merging [#27006](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/b4d4ec5a36acda40f13a8c3c3e19262c095d4c41?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/27006/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #27006      +/-   ##
==========================================
- Coverage   91.99%   91.98%   -0.01%     
==========================================
  Files         180      180              
  Lines       50774    50774              
==========================================
- Hits        46712    46707       -5     
- Misses       4062     4067       +5
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.63% <ø> (ø)` | :arrow_up: |
| #single | `41.83% <ø> (-0.11%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/excel/\_xlsxwriter.py](https://codecov.io/gh/pandas-dev/pandas/pull/27006/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsL194bHN4d3JpdGVyLnB5) | `94.36% <ø> (ø)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/27006/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `88.88% <0%> (-11.12%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/27006/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.89% <0%> (-0.12%)` | :arrow_down: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/27006/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `90.84% <0%> (-0.11%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=footer). Last update [b4d4ec5...4d5eab1](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=h1) Report
> :exclamation: No coverage uploaded for pull request base (`master@08a599b`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference#section-missing-base-commit).
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/27006/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=tree)

```diff
@@            Coverage Diff            @@
##             master   #27006   +/-   ##
=========================================
  Coverage          ?   92.04%           
=========================================
  Files             ?      180           
  Lines             ?    50714           
  Branches          ?        0           
=========================================
  Hits              ?    46679           
  Misses            ?     4035           
  Partials          ?        0
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.68% <ø> (?)` | |
| #single | `41.87% <ø> (?)` | |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/excel/\_xlsxwriter.py](https://codecov.io/gh/pandas-dev/pandas/pull/27006/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsL194bHN4d3JpdGVyLnB5) | `94.36% <ø> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=footer). Last update [08a599b...b16397d](https://codecov.io/gh/pandas-dev/pandas/pull/27006?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

whatsnew added
@riptusk331 looks like a linting error, can you fixup and repush.
@jreback fixed linting and repushed.  

@WillAyd This passes all pytest tests as I pushed it, but I was not able to implement all your suggestions without failing tests for the reasons mentioned in the comments above. Namely using merge_cells in the call and the column type setting simplification. Thinking of how to best resolve, but wanted to get most current working iteration pushed.
thanks @riptusk331 
@jreback glad to contribute and look forward to helping out more. will delete this branch shortly.

@WillAyd thanks for guiding me! when you have time, would love to get input on how my test code could be further improved over what i pushed, as i was unable to figure out how to properly implement 2 of your suggestions (or was possibly not fully understanding what you were looking for / what's proper in a unit test). cheers.