3 Ways to Add Data in Excel Using Python

Ever wondered how to enhance your data handling tasks with Python? Excel is not just a spreadsheet tool; it's a powerhouse for data manipulation, especially when paired with Python's scripting capabilities. In this detailed guide, we'll explore three robust methods to add data in Excel using Python. Whether you're looking to automate repetitive tasks, manage large datasets, or simply boost your productivity, Python and Excel are the dynamic duo you need.

Method 1: Using openpyxl Library

openpyxl is a Python library designed specifically to read from and write to Excel 2010 xlsx/xlsm files. Here’s how you can use it:

• Installation: First, you need to install the library. Use pip for this purpose:

pip install openpyxl

• Creating and Opening Files:

from openpyxl import Workbook



wb = Workbook()



ws = wb.active



wb.save(“new_workbook.xlsx”)
    

• Writing Data: Adding data to cells is straightforward:

ws[‘A1’] = “Header1”
ws.cell(row=1, column=2, value=“Header2”)
wb.save(“new_workbook.xlsx”)
    

• Formatting Cells:

from openpyxl.styles import PatternFill, Font



ws[‘A1’].fill = PatternFill(start_color=‘FFFF00’, end_color=‘FFFF00’, fill_type=‘solid’)



ws[‘B1’].font = Font(color=‘FF0000’, bold=True)
    

🔍 Note: Ensure to save your workbook after any changes to reflect the modifications in the Excel file.

Method 2: Utilizing Pandas DataFrame

Pandas is another popular library that provides robust data structures and data analysis tools. Here’s how you can use Pandas to add data to Excel:

• Installation:

pip install pandas openpyxl

• Creating DataFrames:

import pandas as pd

data = {‘Name’: [‘John’, ‘Jane’, ‘Jack’], ‘Age’: [35, 28, 32]}
df = pd.DataFrame(data)
    

• Exporting DataFrame to Excel:

df.to_excel(“output.xlsx”, sheet_name=‘Sheet1’, index=False, engine=‘openpyxl’)
    

• Writing to Existing Excel File:

with pd.ExcelWriter(‘output.xlsx’, mode=‘a’) as writer:
    df.to_excel(writer, sheet_name=‘NewSheet’, index=False)
    

🚨 Note: When appending to an existing Excel file, make sure you have the write permissions.

Method 3: Using xlsxwriter

xlsxwriter provides an alternative way to write Excel files, particularly useful for its ability to manage workbook formatting and charts:

• Installation:

pip install xlsxwriter

• Writing to Excel:

import xlsxwriter



workbook = xlsxwriter.Workbook(‘new_workbook.xlsx’)
worksheet = workbook.add_worksheet()



worksheet.write(‘A1’, ‘Hello Excel’)
worksheet.write(‘A2’, ‘World’)



workbook.close()
    

• Formatting with xlsxwriter:

format1 = workbook.add_format()
format1.set_bold()
format1.set_font_color(‘red’)



worksheet.write(‘B1’, ‘Formatted Text’, format1)
    

🔧 Note: Remember, xlsxwriter can only create new Excel files, not edit existing ones.

By integrating Python into your Excel workflow, you can significantly enhance your data manipulation and automation capabilities. Each method discussed has its own strengths, tailored to different needs:

• openpyxl offers comprehensive access to Excel's functionality, from data entry to detailed cell formatting.
• Pandas simplifies data handling, especially with large datasets, providing an efficient way to write data to Excel files.
• xlsxwriter excels in creating formatted spreadsheets from scratch with an emphasis on workbook formatting and chart creation.

In summary, mastering these methods can transform how you interact with Excel, making data management more dynamic and less time-consuming. With Python, you can automate Excel tasks, ensuring accuracy, efficiency, and scalability in your data operations. Now, let's move to some frequently asked questions that might help you further refine your skills in Python and Excel integration:

Can Python replace Excel for data analysis?

+

While Python offers powerful tools for data analysis like Pandas and NumPy, Excel remains valuable for its ease of use and visual interface. Python can automate Excel tasks or handle large datasets beyond Excel’s capabilities, but many still use Excel for initial data entry or quick analysis due to its simplicity and immediate visual feedback.

Which Python library is best for reading Excel files?

+

For reading Excel files, openpyxl or Pandas are commonly used. Pandas leverages openpyxl for its Excel capabilities, providing a more Pythonic data handling experience. If you’re looking at Excel spreadsheets for their structure, openpyxl might be more suited due to its direct Excel manipulations.

How do I handle large Excel files efficiently?

+

For large files, consider using Pandas which can read Excel files in chunks or use memory-efficient data types. Also, ensure your system has adequate RAM. If processing power is the issue, you might want to consider data sampling or using databases for preprocessing before Excel analysis.