如何循环生成指定格式输出并写入Excel？求技术协助

Got it, let's work through this together. You need to generate repeating formatted text blocks based on your given parameters and save them into an Excel file. Below are two practical Python solutions—pick the one that fits how you want your data structured in Excel.

Option 1: Structured Tabular Format (Recommended) #

This stores each attribute (CLASS, DESIGN, etc.) as a separate column in Excel, which makes it easier to sort/filter data later. We'll use pandas for this—it's perfect for tabular data and Excel exports.

First, make sure you have the required libraries installed:

pip install pandas openpyxl

Then run this code:

import pandas as pd

# Your given parameters
number3 = 10
linenumber = 200
quantity = linenumber // number3  # Calculates 20, matching your 200/10

# Initialize a list to hold each block's data
block_list = []

# Loop through each block
for block_num in range(quantity):
    # Calculate start and end serial numbers for the current block
    start_line = block_num * number3 + 1
    end_line = (block_num + 1) * number3
    
    # Build a dictionary for the block (each key becomes an Excel column)
    current_block = {
        "CLASS": "SQUARE",
        "DESIGN": "RECTANGLE",
        "START SERIAL": f'"Data Line {start_line}"',
        "END SERIAL": f'"Data Line {end_line}"',
        "NO OF LINE": number3,
        "PER TOTAL": f"{block_num + 1} / {quantity}"
    }
    block_list.append(current_block)

# Convert the list of dictionaries to a DataFrame
df = pd.DataFrame(block_list)

# Write to Excel (index=False removes the default row numbers)
df.to_excel("serial_data_structured.xlsx", index=False)

print("Excel file generated successfully!")

Option 2: Multi-Line Text per Cell #

If you want each block's full multi-line text to live in a single Excel cell (matching your exact example format), use this variation:

import pandas as pd

# Your parameters
number3 = 10
linenumber = 200
quantity = linenumber // number3

block_texts = []

for block_num in range(quantity):
    start_line = block_num * number3 + 1
    end_line = (block_num + 1) * number3
    
    # Build the full multi-line text for the block
    block_content = (
        f"CLASS: SQUARE\n"
        f"DESIGN : RECTANGLE\n"
        f"START SERIAL : \"Data Line {start_line}\"\n"
        f"END SERIAL : \"Data Line {end_line}\"\n"
        f"NO OF LINE : {number3}\n"
        f"PER TOTAL: {block_num + 1} / {quantity}"
    )
    block_texts.append({"Block Content": block_content})

# Convert to DataFrame and write to Excel
df = pd.DataFrame(block_texts)
df.to_excel("serial_data_blocks.xlsx", index=False)

print("Excel file generated successfully!")

Both solutions will generate the exact sequence you described—just choose the format that works best for your needs.

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