pandas 기존 시트에 dataframe 추가하기

B:\python\Excel_Python_code_recipy\07\prg\

import openpyxl

# wb = openpyxl.Workbook()
wb = openpyxl.load_workbook(r"..\data\a.xlsx")
ws = wb.active

for col_no in range(1, 26+1):
    ws.cell(row=1,column=col_no).value = ws.cell(row=1,column=col_no).column_letter
ws["A2"] = "ws.max_column"
ws["B2"] = ws.max_column

wb.save(r"..\data\a.xlsx")

import openpyxl

wb = openpyxl.load_workbook(r"..\data\a.xlsx")
ws = wb.active
print(ws.title)
print(ws["A2"].value," : ",ws["B2"].value)

import openpyxl

wb = openpyxl.load_workbook(r"..\data\a.xlsx")
ws_new = wb.create_sheet(title="시트7장")
for row_no in range(1, 5+1):
    for col_no in range(1, 26+1):
        ws_new.cell(row_no,col_no).value = ws_new.cell(row_no,col_no).column_letter + str(row_no)

wb.save(r"..\data\a.xlsx")

import pandas as pd

df = pd.read_excel(r"..\data\a.xlsx", engine = "openpyxl", sheet_name="시트7장")
df2 = pd.read_excel(r"..\data\a.xlsx", engine = "openpyxl", sheet_name="Sheet")
with pd.ExcelWriter(r"..\data\c.xlsx") as writer:
    df.to_excel(writer, index=False, sheet_name="첫번째")
    df2.to_excel(writer, index=False, sheet_name="두번째")

import pandas as pd
import os

df = pd.read_excel(r"..\data\a.xlsx", engine = "openpyxl", sheet_name="시트7장")
if not os.path.exists('..\data\c.xlsx'):
    with pd.ExcelWriter('..\data\c.xlsx', mode='w', engine='openpyxl') as writer:
        df.to_excel(writer, index=False)
else:
    with pd.ExcelWriter('..\data\c.xlsx', mode='a', engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name="시트7장")

# https://wikidocs.net/151061
# 기존에 동일한 파일 및 시트가 있어도 데이터를 유지한 채로 추가 기록 가능
    
import pandas as pd
import openpyxl

# openpyxl
wb = openpyxl.load_workbook(r"..\data\c.xlsx")

# 1단계 - 시트이름 구하기
# print(wb.sheetnames)

# 2단계 - 시트 마지막행 구하기
ws = wb["첫번째"]
# print(ws.max_row)

# pandas
df = pd.read_excel(r"..\data\c.xlsx", engine = "openpyxl", sheet_name="두번째")
with pd.ExcelWriter('..\data\c.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    df.to_excel(writer, index=False, sheet_name="첫번째", header=None, startrow=ws.max_row,startcol=0)

 

https://wikidocs.net/151061

05. DataFrame 파일로 저장

import pandas as pd
import openpyxl

# openpyxl
wb = openpyxl.load_workbook(r"..\data\c.xlsx")

# 1단계 - 시트이름 구하기
# print(wb.sheetnames)

# 2단계 - 시트 마지막행 구하기
ws = wb["첫번째"]
# print(ws.max_row)

# pandas
df = pd.read_excel(r"..\data\c.xlsx", engine = "openpyxl", sheet_name="두번째")
with pd.ExcelWriter('..\data\c.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    df.to_excel(writer, index=False, sheet_name="첫번째", header=None, startrow=ws.max_row,startcol=0)