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
'컴퓨터 > Python' 카테고리의 다른 글
| python 엑셀 확장자 xls to xlsx / xlsx to xls (0) | 2022.10.10 |
|---|---|
| class101 timepercent 따라하기 (0) | 2022.10.03 |
| python pyside6 QTableWidget excel to excel 다른 엑셀파일 시트 복사하기 (0) | 2022.09.23 |
| pyqt5 qlistwidget 바로바로 표시하기 (0) | 2022.09.23 |
| python 파일 합치기 (0) | 2022.09.14 |