Convert Excel to NumPy

Introduction to NumPy

NumPy (Numerical Python) is an open-source numerical computation extension of Python. This tool can be used to store and process large matrices, which is much more efficient than Python’s nested list structure (which can also be used to represent matrices). It supports a large number of dimensional arrays and matrix operations, and also provides a large number of mathematical function libraries for array operations.

The main functions of NumPy:

  1. Ndarray, a multidimensional array object, is a fast, flexible, and space saving data structure.
  2. Linear algebra operations, including matrix multiplication, transposition, inversion, etc.
  3. Fourier transform, performing a fast Fourier transform on an array.
  4. Fast operation of floating-point arrays.
  5. Integrate C language code into Python to make it run faster.

Using Aspose.Cells for Python via .NET API, you can convert Excel, TSV, CSV, Json and many different formats to Numpy ndarray.

How to Convert Excel Workbook to NumPy ndarray

Here’s an example code snippet to demonstrate how to export excel data to a NumPy array using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Traverse excel data and export data to NumPy ndarray using Aspose.Cells for Python via .NET.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# workbook to ndarray
excel_ndarray = np.array([], dtype= object)
sheet_count = book.worksheets.capacity - 1
excel_list = []
for sheet_index in range(0, sheet_count):
sheet_list =[]
sheet = book.worksheets.get(sheet_index)
cells = sheet.cells
rows = cells.rows
max_column_index = cells.max_column + 1
row_count = rows.count
index = -1
for row_index in range(0, row_count):
row = rows.get_row_by_index(row_index)
if row_index != row.index:
for blank_row_index in range(index+1, row.index):
blank_row =[]
for blank_column_index in range(0,max_column_index):
blank_row.append("")
sheet_list.append(blank_row)
data_row =[]
for column_index in range(0,max_column_index):
curr_cell = cells.check_cell(row.index, column_index)
if curr_cell:
data_row.append(curr_cell.value)
else:
data_row.append("")
sheet_list.append(data_row)
index = row.index
excel_list.append(sheet_list)
excel_ndarray = np.asarray(excel_list)
print(excel_ndarray)

The output result:

[[['City' 'Region' 'Store']    
  ['Chicago' 'Central' '3055'] 
  ['New York' 'East' '3036']   
  ['Detroit' 'Central' '3074']]

 [['City2' 'Region2' 'Store3']
  ['Seattle' 'West' '3000']
  ['philadelph' 'East' '3082']
  ['Detroit' 'Central' '3074']]

 [['City3' 'Region3' 'Store3']
  ['Seattle' 'West' '3166']
  ['New York' 'East' '3090']
  ['Chicago' 'Central' '3055']]]

How to Convert Worksheet to NumPy ndarray

Here’s an example code snippet to demonstrate how to export worksheet data to Numpy ndarray using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Get the first worksheet.
  3. Convert worksheet data to Numpy ndarray using Aspose.Cells for Python Excel library.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# Get the first worksheet
sheet1 = book.worksheets.get(0)
cells = sheet1.cells
rows = cells.rows
max_column_index = sheet1.cells.max_column + 1
# worksheet to ndarray
worksheet_list =[]
row_count = rows.count
index = -1
for row_index in range(0, row_count):
row = rows.get_row_by_index(row_index)
if row_index != row.index:
for blank_row_index in range(index+1, row.index):
blank_row =[]
for blank_column_index in range(0,max_column_index):
blank_row.append("")
worksheet_list.append(blank_row)
data_row =[]
for column_index in range(0,max_column_index):
curr_cell = cells.check_cell(row.index, column_index)
if curr_cell:
data_row.append(curr_cell.value)
else:
data_row.append("")
worksheet_list.append(data_row)
index = row.index
worksheet_ndarray = np.asarray(worksheet_list)
print(worksheet_ndarray)

The output result:

[['City' 'Region' 'Store']    
 ['Chicago' 'Central' '3055'] 
 ['New York' 'East' '3036']   
 ['Detroit' 'Central' '3074']]

How to Convert a Range of Excel to NumPy ndarray

Here’s an example code snippet to demonstrate how to export range data to NumPy ndarray using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Get the first worksheet.
  3. Create the Range.
  4. Convert Range data to NumPy ndarray using Aspose.Cells for Python Excel library.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# Get the first worksheet
sheet1 = book.worksheets.get(0)
# range to ndarray
cells = sheet1.cells
range_obj = cells.create_range("B1", "C3")
range_list =[]
for row_index in range(range_obj.first_row , range_obj.first_row + range_obj.row_count ):
row =[]
for column_index in range(range_obj.first_column, range_obj.first_column + range_obj.column_count):
curr_cell = cells.check_cell(row_index, column_index)
if curr_cell:
row.append(curr_cell.value)
else:
row.append("")
range_list.append(row)
range_ndarray = np.asarray(range_list)
print(range_ndarray)

The output result:

[['Region' 'Store']
 ['Central' '3055']
 ['East' '3036']]

How to Convert a ListObject of Excel to NumPy ndarray

Here’s an example code snippet to demonstrate how to export ListObject data to NumPy ndarray using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Get the first worksheet.
  3. Create ListObject object.
  4. Convert ListObject data to NumPy ndarray using Aspose.Cells for Python Excel library.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# Get the first worksheet
sheet1 = book.worksheets.get(0)
cells = sheet1.cells
# listobject to ndarray
table_index = sheet1.list_objects.add("A1", "C4", True)
table = sheet1.list_objects[table_index]
table_list =[]
for row_index in range(table.start_row , table.end_row + 1):
row =[]
for column_index in range(table.start_column, table.end_column + 1):
curr_cell = cells.check_cell(row_index, column_index)
if curr_cell:
row.append(curr_cell.value)
else:
row.append("")
table_list.append(row)
table_ndarray = np.asarray(table_list)
print(table_ndarray)

The output result:

[['City' 'Region' 'Store']
 ['Chicago' 'Central' '3055']
 ['New York' 'East' '3036']
 ['Detroit' 'Central' '3074']]

How to Convert a Row of Excel to NumPy ndarray

Here’s an example code snippet to demonstrate how to export Row data to NumPy ndarray using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Get the first worksheet.
  3. Get Row object by row index.
  4. Convert Row data to NumPy ndarray using Aspose.Cells for Python Excel library.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# Get the first worksheet
sheet1 = book.worksheets.get(0)
cells = sheet1.cells
max_column_index = cells.max_column + 1
# row to ndarray
row_index = cells.max_data_row
row_list = []
for column_index in range(0,max_column_index):
curr_cell = cells.check_cell(row_index, column_index)
if curr_cell:
row_list.append(curr_cell.value)
else:
row_list.append("")
row_ndarray = np.asarray(row_list)
print(row_ndarray)

The output result:

['Detroit' 'Central' '3074']

How to Convert a Column of Excel to NumPy ndarray

Here’s an example code snippet to demonstrate how to export Column data to NumPy ndarray using Aspose.Cells for Python via .NET:

  1. Load the sample file.
  2. Get the first worksheet.
  3. Get Column object by column index.
  4. Convert Column data to NumPy ndarray using Aspose.Cells for Python Excel library.
import numpy as np
import aspose.cells
from aspose.cells import Workbook, Worksheet, Range
# Open the Excel workbook
book = Workbook("sample_data.xlsx")
# Get the first worksheet
sheet1 = book.worksheets.get(0)
cells = sheet1.cells
max_row_index = cells.max_row + 1
# column to ndarray
column_index = cells.max_data_column
column_list = []
for row_index in range(0,max_row_index):
curr_cell = sheet1.cells.check_cell(row_index, column_index)
if curr_cell:
column_list.append(curr_cell.value)
else:
column_list.append("")
column_ndarray = np.asarray(column_list)
print(column_ndarray)

The output result:

['Store' '3055' '3036' '3074']