Import and Export Excel Data using Pandas and Django Rest Framework

When working with data in a web application, it is often necessary to import and export data in various formats, including Excel. In this article, we will explore how to import and export Excel data using Pandas and Django Rest Framework.

Importing Excel Data

To import Excel data into a Django Rest Framework API, we can use Pandas to read the Excel file and convert it into a DataFrame. Once we have the data in a DataFrame, we can then use Django Rest Framework serializers to convert the data into JSON and save it to the database.

Here is an example of how we can import Excel data using Pandas and Django Rest Framework:

“`python
import pandas as pd
from rest_framework.views import APIView
from rest_framework.response import Response
from .models import MyModel
from .serializers import MyModelSerializer

class ExcelImportView(APIView):
def post(self, request, *args, **kwargs):
file = request.FILES[‘file’]
df = pd.read_excel(file)

# Convert DataFrame to list of dictionaries
data = df.to_dict(orient=’records’)

# Serialize and save data to the database
serializer = MyModelSerializer(data=data, many=True)
if serializer.is_valid():
serializer.save()
return Response({‘message’: ‘Data imported successfully’}, status=201)
else:
return Response(serializer.errors, status=400)
“`

Exporting Excel Data

To export Excel data from a Django Rest Framework API, we can use Pandas to convert the data from the database into a DataFrame, and then write it to an Excel file. We can then return the Excel file as a download link to the user.

Here is an example of how we can export Excel data using Pandas and Django Rest Framework:

“`python
import pandas as pd
from rest_framework.views import APIView
from rest_framework.response import Response
from .models import MyModel
from .serializers import MyModelSerializer

class ExcelExportView(APIView):
def get(self, request, *args, **kwargs):
queryset = MyModel.objects.all()
serializer = MyModelSerializer(queryset, many=True)

# Convert data to DataFrame
df = pd.DataFrame(serializer.data)

# Write DataFrame to Excel file
filename = ‘exported_data.xlsx’
df.to_excel(filename, index=False)

# Return download link to user
return Response({‘excel_file’: filename})
“`

Conclusion

In this article, we have shown how to import and export Excel data using Pandas and Django Rest Framework. By combining the data manipulation capabilities of Pandas with the web API capabilities of Django Rest Framework, we can easily import and export Excel data in a web application.