template-generation

Core task: Generate a ready-to-use SaaS-level management system

What the user asks you to create is not a simple header, but a complete and usable management system, similar to the professional-grade Excel management systems in the Daoke template marketplace. The spreadsheet is the canvas, and the system you generate needs to be something users can actually use.

The management system/template must include all of the following (none may be missing)

Structure layer

✅ System title row (merged and centered, 16pt bold, brand-color background with white text)
✅ Subtitle/statistical period row
✅ Header row (bold, white text, dark background, centered)

Data layer (most critical!)

✅ By default, generate 10-15 rows of realistic test data
✅ Formula columns: Amount=Quantity×Unit Price, Total=SUM, Completion Rate=Completed/Total
✅ Summary row: statistics such as total/average/maximum
✅ Status column: use different background colors to distinguish statuses (green=Completed, blue=In Progress, orange=Pending, red=Overdue)

⚠️ Large data volume generation (>20 rows) must use loops!

When the user requests more than 20 rows of data, it is absolutely forbidden to hardcode each row. A random combination loop must be used:

// 正确：小数组 + 随机组合循环生成 N 行
var names = ["张三","李四","王芳","赵六","刘洋"];
var depts = ["技术部","销售部","市场部","财务部"];
for (var i = 0; i < dataRows; i++) {
  var r = i + 4;
  ws.Range("A"+r).Value2 = i+1;
  ws.Range("B"+r).Value2 = names[Math.floor(Math.random()*names.length)];
  // ...
}
// 禁止：硬编码200行数组（代码会被截断！）

The total code length must be kept within 3000 characters.

Format layer

✅ Alternating row background colors (even rows in a light color)
✅ Regional visual separation (use alternating light and dark background colors to distinguish rows and columns; the Borders API is prohibited)
✅ Reasonable column widths (set according to content, using ws.Range("A:A").ColumnWidth)
✅ Number formats (amount #,##0.00, date yyyy-mm-dd, percentage 0.0%, phone @)

Function layer (make it truly usable for users)

✅ Data validation/dropdown menus (such as the status column: Completed/In Progress/Pending) using ws.Range().Validation.Add(3,1,1,"Option 1,Option 2,Option 3")
✅ Conditional formatting for the status column: different colors for different statuses
✅ Header filtering: ws.Range("Row A:Row Z").AutoFilter()

Reference code pattern

function CL(c){var s="";while(c>0){c--;s=String.fromCharCode(65+(c%26))+s;c=Math.floor(c/26);}return s;}

var ws = Application.ActiveSheet;
var wb = Application.ActiveWorkbook;
ws.Name = "订单管理";
ws.Range("A1:P100").Clear();

// 标题
ws.Range("A1:K1").Merge();
ws.Range("A1").Value2 = "销售订单管理系统";
ws.Range("A1").Font.Size = 16;
ws.Range("A1").Font.Bold = true;
ws.Range("A1").Font.Color = 0xFFFFFF;
ws.Range("A1").Interior.Color = 0x8B4513;
ws.Range("A1").HorizontalAlignment = -4108;
ws.Range("1:1").RowHeight = 40;

// 表头
var h = ["序号","订单编号","客户名称","联系电话","产品名称","数量","单价(元)","金额(元)","下单日期","订单状态","备注"];
ws.Range("A3:K3").Value2 = [h];
ws.Range("A3:K3").Font.Bold = true;
ws.Range("A3:K3").Font.Color = 0xFFFFFF;
ws.Range("A3:K3").Interior.Color = 0x8B4513;
ws.Range("A3:K3").HorizontalAlignment = -4108;

// 测试数据用 Range(CL(c)+r) 写入
// 交替行色、状态列条件格式、公式列、汇总行、数据验证、筛选...