Export List Data to an Excel file with Python ready-to-use function

• tinyytopic.com
• 0
• on Feb 13, 2023

Install the following modules if you haven’t installed them already:

pip install pandas
pip install xlwings
pip install xlsxwriter

Ready to use Python function to export data in List format to an excel file:

def export_to_excel(ImpData, xlColName, xlColWidth=[]):
    # Export list data to an excel sheet with simple formatting
    df = pd.DataFrame()
    endNum = len(xlColName)
    
    for colData in range(0, endNum):
        df[xlColName[colData]] = ImpData[colData::endNum]
    
    xlsFilepath = 'export_dataframe_23.xlsx'
    try: writer = pd.ExcelWriter(xlsFilepath)
    except Exception as e: 
        print(str(e))
        messagebox.showinfo('Check File', 'Please close the excel file and try again!')
        return
    df.to_excel(writer, sheet_name='Data', index = False, header=True)
    
    workbook  = writer.book
    worksheet = writer.sheets['Data']
    
    wrap_format = workbook.add_format({'text_wrap': True, 'align': 'left'})
    
    for idx, col in enumerate(df):  # loop through all columns
        series = df[col]
        worksheet.set_column(idx, idx, 25, wrap_format)  # wrap format
    worksheet.freeze_panes(1, 0) # freeze top row
    
    writer.save()
    
    writer = xw.Book(xlsFilepath).sheets['Data']
        
    # Autofit column and row
    writer.autofit()
    
    # change column width if defined by list
    if len(xlColWidth) != 0:
        for col in range(1, len(xlColWidth)+1):
            ListCol = col - 1
            if xlColWidth[ListCol] != '':
                writer.range(1, col).column_width = xlColWidth[ListCol]

    # Open the excel file
    os.system(xlsFilepath)

Write your main code as a sample below,

import pandas as pd
import xlwings as xw
import os

export_to_excel(['test', 5, 'need to check', 'check', 25, ''], ['Task', 'Qty', 'Remarks'], ['', '', ''])

The output of the code is,

The export_to_excel function is used to export data from a list of lists (ImpData) to an Excel file. The function uses the pandas and openpyxl (xw) libraries to perform the export.

The function takes three arguments:

1. The ImpData is a list of lists, where each inner list represents a row of data.
2. The xlColName is a list of strings, representing the names of the columns in the Excel file.
3. The xlColWidth is a list of integers, representing the widths of the columns in the Excel file.

The function first creates a pandas DataFrame df from the data in ImpData by dividing the data into columns, using the length of xlColName as the number of columns and using df[xlColName[colData]] = ImpData[colData::endNum] to assign the data to the columns. The data for each column is taken from ImpData in slices of length endNum.

Next, the function uses the pandas ExcelWriter class to write the DataFrame to an Excel file named export_dataframe_23.xlsx. The function uses a try-except block to handle any exceptions that may occur while writing to the file and shows an error message if necessary.

The function then uses the openpyxl library to format the Excel file. It adds a text wrap format to the cells and sets the column widths to 25, unless the widths are specified in xlColWidth. The top row is also frozen to keep it visible while scrolling.

Finally, the function uses the os.system function to open the Excel file.