【求教】python读excel的问题

[kui27]

python的一个问题：
想要从excel中读取一个列，判断这个列中的每一个元祖是否满足条件，满足条件的话则在该元祖所在的行添加另外一个列。
现在的问题是：
1、如何从excel中读取该列。
2、判断元祖满足条件后，如何在该元祖所在的行添加另外一个列。

新手求教，分数多才显得心诚：

[Rlay_2]

随便写了2段,应该对你有帮助

#! -*- encoding:UTF-8 -*-
from win32com.client import Dispatch
import win32com.client

path = r'D:\Program Files\workspace\OnlyForTest\src\runEnvironment\test.xlsx'
xlsapp = win32com.client.Dispatch('Excel.Application')
xlsbook = xlsapp.Workbooks.Open(path)
xlssheet = xlsbook.Worksheets('Sheet1')

i = 1
while True:
    value = xlssheet.Cells(i, 1).Value
    if value != None:
        print value
        xlssheet.Cells(i, 2).Value = 'Line %d' % i 
    else:
        break
    
    i += 1

[Rlay_2]

这个也可以参考:

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):
        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 GetExcelLineCount(self):
        count = 0
        sht = self.xlBook.Worksheets('第一轮')
        while True:
            if sht.Cells(count + 1, 1).Value == None:
                break
            count += 1
        return count
    def getCell(self, sheet, row, col):
        "Get value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Cells(row, col).Value
    
    def setCell(self, sheet, row, col, value):
        "set value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Value = value
    
    def getRange(self, sheet, row1, col1, row2, col2):
        "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

if __name__ == "__main__":
    xls = easyExcel('D:/用户目录/pythonworkspace/excel2doc/src/test.xls')
    lineNum = xls.GetExcelLineCount()
    for i in range(1, lineNum + 1):
        for j in range(1, 8):
            print i, j, xls.getCell('第一轮', i, j)
    xls.save()
    xls.close()

[Rlay_2]

python核心编程第2版上的例子:

from Tkinter import Tk
from time import sleep
from tkMessageBox import showwarning
import win32com.client as win32

warn = lambda app: showwarning(app, 'Exit?')
RANGE = range(3, 8)

def excel():
    app = 'Excel'
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    ss = xl.Workbooks.Add()
    sh = ss.ActiveSheet
    xl.Visible = True
    sleep(1)
    
    sh.Cells(1, 1).Value = 'Python-to-%s Demo' % app
    sleep(1)
    
    for i in RANGE:
        sh.Cells(i, 1).Value = 'Line %d' % i
        sleep(1)
    sh.Cells(i + 2, 1).Value = "Th-th-th-that's all folks"
    warn(app)
    ss.Close(False)
    xl.Application.Quit()
    
if __name__ == '__main__':
    Tk().withdraw()
    excel()

[kui27]

[Quote=引用 3 楼 rlay_2 的回复:]

python核心编程第2版上的例子:
Python code

from Tkinter import Tk
from time import sleep
from tkMessageBox import showwarning
import win32com.client as win32

warn = lambda app: showwarning(app, 'Exit?')
RANG……
[/Quote]

多谢兄台相助；
由于是整个代码中的一段，因此要用到pyExcelerator模块。不知兄台对这个有没有了解 ；

[yby4769250]

pyExcelerator只支持python2.x,你要用3.x的话就不行

[Rlay_2]

无能为力,没用过pyExcelerator,贴几段pyExcelerator里的示例代码,希望对你有用

#!/usr/bin/env python
# -*- coding: windows-1251 -*-
# Copyright (C) 2005 Kiseliov Roman

__rev_id__ = """$Id: format.py,v 1.3 2005/03/27 12:47:06 rvk Exp $"""


from pyExcelerator import *

style0 = XFStyle()
style0.font.name = 'Times New Roman'
style0.font.struck_out = True
style0.font.bold = True


wb = Workbook()
ws0 = wb.add_sheet('0')

ws0.write(1, 1, 'Test', style0)

for i in range(0, 0x53):
    style = XFStyle()
    style.font.name = 'Arial'
    style.font.colour_index = i
    style.font.outline = True
    style.borders.left = i

    ws0.write(i, 2, 'colour', style)
    ws0.write(i, 3, hex(i), style0)


wb.save('format.xls')

[Rlay_2]

#!/usr/bin/env python
# -*- coding: windows-1251 -*-
# Copyright (C) 2005 Kiseliov Roman
__rev_id__ = """$Id: panes.py,v 1.1 2005/10/26 07:44:24 rvk Exp $"""


from pyExcelerator import *

w = Workbook()
ws1 = w.add_sheet('sheet 1')
ws2 = w.add_sheet('sheet 2')
ws3 = w.add_sheet('sheet 3')
ws4 = w.add_sheet('sheet 4')
ws5 = w.add_sheet('sheet 5')
ws6 = w.add_sheet('sheet 6')

for i in range(0x100):
    ws1.write(i/0x10, i%0x10, i)

for i in range(0x100):
    ws2.write(i/0x10, i%0x10, i)

for i in range(0x100):
    ws3.write(i/0x10, i%0x10, i)

for i in range(0x100):
    ws4.write(i/0x10, i%0x10, i)

for i in range(0x100):
    ws5.write(i/0x10, i%0x10, i)

for i in range(0x100):
    ws6.write(i/0x10, i%0x10, i)

ws1.panes_frozen = True
ws1.horz_split_pos = 2

ws2.panes_frozen = True
ws2.vert_split_pos = 2

ws3.panes_frozen = True
ws3.horz_split_pos = 1
ws3.vert_split_pos = 1

ws4.panes_frozen = False
ws4.horz_split_pos = 12
ws4.horz_split_first_visible = 2

ws5.panes_frozen = False
ws5.vert_split_pos = 40
ws4.vert_split_first_visible = 2

ws6.panes_frozen = False
ws6.horz_split_pos = 12
ws4.horz_split_first_visible = 2
ws6.vert_split_pos = 40
ws4.vert_split_first_visible = 2

w.save('panes.xls')

[kui27]

补充提问：
上边两个目的已经达成；使用一下代码：

from pyExcelerator import *
sheets = parse_xls('file.xls')
...wirte into date...
#save file.xls

在最后的save遇到问题；之前使用pyExcelerator都是创建新的workbook和worksheet。save的时候workbook有对应的变量名；而这次直接打开worksheet，不知道该如何save了。。

[kui27]

多谢2楼哥们的热心帮助。使用pyExcelerator好像确实没有修改表格的功能，只能读取数据后另外新建一个表格，把数据写到新的表格中。

[d_luo]

引用 3 楼 Rlay_2 的回复:

python核心编程第2版上的例子:

from Tkinter import Tk
from time import sleep
from tkMessageBox import showwarning
import win32com.client as win32

warn = lambda app: showwarning(app, 'Exit?')
RANGE = range(3, 8)

def excel():
    app = 'Excel'
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    ss = xl.Workbooks.Add()
    sh = ss.ActiveSheet
    xl.Visible = True
    sleep(1)
    
    sh.Cells(1, 1).Value = 'Python-to-%s Demo' % app
    sleep(1)
    
    for i in RANGE:
        sh.Cells(i, 1).Value = 'Line %d' % i
        sleep(1)
    sh.Cells(i + 2, 1).Value = "Th-th-th-that's all folks"
    warn(app)
    ss.Close(False)
    xl.Application.Quit()
    
if __name__ == '__main__':
    Tk().withdraw()
    excel()

灰常感谢3楼，python对大小写太敏感了，终于找到bug了