Python | Examples | xlwt, xlrd, xlutils 사용하기 | python - excel 파일 다루기

.

.

.

##
# -*- coding: utf-8 -*-
"""
    Python 에서 xls 파일로 데이터 저장하는 Example
"""

import os
import xlwt
import xlrd
from xlutils.copy import copy as xlcopy

#
import sys
reload(sys)
sys.setdefaultencoding('utf8')

##


def mkdir_if_not(filepath):
    """
    path 부분이 없으면 mkdir 을 한다.
    :param filepath: 파일 패쓰
    :return: filpath 그대로 리턴
    """
    p, _ = os.path.split(filepath)
    if not p:
        return filepath
    if not os.path.exists(p):
        # M.info('%s not exist, trying mkdir ', p)
        os.makedirs(p)

    return filepath


def save_letter_n_best_reply_to_excel(filename_img, origin_letter, letter, best_reply, filepath):

    dir, filename_excel = os.path.split(filepath)
    file_list = os.listdir(dir)

    # 파일이 존재하지 않으면 새로운 파일을 만들어서
    # 새로운 sheet 에 저장
    if filename_excel not in file_list:
        mkdir_if_not(filepath)

        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('letter_best_reply')          # new sheet

        ws.write(0, 0, 'Image File Name')
        ws.write(0, 1, 'Original Letter (From DB)')
        ws.write(0, 2, 'Letter')
        ws.write(0, 3, 'Predicted Best Reply')
        ws.write(1, 0, filename_img)
        ws.write(1, 1, origin_letter)
        ws.write(1, 2, letter)
        ws.write(1, 3, best_reply)
        wb.save(filepath)                               # save to new xls file
    # 이미 xls 파일이 존재하면 기존의 파일을 open 한 후
    # 새로운 내용을 삽입할 row 를 get 한 후에 그 row 에
    # data 를 입력
    else:
        rb = xlrd.open_workbook(filepath, formatting_info=True)
        r_sheet = rb.sheet_by_index(0)
        r = r_sheet.nrows
        print 'saved row in excel file : ', r
        wb = xlcopy(rb)                                 # # wb = xlutils.copy(rb)
        w_sheet = wb.get_sheet(0)
        w_sheet.write(r, 0, filename_img)
        w_sheet.write(r, 1, origin_letter)
        w_sheet.write(r, 2, letter)
        w_sheet.write(r, 3, best_reply)
        wb.save(filepath)


##

if __name__ == '__main__':

    filename_excel = '0_letter_n_best_replies.xls'
    save_folder = '/home/juce/study/python/graph/seaborn_study/'
    # file_list = os.listdir(save_folder)


    # Letter(Original) : 釣りの星行けないんだけどー
    # Letter           : 釣りの星行けないんだけどー
    # Reply            : 私もそう思います！
    filename_img = 'temp_image_name.png'
    letter_origin = u'釣りの星行けないんだけどー'
    letter = u'釣りの星行けないんだけどー'
    best_reply = u'釣りの星行けないんだけどー'

    letter_origin1 = u'釣りの星行けないんだけどー1'
    letter2 = u'釣りの星行けないんだけどー2'
    best_reply3 = u'釣りの星行けないんだけどー3'

    save_letter_n_best_reply_to_excel(filename_img=filename_img,
                                      origin_letter=letter_origin,
                                      letter=letter,
                                      best_reply=best_reply,
                                      filepath=save_folder + filename_excel)

    save_letter_n_best_reply_to_excel(filename_img=filename_img,
                                      origin_letter=letter_origin1,
                                      letter=letter2,
                                      best_reply=best_reply3,
                                      filepath=save_folder + filename_excel)


# if filename_excel not in file_list:
#     # M.mkdir_if_not(save_folder + filename_excel)
#
#     wb = xlwt.Workbook()
#     ws = wb.add_sheet('letter_best_reply')
#
#     ws.write(0, 0, 'Image File Name')
#     ws.write(0, 1, 'Letter')
#     ws.write(0, 2, "Predicted Best Reply")
#     ws.write(1, 0, png)
#     ws.write(1, 1, letter)
#     ws.write(1, 2, best_reply)
#     wb.save(save_folder + filename_excel)
# else:
#     rb = xlrd.open_workbook(save_folder + filename_excel, formatting_info=True)
#     r_sheet = rb.sheet_by_index(0)
#     r = r_sheet.nrows
#     print r
#     # wb = xlutils.copy(rb)
#     wb = xlcopy(rb)
#     w_sheet = wb.get_sheet(0)
#     w_sheet.write(r, 0, png1)
#     w_sheet.write(r, 1, letter2)
#     w_sheet.write(r, 2, best_reply3)
#     wb.save(save_folder + filename_excel)
##

.

.

.