The idea of the article is to provide a Python code that does the following:
- Takes a list or DataFrame;
- Splits it into quite a few smaller parts;
- Writes each one on a separate Excel worksheet;
Pretty much like this:
I will not write lot, this is how it is done:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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.
- 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.
- 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.
- 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!