Why Aspose.Cells for Python via NET
Possible Usage Scenarios
When you need to compare which product is best for your solution, there are many criteria to evaluate, but the main focus will often be the functionality and effort required to use the product. If you are looking for a faster, simpler, and lighter file format library to process files, then you might want to compare Aspose.Cells for Python via NET and other excel python libraries. Then you will be able to notice that in fact these products do not compete, but solve slightly different user tasks.
By comparing the three strongest Python libraries (pandas, xlwings, and Aspose.Cells for Python via NET) for reading data from Excel file, writing data to Excel file and adding chart to Excel file. You can discover the ease of use, high performance, and other unique advantages of the Aspose.Cells for Python via NET library.
- Read data from Excel file using xlwings, pandas and Aspose.Cells python excel library
- Write data to Excel file using xlwings, pandas and Aspose.Cells python excel library
- Add chart to Excel file using xlwings, pandas and Aspose.Cells python excel library
Comparison of Multiple Excel Libraries in Python
Let’s first take a look at the comparison of ten libraries in Python that can operate Excel files.
Why Aspose.Cells for Python via NET
Aspose.Cells for Python is a powerful, easy to use, efficient, and secure library for all kinds of scenarios where you need to work with Excel files. There are many reasons to use Aspose.Cells for Python, Including but not limited to the following points:
Full featured
Aspose.Cells is a powerful library that provides a wide range of capabilities to handle Excel files, including reading, writing, editing, formatting, computing, and more.
Ease of use
Aspose.Cells' API is designed to be intuitive and easy to use, enabling Python developers to easily integrate Excel functionality into their applications.
Cross-platform support
Aspose.Cells supports a variety of operating systems, including Windows, Linux, and macOS, thus ensuring stable operation in a variety of environments.
High performance
Aspose.Cells performs well when handling large Excel files and is able to load and save data quickly, thereby improving the performance of your application.
Security
Aspose.Cells provides data protection and encryption to ensure the security of Excel files against unauthorized access and modification.
Multiple file formats
Aspose.Cells supports a variety of Excel file formats, including XLS, XLSX, CSV, ODS, etc., for easy interaction with data from different sources.
Good technical support
Aspose.Cells provides comprehensive documentation and sample code to help developers get started quickly. At the same time, we also provide professional technical support to solve the problems encountered in the process of use.
The advantages of Aspose.Cells for Python via NET
Aspose.Cells for Python is a fully functional, easy to use, excellent performance, secure, reliable, flexible and highly integrated library. Whether working with small or large Excel files, data analysis, report generation, or other Excel operations, Aspose.Cells provides developers with an efficient and convenient solution. Aspose.Cells for Python has the following advantages:
Flexible APIs
Aspose.Cells' API offers a wealth of features that can be customized and extended to suit different needs. This allows developers to easily implement their own business requirements without relying on other tools or libraries.
Support for multiple programming languages
In addition to Python, Aspose.Cells also supports Java, C#, C++ and other programming languages. This means that developers can choose the most suitable programming language to implement Excel features based on their preferences and skills.
Highly integrated
Aspose.Cells can be easily integrated with other Python libraries and frameworks, such as Django, Flask, etc. This allows developers to seamlessly integrate Excel functionality into their Web applications or desktop applications, increasing the utility and convenience of their applications.
Read data from Excel File
Let’s start from practical applications and compare the three strongest Python libraries (pandas, xlwings, and Aspose.Cells for Python via NET) for reading data from sample file.
Read data from Excel File Using Aspose.Cells for Python via NET
import aspose.cells | |
from aspose.cells import Workbook | |
# Open the Excel workbook | |
book = Workbook("sample_data.xlsx") | |
# Get "Sheet1" worksheet | |
sheet1 = book.worksheets.get('Sheet1') | |
# Read b2 data from "Sheet1" worksheet | |
cell_B2_Sheet1 = sheet1.cells.get("B2") | |
# Get "Sheet2" worksheet | |
sheet2 = book.worksheets.get('Sheet2') | |
# Read b2 data from "Sheet2" worksheet | |
cell_B2_Sheet2 = sheet2.cells.get("B2") | |
# Print the read data | |
print("Data from B2 in Sheet1:", cell_B2_Sheet1.value) | |
print("Data from B2 in Sheet2:", cell_B2_Sheet2.value) |
Read data from Excel File Using xlwings
import xlwings as xw | |
# Open the Excel workbook | |
wb = xw.Book('sample_data.xlsx') | |
# Get "Sheet1" worksheet | |
sheet1 = wb.sheets['Sheet1'] | |
# Read b2 data from "Sheet1" worksheet | |
cell_B2_Sheet1 = sheet1.range('B2') | |
# Get "Sheet2" worksheet | |
sheet2 = wb.sheets['Sheet2'] | |
# Read b2 data from "Sheet2" worksheet | |
cell_B2_Sheet2 = sheet2.range('B2') | |
# Print the read data | |
print("Data from B2 in Sheet1:", cell_B2_Sheet1.value) | |
print("Data from B2 in Sheet2:", cell_B2_Sheet2.value) | |
# Close the workbook | |
wb.close() |
Read data from Excel File Using pandas
import pandas as pd | |
# Replace 'sample_data.xlsx' with the path to your Excel file | |
# Replace 'Sheet1' with the name of the sheet if it's different | |
df = pd.read_excel('sample_data.xlsx', sheet_name='Sheet1', header=None) | |
# Accessing the data from B2 in "Sheet1" | |
cell_B2_Sheet1 = df.iloc[1, 1] | |
df2 = pd.read_excel('sample_data.xlsx', sheet_name='Sheet2', header=None) | |
# Accessing the data from B2 in "Sheet1" | |
cell_B2_Sheet2 = df2.iloc[1, 1] | |
print("Data from B2 in Sheet1:", cell_B2_Sheet1) | |
print("Data from B2 in Sheet2:", cell_B2_Sheet2) |
Write data to Excel File
Let’s start from practical applications and compare the three strongest Python libraries (pandas, xlwings, and Aspose.Cells for Python via NET) for writing data to Excel file.
Write data to Excel File Using Aspose.Cells for Python via NET
import aspose.cells | |
from aspose.cells import Workbook | |
# Open a new workbook | |
book = Workbook() | |
# Add "Fruits" worksheet | |
sheet1 = book.worksheets.add('Fruits') | |
# Add "Vegetables" worksheet | |
sheet2 = book.worksheets.add('Vegetables') | |
# Set output file name | |
file_name = 'output.xlsx' | |
# write data to "Fruits" worksheet | |
sheet1.cells.get('A1').value = 'Fruits' | |
sheet1.cells.get('A2').value = 'Appple' | |
sheet1.cells.get('A3').value = 'Banana' | |
sheet1.cells.get('A4').value = 'Mango' | |
sheet1.cells.get('B1').value = 'Sales in kg' | |
sheet1.cells.get('B2').value = 20 | |
sheet1.cells.get('B3').value = 30 | |
sheet1.cells.get('B4').value = 15 | |
# write data to "Vegetables" worksheet | |
sheet2.cells.get('A1').value = 'Vegetables' | |
sheet2.cells.get('A2').value = 'tomato' | |
sheet2.cells.get('A3').value = 'Onion' | |
sheet2.cells.get('A4').value = 'ladies finger' | |
sheet2.cells.get('B1').value = 'Sales in kg' | |
sheet2.cells.get('B2').value = 200 | |
sheet2.cells.get('B3').value = 310 | |
sheet2.cells.get('B4').value = 115 | |
book.save(file_name) |
Write data to Excel File Using xlwings
import xlwings as xw | |
app=xw.App(visible=True,add_book=False) | |
wb=app.books.add() | |
# add worksheet named "Fruits" | |
wb.sheets.add(name='Fruits') | |
sheet1 = wb.sheets['Fruits'] | |
# add worksheet named "Vegetables" | |
wb.sheets.add(name='Vegetables') | |
sheet2 = wb.sheets['Vegetables'] | |
# Set output file name | |
file_name = 'output.xlsx' | |
try: | |
# write data to "Fruits" worksheet | |
sheet1.range('A1').value = 'Fruits' | |
sheet1.range('A2').value = 'Appple' | |
sheet1.range('A3').value = 'Banana' | |
sheet1.range('A4').value = 'Mango' | |
sheet1.range('B1').value = 'Sales in kg' | |
sheet1.range('B2').value = 20 | |
sheet1.range('B3').value = 30 | |
sheet1.range('B4').value = 15 | |
# write data to "Vegetables" worksheet | |
sheet2.range('A1').value = 'Vegetables' | |
sheet2.range('A2').value = 'tomato' | |
sheet2.range('A3').value = 'Onion' | |
sheet2.range('A4').value = 'ladies finger' | |
sheet2.range('B1').value = 'Sales in kg' | |
sheet2.range('B2').value = 200 | |
sheet2.range('B3').value = 310 | |
sheet2.range('B4').value = 115 | |
wb.save(file_name) | |
wb.close() | |
app.quit() | |
except Exception: | |
wb.close() | |
app.quit() |
Write data to Excel File Using pandas
# import the python pandas package | |
import pandas as pd | |
# create data_frame1 by creating a dictionary | |
# in which values are stored as list | |
data_frame1 = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango'], 'Sales in kg': [20, 30, 15]}) | |
# create data_frame2 by creating a dictionary | |
# in which values are stored as list | |
data_frame2 = pd.DataFrame({'Vegetables': ['tomato', 'Onion', 'ladies finger'], 'Sales in kg': [200, 310, 115]}) | |
# Set output file name | |
file_name = 'output.xlsx' | |
# create a excel writer object | |
with pd.ExcelWriter(file_name) as writer: | |
# use to_excel function and specify the sheet_name and index | |
# to store the dataframe in specified sheet | |
data_frame1.to_excel(writer, sheet_name="Fruits", index=False) | |
data_frame2.to_excel(writer, sheet_name="Vegetables", index=False) |
Add chart to Excel File
Let’s start from practical applications and compare the three strongest Python libraries (pandas, xlwings, and Aspose.Cells for Python via NET) for adding chart to Excel file.
Add chart to Excel File Using Aspose.Cells for Python via NET
import aspose.cells | |
import aspose.cells.charts | |
from aspose.cells import Workbook | |
from aspose.cells.charts import ChartType | |
# Open a new workbook | |
book = Workbook() | |
# Add "DataSheet" worksheet | |
sheet1 = book.worksheets.add('DataSheet') | |
# Set output file name | |
file_name = 'output.xlsx' | |
# write data to "DataSheet" worksheet | |
sheet1.cells.get('A1').value = 'Category' | |
sheet1.cells.get('A2').value = 'A' | |
sheet1.cells.get('A3').value = 'B' | |
sheet1.cells.get('A4').value = 'C' | |
sheet1.cells.get('A5').value = 'D' | |
sheet1.cells.get('A6').value = 'E' | |
sheet1.cells.get('B1').value = 'Value' | |
sheet1.cells.get('B2').value = 10 | |
sheet1.cells.get('B3').value = 20 | |
sheet1.cells.get('B4').value = 30 | |
sheet1.cells.get('B5').value = 20 | |
sheet1.cells.get('B6').value = 15 | |
# Adding a chart to the worksheet | |
chartIndex = sheet1.charts.add(ChartType.COLUMN, 6, 0, 20, 7) | |
# Accessing the instance of the newly added chart | |
chart = sheet1.charts.get(chartIndex) | |
#Setting chart data source as the range "DataSheet!A1:B6" | |
chart.set_chart_data_range('DataSheet!A1:B6', True) | |
chart.title.text = 'Sample Chart' | |
book.save(file_name) |
Add chart to Excel File Using xlwings
import xlwings as xw | |
app=xw.App(visible=True,add_book=False) | |
wb=app.books.add() | |
# add worksheet named "DataSheet" | |
wb.sheets.add(name='DataSheet') | |
sheet1 = wb.sheets['DataSheet'] | |
# Set output file name | |
file_name = 'output.xlsx' | |
try: | |
# write data to "DataSheet" worksheet | |
sheet1.range('A1').value = 'Category' | |
sheet1.range('A2').value = 'A' | |
sheet1.range('A3').value = 'B' | |
sheet1.range('A4').value = 'C' | |
sheet1.range('A5').value = 'D' | |
sheet1.range('A6').value = 'E' | |
sheet1.range('B1').value = 'Value' | |
sheet1.range('B2').value = 10 | |
sheet1.range('B3').value = 20 | |
sheet1.range('B4').value = 30 | |
sheet1.range('B5').value = 20 | |
sheet1.range('B6').value = 15 | |
# add a chart | |
chart = sheet1.charts.add(150,50) | |
# set data source for chart | |
chart.set_source_data(sheet1.range('A1').expand()) | |
# set chart type | |
chart.chart_type = 'column_clustered' | |
# set title name | |
chart.api[1].ChartTitle.Text = 'Sample Chart' | |
chart.api[1].Axes(1).HasTitle = True | |
chart.api[1].Axes(2).HasTitle = True | |
chart.api[1].Axes(1).AxisTitle.Text = 'Category' | |
chart.api[1].Axes(2).AxisTitle.Text = 'Value' | |
wb.save(file_name) | |
wb.close() | |
app.quit() | |
app.kill() | |
except Exception: | |
wb.close() | |
app.quit() | |
app.kill() |
Add chart to Excel File Using pandas
In Pandas, you can use the ExcelWriter object and the to_excel() function to add charts to an Excel file. However, please note that Pandas itself does not support embedding charts directly into Excel files, it can only write data into Excel files. To add a chart, you need to use the openpyxl or xlsxwriter library to manipulate Excel files. Here is an example of using the xlsxwriter library to add a chart to an Excel file.
import pandas as pd | |
# create some data | |
data = {'Category': ['A', 'B', 'C', 'D', 'E'], | |
'Value': [10, 20, 30, 20, 15]} | |
df = pd.DataFrame(data) | |
# Set output file name | |
file_name = 'output.xlsx' | |
# write data to excel file | |
with pd.ExcelWriter(file_name, engine='xlsxwriter') as writer: | |
df.to_excel(writer, index=False, sheet_name='DataSheet') | |
# use xlsxwriter to create chart | |
workbook = writer.book | |
worksheet = writer.sheets['DataSheet'] | |
chart = workbook.add_chart({'type': 'column'}) | |
# set data for chart | |
chart.add_series({ | |
'categories': '=DataSheet!$A$1:$A$5', | |
'values': '=DataSheet!$B$1:$B$5', | |
}) | |
# set title for chart | |
chart.set_title({'name': 'Sample Chart'}) | |
# add chart to excel file | |
worksheet.insert_chart('A7', chart) |