搜索
简帛阁>技术文章>python操作excel

python操作excel

python操作exce的方式:

  • 使用win32com
  • 使用xlrd(读excel)、xlwt(写excel)

1.使用win32com方式

代码:

# coding=utf-8
from win32com.client import Dispatch
import pywintypes

'''
查看excel最大行数和列数
打开一个空白新建EXCEL表格,按CTRL+下箭头,可以查看到最大行数;按CTRL+右箭头,
可以查看到最大列标(若想显示列数,可在最右一列的某单元格中输入=column()回车,出现数字就是最大列数)。
'''


class Excel:
    def __init__(self, filename=None):
        self.excel = Dispatch('Excel.Application')  # 打开excel应用程序
        self.excel.DisplayAlerts = 0
        self.excel.Visible = 0
        if filename is not None:
            self.filename = filename
            self.workBook = self.excel.Workbooks.Open(filename)
        else:
            self.filename = ''
            self.workBook = self.excel.Workbooks.Add()  # 新建excel

    # 保存excel文件
    def save(self, save_path=None):
        if not save_path:
            self.filename = save_path
            self.workBook.SaveAs(save_path)
        else:
            self.workBook.Save()

    # 关闭excel文件
    def close(self):
        self.workBook.Close(SaveChanges=0)

    # 添加工作表
    def addSheet(self, sheetName=None):
        sheet = self.workBook.Sheets.Add()
        if sheetName is not None:
            sheet.Name = sheetName

    def copySheet(self, srcSheetName, destSheetName=None, before=None):
        sheet = self.workBook.WorkSheets(srcSheetName)
        # 在指定工作表的后面插入新的工作表
        if before is None:
            sheet.Copy(None, sheet)
            newSheet = sheet.Next
            if destSheetName is not None:
                newSheet.Name = destSheetName
        # 在指定工作表的前面插入新的工作表
        else:
            sheet.Copy(before, None)
            if destSheetName is not None:
                index = before.Index - 1  # 所获新插入的工作表所在的位置(工作表的index从1开始
                self.workBook.Sheets(index).Name = destSheetName

    # 删除工作表
    def deleteSheet(self, sheetName):
        try:
            sheet = self.workBook.WorkSheets(sheetName)
            if sheet is not None:
                sheet.Delete()
        except pywintypes.com_error:
            pass

    # 获取指定单元格的内容
    def getCell(self, sheet, row, col):
        workSheet = self.workBook.WorkSheets(sheet)
        return workSheet.Cells(row, col).Value

    # 设置单元格内容
    def setCell(self, sheet, row, col, val):
        workSheet = self.workBook.WorkSheets(sheet)
        workSheet.Cells(row, col).Value = val

    # 获取一块区域的内容
    def getRange(self, sheet, row1, col1, row2, col2):
        workSheet = self.workBook.WorkSheets(sheet)
        return workSheet.Range(workSheet.Cells(
            row1, col1), workSheet.Cells(row2, col2)).Value

    # 拷贝一块区域
    def copyRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):
        workSheet = self.workBook.WorkSheets(sheet)
        srcRange = workSheet.Range(
            workSheet.Cells(
                row1, col1), workSheet.Cells(
                row2, col2))
        targetRange = workSheet.Range(
            workSheet.Cells(
                targetRow, targetCol), workSheet.Cells(
                targetRow + row2 - row1, targetCol + col2 - col1))
        #targetRange.Value = srcRange.Value
        srcRange.Copy(targetRange)  # 与targetRange.Value = srcRange.Value效果相同

    # 移动一块区域
    def cutRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):
        workSheet = self.workBook.WorkSheets(sheet)
        srcRange = workSheet.Range(
            workSheet.Cells(
                row1, col1), workSheet.Cells(
                row2, col2))

        # 方案1
        #targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))
        # targetRange.Value = srcRange.Value
        # srcRange.Clear()  #清空原区域的值

        # 方案2
        # targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))
        # srcRange.Cut(targetRange)  #直接调用系统函数

        # 方案3与方案2类似
        srcRange.Cut(workSheet.Cells(targetRow, targetCol))

    # 清空指定区域内容
    def clearRange(self, sheet, row1, col1, row2, col2):
        workSheet = self.workBook.WorkSheets(sheet)
        srcRange = workSheet.Range(
            workSheet.Cells(
                row1, col1), workSheet.Cells(
                row2, col2))
        srcRange.Clear()

    # 添加图片
    def addPicture(self, sheet, pictureName, left, top, width, height):
        workSheet = self.workBook.WorkSheets(sheet)
        ''',
AddPicture(self, Filename=<PyOleMissing object>, LinkToFile=<PyOleMissing object>,
>, Left=<PyOleMissing object>, Top=<PyOleMissing object>, Width=<PyOleMissing objec
hod of win32com.client.CDispatch instance
        '''
        workSheet.Shapes.AddPicture(
            pictureName, 1, 1, left, top, width, height)

    # 删除指定行
    def deletRow(self, sheet, row):
        if row < 1 or row > 1048576:
            return
        deleteRow = self.workBook.WorkSheets(sheet).Rows(row)
        deleteRow.Delete()

    # 删除指定列
    def deleteCol(self, sheet, col):
        if col < 1 or col > 16384:
            return
        deleteCol = self.workBook.WorkSheets(sheet).Columns(col)
        deleteCol.Delete()

 

 

2.使用xlrd,xlwt操作excel

可参考:

http://xlwt.readthedocs.io/en/latest/

http://www.cnblogs.com/hupeng1234/p/6681830.html

http://www.cnblogs.com/hupeng1234/p/6681800.html

 

一、Excel模块:xlrd(读)、xlwt(写)、xlutils(结合读写可修改excel)1、从mysql中读取数据importxlrd,xlwt,xlutilsimportpymysqldefs
Excel模块:xlrd(读)、xlwt(写)、xlutils(结合读写可修改excel)安装pip3installxlrdpip3installxlwtpip3installxlutilsimpor
PythonExcel操作当你从odoo附件中读取excel的时候需要做base64转码,因为odoo中存储的附件都是经过转码的companyselfenv['rescompany']sudo()b
安装openpyxl插件1解压openpyxl2cmd命令行,进入到目录,招到setuppy文件,执行命令:pythonsetuppyinstallcmd>d:>cdD:\pylibs\o
python操作excel写入excel写入excelimportxlwt创建xls对象wbxlwtWorkbook()新增两个表单页(sheet1)sh1wbadd_sheet(成绩)按照位置添加数
python操作exce的方式:使用win32com使用xlrd(读excel)、xlwt(写excel)1使用win32com方式代码:codingutf8fromwin32comclientimp
一、基础安装openpyxl模块pipinstallopenpyxl或者通过轮子安装font(字体类):字号、字体颜色、下划线等fill(填充类):颜色等border(边框类):设置单元格边框alig
Pythonxlwt库操作Excel汇总(全)importxlwtfromconfimportoperation_confasconfigclassSummaryOperationReport(ob
python操作excel的工具类有很多,下面举几个常见的工具类:一、1、xlrd只能读取excel操作,支持xls和xlsx两种格式的2、xlwt只能写入excel操作,只支持xls格式的3、xl
安装xlrd下载地址:https://pypipythonorg/pypi/xlrd一、Excel读写基本操作1导入模块importxlrd2打开excel文件读取数据dataxlrdopen_wor