需求如下:
有一组变量,数量不确定,变量的取值是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; } } }ExportDataDriverTemplateData
二、主程序文件
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); } } }View Code
三、控制台效果部分截图
标签:输出,Name,表格,多组,cells,int,var,new,CellValue From: https://www.cnblogs.com/lucienbao/p/18542348/datacombinations