When it comes to organizing data, analyzing numerical and statistical data, or performing computations or complex formula we are using MS Excel. But, when you need an API that best imitates MS Excel, you are looking for Aspose.Cells API.

Aspose.Cells for Python via .NET is built on the .NET platform which provides robust spreadsheet manipulations. Aspose.Cells support the popular spreadsheet (XLS, XLSX, XLSM, XLSB, XLTX, XLTM, CSV, SpreadsheetML, ODS) file formats for everyday usage.

API also allows exporting Excel files to PDF, DOCX, PPTX, JSON, XPS, HTML, MHTML, JSON, Plain Text, and popular image formats including TIFF, JPG, PNG, BMP, and SVG. API also allows exporting Excel files to PDF, DOCX, PPTX, JSON, XPS, HTML, MHTML, JSON, Plain Text, and popular image formats including TIFF, JPG, PNG, BMP, and SVG.

Therefore, in this article, we will cover the following topics:

1. Creating Excel documents with Python
   • Creating an XLSX or XLS file in Python
   • Loading an existing Excel document using Python
   • Opening a different MS Excel version with Aspose.Cells for Python
2. Working with Smart Markers in MS Excel files
3. Styling in MS Excel with Aspose.Cells for Python

1. Creating Excel documents with Python

In order to start working with Aspose.Cells in Python, creating and manipulating documents, we need to install the latest Aspose.Cells for Python library. The library can be downloaded manually or installed using PyPI with the following pip command.

Loading an Aspose license

First of all, to start working with Aspose API we will have it apply for a license. There are several ways to achieve the same.

Load the license directly from the file:

• Create license object
• Specify the license path to the Stream object
• Close the Stream

from aspose.cells import License
#set license from a file
license = License()
license.set_license("C:\License.lic")

• Loading a license from a Stream
• Create a Stream object
• Pass the location of the license to a Stream

from aspose.cells import License
import io
#set license from a stream
try :
    lic = License()
    license_stream = io.FileIO("C:\License.lic")
    lic.set_license(license_stream)
    license_stream.close()
    print("License set successfully.")
except RuntimeError as err :
    # We do not ship any license with this example, visit the Aspose site to obtain either a temporary or permanent license. 
    print("\nThere was an error setting the license: {0}".format(err))

1.1. Creating an XLSX or XLS file in Python

If you have ever worked with Aspose API, you will have no problem understanding the concept of creating MS Excel files. But if you haven't, it won't be a problem because it's very simple.

The following steps for creating a simple MS Excel file are:

• Create an object of the Workbook class
• Save the object file

import aspose.cells as ac
workbook = ac.Workbook()
workbook.save("Output.xlsx")

Output:

1.2. Loading an existing Excel document using Python

In this section we will demonstrate how to open existing MS Excel files in Python providing a file's path, opening as a Stream, and opening a file with text only.

We create a Workbook object and open an Excel file using its file path

import aspose.cells as ac
dataDir = ""
# Opening through Path
# Creating a Workbook object and opening an Excel file using its file path
workbook = ac.Workbook(dataDir + "Input.xlsx")
print ("Workbook opened using path successfully!")

We create a Workbook object with the stream object

import aspose.cells as ac
dataDir = ""
# Opening through Path
# Creating a Workbook object and opening an Excel file using its file path
workbook = ac.Workbook(dataDir + "Input.xlsx")
print("Workbook opened using path successfully!")

We open a file with Data only

1.3. Opening a different MS Excel version with Aspose.Cells for Python

Since there are many versions of MS Excel file formats, Aspose.Cells take care of opening older versions of the files. We will demonstrate how we can open various versions as well as how we can open files encrypted with a password.

As well as with newer versions we can provide a file path or load a file within a Stream object

• Get the excel file into the stream
• Instantiate LoadOptions specified by the LoadFormat
• Create a Workbook object and open the file from the stream

import aspose.cells as ac
from aspose.pyio import BufferStream
# Create a Stream object
with open('Input.xlsx', 'rb') as f:
data = f.read()
databuff = BufferStream(data)
# Creating an Workbook object with the stream object
workbook = ac.Workbook(databuff)
databuff.close()
print("Workbook opened using stream successfully!")
# Opening a File with Data only
loadOptions = ac.LoadOptions(ac.LoadFormat.XLSX)
# Set LoadFilter property to load only data & cell formatting
loadOptions.load_filter = ac.LoadFilter(ac.LoadDataFilterOptions.CELL_DATA)
# Create a Workbook object and opening the file from its path
workbook = ac.Workbook(dataDir + "Input.xlsx", loadOptions)
print("File data imported successfully!")

Opening MS Excel 2007 XLSX files can be achieved by providing the file's path:

import aspose.cells as ac    
# The path to the documents directory
dataDir = ""    
# Opening Microsoft Excel 2007 Xlsx Files
loadOptions2 = ac.LoadOptions(ac.LoadFormat.XLSX)    
# Create a Workbook object and opening the file from its path
wbExcel07 = ac.Workbook(dataDir + "Input.xlsx", loadOptions2)
print("Microsoft Excel 2007 - Office365 workbook opened successfully!")

Opening MS Excel files that require a password can be achieved by following steps:

• Open an MS Excel file with the LoadOptions object
• Specifying the password for the document in the LoadOptions object
• Creating a Workbook object and opening the file from its path

import aspose.cells as ac
# The path to the documents directory.
dataDir = ""
# Opening Microsoft Excel 2007 Xlsx Files
loadOptions = ac.LoadOptions(ac.LoadFormat.XLSX)
# Specify the password
loadOptions.password = "test"
# Create a Workbook object and opening the file from its path
wbEncrypted = ac.Workbook(dataDir + "EncryptedExcel.xlsx", loadOptions)
print("Encrypted excel file opened successfully!")

2. Working with Smart Markers in MS Excel files

Smart Markers are used as placeholders for data and formulas that are inserted during a merge operation to Excel. A smart marker starts with &=variable. If the data marker results in more than one item, for example, a complete row, then the following rows are moved down automatically to make room for the new information.

• Instantiating a WorkbookDesigner object
• We have to input markers for specific cells we want to operate with
• Set the data source, process the markers
• Save the output Workbook

import aspose.cells as ac
# Instantiating a WorkbookDesigner object
designer = ac.WorkbookDesigner()
# Set a Workbook for the WorkbookDesigner
wb = ac.Workbook()
designer.workbook = wb
# Input some markers to the cells.
cell2 = wb.worksheets.get(0).cells.get(0, 1)
cell2.value = "&=$Variable2"
cell3 = wb.worksheets.get(0).cells.get(0, 2)
cell3.value = "&=&A{r}*B{r}"
# Set the data source, Process the markers.
designer.set_data_source("Variable", 10)
designer.process(0, True)
designer.set_data_source("Variable2", 20)
designer.process(0, True)
designer.set_data_source("Variable", "Total")
designer.process(0, True)
# Save the Output file to check.
wb.save("SmartMarkerOutput.xls")

Output:


Smart markers can contain simple data or complex formulas based on the author's needs. Smart markers consist of the data source and field name parts for most information. Special information may also be passed with variables and variable arrays. Variables always fill only one cell whereas variable arrays may fill several. Only use one data marker per cell. Unused smart markers are removed.

3. Styling in MS Excel with Aspose.Cells for Python

Aspose.Cells provide an excellent and robust range of various styling options for both, basic and advantage users.

In this section, we will demonstrate how to easily style specific cells in MS Excel using Aspose.Cells API.

• Create an object from a Workbook class
• Specify a sheet we want to perform operations on
• Get cells style
• Set font color
• Set pattern
• Set background
• Set border
• Set string value to cell „A1“
• Apply style to cell „A1“
• Save this workbook to „Style.xslx“ resultant file

import aspose.cells as ac
import aspose.pydrawing as ad
workbook = ac.Workbook()
worksheet=workbook.worksheets[0]
#get cells style
style=worksheet.cells.style
#set font color
style.font.color=ad.Color.green
#set pattern
style.pattern=ac.BackgroundType.GRAY12
#set Background
style.background_color = ad.Color.red
#set Border
style.set_border(ac.BorderType.LEFT_BORDER,ac.CellBorderType.THIN,ad.Color.blue)
style.set_border(ac.BorderType.RIGHT_BORDER,ac.CellBorderType.DOUBLE,ad.Color.gold)
#set string value to cell 'A1'
cells=worksheet.cells
cell=cells.get("A1")
cell.put_value("Text")
#apply style to cell 'A1'
cell.set_style(style)
#save this workbook to resultFile
workbook.save("Style.xlsx")

Output:



In this article, we learned how to create MS Excel files with Python. We have covered basic operations like adding licenses, creating new MS Excel files, opening files in different ways, and opening different versions of MS Excel files.

Also, we have performed basic operations on files using the WorkbookDesigner object to create simple styling for a specific cell.

In the future, we will cover more basic features like creating pivot tables, operations on cells like setting auto width/height features, encrypting and decrypting Excel files, operations on protecting unprotected Workbooks and Worksheets, and more.

Paid Support

If you have more questions related to our products or you are interested in how to implement certain functionalities within the product, our Paid Support is at your disposal. Paid support offers support on a priority basis, 24-hour guaranteed initial response time during weekdays.

Get a temporary license

You can get a temporary license in order to use Aspose.Words for Python without evaluation limitations.


#Reading an excel file using Python #How to Import an Excel File into Python #How to read an XLSX file in Python