python - 如何用openpyxl在现有的excel文件中写入数据

Question

openpyxl文档里面有介绍把数据写入单个单元格，和逐行写入数据。但是没有介绍怎么批量把数据写入指定的行或列。想请大神指点一下。

比如我想在第二行这里批量写入10行新的数据（格式和原表格一样），原有的数据往后面排，或者D列写入一列数据。

Answer 1

Pandas reads into the dataframe, then changes the dataframe, and writes it to excel. The code is the simplest

Answer 2

The method above is pretty good, but it’s troublesome to install pandas. It may not be easy for newbies to handle. I have a stupid method

from win32com.client import Dispatch    
import win32com.client

class MyExcel:
    def __init__(self, filename=None):  # 打开文件或者新建文件（如果不存在的话）
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def save(self, newfilename=None):  # 保存文件
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):  # 关闭文件
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp

    def inserRow(self, sheetname, row):
        sht = self.xlBook.Worksheets(sheetname)
        sht.Rows(row).Insert()

if __name__ == "__main__":
      xls = MyExcel(r'd:\jason.li\Desktop\empty_book.xlsx')
      xls.inserRow('sheet1',6)
      xls.save()
      xls.close()

If you find it troublesome, you can use inheritance and override class methods. I will provide a small implementation idea. The efficiency is relatively low. You can also modify the code to insert multiple rows at a time. It is very convenient to use win32com to operate Excel. How to write it as needed.