Excel处理工作表

# 读取保存工作簿
# read_only=True/False  只能读不能写/可以读写
# guess_type=False/True 转换数据/不能转换数据
# data_only=False/True  单元格的真实信息/只读取值

# import openpyxl
#
# wb = openpyxl.load_workbook('工作簿.xlsx')
# from openpyxl import load_workbook
# wb = load_workbook('成绩.xlsx')
# wb.save('成绩_1.xlsx')

#  批量创建工作簿
# import openpyxl
# for m in range(1,13):
#     wb = openpyxl.Workbook()
#     wb.save('%d月.xlsx' %m)
#     print('%d月.xlsx' %m)

# workbook.active      #获取当前活动工作表
# workbook.worksheets[索引值]  # 以索引值方式获取工作表
# workbook['工作表名']    # 以工作表名获取，此表达方式没有成员提示
# workbook.worksheets   #  循环工作表
# workbook.sheetnames   # 获取所有工作表名
# worksheet.title='工作表名'    # 获取指定工作表名，可以返回工作表名称，也可以修改工作表名称
'''
import openpyxl
wb = openpyxl.load_workbook('业绩表.xlsx')
ws=wb.active
print(ws)
'''
''' 
 excel读取
import xlrd
xlsx = xlrd.open_workbook('入库表.xlsx')
table = xlsx.sheet_by_index(0)
# 通过sheet名查找：xlsx.sheet_by_name('入库表')
# 通过索引查找：xlsx.sheet_by_index(3)
# print(table.cell_value(0,0))

# print(table.cell_value(1,2))
# print(table.cell(1,2).value)
# print(table.row(1)[2].value)
for i in xlsx.sheet.names():
    print(i)
    # table = xlsx.sheet_by_name(i)
    # print(table.cell.value(3,3))

Excel写入：
import xlwt
new_workbook = xlwt.Workbook()
worksheet = new_workbook.add_sheet('new_test')
worksheet.write(0,0,'test')
new_workbook.save('test.xls')


设置单元格格式：xlutils
from xlutils.copy import copy
import xlrd
import xlwt

tem_excel = xlrd.open_workbook('日统计.xls', fromatting_info=True)
tem_sheet = tem_excel.sheet_by_index(0)
new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
style = xlwt.XFStyle()

font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
font.height = 360
style.font = font

# 边框：细线==THIN
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

# 对齐
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment

new_sheet.write(2,1,12)
new_sheet.write(3,1,18)
new_sheet.write(4,1,19)
new_sheet.write(5,1,15)

new_excel.save('填写.xls')
'''
'''
# 去重统计学生成绩，并输出新表
import xlrd
import xlwt
xlsx = xlrd.open_workbook('文件.xlsx')    # 读取文件
sheet = xlsx.sheet_by_index(0)           # 选择指定sheet
all_data = []       # 依次单元格数据，并统计总分
num_set = set()     # 统计学生总数，并去重
for row_i in range(1,sheet.nrows):
    num = sheet.cell_value(row_i,0)
    name = sheet.cell_value(row_i,1)
    grade = sheet.cell_value(row_i,3)

    student = {
        'num': num,
        'name': name,
        'grade': grade,
    }
    all_data.append(student)
    num_set.add(num)
print(all_data)
print(len(all_data))
print(len(num_set))
# 计算总分
sum_list = []
for num in num_set:
    name = ''
    sum = 0
    for student in all_data:
        if num == student['num']:
            sum += student['grade']
            name = student['name']
    sum_stu = {
        'num': num,
        'name': name,
        'sum': sum
    }
    sum_list.append(sum_stu)
print(sum_list)
# 写入新的Excel
new_workbook = xlwt.Workbook()
worksheet = new_workbook.add_sheet('2班')
# 写入第一列内容
worksheet.write(0,0,'学号')
worksheet.write(0,1,'姓名')
worksheet.write(0,2,'总分')
# 自动写入后面内容
for row in range(0,len(sum_list)):
    worksheet.write(row + 1, 0, sum_list[row]['num'])
    worksheet.write(row + 1, 1, sum_list[row]['name'])
    worksheet.write(row + 1, 2, sum_list[row]['sum'])
new_workbook.save('学生总分')
'''

# import xlwt
# workbook = xlwt.Workbook()
# sheet0 = workbook.add_sheet('sheet0')
# for i in range(0,20):                 #  不能超过256
#     sheet0.write(0,i,i)
# workbook.save('num.xls')

# 不带格式
# import xlsxwriter as xw
# workbook = xw.Workbook('number.xlsx')
# sheet0 = workbook.add_worksheet('sheet0')
# for i in range(0,300):
#     sheet0.write(0,i,i)
# workbook.close()


# import openpyxl
# workbook = openpyxl.load_workbook('number.xlsx')
# sheet0 = workbook['sheet0']
# sheet0['B1'] = '2'
# sheet0['B2'] = '4'
# sheet0['B3'] = '3'
# workbook.save('num_open.xlsx')