excel2dict

excel2dict support easy loading data from excel files.

Intalling

pip install excel2dict

Quick Over View

Assuming below sample data was saved as excel file named Book.xslx.

Simply, To convert to JSON format text file with command line.

$ excel2dict Book.xlsx > out.json
$ less out.json
[
  {
    "foo": 'a',
    "bar": 1 
  },
  {
    "foo": 'b',
    "bar": 2 
  }
]

As well, you can do the same thing in python script.

>>> import excel2dict
>>> excel2dict.to_dict('Book.xlsx')
[
  {
    "foo": 'a',
    "bar": 1 
  },
  {
    "foo": 'b',
    "bar": 2 
  }
]

Using Sheet Definition

As above example, at simple usage, some data representing dedicated data type(boolean, date, etc) in excel can not be handled usefully.

For this use case, you use a sheet definition file. if exists, excel2dict load a definition file named sheet_definition.yaml from the directory which a target excel file is saved in or optional argument specified to by -s.

sample definition

sheets:
- name: Members
  cols: 
    - name: member_no
      schema:
        type: int
    - name: name
      schema:
        type: string
    - name: is_active
      schema:
        type: bool

Label Definition

Normally, sheet name is named in a business context in which the name may include multibyte character, space, etc. but for handling in script or JSON text file, named only ascii character is useful. For this, you can add label definition to the definition.

For Sheet

sheets:
- name: members
  label: New Members

For Column

cols: 
  - name: name
    label: Member's Name

Data Type Definition

excel2dict suppot below data type.

int

schema:
  type: int

str

schema:
  type: str

bool

schema:
  type: bool

date

schema:
  type: date

datetime

schema:
  type: datetime

For needing to adjust timezone, specifing offset is avalable.

schema:
  type: datetime
  offset: 9

For example, 2019-07-26T09:00:00 in JST, this setting convert the datetime to 2019-07-26T00:00:00

A Bit Odd Function

For rare use case, you may need to convert values defined in other sheets as nested structure.

For example, assuming there were 2 sheets as below,

Sheet1

Sheet2

Sheet Definition

On Sheet1 setting, specify type with ref and sheet with reference sheet name.

sheets:
- name: Sheet1
  columns:
    - name: user
      label: User
      schema:
        type: int
    - name: access_right
      label: Access Right
      schema:
        type: ref
        sheet: Sheet2
- name: Sheet2
  columns:
    - name: ref_name
      label: Access Right

Output

You can get an output like below format defining as ref type.

[
    {
      "user": "Scott",
      "access_right": {
        "Read": "O",
        "Write": "O"
      }
    },
    {
      "user": "Tom",
      "access_right": {
        "Read": "O",
        "Write": "X"
      }
    }
  ]

How to specify

Required setting are type and sheet.

• type: ref
• sheet: reference sheet name

schema:
  type: ref
  sheet: Sheet2

For array, specifing is_array is avalable.

schema:
  type: ref
  sheet: Sheet2
  is_array: true