# -*- coding: utf-8 -*-

"""
三種方式讀寫修改excel文件：
一： xlrd/xlwt/xlutils -- 讀/寫/修改
二： openpyxl
三： pandas -- 只能實(shí)現(xiàn)對(duì)數(shù)據(jù)進(jìn)行操作清钥，不能實(shí)現(xiàn)對(duì)excel樣式操作
"""

import xlrd
import xlwt
import openpyxl
import pandas as pd

from xlutils import copy
from openpyxl import styles

import pymysql
import sqlalchemy as sqla

from sqlalchemy import create_engine



# 測(cè)試用數(shù)據(jù)
province = ['北京市', '天津市', '河北省', '山西省', '內(nèi)蒙古自治區(qū)', '遼寧省',
            '吉林省', '黑龍江省', '上海市', '江蘇省', '浙江省', '安徽省', '福建省',
            '江西省', '山東省', '河南省', '湖北省', '湖南省', '廣東省', '廣西壯族自治區(qū)',
            '海南省', '重慶市', '四川省', '貴州省', '云南省', '西藏自治區(qū)', '陜西省', '甘肅省',
            '青海省', '寧夏回族自治區(qū)', '新疆維吾爾自治區(qū)']

income = ['5047.4', '3247.9', '1514.7', '1374.3', '590.7', '1499.5', '605.1', '654.9',
          '6686.0', '3104.8', '3575.1', '1184.1', '1855.5', '1441.3', '1671.5', '1022.7',
          '1199.2', '1449.6', '2906.2', '972.3', '555.7', '1309.9', '1219.5', '715.5', '441.8',
          '568.4', '848.3', '637.4', '653.3', '823.1', '254.1']

project = ['各省市', '工資性收入', '家庭經(jīng)營(yíng)純收入', '財(cái)產(chǎn)性收入', '轉(zhuǎn)移性收入', '食品', '衣著',
           '居住', '家庭設(shè)備及服務(wù)', '交通和通訊', '文教、娛樂用品及服務(wù)', '醫(yī)療保健', '其他商品及服務(wù)']


def xlrd_read_excel(filename):
    # xlrd和xlwt能處理的xls文件最大的行數(shù)為65535放闺，用xlrd讀取excel是不能對(duì)其進(jìn)行操作的
    # 超過65535就會(huì)遇到錯(cuò)誤： ValueError: row index was 65536, not allowed by .xls format
    read_e = xlrd.open_workbook(filename)  # 讀取excel文件
    names = read_e.sheet_names()  # 獲取所有表的表名,返回list
    print(names)
    sheet1 = read_e.sheet_by_name('Sheet1')  # 根據(jù)名字讀取表
    # sheet1 = read_e.sheet_by_index(0)  # 根據(jù)索引讀取表
    she_name = sheet1.name  # 獲取sheet1工作表的名字
    print(she_name)
    nrows = sheet1.nrows  # 獲取sheet1表的最大行
    ncols = sheet1.ncols  # 獲取sheet1表的最大列
    row_value = sheet1.row_values(0)  # 返回第1行的數(shù)據(jù)祟昭，list的每個(gè)元素是str類型
    col_value = sheet1.col_values(0)  # 第一列的數(shù)據(jù)，list的每個(gè)元素是str類型
    cell_object = sheet1.cell(1, 6)
    cell_value = sheet1.cell(1, 6).value  # 獲取第2行第7列單元格的值
    cell_value_row = sheet1.row(1)[6].value  # 通過行或者列定位單元格獲取值

    print(f'第一行數(shù)據(jù)：{row_value}')
    print(f'第一列數(shù)據(jù)：{col_value}')
    print(ncols)
    print(nrows)
    print(f'單元格對(duì)象：{cell_object}')  # 輸出結(jié)果：text:'數(shù)據(jù)量'
    print(f'單元格的值：{cell_value}')


def xlwt_write_excel(filename):
    write_e = xlwt.Workbook(encoding='utf-8',style_compression=0)  # 創(chuàng)建一個(gè)excel工作簿（文件）,后一個(gè)參數(shù)表示是否壓縮一般不用
    test = write_e.add_sheet('test', cell_overwrite_ok=True)  # 添加一個(gè)excel表,cell_overwrite_ok表示覆蓋單元格怖侦，默認(rèn)False
    test2 = write_e.add_sheet('test2', cell_overwrite_ok=True)  # 添加第二個(gè)excel表
    test.write(3, 4, '測(cè)試寫入數(shù)據(jù)')  # 寫入數(shù)據(jù)到第4行5列單元格

    #  按行或者列批量插入數(shù)據(jù)：
    # 填入第一行
    for i in range(0, len(project)):
        test2.write(0, i, project[i])
    # 填入第一列
    for i in range(0, len(province)):
        test2.write(i + 1, 0, province[i])
    # 填入第二列
    for i in range(0, len(income)):
        test2.write(i + 1, 1, income[i])
    write_e.save(filename)  # 保存到filename文件中


def xlutils_update_excel():
    workbook = xlrd.open_workbook(r'C:\Users\luoshuxiao\Desktop\test33.xls')
    #  生成xlutils文件對(duì)象
    copy_workbook = copy.copy(workbook)
    #  拿到需要修改的表
    copy_sheet = copy_workbook.get_sheet(1)
    # 修改操作（批量操作走循環(huán)）
    copy_sheet.write(0, 0, 'changed by xlutils')
    # 保存到原excel表篡悟，也可以另起名字創(chuàng)建新表
    copy_workbook.save(r'C:\Users\luoshuxiao\Desktop\test33.xls')
    # xlutils官方對(duì)以下方法都有介紹：
    # xlutils.copy/xlutils.display/xlutils.filter/xlutils.margins/xlutils.styles


def openpyxl_read_excel():
    workbook = openpyxl.load_workbook(r'C:\Users\luoshuxiao\Desktop\test.xlsx')  # 讀取文件，創(chuàng)建工作簿對(duì)象
    # she_names = workbook.get_sheet_names()  # 獲取所有的工作表名字（官方已經(jīng)將該方法棄用）
    she_names1 = workbook.sheetnames   # 獲取所有工作表名字
    print(she_names1)   # 輸出結(jié)果：['Sheet1', 'Sheet2', 'Sheet3']
    # worksheet = workbook.get_sheet_by_name('Sheet1')  # 獲取表對(duì)象(官方已經(jīng)將該方法棄用)
    worksheet1 = workbook['Sheet1']  # 獲取表對(duì)象
    worksheet2 = workbook[she_names1[0]]  # 獲取表對(duì)象
    worksheet3 = workbook.worksheets[0]  # 獲取表對(duì)象
    worksheet4 = workbook.active  # 獲取表對(duì)象(當(dāng)前活躍表匾寝，默認(rèn)第一個(gè))
    print(worksheet1)  # 輸出結(jié)果： <Worksheet "Sheet1">
    print(worksheet2)  # 輸出結(jié)果： <Worksheet "Sheet1">
    print(worksheet3)  # 輸出結(jié)果： <Worksheet "Sheet1">
    print(worksheet4)  # 輸出結(jié)果： <Worksheet "Sheet1">
    #  獲取指定工作表的屬性（表名搬葬，行數(shù)，列數(shù)等）
    she_name = worksheet2.title  # 獲取表名
    print(she_name)  # 輸出結(jié)果： Sheet1
    rows = worksheet2.max_row  # 獲取最大行數(shù)
    cols = worksheet2.max_column  # 獲取最大列數(shù)
    print(rows, cols)

    # 按行艳悔、列獲取表中所有數(shù)據(jù)（當(dāng)然也可以通過行列下標(biāo)直接循環(huán)通過cell(row,column)獲燃被恕）
    # sheet.rows  獲取行數(shù)據(jù)（生成器），生成器每個(gè)元素是一個(gè)元祖猜年，保存每一行數(shù)據(jù)抡锈，元祖每個(gè)元素是對(duì)應(yīng)行的每一個(gè)單元格
    # sheet.columns 獲取列數(shù)據(jù)（生成器），生成器每個(gè)元素是一個(gè)元祖乔外，保存每一列數(shù)據(jù)床三，元祖每個(gè)元素是對(duì)應(yīng)列的每一個(gè)單元格
    for row in worksheet2.rows:   # 遍歷每一行
        for cell in row:    # 遍歷每一行的每一列，獲取單元格對(duì)象
            cell_value = cell.value  # 獲取單元格的值
            print(cell_value)
    for col in worksheet2.columns:  # 遍歷每一列
        for cell in col:   # 遍歷每一列的每一行袁稽，獲取單元格對(duì)象
            cell_value = cell.value  # 獲取單元格的值
            print(cell_value)
            print(cell.row)  # 獲取單元格所在的行
            print(cell.column)  # 獲取單元格所在的列
            print(cell.coordinate)  # 獲取單元格的坐標(biāo) -- 'B2'

    # 獲取指定行勿璃、列的數(shù)據(jù) -- 將sheet.rows/columns生成器轉(zhuǎn)換成list類型通過索引獲取
    for cell in list(worksheet2.rows)[0]:
        print(cell.value)
    for cell in list(worksheet2.columns)[0]:
        print(cell.value)

    # 兩種方式獲取某一局部數(shù)據(jù)信息（某一行到某一行的某一列到某一列數(shù)據(jù)）
    # 方式一：將行、列迭代器轉(zhuǎn)成列表進(jìn)行切片獲取局部數(shù)據(jù)
    for rows in list(worksheet2.rows)[0:3]:  # 獲取行的切片（如果先獲取列也是一樣到你）
        for cell in rows[1:5]:  # 對(duì)每一行數(shù)據(jù)中的列數(shù)據(jù)切片
            print(cell.value)
    # 方式二：定位行列位置推汽，直接動(dòng)態(tài)獲取局部區(qū)域每一個(gè)單元格
    # 與xlrd不同的是补疑，openpyxl下標(biāo)是從1開始，而xlrd是從0開始
    for i in range(1, 4):
        for j in range(2, 6):
            print(worksheet2.cell(row=i, column=j).value)  # cell可以直接寫：cell(i,j)

    # 獲取某一個(gè)單元格數(shù)據(jù)：
    c_value = worksheet2['A1'].value  # 通過excel表中的單元格定位方式定位單元格
    c_value1 = worksheet2.cell(1, 1).value  # 通過下標(biāo)索引定位單元格（下標(biāo)從1開始）
    print(c_value)
    print(c_value1)


def openpyxl_write_excel():
    workbook = openpyxl.Workbook()  # 創(chuàng)建一個(gè)Excel文件（默認(rèn)utf8）
    worksheet = workbook.active  # 獲取當(dāng)前活躍的表（默認(rèn)第一個(gè)）
    worksheet.title = 'openpyxl'  # 為該表取表名
    worksheet1 = workbook.create_sheet()  # 默認(rèn)在工作簿的最后一頁(yè)（傳入頁(yè)碼參數(shù)可以插入到指定頁(yè)碼前）
    worksheet1.title = 'openpyxl1'
    # 邊框的樣式
    border_thin = styles.Side(border_style='thin', color=openpyxl.styles.colors.BLACK)
    for row in worksheet.rows:
        for cell in row:
            # 給每一個(gè)單元格加四個(gè)邊框（可以指定只加相應(yīng)的上下左右邊框）
            cell.border = styles.Border(left=border_thin, right=border_thin, top=border_thin, bottom=border_thin)
    # 將數(shù)據(jù)一行一行或者一列一列寫入excel表中（可以將整張表數(shù)據(jù)放在一個(gè)列表中寫入）
    for i in range(len(project)):
        worksheet.cell(1, i+1, project[i])  # 寫第一行數(shù)據(jù)歹撒，openpyxl單元格下標(biāo)是從1開始的
    for i in range(len(province)):
        worksheet.cell(i+2, 1, province[i])  # 寫第一列數(shù)據(jù)莲组，i+2表示第一行數(shù)據(jù)已經(jīng)寫入
    for i in range(len(income)):
        worksheet.cell(i+2, 2, income[i])  # 寫入第二列數(shù)據(jù)
    workbook.save(r'C:\Users\luoshuxiao\Desktop\openpyxl.xlsx')  # 將excel保存到指定位置
    # workbook.save(r'C:\Users\luoshuxiao\Desktop\openpyxl.xls')  # 雖然不能讀取xls文件格式，但是openpyxl可以保存為xls文件


def openpyxl_update_excel():
    workbook = openpyxl.load_workbook(r'C:\Users\luoshuxiao\Desktop\openpyxl.xlsx')
    worksheet = workbook.worksheets[0]
    worksheet.insert_cols(1)  # 在第一列前插入一列
    # worksheet.insert_rows(1)  # 在第一行前插入一行
    update_column = [i for i in range(1, 33)]
    update_column.insert(0, '第一列')
    for index, row in enumerate(worksheet.rows):
        row[0].value = update_column[index]  # 給第一列每一行單元格賦值（循環(huán)插入時(shí)單元格個(gè)數(shù)需要匹配暖夭，不然要報(bào)下標(biāo)越界）
    worksheet.cell(1, 1, '編號(hào)')  # 將第一行第一列單元格值改為：編號(hào)
    worksheet['A1'] = 'number'  # 將第一行第一列單元格值改為：number
    HongKong = [32, '香港', 2000]
    worksheet.append(HongKong)   # 添加一行數(shù)據(jù)到指定行
    worksheet.delete_rows(5, 1)  # 刪除第5行后的一行數(shù)據(jù)
    worksheet.delete_cols(5, 2)  # 刪除第五列后的兩列數(shù)據(jù)
    # workbook.remove(worksheet)  # 刪除工作表worksheet
    # del workbook[worksheet]  # 刪除工作表worksheet

    # 表和單元格的樣式修改：
    # 改變sheet標(biāo)簽顏色：
    worksheet.sheet_properties.tabColor = '660066'  # 必須是aRGB的十六進(jìn)制值锹杈，不能是英文
    #  設(shè)置字體（cell的font屬性）：等線24號(hào)，加粗斜體迈着，字體顏色紅色
    worksheet['A1'].font = styles.Font(name='等線', size=20, italic=True, color=styles.colors.RED, bold=True)
    #  對(duì)齊方式（cell的aligment屬性）：垂直和水平居中
    worksheet['B1'].alignment = styles.Alignment(horizontal='center', vertical='center')
    #  設(shè)置行高竭望，列寬：
    worksheet.row_dimensions[2].height = 40      # 設(shè)置第2行行高40px
    worksheet.column_dimensions['C'].width = 40      # 設(shè)置C列列寬40px
    worksheet.freeze_panes = 'B4'  # 凍結(jié)單元格B4 上面的行和左邊的列，字符串'B4'用cell對(duì)象表示也可以（滾動(dòng)時(shí)一直顯示）
    worksheet.cell(1, 1).fill = styles.PatternFill('solid', fgColor='FAF0E6')  # 將第一行第一列單元格的填充色改為 FAF0E6
    #  合并拆分單元格：
    #  合并后的單元格在表中的定位和數(shù)據(jù)值裕菠，都以左上角單元格為準(zhǔn)（無論是空還是有數(shù)據(jù)）
    worksheet.merge_cells('B1:G1')  # 合并一行中的幾個(gè)單元格（合并后以B1為準(zhǔn)）
    worksheet.merge_cells('A1:C3')  # 合并一個(gè)局部區(qū)域中的單元格
    #  拆分單元格也是以左上角單元格為準(zhǔn)
    worksheet.unmerge_cells('A1:C3')  # 有值就填充到A1咬清，拆分后的其他單元格均為空
    workbook.save(r'C:\Users\luoshuxiao\Desktop\openpyxl.xlsx')


# pandas支持很多文件類型的數(shù)據(jù)讀寫功能json/excel/dat/csv/等，包括html中的table奴潘，數(shù)據(jù)庫(kù)中的table
def pandas_read_excel():
    xls_file = pd.ExcelFile(r'C:\Users\luoshuxiao\Desktop\爬蟲項(xiàng)目總結(jié).xls')   # 用pandas讀取2003excel版本以上的xls文件旧烧，生成一個(gè)excel實(shí)列對(duì)象
    table = xls_file.parse('Sheet1')   # 解析出表Sheet1的內(nèi)容，轉(zhuǎn)換成DataFrame數(shù)據(jù)類型
    print(table.head(5))
    data = pd.read_excel(r'C:\Users\luoshuxiao\Desktop\爬蟲項(xiàng)目總結(jié).xls')  # 通過read_excel方法讀取excel
    print(data.head(10))

    # 從mysql讀取數(shù)據(jù)轉(zhuǎn)為dataframe：
    db = sqla.create_engine('mysql+pymysql://root:123456@127.0.0.1/jd?charset=utf8')
    data = pd.read_sql('select * from goods', db)
    print(data)


def pandas_write_excel():
    df1 = pd.DataFrame({'data1': [1, 2, 3, 4, 'NaN', 6, 7, 8]})
    df2 = pd.DataFrame({'data2': [11, 21, 31, 41, 51, 61, 71, 81]})
    df3 = pd.DataFrame({'data3': [11, 22, 33, 44, 55, 66, 77, 88]})
    #  寫入方式一：to_excel 默認(rèn)的一個(gè)數(shù)據(jù)集寫入一個(gè)文件中（直接傳入文件名）
    df1.to_excel(r'C:\Users\luoshuxiao\Desktop\pandas_write1.xlsx', na_rep=0, index=False, header=None)
    #  寫入方式二：pd.ExcelWriter創(chuàng)建文件對(duì)象画髓，將多個(gè)數(shù)據(jù)集寫入同一個(gè)文件
    #  生成一個(gè)excel文件對(duì)象
    writer_pd = pd.ExcelWriter(r'C:\Users\luoshuxiao\Desktop\pandas_write2.xlsx')
    #  數(shù)據(jù)df1寫入到df1表中
    df1.to_excel(writer_pd, sheet_name='df1', startcol=0, index=True)
    #  數(shù)據(jù)df2寫入到df1表中
    df2.to_excel(writer_pd, sheet_name='df2', startcol=4, index=False)
    #  將數(shù)據(jù)df3寫入到df3表中
    df3.to_excel(writer_pd, sheet_name='df3', index_label='索引列標(biāo)題')
    writer_pd.save()  # 保存

    # # pandas將datafeame寫入mysql數(shù)據(jù)庫(kù):
    # path1 = r'C:\Users\luoshuxiao\Desktop\PYTHON\總結(jié)\github倉(cāng)庫(kù)\data-analyst\datasets\movielens\movies.dat'
    # movie_table = pd.read_csv(path1, header=None,
    #               names=['movie_id', 'title', 'genres'],
    #               sep='::', engine='python')  # 用pandas讀取本地dat文件(分隔符文件)或者其他支持類型文件掘剪，生成DataFrame數(shù)據(jù)
    # connect_db = create_engine('mysql+pymysql://root:123456@127.0.0.1/movie_db?charset=utf8')  # 通過sqlalchemy創(chuàng)建數(shù)據(jù)庫(kù)引擎
    # #  注意：如果用if_exists = 'replace'，會(huì)先刪除原表奈虾，再創(chuàng)建新表字段夺谁，所以新建的數(shù)據(jù)表與原來不同（字段的數(shù)據(jù)類型）。
    # #   append參數(shù)不會(huì)改變?cè)瓉碜侄蔚臄?shù)據(jù)類型肉微，會(huì)在原表下添加數(shù)據(jù)
    # movie_table.to_sql('movies', connect_db, if_exists='append', index=False)


def main():
    # xlrd_read_excel(r'C:\Users\luoshuxiao\Desktop\test.xls')
    # xlwt_write_excel(r'C:\Users\luoshuxiao\Desktop\test33.xls')
    # xlutils_update_excel()
    # openpyxl_read_excel()
    # openpyxl_write_excel()
    # openpyxl_update_excel()
    # pandas_read_excel()
    pandas_write_excel()


if __name__ == '__main__':
    main()