How to read Excel files from a stream (not a disk-backed file) in Python?

Question

XLRD is installed and tested:

>>> import xlrd
>>> workbook = xlrd.open_workbook('Sample.xls')

When I read the file through html form like below, I'm able to access all the values.

  xls_file = request.params['xls_file']
  print xls_file.filename, xls_file.type

I'm using Pylons module, request comes from: from pylons import request, tmpl_context as c

My questions:

1. Is xls_file read through requst.params an object?
2. How can I read xls_file and make it work with xlrd?

Update:

The xls_file is uploaded on web server, but the xlrd library expects a filename instead of an open file object, How can I make the uploaded file to work with xlrd? (Thanks to Martijn Pieters, I was being unable to formulate the question clearly.)

Answer 1

xlrd does support providing data directly without a filepath, just use the file_contents argument:

xlrd.open_workbook(file_contents=fileobj.read())

From the documentation:

file_contents – A string or an mmap.mmap object or some other behave-alike object. If file_contents is supplied, filename will not be used, except (possibly) in messages.

Answer 2

What I met is not totally the same with the question, but I think maybe it is similar and I can give some hints.

I am using django rest framework's request instead of pylons request.

If I write simple codes like following:

@api_view(['POST'])
@renderer_classes([JSONRenderer])
def upload_files(request):
    file_obj = request.FILES['file']
    from xlrd import open_workbook
    wb = open_workbook(file_contents=file_obj.read())
    result = {"code": "0", "message": "success", "data": {}}
    return Response(status=200, data=result)

Here We can read using open_workbook(file_contents=file_obj.read()) as mentioned in previous comments.

But if you write code in following way:

from rest_framework.views import APIView
from rest_framework.parsers import MultiPartParser
class FileUploadView(APIView):
    parser_classes = (MultiPartParser,)

    def put(self, request, filename, format=None):
        file_obj = request.FILES.get('file')
        from xlrd import open_workbook
        wb = open_workbook(file_contents=file_obj.read())
        # do some stuff with uploaded file
        return Response(status=204)

You must pay attention that using MultiPartParser instead of FileUploadParser, using FileUploadParser will raise some BOF error.

So I am wondering somehow it is also affected by how you write the API.

Answer 3

For me this code works. Python 3

xlrd.open_workbook(file_contents=fileobj.content)

Answer 4

You could try something like...

import xlrd

def newopen(fileobject, modes):
    return fileobject

oldopen = __builtins__.open
__builtins__.open = newopen
InputWorkBook = xlrd.open_workbook(fileobject)
__builtins__.open = oldopen

You may have to wrap the fileobject in StringIO if it isn't already a file handle.