Search code examples
pythonpandasxlwings

Python xlWings generate json-File from Range object

I want to generate a json File from a Python xlWings Range object:

import pandas as pd
import numpy as np
import os
import xlwings as xw
 wb=xw.Book(file)
 ws=wb.sheets[0]
 dr=ws.used_range.options(pd.DataFrame)
 df=pd.DataFrame(data=dr)

 jfile=df.to_json(orient='columns')

at this point the program is in an endless loop: df=pd.DataFrame(data=dr)

Solution

  • If you use .value on the end of the dr= line, then you will set dr as a DataFrame, and there will be no need to use the line df=pd.DataFrame(data=dr)

    ws.used_range.options(pd.DataFrame) returns a range object, so trying to turn this into a DataFrame is unlikely to be what you are trying to do. This was the output from my test data, where the used range was A1:D11:

    #Out[]: 
#                                    0
#0    (((<Range [Book1]Sheet1!$A$1>)))
#1    (((<Range [Book1]Sheet1!$B$1>)))
#2    (((<Range [Book1]Sheet1!$C$1>)))
#3    (((<Range [Book1]Sheet1!$D$1>)))
#4    (((<Range [Book1]Sheet1!$A$2>)))
#...
#39  (((<Range [Book1]Sheet1!$D$10>)))
#40  (((<Range [Book1]Sheet1!$A$11>)))
#41  (((<Range [Book1]Sheet1!$B$11>)))
#42  (((<Range [Book1]Sheet1!$C$11>)))
#43  (((<Range [Book1]Sheet1!$D$11>)))

    If you instead use ws.used_range.options(pd.DataFrame).value, then the type of data returns is pd.DataFrame and then then can be converted to json:

    df = ws.used_range.options(pd.DataFrame).value
jfile=df.to_json(orient='columns')

    See the documentation for converters and options here with an example, which uses .value, here.

    Returned types:

    type(ws.used_range.options(pd.DataFrame))
#Out[]: xlwings.main.Range
type(ws.used_range.options(pd.DataFrame).value)
#Out[]: pandas.core.frame.DataFrame