excel2dict 0.0.2
A converting excel file to python data structure package

# 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.

|foo|bar|
|--|--|
|'a'|1|
|'b'|2|


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
|User|Access Right|
|--|--|
|Scott|Admin|
|Tom|General|

#### Sheet2
|Access Right|Read|Write|
|--|--|--|
|Admin|O|O|
|General|O|X|

#### 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        
```