Python for 循环遍历 excel 工作表

Question

我正在尝试创建一个 for 循环，循环遍历 excel 电子表格的每个单元格。从该工作表中提取数据，然后将其放入列表中。

然后是一个单独的 for 循环，循环遍历列表并将数据分发到单独的工作表中。

我知道我如何一一单独完成 这就是我现在的代码中的一种。

import openpyxl
import xlrd
import xlwt
wb = openpyxl.load_workbook('Z:PublicSafetySafetyDataPullProjectTestFile.xlsx')
type(wb)
sheet = wb.get_sheet_by_name('Sheet1')
a = sheet['E5']
#Data From Old Sheets
wb = openpyxl.load_workbook('Z:PublicSafetySafetyDataPullProjectTestFile.xlsx')
type(wb)
sheet = wb.get_sheet_by_name('Sheet1')


#New Excel Sheet Creation
book = xlwt.Workbook(encoding="utf-8")
sheet1 = book.add_sheet("Current")
sheet1.write(0, 0, "BFI4 AM CARE EOS REPORT", style0)
sheet1.write(0, 6,(time.strftime("%d/%m/%Y")), style0)
sheet1.write(1, 0, 'Encounter Type', style1)
sheet1.write(1, 3, "Day Shift", style1)
sheet1.write(1, 4, "Night Shift", style1)
sheet1.write(1, 5, "Totals", style1)
sheet1.write(1, 6, "WTD", style1)

sheet1.write(5, 3, "Day Shift", style1)
sheet1.write(5, 4, "Night Shift", style1)
sheet1.write(5, 5, "Totals", style1)
sheet1.write(5, 6, "WTD", style1)
sheet1.write(2, 0 , "New Wrk Related Injury",style1)
sheet1.write(2, 3 , a.value)
sheet1.write(3, 0 , "Wrk Related Follow-up")
sheet1.write(4, 0 , "Missed Follow-up")

Answer 1

考虑这个例子，用openpyxl做所有的事情：

# Old Sheet
ws = wb.get_sheet_by_name('Sheet1')
from openpyxl.styles import Font, colors, PatternFill
style = {'font': Font(color=colors.BLACK)}
style1 = {'font':Font(color=colors.WHITE, bold=True,italic=True),
          'fill':PatternFill(start_color=colors.RED,end_color=colors.RED,fill_type='solid')
         }
data = [
    ["BFI4 AM CARE EOS REPORT", '', '', '', '', '', (time.strftime("%d/%m/%Y"))],
    ['Encounter Type', '', '', "Day Shift", "Night Shift", "Totals", "WTD"],
    ["New Wrk Related Injury", '', '', '', '', '', ''],
    ["Wrk Related Follow-up", '', '', '', '', '', ''],
    ["Missed Follow-up", '', '', '', '', '', ''],
    ['', '', '', "Day Shift", "Night Shift", "Totals", "WTD"]
    ]
# Copy Data From Old Sheet
for dRow in range(2, 5):
    for dCol in range(3, 7):
        data[dRow][dCol] = ws.cell(row=dRow+3, column=dCol+2).value
# New Sheet Creation
ws1 = wb.create_sheet("Current")
for row, rData in enumerate(data, 1):
    ws1.append(rData)
    for c, rD in enumerate(rData,1):
        cell = ws1.cell(row=row, column=c)
        if row == 1:
            cell.font = style1['font']
            cell.fill = style1['fill']
        else:
            cell.font = style['font']
wb.save('test.xlsx')

用 Python 测试：3.4.2 -

openpyxl： 2.4.1 - LibreOffice： 4.3.3.2