@TOC

读excel

import xlrd

1.打开excel文件

data = xlrd.open_workbook(r"./my.xls")
table1 = data.sheets()[0] #通过索引顺序获取
table2 = data.sheet_by_index(0) #通过索引顺序获取
table3 = data.sheet_by_name(u'应变')#通过名称获取
print(table1)
print(table2)
print(table3)
<xlrd.sheet.Sheet object at 0x00000119C9332908>
<xlrd.sheet.Sheet object at 0x00000119C9332908>
<xlrd.sheet.Sheet object at 0x00000119C974BEC8>

2.获取行数和列数

nrows = table1.nrows
ncols = table1.ncols
print("行数:",nrows)
print("列数:",ncols)
行数: 1079
列数: 23
在这里插入图片描述

3.获取整行和整列的值,以列表形式返回

(0,0):0为第一个数据,"0,0"表示从第1行第一个数据开始———————————
(1,1):0为第一个数据,"1,1"表示从第二行第二个数据开始

rows = table1.row_values(0,0)
cols = table1.col_values(1,1)
print(rows)
print(cols)
['日期', '挠度n1', '挠度n2', '挠度n3', '挠度n4', '挠度n5', '挠度n6', '挠度n7', '挠度n8', '挠度n9', '挠度n10', '挠度n11', '挠度s1', '挠度s2', '挠度s3', '挠度s4', '挠度s5', '挠度s6', '挠度s7', '挠度s8', '挠度s9', '挠度s10', '挠度s11']
[468.555, 466.295, 468.879, 467.422, 467.155, 466.587, 464.944, 464.994, 465.997, 464.835, 463.678, 463.513, 463.376, 466.94, 463.699, 462.728, 462.081, 462.083, 462.081, 460.812, 460.278, 460.117, 460.462, 460.906, 460.767, 462.381, 458.596, 460.902, 459.818, 459.61, 457.369, 458.844, 457.349, 455.407, 456.882, 456.562, 455.59, 455.607, 455.607, 455.953, 455.426, 454.079, 454.153, 453.575, 457.387, 455.265, 456.076, 455.308, 453.808, 453.485, 454.455, 453.288, 454.92, 454.275, 453.83, 454.171, 452.155, 454.499, 455.709, 451.51, 452.19, 452.879, 452.801, 453.125, 452.015, 453.107, 452.7, 453.53, 454.906, 456.461, 458.342, 459.287, 456.604, 457.466, 456.861, 459.217, 460.234, 458.197, 458.18, 459.215, 459.58, 458.64, 458.682, 459.61, 459.425, 460.234, 460.534, 460.903, 462.659, 463.699, 463.402, 464.351, 464.723, 464.298, 464.622, 464.185, 464.112, 465.678, 468.098, 465.318, 466.965, 466.669, 466.616, 479.273, 467.558, 466.936, 466.263, 467.422, 467.422, 467.558, 465.455, 468.016, 467.073, 465.345, 465.318, 464.671, 465.156, 463.699, 463.863, 463.699, 464.509, 463.403, 463.676, 462.081, 462.728, 462.566, 460.948, 461.088, 462.034, 461.414, 462.268, 461.088, 462.081, 461.711, 459.772, 460.462, 460.581, 460.257, 459.006, 460.117, 460.139, 460.139, 464.373, 459.006, 458.844, 459.006, 458.844, 459.864, 458.871, 458.844, 457.411, 458.844, 457.535, 455.769, 459.815, 458.661, 456.377, 455.772, 458.358, 463.698, 457.067, 456.397, 457.387, 456.882, 456.969, 456.584, 455.244, 456.096, 455.607, 455.467, 458.197, 453.791, 455.769, 454.312, 454.15, 453.506, 454.819, 455.124, 454.334, 453.988, 454.334, 457.248, 453.647, 453.988, 454.617, 453.0, 454.192, 453.428, 455.144, 454.58, 453.486, 455.283, 455.711, 457.691, 457.109, 456.357, 459.033, 460.487, 465.537, 467.64, 463.999, 463.24, 463.564, 463.491, 466.615, 466.049, 466.992, 469.424, 469.984, 472.148, 475.838, 473.159, 474.19, 475.029, 476.26, 479.506, 479.561, 479.427, 479.884, 480.855, 483.2, 482.924, 485.762, 485.103, 484.954, 486.284, 486.848, 488.95, 486.732, 487.819, 488.789, 490.57, 490.002, 489.196, 489.88, 488.189, 489.964, 490.126, 490.488, 495.014, 491.214, 491.218, 491.214, 490.851, 490.81, 495.793, 489.115, 491.746, 492.306, 490.003, 491.986, 488.674, 490.006, 490.693, 490.366, 489.843, 489.595, 489.64, 489.475, 489.595, 486.483, 488.462, 487.818, 487.778, 486.197, 489.557, 484.74, 483.698, 481.792, 481.953, 482.566, 483.121, 484.74, 482.081, 480.915, 481.503, 481.503, 480.141, 480.953, 480.208, 479.884, 479.043, 478.428, 472.116, 477.393, 476.876, 476.939, 476.647, 473.969, 476.647, 475.422, 476.0, 478.395, 475.353, 476.26, 475.029, 473.969, 473.572, 473.41, 473.249, 473.896, 472.763, 472.734, 472.381, 471.44, 471.792, 469.956, 475.16, 468.852, 470.173, 467.989, 468.312, 468.555, 468.555, 468.393, 468.775, 468.555, 476.42, 466.076, 466.936, 467.26, 466.936, 466.451, 464.646, 465.318, 463.49, 467.181, 464.57, 467.129, 467.395, 470.497, 471.251, 471.747, 469.337, 469.18, 470.412, 471.897, 471.44, 473.474, 476.72, 472.201, 473.969, 476.845, 475.874, 475.972, 477.069, 480.894, 479.689, 480.138, 481.146, 481.864, 481.988, 482.854, 482.1, 483.379, 486.077, 486.324, 486.931, 484.902, 488.666, 490.208, 490.488, 489.963, 489.717, 489.557, 492.63, 492.469, 498.683, 498.666, 495.984, 496.146, 494.41, 500.672, 498.643, 495.908, 497.692, 498.824, 502.982, 500.399, 500.879, 501.063, 500.717, 501.044, 500.557, 501.0, 500.996, 500.996, 499.703, 502.08, 501.854, 504.166, 503.118, 499.819, 499.562, 502.822, 499.31, 500.439, 499.173, 501.711, 497.322, 494.251, 494.691, 495.703, 495.176, 495.056, 496.836, 494.247, 495.746, 494.41, 492.023, 492.185, 491.094, 491.214, 491.418, 492.551, 489.595, 489.394, 489.595, 488.586, 488.342, 489.156, 486.409, 487.862, 485.761, 485.278, 485.064, 487.939, 486.808, 484.543, 484.057, 483.823, 483.121, 483.121, 483.121, 482.081, 483.051, 480.303, 481.238, 480.208, 479.265, 479.884, 479.884, 482.117, 477.683, 479.689, 477.555, 479.494, 476.067, 476.647, 475.16, 475.484, 474.677, 475.907, 475.029, 474.837, 472.806, 473.41, 471.0, 470.898, 472.087, 474.452, 472.734, 471.22, 471.151, 471.899, 470.09, 470.307, 472.116, 473.514, 474.615, 472.22, 476.261, 473.807, 474.68, 476.683, 476.747, 476.359, 480.016, 475.842, 477.72, 479.527, 479.656, 484.06, 481.796, 482.803, 479.73, 482.997, 482.44, 483.015, 484.829, 486.077, 486.972, 485.849, 487.703, 489.478, 488.993, 490.081, 489.718, 491.908, 491.175, 493.924, 494.086, 493.56, 496.836, 496.115, 495.865, 496.222, 496.555, 497.688, 497.483, 500.878, 499.007, 500.03, 499.796, 499.63, 501.549, 504.162, 500.876, 501.918, 502.889, 504.162, 505.312, 512.528, 505.687, 506.206, 507.241, 509.998, 505.457, 502.519, 507.622, 500.353, 499.541, 501.965, 502.174, 501.896, 500.466, 500.557, 504.439, 500.233, 499.1, 499.468, 498.059, 497.364, 498.409, 494.409, 495.014, 496.069, 495.26, 492.954, 495.015, 495.295, 493.721, 493.318, 491.943, 491.336, 491.296, 495.216, 493.149, 487.329, 487.263, 487.615, 487.339, 489.517, 485.278, 489.595, 484.307, 486.969, 482.459, 482.818, 486.358, 481.331, 482.405, 480.949, 483.322, 479.979, 479.884, 479.01, 479.459, 478.072, 479.332, 478.395, 476.489, 476.229, 476.909, 475.776, 474.906, 475.029, 476.293, 475.26, 475.029, 472.807, 472.734, 473.41, 472.836, 471.925, 477.133, 472.572, 474.029, 471.792, 471.792, 472.188, 474.26, 469.956, 469.794, 469.472, 469.092, 467.719, 470.173, 468.852, 468.825, 468.555, 468.528, 471.383, 466.832, 468.529, 469.472, 470.631, 470.173, 470.146, 470.09, 470.737, 471.415, 472.446, 472.938, 474.352, 476.26, 478.108, 475.487, 477.878, 479.924, 479.622, 482.245, 482.277, 482.369, 484.744, 483.536, 490.527, 487.217, 488.142, 488.828, 489.071, 491.905, 492.674, 492.548, 493.722, 491.218, 496.469, 496.674, 496.631, 496.678, 501.46, 498.335, 498.821, 499.518, 499.192, 499.747, 498.808, 499.868, 502.272, 502.711, 502.773, 502.13, 504.119, 503.953, 503.535, 505.238, 506.704, 507.953, 504.067, 506.534, 502.658, 503.098, 505.732, 505.78, 505.215, 510.851, 505.911, 507.13, 505.845, 505.523, 505.86, 504.99, 507.511, 504.114, 504.162, 503.906, 502.288, 500.904, 501.688, 499.82, 498.85, 500.511, 498.642, 498.012, 497.6, 496.998, 496.307, 496.631, 496.674, 494.972, 495.542, 494.81, 494.451, 492.628, 493.156, 493.6, 492.183, 491.538, 490.608, 489.595, 490.326, 489.434, 489.841, 489.394, 489.595, 488.872, 491.982, 487.977, 490.405, 488.101, 487.653, 486.085, 486.159, 486.358, 484.957, 485.477, 486.358, 484.74, 482.854, 484.74, 483.895, 484.74, 482.924, 482.889, 483.121, 482.924, 481.503, 482.763, 482.636, 482.763, 480.463, 480.627, 479.003, 480.175, 478.395, 484.865, 480.108, 480.141, 477.585, 478.006, 478.266, 481.922, 478.266, 476.486, 478.266, 480.756, 478.33, 477.262, 479.884, 478.006, 479.946, 478.645, 479.393, 480.915, 480.273, 484.183, 483.051, 484.795, 483.627, 484.74, 485.724, 487.741, 485.751, 485.96, 487.977, 485.799, 486.483, 486.371, 487.292, 489.11, 488.589, 491.175, 491.218, 490.204, 491.38, 492.265, 493.205, 495.955, 492.304, 492.95, 495.512, 497.32, 494.733, 497.483, 498.591, 500.879, 498.849, 499.055, 499.172, 499.331, 499.82, 504.828, 501.417, 501.112, 502.177, 505.457, 501.99, 503.63, 506.428, 502.335, 502.174, 501.042, 502.824, 501.161, 504.28, 500.303, 500.835, 501.09, 502.499, 499.334, 499.909, 498.662, 500.026, 505.78, 499.819, 499.172, 497.853, 498.173, 498.78, 497.762, 499.36, 496.485, 496.512, 496.629, 494.94, 494.451, 494.445, 495.703, 497.276, 494.82, 493.56, 493.722, 492.507, 491.214, 490.448, 492.994, 491.214, 493.559, 491.297, 490.728, 489.762, 489.154, 489.595, 488.224, 487.417, 489.234, 488.71, 494.853, 492.185, 495.219, 486.931, 486.446, 487.254, 485.724, 487.092, 486.358, 484.757, 485.477, 483.859, 484.74, 486.608, 484.74, 483.698, 484.74, 484.219, 484.381, 483.121, 481.92, 482.924, 483.121, 479.336, 481.365, 481.11, 480.914, 480.949, 481.503, 481.503, 481.179, 480.465, 478.322, 478.712, 479.884, 479.265, 479.689, 480.855, 479.884, 479.884, 479.528, 479.204, 479.723, 478.848, 480.013, 481.275, 482.763, 485.676, 483.213, 483.121, 482.818, 489.11, 482.98, 486.161, 485.151, 486.21, 486.358, 486.124, 485.96, 489.313, 487.977, 488.467, 488.91, 489.678, 489.272, 490.206, 490.689, 492.268, 493.725, 491.869, 491.904, 492.428, 492.468, 494.293, 492.628, 492.992, 493.236, 495.866, 494.044, 496.069, 495.337, 497.975, 499.055, 496.512, 496.265, 497.688, 501.138, 498.939, 500.353, 498.824, 497.764, 498.897, 500.668, 499.677, 500.951, 498.598, 500.763, 500.835, 503.489, 501.595, 503.811, 500.587, 501.157, 500.626, 500.557, 500.557, 501.249, 501.757, 500.395, 501.804, 499.748, 499.515, 500.116, 502.612, 502.011, 502.335, 500.395, 503.326, 498.452, 496.997, 497.364, 496.411, 498.012, 496.793, 498.598, 498.409, 495.822, 494.767, 496.188, 493.074, 496.027, 494.451, 494.691, 493.883, 492.832, 492.63, 492.307, 492.023, 491.296, 491.539, 495.866, 495.822, 490.406, 490.609, 490.326, 492.266, 489.595, 489.595, 486.447, 486.16, 487.701, 489.394, 488.909, 487.491, 486.608, 489.355, 486.21, 487.653, 486.159, 484.667, 484.918, 485.189, 484.74, 483.894, 484.022, 484.381, 484.74, 483.733, 484.344, 482.656, 483.121, 483.121, 483.121, 483.931, 483.121, 483.051, 480.949, 482.243, 481.307, 481.307, 480.592, 481.503, 481.503, 480.591, 481.503, 480.855, 479.298, 480.532, 483.484, 481.63, 480.719, 482.368, 483.121, 483.015, 483.667, 484.183, 483.895, 484.435, 482.727, 485.761, 485.886, 486.361, 485.96, 486.358, 485.724, 486.409, 485.6, 486.682, 488.591, 487.977, 488.139, 489.4, 488.67, 489.841, 490.003, 490.126, 492.832, 493.318, 491.703, 492.832, 492.265]
在这里插入图片描述

4.获取单元格数据

(0,0)第一行第一列------(3,2)第四行第五列

cell_A1 = table1.cell_value(0,0)
cell_C4 = table1.cell_value(3,2)
print(cell_A1)
print(cell_C4)
日期
603.524
在这里插入图片描述

5.使用行列索引来获取单元格数据

row(2)[1]:返回第三行第二列的值---------
col(2)[3]:返回第三列第四行的值

cell_A1 = table1.row(2)[1].value
cell_C4 = table1.col(2)[3].value
print(cell_A1)
print(cell_C4)
466.295
603.524

写excel

import xlwt

1.创建workbook

workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)

encoding:设置字符编码,一般要这样设置:w = Workbook(encoding=’utf-8’),就可以在excel中输出中文了。默认是ascii
style_compression:表示是否压缩,不常用。

2.创建一个sheet对象,一个sheet对象对应Excel文件中的一张表格

sheet = workbook.add_sheet('test', cell_overwrite_ok=True)

其中的test是这张表的名字,cell_overwrite_ok,表示是否可以覆盖单元格,其实是Worksheet实例化的一个参数,默认值是False

3.向表中添加数据

txt0 = '分数'
sheet.write(1, 0, txt0)
txt1 = 'python'
sheet.write(0, 1, txt1)
txt2 = '90'
sheet.write(1, 1, txt2)

4.保存

workbook.save(r'C:/Users/Administrator/Desktop/1.xls')
在这里插入图片描述

追加数据

data = xlrd.open_workbook(r"C:/Users/Administrator/Desktop/1.xls")
txt4 = 'java'
sheet.write(0, 2, txt4)
txt5 = '85'
sheet.write(1,2,txt5)
workbook.save(r'C:/Users/Administrator/Desktop/2.xls')




在这里插入图片描述

粉色的花瓣,美丽地缠绕在身上。依在风里。