python操作excel

读取excel

 首先导入包xlrd

import xlrd # 用来读取excel

打开excel

workBook = xlrd.open_workbook(r'xj.xlsx')    # 打开文件

获取表

workBook.sheet_names()    # 获取excel中所有的sheet表
>>> ['Sheet1', '净持仓', '保税区库提价', '期货合约', '国内市场上海地区', '保税区库存', '期货情况', '日胶收盘价', '新加坡结算价', '渤商所天然橡胶']

获取表中内容

# 根据索引来获取到sheet表的内容
sheet1 = workBook.sheet_by_index(0)
print(sheet1)
>>> <xlrd.sheet.Sheet object at 0x0000000005C16358>

# 根据表名来获取到sheet表的内容        
sheet2 = workBook.sheet_by_name('保税区库提价')
print(sheet2)
>>> <xlrd.sheet.Sheet object at 0x0000000005C16358>

获取表的详细内容

# 获取表的表名 行数 列数
print(sheet.name,sheet.nrows,sheet.ncols)
>>> 保税区库提价 586 16

# 获取整行数据
print(sheet.row_values(3))
>>> [43307.0, 1490.0, 1305.0, 0.0, 0.0, 1315.0, 0.0, '', '', 43307.0, 1490.0, 1305.0, 1320.0, 1340.0, 1315.0, 0.0]

# 获取整列数据
print(sheet.col_values(1))
['', '库提价:天然橡胶(RSS3,泰国产):青岛保税区仓库', '日', 1490.0, 1490.0, 1460.0, 0.0, 1485.0, 1485.0, 1490.0, 1490.0, 1490.0, 1495.0, 1495.0, 1495.0, 1495.0, 1480.0, 1480.0, 1500.0, 1510.0, 1530.0, 1525.0, 1525.0, 1525.0, 1525.0, 1520.0, 1550.0, 1555.0, 1550.0, 1550.0, 0.0, 1610.0, 1610.0, 1640.0, 1640.0, 1640.0, 1640.0, 1650.0, 1650.0, 1650.0, 0.0, 1685.0, 1685.0, 0.0, 0.0, 1705.0, 1695.0, 0.0, 0.0, 1740.0, 1740.0, 1730.0, 1725.0, 1725.0, 0.0, 1735.0, 1735.0, 1735.0, 1750.0, 1750.0, 1750.0, 1750.0, 0.0, 1730.0, 1735.0, 1745.0, 1755.0, 1750.0, 1750.0, 1760.0, 1770.0, 0.0, 1730.0, 1730.0, 1740.0, 1735.0, 1740.0, 1735.0, 1735.0, 1750.0, 1740.0, 1740.0, 1685.0, 1685.0, 1655.0, 1650.0, 0.0, 1750.0, 0.0, 0.0, 0.0, 1755.0, 1775.0, 1780.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1795.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1690.0, 1690.0, 1690.0, 0.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 1690.0, 0.0, 0.0, 0.0, 0.0, 1660.0, 1660.0, 1660.0, 1660.0, 1660.0, 1670.0, 1670.0, 1650.0, 0.0, 0.0, 1660.0, 0.0, 0.0, 1680.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1640.0, 1640.0, 0.0, 0.0, 1670.0, 1680.0, 1600.0, 1550.0, 1550.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1620.0, 1625.0, 0.0, 0.0, 1630.0, 0.0, 1650.0, 1590.0, 1590.0, 0.0, 1605.0, 1605.0, 1605.0, 1610.0, 1610.0, 1630.0, 1640.0, 1640.0, 1630.0, 1640.0, 1650.0, 1665.0, 1670.0, 1660.0, 1700.0, 0.0, 0.0, 0.0, 1790.0, 0.0, 0.0, 0.0, 0.0, 1790.0, 1800.0, 1830.0, 1880.0, 1930.0, 1950.0, 1950.0, 1940.0, 1940.0, 1970.0, 1980.0, 1960.0, 0.0, 1910.0, 0.0, 0.0, 1910.0, 1910.0, 0.0, 0.0, 1890.0, 1890.0, 1890.0, 1870.0, 0.0, 1830.0, 1830.0, 1820.0, 1850.0, 1880.0, 1880.0, 1790.0, 1790.0, 1790.0, 1760.0, 1760.0, 1760.0, 1800.0, 0.0, 1820.0, 1810.0, 1810.0, 1820.0, 1820.0, 1840.0, 1830.0, 1780.0, 1780.0, 1760.0, 1740.0, 1760.0, 1710.0, 1710.0, 1740.0, 1740.0, 1740.0, 1800.0, 1800.0, 1790.0, 1740.0, 1740.0, 1710.0, 1710.0, 1750.0, 1800.0, 1810.0, 1810.0, 1830.0, 1830.0, 1810.0, 0.0, 0.0, 0.0, 0.0, 1800.0, 1800.0, 1800.0, 1825.0, 1825.0, 1900.0, 1950.0, 1965.0, 2000.0, 0.0, 1900.0, 1850.0, 1850.0, 1850.0, 1830.0, 1810.0, 1800.0, 1800.0, 1800.0, 1800.0, 1800.0, 1800.0, 1850.0, 1900.0, 1900.0, 1875.0, 1900.0, 0.0, 0.0, 0.0, 0.0, 2000.0, 0.0, 0.0, 1880.0, 1900.0, 1910.0, 0.0, 2000.0, 2020.0, 2100.0, 2150.0, 2100.0, 0.0, 2150.0, 2125.0, 2200.0, 2275.0, 2275.0, 2300.0, 2360.0, 2390.0, 2390.0, 2350.0, 2350.0, 2350.0, 2350.0, 2300.0, 2300.0, 2375.0, 2430.0, 2430.0, 2440.0, 2440.0, 2410.0, 2390.0, 2500.0, 2540.0, 2540.0, 2630.0, 2680.0, 0.0, 0.0, 0.0, 2850.0, 2900.0, 0.0, 2775.0, 2775.0, 2850.0, 2815.0, 2765.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2470.0, 2390.0, 2330.0, 2330.0, 2330.0, 2290.0, 2250.0, 2240.0, 2240.0, 2270.0, 2310.0, 2310.0, 2360.0, 2340.0, 2390.0, 2390.0, 0.0, 0.0, 0.0, 0.0, 2065.0, 2065.0, 2070.0, 2050.0, 2050.0, 1960.0, 1960.0, 1960.0, 2030.0, 2090.0, 2010.0, 2010.0, 2010.0, 2050.0, 0.0, 1900.0, 1900.0, 1890.0, 1830.0, 1835.0, 0.0, 0.0, 1810.0, 1790.0, 1710.0, 1660.0, 1660.0, 1660.0, 1670.0, 1670.0, 0.0, 1670.0, 1660.0, 1660.0, 1660.0, 1630.0, 1640.0, 1665.0, 1690.0, 1700.0, 0.0, 1710.0, 1750.0, 1790.0, 1730.0, 1700.0, 1700.0, 1700.0, 1690.0, 1690.0, 1690.0, 1690.0, 1670.0, 1680.0, 1660.0, 1610.0, 1610.0, 1610.0, 1620.0, 1620.0, 1620.0, 1620.0, 1620.0, 1610.0, 1610.0, 1590.0, 1610.0, 1610.0, 1610.0, 1630.0, 1640.0, 1640.0, 1640.0, 1660.0, 1660.0, 1660.0, 1660.0, 1660.0, 1660.0, 1640.0, 1620.0, 1620.0, 1630.0, 1680.0, 1680.0, 1680.0, 1650.0, 1620.0, 1620.0, 1600.0, 1580.0, 1580.0, 1580.0, 1580.0, 1540.0, 1540.0, 1540.0, 1540.0, 1530.0, 1520.0, 1510.0, 1500.0, 1480.0, 1500.0, 1550.0, 1590.0, 1600.0, 1560.0, 1530.0, 1530.0, 1530.0, 1530.0, 1490.0, 1510.0, 1510.0, 1490.0, 1510.0, 1510.0, 1510.0, 1530.0, 1530.0, 1530.0, 1530.0, 1540.0, 1540.0, 1520.0, 1510.0, 1510.0, 1530.0, 1530.0, 1520.0, 1520.0, 1500.0, 1500.0, 1510.0, 1535.0, 1540.0, 1590.0, 1620.0, 1620.0, 1640.0, 1680.0, 1680.0, 1680.0, 1700.0, 1720.0, 1720.0, 1720.0, 1720.0, 1700.0, 1710.0, 1730.0, 1750.0, 1750.0, 1750.0, 1770.0, 1740.0, 1690.0, 1690.0, 1650.0, 1670.0, 1710.0, 1660.0, 1630.0, 1570.0, 1570.0, 1540.0, 1530.0, 1470.0, 1460.0, 1450.0, 1440.0, 1450.0, 1450.0, 1450.0, 1470.0, 1450.0, 1460.0, 1440.0, 1410.0, 1390.0, 1380.0, 1400.0, 1400.0, 1400.0, 1440.0, 1500.0, 1510.0, 1440.0, 1360.0, 1300.0, 1300.0, 1295.0, 1305.0, 1305.0, 1305.0, 1305.0, 1295.0, 1295.0, 1295.0, 1295.0, 1285.0]

获取表中单元格内容

# 获取单元格内数据
print(sheet.cell(1,3).value)
print(sheet.cell_value(1,3))
print(sheet.row(1)[3].value)
print(sheet.row_values(1)[3])

>>> 库提价:天然橡胶(SIR20,印度尼西亚产):青岛保税区仓库
>>> 库提价:天然橡胶(SIR20,印度尼西亚产):青岛保税区仓库
>>> 库提价:天然橡胶(SIR20,印度尼西亚产):青岛保税区仓库
>>> 库提价:天然橡胶(SIR20,印度尼西亚产):青岛保税区仓库

获取单元的数据类型

# ctype :  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
print(sheet.cell(1,0).value)
print(sheet.cell(1,0).ctype)
print(sheet.cell(3,0).value)
print(sheet.cell(3,0).ctype)

>>> 指标名称
>>> 1
>>> 43307.0
>>> 3

因为获取到excel中的时间为date,所以需要特殊处理

print(xlrd.xldate_as_datetime(sheet.cell(3,0).value,0))
>>> 2018-07-26 00:00:00
print(xlrd.xldate_as_tuple(sheet.cell(3,0).value,0))
>>> (2018, 7, 26, 0, 0, 0)
xlrd.xldate_as_tuple(sheet.cell(3,0).value,workBook.datemode)
>>> (2018, 7, 26, 0, 0, 0)

# 数数据格式
date(*value[:3]).strftime('%Y/%m/%d')