ENH: Add formatters parameter for the xlsxwriter engine
State: closed

- [X] closes #30275
- [X] tests added / passed
- [X] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry

Currently pandas doesn't support  different formats for columns of the type `date`/`datetime`. Giving the provision to do so using the `formatters` parameter as shown below:

**Master**
```
from datetime import datetime
df = pd.DataFrame(
    {
        "Col1": [1010.34, 2020.67, 3030.65,3210.65],
        "Col2": [
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31)
        ],
        "Col3": [
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31)
        ],
        "Col4": [
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31),
            datetime(2014, 1, 31)]
    },
)

writer = pd.ExcelWriter("pd_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='MM/DD/YYYY HH:MM:SS',
                        date_format='MM/DD/YYYY')

df.to_excel(writer, sheet_name='Sheet1')
writer.save()
```
**Output of the file**
![image](https://user-images.githubusercontent.com/55484403/116774842-e9328180-aa7c-11eb-831e-de637cad0959.png)



**Proposed change**
```
writer = pd.ExcelWriter(
    "pd_datetime.xlsx",
    engine="xlsxwriter",
    datetime_format="MM/DD/YYYY HH:MM:SS",
    date_format="MM/DD/YYYY",
    formatters={"Col2": "mmmm", "Col3": "YYYY"},
)

df.to_excel(writer, sheet_name="Sheet1")
writer.save()

```
**Output of the file**
![image](https://user-images.githubusercontent.com/55484403/116774928-85f51f00-aa7d-11eb-9cdd-1a41c0562fad.png)

The `date`/`datetime` columns that do not have a format specified in the `formatters` dict will take the format from the   `date_format`/`datetime_format` parameters.

PS- The proposed change is currently applicable **only** for the `date`/`datetime` types but can easily be extended for `int` and `float` as well.

Labels:
name: Enhancement
name: IO Excel
description: read_excel, to_excel
name: Stale

Comments:
if you are proposing this, then we need to deprecate date_format/datetime_format. there are too many options. 
@jreback, should I go ahead with the deprecation then? 
Also, plz help with the following:

1) As the issue raised talks only about the `date`/`datetime` types, should I keep this ENH specific only to these dtypes?
2) Would extending this change to `int` and `float` be OK? If yes, should it be dealt with in this ENH itself or as a separate PR.


This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
Thanks for the pull request, but it appears to have gone stale. Closing for now, but I recommend opening a new pull request starting with the deprecation of the date/datetime format.