多组数据组合输出成表格

需求如下：

有一组变量，数量不确定，变量的取值是1-n个，组合每个变量的各种取值得到图2的结果并保存为文件。

 （图1）

 （图2）

直接上代码：

一、先定义要导出的数据结构

namespace TestAppendCellData
{
    public class ExportDataDriverTemplateData
    {
        /// <summary>
        /// key
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 值
        /// </summary>
        public object Value { get; set; }

        /// <summary>
        /// 参数提示
        /// </summary>
        public string Tip { get; set; }
        /// <summary>
        /// 是否包含驱动数据
        /// </summary>
        public bool IsDriverData { get; set; } = false;

        public List<string>? DriverData { get; set; }
    }
}

二、主程序文件

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TestAppendCellData
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");

            var d1 = new ExportDataDriverTemplateData { Tip = "等待毫秒1", Name = "ms1", Value = "1000" };
            var d2 = new ExportDataDriverTemplateData { Tip = "等待毫秒2", Name = "ms2", Value = "1000" };
            d2.IsDriverData = true;
            d2.DriverData = ["2000", "2001", "2002", "2003"];
            var d3 = new ExportDataDriverTemplateData { Tip = "等待毫秒3", Name = "ms3", Value = "1000" };
            d3.IsDriverData = true;
            d3.DriverData = ["3000", "3001"];
            var d4 = new ExportDataDriverTemplateData { Tip = "等待毫秒4", Name = "ms4", Value = "1000" };
            d4.IsDriverData = true;
            d4.DriverData = ["4000", "4001", "4002"];
            var d5 = new ExportDataDriverTemplateData { Tip = "等待毫秒5", Name = "ms5", Value = "1000" };

            var d6 = new ExportDataDriverTemplateData { Tip = "等待毫秒6", Name = "ms6", Value = "1000" };
            d6.IsDriverData = true;
            d6.DriverData = ["6000", "6001"];

            List<ExportDataDriverTemplateData> datas = [d1, d2, d3, d4, d5, d6];
            using var ms = CreateExcelStreamFromJson(datas);
            File.WriteAllBytes(@"d:/cel.xls", ms.ToArray());
            Console.WriteLine("done!");
            Console.ReadLine();
        }


        private static MemoryStream CreateExcelStreamFromJson(List<ExportDataDriverTemplateData> datas)
        {
            MemoryStream memoryStream = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());

                Sheets sheets = document.WorkbookPart!.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
                sheets.Append(sheet);

                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>()!;
                AppendCellData(datas, sheetData);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        private static void AppendCellData(List<ExportDataDriverTemplateData> datas, SheetData sheetData)
        {
            Row tipRow = new();
            Row nameRow = new();
            List<List<Cell>> cells = [];
            foreach (var item in datas)
            {
                Cell tipCell = new() { CellValue = new CellValue(item.Tip), DataType = CellValues.String };
                Cell nameCell = new() { CellValue = new CellValue(item.Name), DataType = CellValues.String };
                tipRow.Append(tipCell);
                nameRow.Append(nameCell);
                if (item.IsDriverData)
                {
                    List<Cell> ddCList = [];
                    foreach (var ddata in item.DriverData!)
                    {
                        Cell valueCell = new() { CellValue = new CellValue(ddata ?? ""), DataType = CellValues.String };
                        ddCList.Add(valueCell);
                    }
                    cells.Add(ddCList);
                }
                else
                {
                    Cell valueCell = new() { CellValue = new CellValue(item.Value?.ToString() ?? ""), DataType = CellValues.String };
                    cells.Add([valueCell]);
                }
            }

            sheetData.Append(tipRow);
            sheetData.Append(nameRow);

            int totalRows = 1;
            foreach (var c in cells)
            {
                totalRows *= c.Count;
            }

            var totalCells = cells.Count;
            int[][] data = new int[totalRows][];
            for (int i = 0; i < data.Length; i++)
            {
                data[i] = new int[totalCells];
            }


            for (int cellIndex = totalCells - 1; cellIndex >= 0; cellIndex--)
            {
                var rowRepeat = GetRowRepeat(cells, cellIndex);
                for (int row = 0; row < totalRows; row++)
                {
                    var idx = (row / rowRepeat) % cells[cellIndex].Count;
                    data[row][cellIndex] = idx;
                }
            }

            int GetRowRepeat(List<List<Cell>> cells, int cellIndex)
            {
                int count = 1;
                int start = cellIndex + 1;
                if (start < cells.Count)
                {
                    for (int i = start; i < cells.Count; i++)
                    {
                        count *= cells[i].Count;
                    }
                }
                return count;
            }

            for (int i = 0; i < totalRows; i++)
            {
                var clist = new List<Cell>();
                var row = data[i];
                Console.Write($"{i:00} >>  ");
                for (int j = 0; j < totalCells; j++)
                {
                    Console.Write($" {row[j]:00}");
                    clist.Add((cells[j][row[j]].CloneNode(true) as Cell)!);
                }
                Console.WriteLine();

                Row valueRow = new();
                valueRow.Append(clist);
                sheetData.Append(valueRow);
            }


            Thread.Sleep(10);
        }

    }
}

三、控制台效果部分截图