import timeit
import xlwt
import openpyxl
import xlsxwriter


# 使用xlwt生成Excel文件
def test_xlwt():
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('Sheet1')
    for i in range(1000):
        for j in range(100):
            worksheet.write(i, j, f'Cell ({i}, {j})')
    workbook.save('xlwt_example.xls')


# 使用openpyxl生成Excel文件
def test_openpyxl():
    workbook = openpyxl.Workbook()
    worksheet = workbook.active
    for i in range(1, 1001):
        for j in range(1, 101):
            worksheet.cell(i, j, value=f'Cell ({i}, {j})')
    workbook.save('openpyxl_example.xlsx')


# 使用openpyxl生成Excel文件
def test_openpyxl_fast():
    workbook = openpyxl.Workbook(write_only=True)
    worksheet = workbook.create_sheet()
    for i in range(1, 1001):
        row_data = [f'Cell ({i}, {j})' for j in range(1, 101)]
        worksheet.append(row_data)
    workbook.save('openpyxl_example.xlsx')


# 使用xlsxwriter生成Excel文件
def test_xlsxwriter():
    workbook = xlsxwriter.Workbook('xlsxwriter_example.xlsx')
    worksheet = workbook.add_worksheet()
    for i in range(1000):
        for j in range(100):
            worksheet.write(i, j, f'Cell ({i}, {j})')
    workbook.close()


if __name__ == '__main__':
    xlwt_time = timeit.timeit(test_xlwt, number=1)
    print(f"xlwt: {xlwt_time:.5f} seconds")

    openpyxl_time = timeit.timeit(test_openpyxl, number=1)
    print(f"openpyxl: {openpyxl_time:.5f} seconds")

    openpyxl_time = timeit.timeit(test_openpyxl_fast, number=1)
    print(f"openpyxl_fast: {openpyxl_time:.5f} seconds")
    
    xlsxwriter_time = timeit.timeit(test_xlsxwriter, number=1)
    print(f"xlsxwriter: {xlsxwriter_time:.5f} seconds")

import timeit
import xlwt
import openpyxl
import xlsxwriter
import random
import string
from datetime import datetime, timedelta


def generate_random_data(num_rows, num_cols):
    data = []
    for i in range(num_rows):
        row_data = []
        for j in range(num_cols):
            # 生成随机数据，包括数字、字符串、长字符串和日期
            if j == 0:
                row_data.append(''.join(random.choices(string.ascii_uppercase + string.digits, k=10)))  # 随机生成10位字符串
            elif j == 1:
                row_data.append(random.randint(20, 60))  # 随机生成20到60之间的整数
            elif j == 2:
                row_data.append(''.join(
                    random.choices(string.ascii_letters + string.digits, k=random.randint(20, 50))))  # 随机生成20到50位字符串
            elif j == 3:
                row_data.append(''.join(random.choices(string.ascii_letters + string.digits + string.punctuation,
                                                       k=random.randint(50, 100))))  # 随机生成50到100位字符串
            elif j == 4:
                random_date = datetime(2023, 1, 1) + timedelta(days=random.randint(0, 365))  # 随机生成2023年内的日期
                row_data.append(random_date.strftime('%Y-%m-%d'))
            else:
                row_data.append(random.random() * 1000)  # 随机生成0到1000之间的小数
        data.append(row_data)
    return data


# 使用xlwt生成Excel文件
def test_xlwt():
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('Sheet1')

    # 设置表头样式
    header_style = xlwt.easyxf(
        'font: bold on, name SimSun; alignment: vertical center, horizontal center; borders: left thin, right thin, top thin, bottom thin; pattern: pattern solid, fore_colour gray25;')
    worksheet.col(0).width = 17 * 256

    # 写入数据和样式
    data = generate_random_data(1000, 105)
    for row_index, row_data in enumerate(data):
        for col_index, value in enumerate(row_data):
            if row_index == 0:
                worksheet.write(row_index, col_index, value, header_style)
            else:
                style = xlwt.easyxf(
                    'font: name SimSun; alignment: vertical center, horizontal center; borders: left thin, right thin, top thin, bottom thin;')
                if isinstance(value, int):
                    style.pattern.pattern_fore_colour = xlwt.Style.colour_map['light_blue']
                if col_index == 0:
                    style = xlwt.easyxf(
                        'font: name SimSun, bold on; alignment: vertical center, horizontal center; borders: left thin, right thin, top thin, bottom thin;')
                if col_index == 1:
                    style.num_format_str = '0'  # 设置数字列的格式
                if col_index == 4:
                    style.num_format_str = 'yyyy-mm-dd'  # 设置日期列的格式
                worksheet.write(row_index, col_index, value, style)

            # 为超链接列添加超链接
            if col_index == 5 and row_index > 0:
                link = random.choice(['https://www.baidu.com/', 'https://www.google.com/'])
                url_format = xlwt.easyxf('font: color blue, underline single;')  # 设置超链接样式
                hyperlink = xlwt.Hyperlink()
                hyperlink.link_type = xlwt.Hyperlink.LINK_TYPE_URL
                hyperlink.link_address = link
                worksheet.write(row_index, col_index, xlwt.Formula('HYPERLINK("%s"; "%s")' % (link, link)), url_format)

    workbook.save('xlwt_example.xls')


# 使用openpyxl生成Excel文件
def test_openpyxl():
    workbook = openpyxl.Workbook(write_only=True)
    worksheet = workbook.create_sheet()

    # 设置表头样式
    header_style = openpyxl.styles.NamedStyle(name='header_style', font=openpyxl.styles.Font(bold=True, name='SimSun'),
                                              alignment=openpyxl.styles.Alignment(vertical='center',
                                                                                  horizontal='center'),
                                              border=openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'),
                                                                            right=openpyxl.styles.Side(style='thin'),
                                                                            top=openpyxl.styles.Side(style='thin'),
                                                                            bottom=openpyxl.styles.Side(style='thin')),
                                              fill=openpyxl.styles.PatternFill(start_color='FFC0C0C0',
                                                                               end_color='FFC0C0C0', fill_type='solid'))
    worksheet.column_dimensions['A'].width = 17

    # 写入数据和样式
    data = generate_random_data(1000, 105)
    for row_data in data:
        row = [openpyxl.styles.Cell(value=value) for value in row_data]
        if row_data[0] == 'Name':
            for cell in row:
                cell.style = header_style
        elif isinstance(row_data[1], int):
            for cell in row:
                cell.alignment = openpyxl.styles.Alignment(vertical='center', horizontal='center')
                cell.font = openpyxl.styles.Font(name='SimSun')
                cell.border = openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'),
                                                     right=openpyxl.styles.Side(style='thin'),
                                                     top=openpyxl.styles.Side(style='thin'),
                                                     bottom=openpyxl.styles.Side(style='thin'))
                if isinstance(cell.value, int):
                    cell.fill = openpyxl.styles.PatternFill(start_color='FF99CCFF', end_color='FF99CCFF',
                                                            fill_type='solid')
        elif isinstance(row_data[4], str):
            for cell in row:
                cell.alignment = openpyxl.styles.Alignment(vertical='center', horizontal='center')
                cell.font = openpyxl.styles.Font(name='SimSun')
                cell.border = openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'),
                                                     right=openpyxl.styles.Side(style='thin'),
                                                     top=openpyxl.styles.Side(style='thin'),
                                                     bottom=openpyxl.styles.Side(style='thin'))
        worksheet.append(row)

        # 为超链接列添加超链接
        if row_data[5] != 'Name':
            link = random.choice(['https://www.baidu.com/', 'https://www.google.com/'])
            worksheet.append([openpyxl.styles.Cell(value='', hyperlink=link)])

    workbook.save('openpyxl_example.xlsx')


# 使用xlsxwriter生成Excel文件
def test_xlsxwriter():
    workbook = xlsxwriter.Workbook('xlsxwriter_example.xlsx')
    worksheet = workbook.add_worksheet()

    # 设置表头样式
    header_format = workbook.add_format(
        {'bold': True, 'font_name': 'SimSun', 'align': 'center', 'valign': 'vcenter', 'border': 1, 'bg_color': 'gray25',
         'text_wrap': True})
    worksheet.set_column(0, 0, 17)

    # 写入数据和样式
    data = generate_random_data(1000, 105)
    for row_index, row_data in enumerate(data):
        for col_index, value in enumerate(row_data):
            if row_index == 0:
                worksheet.write(row_index, col_index, value, header_format)
            else:
                format = workbook.add_format(
                    {'font_name': 'SimSun', 'align': 'center', 'valign': 'vcenter', 'border': 1})
                if isinstance(value, int):
                    format.set_bg_color('light_blue')
                if col_index == 0:
                    format.set_bold()
                if col_index == 1:
                    format.set_num_format('0')  # 设置数字列的格式
                if col_index == 4:
                    format.set_num_format('yyyy-mm-dd')  # 设置日期列的格式
                worksheet.write(row_index, col_index, value, format)

            # 为超链接列添加超链接
            if col_index == 5 and row_index > 0:
                link = random.choice(['https://www.baidu.com/', 'https://www.google.com/'])
                worksheet.write_url(row_index, col_index, link)

    workbook.close()


if __name__ == '__main__':
    xlwt_time = timeit.timeit(test_xlwt, number=1)
    print(f"xlwt: {xlwt_time:.5f} seconds")
    #
    # openpyxl_time = timeit.timeit(test_openpyxl, number=1)
    # print(f"openpyxl (write_only=True): {openpyxl_time:.5f} seconds")

    xlsxwriter_time = timeit.timeit(test_xlsxwriter, number=1)
    print(f"xlsxwriter: {xlsxwriter_time:.5f} seconds")

import pandas as pd
import xlsxwriter
import random
import string
from datetime import datetime, timedelta

def generate_random_data(num_rows, num_cols):
    data = []
    for i in range(num_rows):
        row_data = []
        for j in range(num_cols):
            # 生成随机数据，包括数字、字符串、长字符串和日期
            if j == 0:
                row_data.append(''.join(random.choices(string.ascii_uppercase + string.digits, k=10)))  # 随机生成10位字符串
            elif j == 1:
                row_data.append(random.randint(20, 60))  # 随机生成20到60之间的整数
            elif j == 2:
                row_data.append(''.join(random.choices(string.ascii_letters + string.digits, k=random.randint(20, 50))))  # 随机生成20到50位字符串
            elif j == 3:
                row_data.append(''.join(random.choices(string.ascii_letters + string.digits + string.punctuation, k=random.randint(50, 100))))  # 随机生成50到100位字符串
            elif j == 4:
                random_date = datetime(2023, 1, 1) + timedelta(days=random.randint(0, 365))  # 随机生成2023年内的日期
                row_data.append(random_date.strftime('%Y-%m-%d'))
            else:
                row_data.append(random.random() * 1000)  # 随机生成0到1000之间的小数
        data.append(row_data)
    return data

def test_pandas_xlsxwriter():
    data = generate_random_data(1000, 105)
    df = pd.DataFrame(data, columns=[f'Col{i}' for i in range(1, 106)])

    writer = pd.ExcelWriter('pandas_xlsxwriter_example.xlsx', engine='xlsxwriter')
    df.to_excel(writer, index=False, sheet_name='Sheet1')

    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # 设置表头样式
    header_format = workbook.add_format({'bold': True, 'font_name': 'SimSun', 'align': 'center', 'valign': 'vcenter', 'border': 1, 'bg_color': 'gray25', 'text_wrap': True})
    worksheet.set_column(0, 0, 17, header_format)

    # 设置超链接
    link_format = workbook.add_format({'font_name': 'SimSun', 'color': 'blue', 'underline': 1})  # 设置超链接样式
    for row_index in range(1, 1001):
        link = random.choice(['https://www.baidu.com/', 'https://www.google.com/'])
        col_index = 5  # 设置超链接的列索引
        worksheet.write_url(row_index, col_index, link, link_format)

    writer._save()

if __name__ == '__main__':
    test_pandas_xlsxwriter()