python 操作 excel

## excel 文件读取 pip install openpyxl
 
import openpyxl
from openpyxl.utils import get_column_letter,column_index_from_string
import xlrd
 
## 读取 2007版 excel
workbook_2007 = openpyxl.load_workbook("temp_excel_2007.xlsx")
# type(workbook_2007) 
## 表格名称
sheetnames_2007 = workbook_2007.sheetnames
print("sheetnames_2007:",sheetnames_2007)
worksheets_2007 = workbook_2007.worksheets
print("worksheets_2007:",worksheets_2007)
 
 
## 读取excel 信息
for sheet in worksheets_2007:
   
    worksheets_2007_title = sheet.title
    print("worksheets_2007_title:",worksheets_2007_title)
    worksheets_2007_max_row = sheet.max_row
    print("worksheets_2007_max_row:",worksheets_2007_max_row)
    worksheets_2007_max_column = sheet.max_column
    print("worksheets_2007_max_column:",worksheets_2007_max_column)
    ## 读取每行数据
    for index in range(1,worksheets_2007_max_row+1):
        ## 列与字母之间的转换 get_column_letter 列字母与数字之间的转换 column_index_from_string
        print(get_column_letter(1) + "-" + str(column_index_from_string("A")))
 
        ## 获得第一列的值
        sheetnames_2007_cell = sheet.cell(row=index,column=1).value
        ## 为None 的时候不再显示
        if sheetnames_2007_cell == None:
            break
        print("sheetnames_2007_cell:",sheetnames_2007_cell)
 
 
## 创建工作表
workbook = openpyxl.Workbook()
new_ws_1 = workbook.create_sheet(title="创建测试1",index=0)
new_ws_2 = workbook.create_sheet(title="创建测试2",index=1)
 
## 创建 表格
workbook.save("test.xlsx")
workbook.remove(new_ws_2)
workbook.save("test.xlsx")
 
## 向表格写入数据
workbook_insert = openpyxl.Workbook()
## 插入 sheet 并设定其位置
new_ws = workbook_insert.create_sheet(title="创建测试",index=0)
print(new_ws)
new_ws.cell(1,1,"测试插入值")
 
## 创建 表格
workbook_insert.save("test_insert.xlsx")