Python win32com easy way to operate Exce l of must see

  • 2020-06-01 10:17:54
  • OfStack

Examples are as follows:

from win32com.client import Dispatch  
import win32com.client  
class easyExcel:  
   """A utility to make it easier to get at Excel.  Remembering 
   to save the data is your problem, as is  error handling. 
   Operates on one workbook at a time."""  
   def __init__(self, filename=None): # Open a file or create a new file (if it doesn't exist)  
     self.xlApp = win32com.client.Dispatch('Excel.Application')  
     if filename:  
       self.filename = filename  
       self.xlBook = self.xlApp.Workbooks.Open(filename)  
       self.xlBook = self.xlApp.Workbooks.Add()  
       self.filename = '' 
   def save(self, newfilename=None): # Save the file  
     if newfilename:  
       self.filename = newfilename  
   def close(self): # Close the file  
     del self.xlApp  
   def getCell(self, sheet, row, col): # Gets the data for the cell  
     "Get value of one cell"  
     sht = self.xlBook.Worksheets(sheet)  
     return sht.Cells(row, col).Value  
   def setCell(self, sheet, row, col, value): # Sets the data for the cell  
     "set value of one cell"  
     sht = self.xlBook.Worksheets(sheet)  
     sht.Cells(row, col).Value = value 
   def setCellformat(self, sheet, row, col): # Sets the data for the cell  
     "set value of one cell"  
     sht = self.xlBook.Worksheets(sheet)  
     sht.Cells(row, col).Font.Size = 15# The font size  
     sht.Cells(row, col).Font.Bold = True# Whether the black body  
     sht.Cells(row, col).Name = "Arial"# Font type  
     sht.Cells(row, col).Interior.ColorIndex = 3# Table cell background  
     #sht.Range("A1").Borders.LineStyle = xlDouble 
     sht.Cells(row, col).BorderAround(1,4)# Table borders  
     sht.Rows(3).RowHeight = 30# Line height  
     sht.Cells(row, col).HorizontalAlignment = -4131 # Horizontal center xlCenter 
     sht.Cells(row, col).VerticalAlignment = -4160 # 
   def deleteRow(self, sheet, row): 
     sht = self.xlBook.Worksheets(sheet) 
     sht.Rows(row).Delete()# Delete rows  
     sht.Columns(row).Delete()# Delete the column 
   def getRange(self, sheet, row1, col1, row2, col2): # To obtain 1 The data of the block area is returned as 1 a 2 D a tuple  
     "return a 2d array (i.e. tuple of tuples)"  
     sht = self.xlBook.Worksheets(sheet) 
     return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value  
   def addPicture(self, sheet, pictureName, Left, Top, Width, Height): # Insert the picture  
     "Insert a picture in sheet"  
     sht = self.xlBook.Worksheets(sheet)  
     sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)  
   def cpSheet(self, before): # Copy worksheet  
     "copy sheet"  
     shts = self.xlBook.Worksheets  

   def inserRow(self,sheet,row):
     sht = self.xlBook.Worksheets(sheet)

   # The following is 1 Some test code. 
if __name__ == "__main__":  
   #PNFILE = r'c:/screenshot.bmp' 
   xls = easyExcel(r'd:\\Desktop\empty_book.xlsx')   
   #xls.addPicture('Sheet1', PNFILE, 20,20,1000,1000)  
   # while(row<5):
   #  while(col<5):
   #    xls.setCellformat('sheet1',row,col)
   #    col += 1
   #    print("row=%s,col=%s" %(row,col))
   #  row += 1
   #  col=1
   #  print("*******row********")
   # print("*******endsetCellformat********")
   # print("*******deleteRow********")
   # xls.deleteRow('sheet1',5)

Related articles: