{"id":405,"date":"2023-02-13T09:43:01","date_gmt":"2023-02-13T09:43:01","guid":{"rendered":"https:\/\/tinyytopic.com\/?p=405"},"modified":"2023-02-13T09:42:34","modified_gmt":"2023-02-13T09:42:34","slug":"export-list-data-to-an-excel-file-with-python-ready-to-use-function","status":"publish","type":"post","link":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/","title":{"rendered":"Export List Data to an Excel file with Python ready-to-use function"},"content":{"rendered":"\n

How to export List Data to an Excel File with Python?<\/mark><\/h5><\/div>\n\n\n\n

Install the following modules if you haven’t installed them already:<\/p>\n\n\n\n

pip install pandas\npip install xlwings\npip install xlsxwriter<\/code><\/pre>\n\n\n\n
Ready to use Python function to export data in List format to an excel file:<\/p>\n\n\n\n
def export_to_excel(ImpData, xlColName, xlColWidth=[]):\n    # Export list data to an excel sheet with simple formatting\n    df = pd.DataFrame()\n    endNum = len(xlColName)\n    \n    for colData in range(0, endNum):\n        df[xlColName[colData]] = ImpData[colData::endNum]\n    \n    xlsFilepath = 'export_dataframe_23.xlsx'\n    try: writer = pd.ExcelWriter(xlsFilepath)\n    except Exception as e: \n        print(str(e))\n        messagebox.showinfo('Check File', 'Please close the excel file and try again!')\n        return\n    df.to_excel(writer, sheet_name='Data', index = False, header=True)\n    \n    workbook  = writer.book\n    worksheet = writer.sheets['Data']\n    \n    wrap_format = workbook.add_format({'text_wrap': True, 'align': 'left'})\n    \n    for idx, col in enumerate(df):  # loop through all columns\n        series = df[col]\n        worksheet.set_column(idx, idx, 25, wrap_format)  # wrap format\n    worksheet.freeze_panes(1, 0) # freeze top row\n    \n    writer.save()\n    \n    writer = xw.Book(xlsFilepath).sheets['Data']\n        \n    # Autofit column and row\n    writer.autofit()\n    \n    # change column width if defined by list\n    if len(xlColWidth) != 0:\n        for col in range(1, len(xlColWidth)+1):\n            ListCol = col - 1\n            if xlColWidth[ListCol] != '':\n                writer.range(1, col).column_width = xlColWidth[ListCol]\n\n    # Open the excel file\n    os.system(xlsFilepath)<\/pre>\n\n\n\n
Write your main code as a sample below,<\/p>\n\n\n\n
import pandas as pd\nimport xlwings as xw\nimport os\n\nexport_to_excel(['test', 5, 'need to check', 'check', 25, ''], ['Task', 'Qty', 'Remarks'], ['', '', ''])<\/code><\/pre>\n\n\n\n
The output of the code is,<\/p>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n

How does the function work?<\/mark><\/h5><\/div>\n\n\n\n
The export_to_excel<\/code> function is used to export data from a list of lists (ImpData<\/code>) to an Excel file. The function uses the pandas<\/code> and openpyxl<\/code> (xw) libraries to perform the export.<\/p>\n\n\n\n

The function takes three arguments:<\/p>\n\n\n\n
    \n
  1. The ImpData<\/code> is a list of lists, where each inner list represents a row of data.<\/li>\n\n\n\n
  2. The xlColName<\/code> is a list of strings, representing the names of the columns in the Excel file.<\/li>\n\n\n\n
  3. The xlColWidth<\/code> is a list of integers, representing the widths of the columns in the Excel file.<\/li>\n<\/ol>\n\n\n\n

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

    Next, the function uses the pandas<\/code> ExcelWriter<\/code> class to write the DataFrame to an Excel file named export_dataframe_23.xlsx<\/code>. 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.<\/p>\n\n\n\n

    The function then uses the openpyxl<\/code> 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<\/code>. The top row is also frozen to keep it visible while scrolling.<\/p>\n\n\n\n

    Finally, the function uses the os.system<\/code> function to open the Excel file.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Install the following modules if you haven’t installed them already: Ready to use Python function to export data in List format to an excel file: Write your main code as a sample below, The output of the code is, The export_to_excel function is used to export data from a list of lists (ImpData) to an […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_uag_custom_page_level_css":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[12,17],"tags":[19,20,14,13,16,15],"class_list":["post-405","post","type-post","status-publish","format-standard","hentry","category-python","category-useful-function","tag-excel","tag-export-data","tag-programming-language","tag-python","tag-python-code","tag-python-sample-code"],"aioseo_notices":[],"yoast_head":"\nExport List Data to an Excel file with Python ready-to-use function - tinyytopic.com<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Export List Data to an Excel file with Python ready-to-use function - tinyytopic.com\" \/>\n<meta property=\"og:description\" content=\"Install the following modules if you haven’t installed them already: Ready to use Python function to export data in List format to an excel file: Write your main code as a sample below, The output of the code is, The export_to_excel function is used to export data from a list of lists (ImpData) to an […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/\" \/>\n<meta property=\"og:site_name\" content=\"tinyytopic.com\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-13T09:43:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-13T09:42:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/tinyytopic.com\/wp-content\/uploads\/2023\/02\/excel-file.jpg\" \/>\n<meta name=\"author\" content=\"tinyytopic.com\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"tinyytopic.com\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/\",\"url\":\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/\",\"name\":\"Export List Data to an Excel file with Python ready-to-use function - tinyytopic.com\",\"isPartOf\":{\"@id\":\"https:\/\/tinyytopic.com\/#website\"},\"datePublished\":\"2023-02-13T09:43:01+00:00\",\"dateModified\":\"2023-02-13T09:42:34+00:00\",\"author\":{\"@id\":\"https:\/\/tinyytopic.com\/#\/schema\/person\/56c840cea8539fb221a03c5fa2ef32eb\"},\"breadcrumb\":{\"@id\":\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/tinyytopic.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Export List Data to an Excel file with Python ready-to-use function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/tinyytopic.com\/#website\",\"url\":\"https:\/\/tinyytopic.com\/\",\"name\":\"tinyytopic.com\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/tinyytopic.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/tinyytopic.com\/#\/schema\/person\/56c840cea8539fb221a03c5fa2ef32eb\",\"name\":\"tinyytopic.com\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/tinyytopic.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5f153681c8ca1e6d7287d858de51f968bb687221c89cf96d763ead4393881029?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5f153681c8ca1e6d7287d858de51f968bb687221c89cf96d763ead4393881029?s=96&d=mm&r=g\",\"caption\":\"tinyytopic.com\"},\"sameAs\":[\"http:\/\/tinyytopic.com\"],\"url\":\"https:\/\/tinyytopic.com\/index.php\/author\/mmkmuthukumar21gmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Export List Data to an Excel file with Python ready-to-use function - tinyytopic.com","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/","og_locale":"en_US","og_type":"article","og_title":"Export List Data to an Excel file with Python ready-to-use function - tinyytopic.com","og_description":"Install the following modules if you haven’t installed them already: Ready to use Python function to export data in List format to an excel file: Write your main code as a sample below, The output of the code is, The export_to_excel function is used to export data from a list of lists (ImpData) to an […]","og_url":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/","og_site_name":"tinyytopic.com","article_published_time":"2023-02-13T09:43:01+00:00","article_modified_time":"2023-02-13T09:42:34+00:00","og_image":[{"url":"https:\/\/tinyytopic.com\/wp-content\/uploads\/2023\/02\/excel-file.jpg"}],"author":"tinyytopic.com","twitter_card":"summary_large_image","twitter_misc":{"Written by":"tinyytopic.com","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/","url":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/","name":"Export List Data to an Excel file with Python ready-to-use function - tinyytopic.com","isPartOf":{"@id":"https:\/\/tinyytopic.com\/#website"},"datePublished":"2023-02-13T09:43:01+00:00","dateModified":"2023-02-13T09:42:34+00:00","author":{"@id":"https:\/\/tinyytopic.com\/#\/schema\/person\/56c840cea8539fb221a03c5fa2ef32eb"},"breadcrumb":{"@id":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/tinyytopic.com\/index.php\/2023\/02\/13\/export-list-data-to-an-excel-file-with-python-ready-to-use-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/tinyytopic.com\/"},{"@type":"ListItem","position":2,"name":"Export List Data to an Excel file with Python ready-to-use function"}]},{"@type":"WebSite","@id":"https:\/\/tinyytopic.com\/#website","url":"https:\/\/tinyytopic.com\/","name":"tinyytopic.com","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/tinyytopic.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/tinyytopic.com\/#\/schema\/person\/56c840cea8539fb221a03c5fa2ef32eb","name":"tinyytopic.com","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/tinyytopic.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5f153681c8ca1e6d7287d858de51f968bb687221c89cf96d763ead4393881029?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5f153681c8ca1e6d7287d858de51f968bb687221c89cf96d763ead4393881029?s=96&d=mm&r=g","caption":"tinyytopic.com"},"sameAs":["http:\/\/tinyytopic.com"],"url":"https:\/\/tinyytopic.com\/index.php\/author\/mmkmuthukumar21gmail-com\/"}]}},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"tinyytopic.com","author_link":"https:\/\/tinyytopic.com\/index.php\/author\/mmkmuthukumar21gmail-com\/"},"uagb_comment_info":86,"uagb_excerpt":"Install the following modules if you haven’t installed them already: Ready to use Python function to export data in List format to an excel file: Write your main code as a sample below, The output of the code is, The export_to_excel function is used to export data from a list of lists (ImpData) to an…","_links":{"self":[{"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/posts\/405","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/comments?post=405"}],"version-history":[{"count":11,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/posts\/405\/revisions"}],"predecessor-version":[{"id":421,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/posts\/405\/revisions\/421"}],"wp:attachment":[{"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/media?parent=405"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/categories?post=405"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tinyytopic.com\/index.php\/wp-json\/wp\/v2\/tags?post=405"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}