pythonでexcelを読んでみた。便利

#!/usr/bin/env python36

# coding=utf-8
import openpyxl as excel
import json
import gzip
#import pickle

def excel2dic( filename ):
    wb = excel.load_workbook( filename )
    context = {}
    for sheet in wb.get_sheet_names():
        ws = wb.get_sheet_by_name(sheet)
        columns = [ row.value for row in list(ws.rows)[0]]
        dic  = [ dict(zip(columns, [ ws.cell(column=cell.col_idx, row=cell.row).value for cell in row ])) for row in ws.rows]
        dic.pop(0)
        context[ sheet ] = dic
    wb.close()
    return context

def excel_getsheetnames( filename ):
    wb = excel.load_workbook( filename )
    sheets = wb.get_sheet_names()
    wb.close()
    return sheets

def create_sql_insert( context ):
    sql =''
    for sheet in context.keys():
        for n in range( len( context[ sheet ])):
            s1 = str(list(context[ sheet ][n].keys())).replace('[','').replace(']','')
            s2 = str(list(context[ sheet ][n].values())).replace('[','').replace(']','')
            sql = sql + 'into {0} ( {1} ) values ({2})\n'.format( sheet, s1, s2)

    sql = 'SAVEPOINT;INSERT ALL\n{0}SELECT * DUAL'.format( sql )
    return sql

def create_sql_delete( gcode, ccode, scode, context  ):
    sql = 'SAVEPOINT;\n'
    for sheet in context.keys():
        sql = sql + 'DELETE FROM {0} WHERE GCODE={1} and CCODE={2} and SCODE={3}; \n'.format( sheet, gcode, ccode, scode)
    return sql

def create_sql_update( gcode, ccode, scode, context  ):
    comma=''
    sql =''
    for sheet, value in context.items():
        s=''
        for n in range( len( context[ sheet ])):
            for k, v in value[n].items():
                s = s + comma + '{1} = "{2}" '.format( sheet, k, v)
                comma = ','
        sql = sql + 'UPDATE {0} SET {4} WHERE  GCODE={1} and CCODE={2} and SCODE={3}; \n'.format( sheet, gcode, ccode, scode, s )
    return sql

def main():
    context = excel2dic( 'test.xlsx')
    sql = create_sql_insert( context )
    print( sql )
    sql = create_sql_delete( '"101"', '"1234"', '"1234"', context )
    print( sql )
    sql = create_sql_update( '"101"', '"1234"', '"1234"', context )
    print( sql )
    enc = json.dumps( context )
    with gzip.open( 'backup.gz', mode='wb', compresslevel=9) as fp:
        fp.write( enc.encode() )

    print( excel_getsheetnames( 'test.xlsx' ))

if __name__ == "__main__":
    main()