python用lxml库直接读写office excel文件

python读写excel文件有好几个工具。我用过pywin32。但最近发现用直接操纵xml的方法更快,而且不依赖于平台。excel文件可以保存为xml文件,之后读写就变成了操作xml Element。excel 文件的 格式还有需要了解的地方。下面代码是读写Cell的例子。

def setCell(table,row,col,value):
    row=row-1
    col=col-1
    rows=table.findall("{urn:schemas-microsoft-com:office:spreadsheet}Row")
    row1=rows[row]
    cells=row1.findall("{urn:schemas-microsoft-com:office:spreadsheet}Cell")
    #{urn:schemas-microsoft-com:office:spreadsheet}Index
    at=0
    mycells={}
    for cell in cells:#MergeAcross
        #print(cell.attrib)
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}Index")==None:
            mycells[at]=cell
            at+=1
        else:
            at=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}Index"])
            mycells[at-1]=cell
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross")!=None:
            at+=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross"])
    # print(mycells)
    # print(col)
    # input("here")
    data=mycells.get(col).find("{urn:schemas-microsoft-com:office:spreadsheet}Data")
    if data==None:
        data=ET.fromstring('<Data xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">CS-2800□</Data>')
        mycells.get(col).append(data)
    data.text=value#"合同号:"+contact.hetongbh
def setCellWithFont(table,row,col,values):
    row=row-1
    col=col-1
    rows=table.findall("{urn:schemas-microsoft-com:office:spreadsheet}Row")
    row1=rows[row]
    cells=row1.findall("{urn:schemas-microsoft-com:office:spreadsheet}Cell")
    #{urn:schemas-microsoft-com:office:spreadsheet}Index
    at=0
    mycells={}
    for cell in cells:#MergeAcross
        #print(cell.attrib)
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}Index")==None:
            mycells[at]=cell
            at+=1
        else:
            at=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}Index"])
            mycells[at-1]=cell
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross")!=None:
            at+=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross"])
    data=mycells.get(col).find("{urn:schemas-microsoft-com:office:spreadsheet}Data")
    if data==None:
        data=ET.fromstring('<Data xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">CS-2800□</Data>')
        mycells.get(col).append(data)
        data.text="".join(values)#"合同号:"+contact.hetongbh
    else:
        fonts=data.findall("{http://www.w3.org/TR/REC-html40}Font")
        at=0
        for font in fonts:
            if at==len(values):
                break
            print(at,font.text)
            font.text=values[at]
            at+=1
def getCell(table,row,col):
    row=row-1
    col=col-1
    rows=table.findall("{urn:schemas-microsoft-com:office:spreadsheet}Row")
    row1=rows[row]
    cells=row1.findall("{urn:schemas-microsoft-com:office:spreadsheet}Cell")
    #{urn:schemas-microsoft-com:office:spreadsheet}Index
    #print(cells)
    at=0
    mycells={}
    for cell in cells:#MergeAcross
        #print(cell.attrib)
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}Index")==None:
            mycells[at]=cell
            at+=1
        else:
            at=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}Index"])
            mycells[at-1]=cell
        if cell.attrib.get("{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross")!=None:
            at+=int(cell.attrib["{urn:schemas-microsoft-com:office:spreadsheet}MergeAcross"])
    #print(mycells)
    data=mycells.get(col).find("{urn:schemas-microsoft-com:office:spreadsheet}Data")
    if data==None:
        return ""#None
    else:
        #print(data.attrib)
        if data.attrib.get('{urn:schemas-microsoft-com:office:spreadsheet}Type')=="String":
            return data.text#"合同号:"+contact.hetongbh
        elif data.attrib.get('{urn:schemas-microsoft-com:office:spreadsheet}Type')=="Number":
            return float(data.text)#"合同号:"+contact.hetongbh
        else:
            return data.text



  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值