컴퓨터/Python
xlsxwriter dataframe image 넣기
풍경소리^^
2022. 9. 30. 18:26
xlsxwriter_modify_pandas.py--------------------
# from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
# QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
# QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
# QLineEdit, QLabel
import xlsxwriter
import xlrd
import pandas as pd
# class MyApp(QWidget):
# # class MyApp(QMainWindow):
# def __init__(self):
# super().__init__()
# self.window_width, self.window_height = 1000, 500
# self.resize(self.window_width, self.window_height)
# ##### layout_main ######
# layout_main = QHBoxLayout()
# self.setLayout(layout_main)
# ##### layout_1 ######
# layout_1 = QVBoxLayout()
# s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
# # s_open_btn.setFixedSize(200, 30)
# # s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# # s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
# self.s_listwidget = QListWidget(self)
# # self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
# self.s_listwidget.setMaximumWidth(300)
# layout_1.addWidget(s_open_btn)
# layout_1.addWidget(self.s_listwidget)
# t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
# t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
# self.t_listwidget = QListWidget(self)
# # self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
# self.t_listwidget.setMaximumWidth(300)
# layout_1.addWidget(t_open_btn)
# self.label2 = QLabel()
# self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
# layout_1.addWidget(self.label2)
# layout_1.addWidget(self.t_listwidget)
# layout_main.addLayout(layout_1)
# ##### layout_2 ######
# layout_2 = QVBoxLayout()
# self.table = QTableWidget()
# layout_2.addWidget(self.table)
# layout_main.addLayout(layout_2)
# # 시그널 연결
# s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
# self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
# self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
# def getItem_s(self):
# self.s_row = self.s_listwidget.currentRow()
# self.loadData(self.s_row)
# def getItem_t(self):
# self.t_row = self.t_listwidget.currentRow()
# self.fn_df_save(self.t_row)
# def fn_s_clickOpenBtn(self):
# self.table.clear()
# self.s_listwidget.clear()
# # file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
# self.file_path, ext = QFileDialog.getOpenFileName(
# parent=self,
# caption='파일 열기',
# # directory=os.getcwd(),
# # directory=r'G:\python\vscode\pay',
# directory=r'B:\python\vscode\excel',
# filter=file_filter,
# # initialFilter='Excel File (*.xls *.xlsx)'
# initialFilter='Excel File (*.xls* *.xls)'
# )
# if self.file_path:
# self.df_list = self.sheetData(self.file_path) # 파일 내 sheet이름 리스트
# for sheet in self.df_list:
# self.s_listwidget.addItem(sheet.name)
# self.loadData(0)
# # self.initTableWidget(0)
# def fn_t_clickOpenBtn(self):
# self.label2.clear()
# self.t_listwidget.clear()
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
# self.t_file_path, ext = QFileDialog.getOpenFileName(
# parent=self,
# caption='파일 열기',
# # directory=os.getcwd(),
# # directory=r'E:\python\vscode\pay',
# directory=r'B:\python\vscode\excel',
# filter=file_filter,
# # initialFilter='Excel File (*.xls *.xlsx)'
# initialFilter='Excel File (*.xls* *.xls)'
# )
# if self.t_file_path:
# self.df_list_t = self.sheetData(self.t_file_path)
# for sheet in self.df_list_t:
# self.t_listwidget.addItem(sheet.name)
# # self.loadData(0)
# self.label2.setText(self.t_file_path)
# def sheetData(self, file_path):
# df_list = []
# with pd.ExcelFile(file_path) as wb:
# for indx, stn in enumerate(wb.sheet_names):
# try:
# df = pd.read_excel(wb, sheet_name=stn)
# except Exception as e:
# print('File read error:', e)
# else:
# df = df.fillna("")
# df.name = stn
# df_list.append(df)
# return df_list
# def loadData(self, row): # 선택 sheet
# try:
# self.table.clear()
# xl_File = []
# # df = pd.read_excel(self.file_path)
# self.df = self.df_list[row]
# # print(self.df)
# if self.df.size == 0:
# return
# self.df.fillna('',inplace=True)
# self.table.setRowCount(self.df.shape[0])
# self.table.setColumnCount(self.df.shape[1])
# self.table.setHorizontalHeaderLabels(self.df.columns)
# for row in self.df.iterrows():
# values = row[1]
# for col_index, value in enumerate(values):
# if isinstance(value, (float, int)):
# # value = '{0:0,.0f}'.format(value)
# value = '{0:0.0f}'.format(value)
# tableItem = QTableWidgetItem(str(value))
# self.table.setItem(row[0], col_index, tableItem)
# # QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
# except:
# return
# def fn_df_save(self, t_row):
# try:
# xl_File = []
# self.df_t = self.df_list_t[t_row]
# if self.df.size == 0:
# return
# # 원본
# self.df.fillna('',inplace=True)
# self.df = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
# wb_t = openpyxl.load_workbook(self.t_file_path)
# ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# wb_t.remove(wb_t[self.t_listwidget.item(t_row).text()]) # 타겟 시트 삭제
# wb_t.save(filename=self.t_file_path)
# book = openpyxl.load_workbook(self.t_file_path)
# writer = pd.ExcelWriter(self.t_file_path, engine = 'openpyxl')
# writer.book = book
# # for row in self.df.iterrows():
# # values = row[1]
# # for col_index, value in enumerate(values):
# # if isinstance(value, (float, int)):
# # value = '{0:0,.0f}'.format(value)
# self.df.to_excel(writer, sheet_name=self.t_listwidget.item(t_row).text(),index=False)
# writer.save()
# QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
# except:
# return
########################################################################
# open old file
# old_path = r"B:\python\vscode\excel\xlsxwriter01.xls" # xlrd는 .xls 확장자만 지원
old_path = r"B:\python\vscode\excel\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\vscode\excel\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\b.png"
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\vscode\excel\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.save()
# ########################################################################
# if __name__ == '__main__':
# app = QApplication(sys.argv)
# app.setStyleSheet('''
# QWidget {
# font-size: 17px;
# }
# QHeaderView::section::Horizontal {
# background-color: rgb(191, 255, 0);
# }
# ''')
# myApp = MyApp()
# myApp.show()
# try:
# sys.exit(app.exec())
# except SystemExit:
# print('Closing Window...')====================
Python: Writing Images and dataframes to the same excel file
I'm creating an excel dashboard and I want to generate an excel workbook that has some dataframes on half of the sheets, and .png files for the other half. I'm having difficulty writing them to th...
stackoverflow.com