如何实现Excel尺寸配置及Python排序计时器程序修改以完成数据采集？

Let's break down how to tackle your two technical tasks clearly, with practical code examples and explanations:

一、用sort和size相关操作获取Excel表格的目标尺寸 #

First up, getting the required size from your Excel spreadsheet. Depending on whether you prefer command-line tools or Python, here are two reliable approaches:

• Command-line workflow (convert Excel to CSV first, then sort and count rows):
  Convert your Excel file to CSV, sort the data, then use wc -l to get the row count (your "size"):

  # Install xlsx2csv if you haven't: pip install xlsx2csv
  xlsx2csv your_spreadsheet.xlsx | sort | wc -l
  

  The final number from wc -l is the size you'll use in your Python program.

• Python workflow (use pandas to handle Excel directly):
  If you want to integrate this into code, use pandas to read, sort, and get the row count:

  import pandas as pd
  
  # Load your Excel file
  df = pd.read_excel("your_spreadsheet.xlsx")
  # Sort the data (adjust the column name to match your sheet)
  sorted_df = df.sort_values(by="target_column")
  # Get the size (number of rows)
  target_size = len(sorted_df)
  print(f"Target size from Excel: {target_size}")
  

二、修改Python「Sort Timer」程序完成数据采集 #

Now let's adjust the sorting timer program to meet your requirements. I'll provide a modified version with comments explaining each change, plus examples of alternative sorting algorithms you can use.

Modified Full Code #

import random
import time
import sys
# Bump up recursion limit for recursive sorts (like quicksort) with large sizes
sys.setrecursionlimit(200000)

def selection_sort(arr):
    # Original selection sort implementation (replace this if needed)
    for i in range(len(arr)):
        min_idx = i
        for j in range(i+1, len(arr)):
            if arr[j] < arr[min_idx]:
                min_idx = j
        arr[i], arr[min_idx] = arr[min_idx], arr[i]

def bubble_sort(arr):
    # Example alternative: Bubble Sort
    n = len(arr)
    for i in range(n):
        swapped = False
        for j in range(0, n-i-1):
            if arr[j] > arr[j+1]:
                arr[j], arr[j+1] = arr[j+1], arr[j]
                swapped = True
        # Exit early if no swaps (array is sorted)
        if not swapped:
            break

def quick_sort(arr):
    # Example alternative: Quick Sort (recursive)
    if len(arr) <= 1:
        return arr
    pivot = arr[len(arr)//2]
    left = [x for x in arr if x < pivot]
    middle = [x for x in arr if x == pivot]
    right = [x for x in arr if x > pivot]
    return quick_sort(left) + middle + quick_sort(right)

def main():
    # Replace this with the size you got from your Excel spreadsheet
    size = 50000  # Update this number!
    # Generate random test data (matches your original setup)
    test_list = [random.randint(0, 99999) for _ in range(size)]
    
    print(f"Starting sort tests with dataset size: {size}")

    # Test Selection Sort (or replace with your chosen sort)
    list_copy = test_list.copy()
    start = time.time()
    selection_sort(list_copy)
    elapsed_time = round(time.time() - start)  # Round to nearest second
    print(f"Selection Sort completed in {elapsed_time} seconds")

    # Example: Test Bubble Sort (uncomment to use)
    # list_copy = test_list.copy()
    # start = time.time()
    # bubble_sort(list_copy)
    # elapsed_time = round(time.time() - start)
    # print(f"Bubble Sort completed in {elapsed_time} seconds")

    # Example: Test Quick Sort (uncomment to use)
    # list_copy = test_list.copy()
    # start = time.time()
    # quick_sort(list_copy)
    # elapsed_time = round(time.time() - start)
    # print(f"Quick Sort completed in {elapsed_time} seconds")

if __name__ == "__main__":
    main()

Key Changes Explained #

• Update the size variable: Swap out 50000 in the main function with the exact size you retrieved from your Excel sheet.
• Replace sorting algorithms:
  • To replace Selection Sort, either rewrite the selection_sort function, or use one of the example sorts (Bubble/Quick) by uncommenting their test blocks and commenting out the Selection Sort block.
  • For recursive sorts like Quick Sort, we increased the recursion limit to avoid crashes with large datasets.
• Round time to nearest second: The round() function takes the calculated elapsed time (in seconds) and rounds it to the closest integer, just like you requested.

内容的提问来源于stack exchange，提问作者Tyler Dowsland