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:

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!

Tagged with: , , , , ,