如何使用C#的CSVHelper将多个对象写入同一行？

问题说明 #

你期望用CsvHelper将Part和PartsPerList两个对象的字段写入同一行，输出格式如下：

TotalQuantity,description,designator,...,test
10,电阻,R1,...,file1.xlsx
5,电容,C2,...,file2.xlsx

但当前代码会先写完所有Part的行，再写PartsPerList的行，导致两者完全分开。

核心原因 #

WriteRecords<T>会将每个T对象的所有字段作为单独一行写入。分开调用WriteRecords<Part>和WriteRecords<PartsPerList>，必然会生成两个独立的内容区块，无法将两个对象的字段合并到同一行。

解决方案：使用合并DTO类 #

最简洁易维护的方式是创建一个包含两个类所有属性的DTO（数据传输对象），将数据整合后一次性写入。

步骤1：创建合并DTO类 #

public class PartWithListInfo
{
    // 复制Part的所有属性
    public int TotalQuantity { get; set; }
    public string Description { get; set; } = string.Empty;
    public string Designator { get; set; } = string.Empty;
    public string Case { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
    public string Tolerance { get; set; } = string.Empty;
    public string Remark { get; set; } = string.Empty;
    public string PartNumber { get; set; } = string.Empty;
    public string Manufacturer { get; set; } = string.Empty;
    public string ManufacturerNr { get; set; } = string.Empty;
    public string RoHS { get; set; } = string.Empty;
    public string Nachweis { get; set; } = string.Empty;
    // 添加PartsPerList的属性
    public string Listname { get; set; } = string.Empty;
}

步骤2：创建DTO的ClassMap #

public sealed class PartWithListInfoMap : ClassMap<PartWithListInfo>
{
    public PartWithListInfoMap()
    {
        // 复用原Part的映射配置
        Map(m => m.TotalQuantity).Index(0);
        Map(m => m.Description).Name("description");
        Map(m => m.Designator).Name("designator");
        Map(m => m.Case).Name("case");
        Map(m => m.Value).Name("value");
        Map(m => m.Tolerance).Name("tolerance");
        Map(m => m.Remark).Name("remark");
        Map(m => m.PartNumber).Name("partnumber");
        Map(m => m.Manufacturer).Name("manufacturer");
        Map(m => m.ManufacturerNr).Name("Manufactorer number");
        Map(m => m.RoHS).Name("RoHS");
        Map(m => m.Nachweis).Name("Nachweis");
        // 配置Listname的映射
        Map(m => m.Listname).Name("test").Optional();
    }
}

步骤3：修改数据收集与写入逻辑 #

string fileReadDirectory =
    Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "Stuecklisten");
string fileWriteDirectory =
    Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "Stueckliste.csv");
List<string> files = Directory.GetFiles(fileReadDirectory).ToList();
List<PartWithListInfo> partWithListInfos = new List<PartWithListInfo>();

foreach (string file in files)
{
    CsvReader reader = new CsvReader(new ExcelParser(file));
    reader.Context.RegisterClassMap<ExcelSheetMap>();
    IEnumerable<Part>? excelRecords = reader.GetRecords<Part>();
    
    foreach (var record in excelRecords)
    {
        var partWithList = new PartWithListInfo
        {
            TotalQuantity = record.TotalQuantity,
            Description = record.Description,
            Designator = record.Designator,
            Case = record.Case,
            Value = record.Value,
            Tolerance = record.Tolerance,
            Remark = record.Remark,
            PartNumber = record.PartNumber,
            Manufacturer = record.Manufacturer,
            ManufacturerNr = record.ManufacturerNr,
            RoHS = record.RoHS,
            Nachweis = record.Nachweis,
            Listname = file
        };
        
        var existing = partWithListInfos.FirstOrDefault(p => p.ManufacturerNr == record.ManufacturerNr);
        if (existing != null)
        {
            existing.TotalQuantity += record.TotalQuantity;
        }
        else
        {
            partWithListInfos.Add(partWithList);
        }
    }
}

using (var stream = File.Open(fileWriteDirectory, FileMode.Create))
using (var streamWriter = new StreamWriter(stream))
using (var writer = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
    writer.Context.RegisterClassMap<PartWithListInfoMap>();
    writer.WriteHeader<PartWithListInfo>();
    writer.WriteRecords(partWithListInfos);
}

替代方案：手动逐行写入 #

如果不想创建DTO，可手动控制每行的字段写入（需确保两个列表长度一致）：

using (var stream = File.Open(fileWriteDirectory, FileMode.Create))
using (var streamWriter = new StreamWriter(stream))
using (var writer = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
    // 写入合并后的表头
    writer.WriteHeader<Part>();
    writer.WriteField("test");
    writer.NextRecord();
    
    // 逐行写入Part和PartsPerList的字段
    for (int i = 0; i < parts.Count; i++)
    {
        writer.WriteRecord(parts[i]);
        writer.WriteField(partsPerLists[i].Listname);
        writer.NextRecord();
    }
}

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