Work with In Memory Spreadsheets Python pyexcel

In-memory spreadsheets are powerful tools. They let you work with data without files. Python's pyexcel makes this easy.

You can manipulate data directly in memory. This saves time and disk space. It is perfect for web applications and data processing.

What are In-Memory Spreadsheets?

In-memory spreadsheets exist only in RAM. They are not saved as files. This makes data operations very fast.

You can create, edit, and process data quickly. No need to wait for file I/O operations. Everything happens in your computer's memory.

Pyexcel provides simple interfaces for this. You can work with data as lists or dictionaries. The library handles the complex parts.

Install pyexcel

First, you need to install pyexcel. Use pip for installation.

pip install pyexcel

You might also want pyexcel-xls or pyexcel-xlsx. These handle specific file formats.

pip install pyexcel-xls pyexcel-xlsx

Create In-Memory Spreadsheets

Let's start by creating a simple spreadsheet. We'll use a list of lists.

import pyexcel as pe

# Create data as list of lists
data = [
    ["Name", "Age", "City"],
    ["John", 30, "New York"],
    ["Alice", 25, "London"],
    ["Bob", 35, "Tokyo"]
]

# Create in-memory spreadsheet
sheet = pe.Sheet(data)
print(sheet)

pyexcel sheet:
+-------+-----+---------+
| Name  | Age | City    |
+-------+-----+---------+
| John  | 30  | New York|
+-------+-----+---------+
| Alice | 25  | London  |
+-------+-----+---------+
| Bob   | 35  | Tokyo   |
+-------+-----+---------+

The pe.Sheet function creates a spreadsheet object. It holds your data in memory. You can access and modify it easily.

Access and Modify Data

You can read and change data in your spreadsheet. Use row and column indices.

# Access specific cell
print(sheet[1, 1])  # Row 1, Column 1

# Modify a cell
sheet[1, 1] = 31
print(sheet[1, 1])

# Add a new row
sheet.row += ["Charlie", 28, "Paris"]
print(sheet)

30
31
pyexcel sheet:
+---------+-----+---------+
| Name    | Age | City    |
+---------+-----+---------+
| John    | 31  | New York|
+---------+-----+---------+
| Alice   | 25  | London  |
+---------+-----+---------+
| Bob     | 35  | Tokyo   |
+---------+-----+---------+
| Charlie | 28  | Paris   |
+---------+-----+---------+

The sheet.row += method adds new rows. You can also remove rows or columns. This makes data manipulation flexible.

Convert Between Formats

Pyexcel can convert in-memory data to various formats. You can export to JSON, CSV, or dictionaries.

# Convert to dictionary
dict_data = sheet.to_dict()
print("Dictionary:", dict_data)

# Convert to records (list of dictionaries)
records = sheet.to_records()
print("Records:", records)

# Convert to JSON string
json_data = sheet.json
print("JSON:", json_data[:100] + "...")

Dictionary: {'Name': ['John', 'Alice', 'Bob', 'Charlie'], 'Age': [31, 25, 35, 28], 'City': ['New York', 'London', 'Tokyo', 'Paris']}
Records: [{'Name': 'John', 'Age': 31, 'City': 'New York'}, {'Name': 'Alice', 'Age': 25, 'City': 'London'}, {'Name': 'Bob', 'Age': 35, 'City': 'Tokyo'}, {'Name': 'Charlie', 'Age': 28, 'City': 'Paris'}]
JSON: [{"Name": "John", "Age": 31, "City": "New York"}, {"Name": "Alice", "Age": 25, "City": "London"}, {"Name": "Bob",...

The to_dict method converts your data to a dictionary. to_records creates a list of dictionaries. json property gives you JSON string.

Filter and Search Data

You can filter rows based on conditions. This is useful for data analysis.

# Filter rows where Age > 28
filtered_sheet = sheet.filter(lambda row: row[1] > 28)
print("Filtered (Age > 28):")
print(filtered_sheet)

Filtered (Age > 28):
pyexcel sheet:
+-------+-----+---------+
| Name  | Age | City    |
+-------+-----+---------+
| John  | 31  | New York|
+-------+-----+---------+
| Bob   | 35  | Tokyo   |
+-------+-----+---------+

The filter method applies a function to each row. It keeps rows where the function returns True. For more advanced filtering, check our guide on Filter Search Spreadsheet Rows Python pyexcel.

Working with Multiple Sheets

You can create workbooks with multiple sheets. This organizes related data together.

# Create another dataset
sales_data = [
    ["Product", "Q1", "Q2"],
    ["Laptop", 150, 200],
    ["Phone", 300, 250],
    ["Tablet", 100, 150]
]

# Create a workbook with multiple sheets
book = pe.Book({"Employees": data, "Sales": sales_data})

# Access specific sheet
employees_sheet = book["Employees"]
sales_sheet = book["Sales"]

print("Employees sheet:")
print(employees_sheet)
print("\nSales sheet:")
print(sales_sheet)

Employees sheet:
pyexcel sheet:
+---------+-----+---------+
| Name    | Age | City    |
+---------+-----+---------+
| John    | 31  | New York|
+---------+-----+---------+
| Alice   | 25  | London  |
+---------+-----+---------+
| Bob     | 35  | Tokyo   |
+---------+-----+---------+
| Charlie | 28  | Paris   |
+---------+-----+---------+

Sales sheet:
pyexcel sheet:
+---------+----+----+
| Product | Q1 | Q2 |
+---------+----+----+
| Laptop  |150 |200 |
+---------+----+----+
| Phone   |300 |250 |
+---------+----+----+
| Tablet  |100 |150 |
+---------+----+----+

The pe.Book function creates a multi-sheet workbook. You can access sheets by name. This is great for complex data structures.

Export to Various Formats

You can export your in-memory data to different formats. This includes CSV, Excel, and JSON.

# Export to different formats without saving files
csv_content = sheet.csv
json_content = sheet.json
html_content = sheet.html

print("CSV format:")
print(csv_content)
print("\nHTML format (first 200 chars):")
print(html_content[:200] + "...")

CSV format:
Name,Age,City
John,31,New York
Alice,25,London
Bob,35,Tokyo
Charlie,28,Paris

HTML format (first 200 chars):

...
Name
Age
City
John
31
New York
Alice
25
London

The csv property gives you CSV string. json provides JSON. html creates an HTML table. For more conversion options, see Python pyexcel Guide: Convert CSV XLSX JSON.

Real-World Use Cases

In-memory spreadsheets are useful in many scenarios. Web applications often use them.

You can process uploaded files without saving them. This improves security and performance. Data validation happens in memory.

APIs can generate Excel reports on the fly. No temporary files are created. Responses are faster and more efficient.

Data pipelines benefit from in-memory processing. You can transform data between steps. For batch operations, check Batch Process Excel Files with Python pyexcel.

Performance Benefits

In-memory operations are much faster than file operations. RAM access is quicker than disk I/O.

This speed matters for large datasets. Processing thousands of rows becomes efficient. Your applications respond faster.

Memory usage is optimized. Pyexcel handles data efficiently. You can work with substantial datasets.

Best Practices

Always validate your data before processing. Check for missing values or incorrect types.

Use appropriate data structures. Lists for simple data, dictionaries for structured data.

Handle exceptions properly. Memory errors can occur with very large datasets.

Clean up resources when done. Although automatic, explicit cleanup helps.

Conclusion

In-memory spreadsheets with pyexcel are powerful. They offer speed and flexibility for data tasks.

You can create, modify, and convert data easily. No files are needed for temporary processing.

This approach is ideal for web applications and data pipelines. It improves performance and simplifies code.

Start using pyexcel for your in-memory spreadsheet needs. You will appreciate the efficiency gains.