# Read Write Excel

## Read

### Requirement

```
(sudo) pip install xlrd
```

### Data

test3.txt:\
![](/files/-M1JITh_bEZwBAt6lcvV)

### Code

```python
import xlrd
from datetime import date,datetime

file = 'test3.xlsx'

def read_excel():
    wb = xlrd.open_workbook(filename=file)#打开文件
    print(wb.sheet_names())#获取所有表格名字

    sheet1 = wb.sheet_by_index(0)#通过索引获取表格
    sheet2 = wb.sheet_by_name('年级')#通过名字获取表格
    print(sheet1,sheet2)
    print(sheet1.name,sheet1.nrows,sheet1.ncols)

    rows = sheet1.row_values(2)#获取行内容
    cols = sheet1.col_values(3)#获取列内容
    print(rows)
    print(cols)

    print(sheet1.cell(1,0).value)#获取表格里的内容，三种方式
    print(sheet1.cell_value(1,0))
    print(sheet1.row(1)[0].value)
```

### Effect

![](/files/-M1JIThbzDD5vit3Vv1A)

### Fix it

**python读取excel中单元格内容为日期的方式**\
python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype：\
0: empty，1: string，2: number，3: date，4: boolean，5: error\
即date的 ctype=3，这时需要使用xlrd的 `xldate_as_tuple` 来处理为date格式，先判断表格的 ctype=3 时 xldate 才能开始操作。

修改代码：

```python
import xlrd
from datetime import date,datetime

print(sheet1.cell(1,2).ctype)
date_value = xlrd.xldate_as_tuple(sheet1.cell_value(1,2),wb.datemode)
print(date_value)
print(date(*date_value[:3]))
print(date(*date_value[:3]).strftime('%Y/%m/%d'))
```

结果：![](/files/-M1JIThfu3gq1NVz97L5)

### Details

## Write

### Requirement

```
(sudo) pip install xlwt
```

### Code

```python
import xlwt

#设置表格样式
def set_style(name,height,bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    return style

#写Excel
def write_excel():
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('学生',cell_overwrite_ok=True)
    row0 = ["姓名","年龄","出生日期","爱好"]
    colum0 = ["张三","李四","恋习Python","小明","小红","无名"]

    #写第一行
    for i in range(0,len(row0)):
        sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))

    #写第一列
    for i in range(0,len(colum0)):
        sheet1.write(i+1,0,colum0[i],set_style('Times New Roman',220,True))

    sheet1.write(1,3,'2006/12/12')
    sheet1.write_merge(6,6,1,3,'未知')#合并行单元格
    sheet1.write_merge(1,2,3,3,'打游戏')#合并列单元格
    sheet1.write_merge(4,5,3,3,'打篮球')

    f.save('test.xls')
if __name__ == '__main__':
    write_excel()
```

### Effect

![](/files/-M1JIThj7An3uFDjIihi)

## Excel 手算部分

### EXCEL中如何计算符合条件的值的平均值

平均值直接使用 `=averageifs(average_range, criteria_range, criteria)`

### EXCEL中如何计算符合条件的值的标准差

假设数据在A1:A20\
`=STDEVP(IF((A1:A20>50)*(A1:A20<100),A1:A20))` **数组公式**，按 **Ctrl+Shift+Enter** 结束公式输入。

## Reference

1. [腾讯课堂Coding学院 | Python读写Excel表格，就是这么简单粗暴又好用](https://mp.weixin.qq.com/s/lVqgW0F8dJCte_jz9BTYdw) &#x20;
2. [百度知道 | EXCEL中如何计算符合条件的值的标准差](https://zhidao.baidu.com/question/129945517.html) &#x20;


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://legacy.cookielau.com/archives/3-python/1-pythontools/4-readwriteexcel.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.