Python – Write a DataFrame or List to multiple tabs in Excel

The idea of the article is to provide a Python code that does the following:

1. Takes a list or DataFrame;
2. Splits it into quite a few smaller parts;
3. Writes each one on a separate Excel worksheet;

Pretty much like this:

I will not write lot, this is how it is done:

import logging
import os
import shutil
import pandas as pd
import numpy as np


def main():

    logging.basicConfig(
        format="%(asctime)s %(message)s",
        datefmt="%m/%d/%Y %I:%M:%S %p",
        level=logging.INFO,
    )
    report_folder = "Reports"
    if os.path.exists(report_folder):
        shutil.rmtree(report_folder, ignore_errors=True)
        logging.info("Report folder is removed.")
    os.mkdir(report_folder)
    logging.info("Report folder is created.")

    my_list = range(0, 100_000, 11)
    my_lists = np.array_split(my_list, 1_000)
    excel_file_name = f"{report_folder}\My_Excel_Report.xlsx"

    n = 0
    with pd.ExcelWriter(excel_file_name) as writer:
        for small_list in my_lists:
            n = n + 1
            wks_name = f"Tab_{n}"
            pd.DataFrame(small_list).to_excel(
                writer, sheet_name=wks_name, header=False, index=False
            )
            logging.info(f"{n}/{len(my_lists)}")
    logging.info(f"File {excel_file_name} is created.")


if __name__ == "__main__":
    main()

Ok, starting with the analysis.

1. First things first – creation of the big list and splitting it is trivial – my_list = range(0, 100_000, 11) and my_lists = np.array_split(my_list, 1_000) Probably there are better ways to do it, but that is not the important now.
2. Now, the whole magic for putting the lists into different worksheets lies in pd.DataFrame(small_list).to_excel(writer, sheet_name=wks_name, header=False, index=False) . That’s why I have written in the title, that it also works with DataFrame, as the pd.DataFrame  takes the small_list and writes it into Excel with its to_excel  function.
3. No need to save the writer explicitly, this is done in the with pd.ExcelWriter(excel_file_name) as writer  construction automatically.

That’s all folks!