🍪
cookielau
  • Introduction
  • Machine Learning
    • Distributed
      • Bookmarks
    • NLP
      • Transformers
    • MLC
      • Tensor Program Abstraction
      • End-to-End Module Execution
  • Framework
    • PyTorch
      • Bookmarks
      • Model
      • Shared
      • Miscellaneous
    • Tensorflow
      • Bookmarks
      • Model
      • Shared
      • Miscellaneous
    • CUDA
      • Bookmarks
    • DeepSpeed
    • Bagua
      • Model
      • Optimizer
    • Others
      • Bookmarks
  • About Me
    • 2022-04-28
  • Random Thoughts
  • Archives
    • CPP
      • Bookmarks
      • Container
      • Algorithm
      • FILE CONTROL
      • Virtual Table
      • Assembly
      • Key Words
      • Problems
      • Others
    • JAVA
      • String Container
      • Maps
    • PYTHON
      • Bookmarks
      • Python Tools
        • Batch Rename
        • Combine Excel
        • Excel Oprations
        • Read Write Excel
        • Rotate PDF
      • Library
        • Pandas Notes
        • Numpy Notes
        • Json Notes
      • Spider
        • Selenium Install
        • Selenium Locating
        • Selenium Errors
        • Selenium Basics
      • Django
        • Start Up
      • Others
    • LINUX
      • Installation
      • Cli Tools
      • WSL
      • Bugs
    • JUNIOR2
      • Economics
        • Chapter 0x01 经济管理概述
        • Chapter 0x02 微观市场机制分析
        • Chapter 0x03 生产决策与市场结构
        • Chapter 0x04 宏观经济市场分析
        • Chapter 0x05 管理的职能
        • Chapter 0x06 生产系统结构与战略
        • Chapter 0x0b 投资项目经济评价
        • Chapter 0x0f 投资项目经济评价
      • Computer Network
        • 概述
        • 分层模型
        • 物理层
        • 数据链路层
        • 网络层
        • 传输层
        • 应用层
        • HTTP(s)实验
        • [Practice]
      • Software Engineering
        • Introduction
        • Demand Analysis
        • Task Estimation
        • Presentation
      • Network Security
        • Chapter 0x01 概述
        • Chapter 0x02 密码学
        • Chapter 0x03 公钥体制
        • Chapter 0x04 消息认证
        • Chapter 0x05 密钥管理
        • Chapter 0x06 访问控制
        • Assignments
      • x86 Programming
        • Basic Knowledge
        • Program Design
        • System Interruption
        • Frequently used functions
    • MD&LaTex
      • Markdown
      • LaTex
    • NPM
      • NPM LINK
    • MyBlogs
      • 2020BUAA软工——“停下来,回头看”
      • 2020BUAA软工——“初窥构建之法”
      • 2020BUAA软工——“上手软件工程,PSP初体验!”
      • 2020BUAA软工——“深度评测官”
      • 2020BUAA软工——“并肩作战,平面交点Pro”
    • SC
      • PAC 2022
        • Lectures
      • OpenMP & MPI
        • MPI Overview
        • Message Passing Programming
        • OpenMP Overview
        • Work Sharing Directives
        • Annual Challenge
        • Future Topics in OpenMP
        • Tasks
        • OpenMP & MPI
    • Hardware
      • Nvidia GPU
        • Frequent Error
        • Memory Classification
        • CUDA_7_Streams_Simplify_Concurrency
        • Optimize_Data_Transfers_in_CUDA
        • Overlap_Data_Transfers_in_CUDA
        • Write_Flexible_Kernels_with_Grid-Stride_Loops
        • How_to_Access_Global_Memory_Efficiently
        • Using_Shared_Memory
      • Intel CPU
        • Construction
        • Optimization
        • Compilation
        • OpenMP
    • English
      • Vocab
      • Composition
    • Interview
      • Computer Network
Powered by GitBook
On this page
  • Read
  • Requirement
  • Data
  • Code
  • Effect
  • Fix it
  • Details
  • Write
  • Requirement
  • Code
  • Effect
  • Excel 手算部分
  • EXCEL中如何计算符合条件的值的平均值
  • EXCEL中如何计算符合条件的值的标准差
  • Reference

Was this helpful?

  1. Archives
  2. PYTHON
  3. Python Tools

Read Write Excel

Feb 27th, 2020

PreviousExcel OprationsNextRotate PDF

Last updated 5 years ago

Was this helpful?

Read

Requirement

(sudo) pip install xlrd

Data

test3.txt:

Code

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

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 才能开始操作。

修改代码:

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'))

Details

Write

Requirement

(sudo) pip install xlwt

Code

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

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

结果:

腾讯课堂Coding学院 | Python读写Excel表格,就是这么简单粗暴又好用
百度知道 | EXCEL中如何计算符合条件的值的标准差