python exceltoexcel 비교

xlsmtoxls.py==========================원본xlsm openpyxl

문제점

기존 다른 시트 삭제됨

import openpyxl as xl
import xlrd
import xlwt

# opening the source excel file
filename1 =r"B:\python\vscode\excel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename1) # openpyxl
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]
# print(ws1.title)

filename2 =r"B:\python\vscode\excel\excel_data\급여s1.xls"	# 성공
wb2 = xlrd.open_workbook(filename2) # xlrd
sh_name = "4대급여"
ws2 = wb2[sh_name]

nrows = ws1.max_row
ncols = ws1.max_column

datadict = {}
for row_num in range(1,nrows+1):
    datadict[row_num] = {}
    for col in range(1,ncols+1):
        datadict[row_num][col] = ws1.cell(row=row_num, column=col).value
        # datadict[row_num][col] = ws2.cell_value(row_num, col)

wb2 = xlwt.Workbook(encoding='utf-8')  # utf-8 인코딩 방식의 workbook 생성
worksheetw = wb2.add_sheet(sh_name)  # 시트 생성
for row_num in range(1,nrows+1):
    for col in range(1,ncols+1):
        worksheetw.write(row_num-1, col-1, datadict[row_num][col])

# saving the destination excel file
wb2.save(filename2)
# wb2.save(str(filename2))

# https://python.engineering/python-how-to-copy-data-from-one-excel-sheet-to-another/

 

xlsm_xlsx_by_pandas.py==========================원본xlsm pandas 반올림

import pandas as pd
import openpyxl

df_data = pd.read_excel(r"B:\python\vscode\excel\excel_data\급여대장m.xlsm", sheet_name="4대급여").fillna(0)

# pandas datetime64 to date
df_data['지급일'] = pd.to_datetime(df_data['지급일']).dt.date

# 반올림 대상 열 리스트
round_list = ['추가수당', '급여계', '차감지급액']
for col in round_list:
    df_data[col] = round(df_data[col],0)

filepath = '급여가져오기.xlsx'
with pd.ExcelWriter(filepath, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    df_data.to_excel(writer, index=False, sheet_name="4대급여", header=None, startrow=1,startcol=0)

wb = openpyxl.load_workbook(filepath)
ws_data = wb["4대급여"]
ws = wb["급여명세서사대보험"]
ws.cell(row=3, column=4).value = ws_data.cell(row=2, column=2).value
ws.cell(row=1, column=1).value = ws_data.cell(row=2, column=2).value
# =YEAR('4대급여'!$B$2)&"년 "&TEXT(MONTH('4대급여'!$B$2),"00")&"월 급여명세서"
ws.cell(row=1, column=1).number_format = "yyyy년 mm월 급여명세서"

wb.save(filepath)

 

xlsxwrite_modify.py==========================원본xls,xlsx 둘 다 지원

대상은 xlsx만 지원

import xlsxwriter
import xlrd

# open old file
old_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter01.xls"   # xlrd는 .xls 확장자만 지원
old_workbook = xlrd.open_workbook(old_path)
old_worksheet = old_workbook.sheet_by_index(0)
# old_worksheet = old_workbook.sheet_by_name("Sheet1")

# copy data
all_rows = []
for row in range(old_worksheet.nrows):
    curr_row = []
    for col in range(old_worksheet.ncols):
        curr_row.append(old_worksheet.cell_value(row,col))
    all_rows.append(curr_row)

# modify data
# all_rows[3][1] = 100000

# create new file
# old_worksheet.insert_image('B2', img, {'x_scale':0.5,'y_scale':0.5})
new_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter03.xlsx" # xlsxwriter는 확장자 .xlsx로 해야 문제가 안생깁니다
new_workbook = xlsxwriter.Workbook(new_path)
new_worksheet = new_workbook.add_worksheet()

# populate the new file
for row in range(len(all_rows)):
    for col in range(len(all_rows[0])):
        new_worksheet.write(row, col, all_rows[row][col])

img = r"C:\Users\newstep\Pictures\b.png"
new_worksheet.insert_image('P2',img, {
    'x_scale': 0.5, 'y_scale': 0.5,
    'url': 'https://www.naver.com'
})
new_workbook.close()

 

xlsxwrite_modify_pandas.py==========================원본xls,xlsx 둘 다 지원

대상은 xlsx만 지원

import xlsxwriter
import xlrd
import pandas as pd

# open old file
old_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter01.xls"   # xlrd는 .xls 확장자만 지원
# old_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter1x.xlsx"
file_path, ext = old_path.split(".")
if ext == "xls":
    old_workbook = xlrd.open_workbook(old_path)
    # old_worksheet = old_workbook.sheet_by_index(0)
    old_worksheet = old_workbook.sheet_by_name("Sheet1")

    # copy data
    all_rows = []
    for row in range(old_worksheet.nrows):
        curr_row = []
        for col in range(old_worksheet.ncols):
            curr_row.append(old_worksheet.cell_value(row,col))
        all_rows.append(curr_row)

    # modify data
    all_rows[3][1] = 100000

    # create new file
    # old_worksheet.insert_image('B2', img, {'x_scale':0.5,'y_scale':0.5})
    new_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter2x.xlsx" # xlsxwriter는 확장자 .xlsx로 해야 문제가 안생깁니다
    new_workbook = xlsxwriter.Workbook(new_path)
    new_worksheet = new_workbook.add_worksheet()

    # populate the new file
    for row in range(len(all_rows)):
        for col in range(len(all_rows[0])):
            new_worksheet.write(row, col, all_rows[row][col])

    img = r"C:\Users\newstep\Pictures\1635223026415PQSfrQF.jpg"
    new_worksheet.insert_image('P2',img, {
        'x_scale': 0.5, 'y_scale': 0.5,
        'url': 'https://www.naver.com'
    })
    new_workbook.close()

elif ext == "xlsx":
    df = pd.read_excel(old_path, engine = "openpyxl", sheet_name="Sheet1")
    # df = pd.read_excel(old_path, engine = "openpyxl")
    # print(df)
    new_path = r"B:\python\exceltoexcel\excel_data\xlsxwriter2x.xlsx" # xlsxwriter는 확장자 .xlsx로 해야 문제가 안생깁니다
    writer = pd.ExcelWriter(new_path, engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    # new_workbook = xlsxwriter.Workbook(new_path)
    new_workbook = writer.book
    new_worksheet = writer.sheets['Sheet1']
    # Insert an image.
    img = r"C:\Users\newstep\Pictures\b.png"
    new_worksheet.insert_image('P2',img, {
        'x_scale': 0.5, 'y_scale': 0.5,
        'url': 'https://www.naver.com'
    })
    writer.close()

 

xlstoxlsxtoxls.py==========================xls to xlsx, xlsx to xls

# xls to xlsx
import win32com.client
fname = r"E:\python\vscode\pay\급여s1.xls"
# excel = win32.gencache.EnsureDispatch('Excel.Application') ##################
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible =False
# wb = excel.Workbooks.Add() ##################################################
wb = excel.Workbooks.Open(fname)
ws = wb.Worksheets('4대급여')
ws.Cells(2,1).Value = "win32com excel test1"
ws.Range("A3").Value = "win32com excel test2"
wb.SaveAs(fname+"x", FileFormat = 51) #FileFormat = 51 is for .xlsx extension
# wb.Save() # 기본파일경로에 저장 ###############################################
wb.Close() #FileFormat = 56 is for .xls extension
excel.Application.Quit()

# xlsx to xls
# import win32com.client

# excel = win32com.client.Dispatch('Excel.Application')
# excel.Visible =False
# fname = r"E:\python\vscode\pay\급여x1.xlsx"
# # print(fname[:-1])
# wb = excel.Workbooks.Open(fname)
# # wb.Close()
# wb.SaveAs(fname[:-1], FileFormat=56)
# # wb.Close()
# excel.Application.Quit()

 

xlsmtoxlsx.py==========================openpyxl

# importing openpyxl module
import openpyxl as xl;

# opening the source excel file
filename =r"B:\python\exceltoexcel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename)
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]

# opening the destination excel file
filename1 =r"B:\python\exceltoexcel\excel_data\급여x1.xlsx"	# 성공
# filename1 =r"E:\python\vscode\pay\급여1.xlsm"	# 실패
wb2 = xl.load_workbook(filename1)
# ws2 = wb2.active
ws2 = wb2["4대급여"]

# calculate total number of rows and
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column

# copying the cell values from source
# excel file to destination excel file
for i in range (1, mr + 1):
	for j in range (1, mc + 1):
		# reading cell value from source excel file
		c = ws1.cell(row = i, column = j)

		# writing the read value to destination excel file
		ws2.cell(row = i, column = j).value = c.value

# saving the destination excel file
wb2.save(str(filename1))

 

xlsmtoxlsm.py==========================openpyxl 날짜표시

# importing openpyxl module
import openpyxl as xl
from openpyxl.styles import numbers
import xlrd

# opening the source excel file
filename1 =r"B:\python\exceltoexcel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename1)
# wb1 = xl.load_workbook(filename1, read_only=False, keep_vba=True)
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]

# opening the destination excel file
# filename2 =r"B:\python\vscode\excel\excel_data\급여x1.xlsx"	# 성공
# wb2 = xl.load_workbook(filename2)	# 성공
filename2 =r"B:\python\exceltoexcel\excel_data\급여대장m1.xlsm"	# 성공
wb2 = xl.load_workbook(filename2, read_only=False, keep_vba=True)	# 성공
# filename2 =r"B:\python\vscode\excel\excel_data\급여s1.xls"	# 
# wb2 = xlrd.open_workbook(filename2)	# 성공
# ws2 = wb2.active
ws2 = wb2["4대급여"]

# calculate total number of rows and
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column

# copying the cell values from source excel file to destination excel file
for i in range(1, mr + 1):
    for j in range(1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row=i, column=j)

        # writing the read value to destination excel file
        ws2.cell(row=i, column=j).value = c.value

        # if the source cell contains a date, set the number format in the destination cell
        if c.is_date:
            ws2.cell(row=i, column=j).number_format = numbers.FORMAT_DATE_YYYYMMDD2  # "yyyy-mm-dd" format

# saving the destination excel file
wb2.save(str(filename2))

 

xlsmtoxls.py==========================openpyxl 날짜표시

문제점

서식이 다 날아감

import openpyxl as xl
import xlrd
import xlwt
from xlutils.copy import copy
from datetime import datetime

# opening the source excel file
filename1 = r"B:\python\exceltoexcel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename1)  # openpyxl
ws1 = wb1["4대급여"]

# opening the destination excel file
filename2 = r"B:\python\exceltoexcel\excel_data\급여s1.xls"
wb2 = xlrd.open_workbook(filename2, formatting_info=True)  # xlrd (formatting_info=True ensures we keep the original format)
wb2_copy = copy(wb2)  # Copy the workbook using xlutils.copy
worksheetw = wb2_copy.get_sheet(wb2.sheet_names().index("4대급여"))  # Get the target sheet to modify

nrows = ws1.max_row
ncols = ws1.max_column

datadict = {}
for row_num in range(1, nrows + 1):
    datadict[row_num] = {}
    for col in range(1, ncols + 1):
        cell_value = ws1.cell(row=row_num, column=col).value
        # 날짜 데이터일 경우 처리
        if isinstance(cell_value, datetime):
            datadict[row_num][col] = cell_value.strftime("%Y-%m-%d")
        else:
            datadict[row_num][col] = cell_value

# Write the data to the existing sheet
for row_num in range(1, nrows + 1):
    for col in range(1, ncols + 1):
        worksheetw.write(row_num - 1, col - 1, datadict[row_num][col])

# Save the modified workbook without losing the other sheets
wb2_copy.save(filename2)