Is CSV with Multi Tabs/Sheet Possible

Multiple sheets in same csv

You can't have 2 sheets in 1 CSV. That's the way CSV's (or 'flat-files') are created.

You can, however, create 2 sheets in 1 XLSX - the newer Excel format.

Python Modules to write to XLSX

XLSXWriter (Python)
Openpyxl (my favourite)

Creating multiple sheets in CSV file

CSV is a very simple format, and does not have the concept of a "sheet".
So, no, it's not possible directly.
The only thing that I can suggest is to send multiple csv files to the client, perhaps as a .zip file, and have the client unizp it and import one sheet at a time into Excel.

If you need it to open directly in the browser, you'll need to go with an xls file.

Recovery other sheets in .CSV format

No, CSV files do not support sheets. Excel will only save the active worksheet when saved to .csv format. This has to do with how csv files are merely "flat files" where .xls/.xlsx formats are not.

Like some of the comments have mentioned - it may exist in some temporary file, but your work is most likely lost.

Is it possible to output to a csv file with multiple sheets?

CSV file is interpreted a sequence of characters which comply to some standardization, therefor it cannot contains more than one sheet. You can output your data in a Excel file that contains more than one sheet using the Apache POI api.

How to convert multiple sheets in an excel workbook to csv files in python

If you want to get all of the sheets, you can pass sheet_name=None to the read_excel() call. This will then return a dictionary containing each sheet name as a key, with the value being the dataframe. With this you can iterate over each and create separate CSV files.

The following example uses a base filename with the sheetname appended, e.g. output_sheet1.csv output_sheet2.csv:

import pandas as pd

for sheet_name, df in pd.read_excel(r"input.xlsx", index_col=0, sheet_name=None).items():
    df.to_csv(f'output_{sheet_name}.csv', index=False, encoding='utf-8')

It assumes that all of your sheetnames are suitable for being used as filenames.

Write table or csv that include multiple tabs in excel

Adding to all the comments, XLConnect is a good idea. The download handler code would be:

library(XLConnect)
output$dwnld <- donwloadHandler(
    filename = "xyz.xlsx"
},
    content = function(file) {
        wb <- loadWorkbook(file, create = TRUE)
        createSheet(wb, name="x")
        createSheet(wb, name="y")
        writeWorkSheet(wb, data=df_x, sheet=x, header=TRUE)
        writeWorkSheet(wb, data=df_y, sheet=y, header=TRUE)
        saveWorkbook(wb)
     }
)