Python 3.5 write numpy using openpyxl

Question

I have been working through Hilpisch's "Python for Finance" and haven't had much trouble until I got to the chapter on writing to Excel. I am running Python 3.5 and using the OpenPyxl module

import openpyxl as oxl
import numpy as np 
wb = oxl.Workbook()
ws = wb.create_sheet(index = 0, title = 'oxl_sheet')
data = np.arange(1, 65).reshape((8, 8))
for c in range(1, data.shape[0]):
    for r in range(1, data.shape[1]):
        ws.cell(row=r, column=c, value=data[c, r])

This returns: ValueError: Cannot convert 10 to Excel

Is it an issue with using numpy? For example, the following code returns a similar error.

ws.cell(row=1, column=1, value=data[0,0])

But replacing data[0, 0] with an integer or float raises no problem.

Seems like there should be an easy fix to this.

Sergey Arkhipov · Accepted Answer · 2016-04-15 10:31:55Z

2

openpyxl operates only with "known" types (int, string etc) which it knows how to convert to Excel data types. Unfortunately data[0, 0] is not known type:

➜ python 
Python 2.7.10 (default, Oct 14 2015, 16:09:02) 
[GCC 5.2.1 20151010] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import numpy as np
>>> data = np.arange(1, 65).reshape((8, 8))
>>> type(data[0, 1])
<type 'numpy.int64'>

You have to convert numpy.int64 to int type:

>>> for c in range(1, data.shape[0]):
...     for r in range(1, data.shape[1]):
...         ws.cell(row = r, column = c).value = data[c, r].astype(int)

answered Apr 15, 2016 at 10:31

Sergey Arkhipov

1564 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlie Clark · Accepted Answer · 2016-04-15 11:36:55Z

0

Support for numpy types has been added to openpyxl 2.4 which you can install using pip install -U --pre openpyxl.

answered Apr 15, 2016 at 11:36

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

Collectives™ on Stack Overflow

Python 3.5 write numpy using openpyxl

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related