基于C#实现Excel转指定格式XML的技术求助

API 探路者

2026-8-19

Excel转指定格式XML的实现方案

我使用ASP.NET MVC 4.0搭配C#编写的WCF REST服务，需要将特定结构的Excel文件转换为指定格式的XML。尝试过常规Excel转XML方法，无法得到符合预期的输出，求可行方案。

Excel文件结构

No	Model Year	Make	Model	Engine Size (liters)	Test Group Name	Certification Level	Weight Class	Exhaust Type	Converter Type	Total Converter	Monitored Converter	Conv. Location	Converter Part Type	MFR Part Number	Application Type
1	1996	Acura	Integra	1.8	THN1.8VJGFFK	TIER1	PC	S	TWC	1	1	U	DIRECT FIT	251001	ALL
2	1994	Acura	Integra	2.8	THN1.8VJGFFK	TIER1	PC	S	TWC	1	1	U	DIRECT FIT	251041	ALL
3	1995	Acura	Integra	3.8	THN1.8VJGFFK	TIER1	PC	S	TWC	1	1	U	DIRECT FIT	251041	ALL

期望生成的XML格式

<tns:PART>
    <tns:am_mfr_code>CONV</tns:am_mfr_code>
    <tns:part_type>Pre</tns:part_type>
    <tns:CATALYST_INFORMATION>
        <tns:converter_series>20000</tns:converter_series>
        <tns:cat_part_type>DIRECT FIT</tns:cat_part_type>
        <tns:mfr_part_number>251001</tns:mfr_part_number>
    </tns:CATALYST_INFORMATION>
    <tns:VEHICLE_INFORMATION>
        <tns:make>Acura</tns:make>
        <tns:model>Integra</tns:model>
        <tns:model_year>1996</tns:model_year>
        <tns:BaseVehicleId xsi:nil="true"/>
        <tns:test_group_name>THN1.8VJGFFK</tns:test_group_name>
        <tns:displacement_ldv>1.8</tns:displacement_ldv>
        <tns:application_type>ALL</tns:application_type>
        <tns:carb_ftp_std>TIER1</tns:carb_ftp_std>
        <tns:vehicle_class_code>PC</tns:vehicle_class_code>
        <tns:total_atd_number>1</tns:total_atd_number>
        <tns:catalyst_type_am>TWC</tns:catalyst_type_am>
        <tns:exhaust_type>S</tns:exhaust_type>
        <tns:catalyst_location>U</tns:catalyst_location>
        <tns:monitored_converters>1</tns:monitored_converters>
    </tns:VEHICLE_INFORMATION>
</tns:PART>
<tns:PART>
    <tns:am_mfr_code>CONV</tns:am_mfr_code>
    <tns:part_type>Pre</tns:part_type>
    <tns:CATALYST_INFORMATION>
        <tns:converter_series>20000</tns:converter_series>
        <tns:cat_part_type>UNIVERSAL</tns:cat_part_type>
        <tns:mfr_part_number>251041</tns:mfr_part_number>
    </tns:CATALYST_INFORMATION>
    <tns:VEHICLE_INFORMATION>
        <tns:make>Acura</tns:make>
        <tns:model>Integra</tns:model>
        <tns:model_year>1996</tns:model_year>
        <tns:BaseVehicleId xsi:nil="true"/>
        <tns:test_group_name>THN1.8VJGFFK</tns:test_group_name>
        <tns:displacement_ldv>2</tns:displacement_ldv>
        <tns:application_type>ALL</tns:application_type>
        <tns:carb_ftp_std>TIER1</tns:carb_ftp_std>
        <tns:vehicle_class_code>PC</tns:vehicle_class_code>
        <tns:total_atd_number>1</tns:total_atd_number>
        <tns:catalyst_type_am>TWC</tns:catalyst_type_am>
        <tns:exhaust_type>S</tns:exhaust_type>
        <tns:catalyst_location>U</tns:catalyst_location>
        <tns:monitored_converters>1</tns:monitored_converters>
    </tns:VEHICLE_INFORMATION>
    <tns:VEHICLE_INFORMATION>
        <tns:make>Acura</tns:make>
        <tns:model>RL</tns:model>
        <tns:model_year>1996</tns:model_year>
        <tns:BaseVehicleId xsi:nil="true"/>
        <tns:test_group_name>THN3.5VJGKEK</tns:test_group_name>
        <tns:displacement_ldv>3.5</tns:displacement_ldv>
        <tns:application_type>ALL</tns:application_type>
        <tns:carb_ftp_std>TIER1</tns:carb_ftp_std>
        <tns:vehicle_class_code>PC</tns:vehicle_class_code>
        <tns:total_atd_number>1</tns:total_atd_number>
        <tns:catalyst_type_am>TWC</tns:catalyst_type_am>
        <tns:exhaust_type>S</tns:exhaust_type>
        <tns:catalyst_location>U</tns:catalyst_location>
        <tns:monitored_converters>1</tns:monitored_converters>
    </tns:VEHICLE_INFORMATION>
</tns:PART>

现有C#代码片段

while (i <= oItem)
{
    XmlElement dobEle = doc.CreateElement("tns", "PART", "http://www.arb.ca.gov");
    XmlElement am_mfr_code = doc.CreateElement("tns", "am_mfr_code", "http://www.arb.ca.gov");
    XmlElement part_type = doc.CreateElement("tns", "part_type", "http://www.arb.ca.gov");
    XmlElement CATALYST_INFORMATION = doc.CreateElement("tns", "CATALYST_INFORMATION", "http://www.arb.ca.gov");
    XmlElement converter_series = doc.CreateElement("tns", "converter_series", "http://www.arb.ca.gov");
    XmlElement cat_part_type = doc.CreateElement("tns", "cat_part_type", "http://www.arb.ca.gov");
    XmlElement mfr_part_number = doc.CreateElement("tns", "mfr_part_number", "http://www.arb.ca.gov");

    am_mfr_code.InnerText = "CONV";
    string part_type1 = DropDownList1.SelectedItem.Value;
    part_type.InnerText = part_type1;

    string converter_series1 = text2.Text;
    converter_series.InnerText = converter_series1;
    cat_part_type.InnerText = dt.Rows[i].ItemArray[13].ToString();
    mfr_part_number.InnerText = dt.Rows[i].ItemArray[14].ToString();

    CATALYST_INFORMATION.AppendChild(converter_series);
    CATALYST_INFORMATION.AppendChild(cat_part_type);
    CATALYST_INFORMATION.AppendChild(mfr_part_number);

    dobEle.AppendChild(am_mfr_code);
    dobEle.AppendChild(part_type);
    dobEle.AppendChild(CATALYST_INFORMATION);

    XmlElement VEHICLE_INFORMATION = doc.CreateElement("tns", "VEHICLE_INFORMATION", "http://www.arb.ca.gov");
    XmlElement make = doc.CreateElement("tns", "make", "http://www.arb.ca.gov");
    XmlElement model = doc.CreateElement("tns", "model", "http://www.arb.ca.gov");
    XmlElement model_year = doc.CreateElement("tns", "model_year", "http://www.arb.ca.gov");
    XmlElement BaseVehicleId = doc.CreateElement("tns", "BaseVehicleId", "http://www.arb.ca.gov");
    BaseVehicleId.SetAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance", "true");
    XmlElement Test_Group_Name = doc.CreateElement("tns", "test_group_name", "http://www.arb.ca.gov");
    XmlElement Engine = doc.CreateElement("tns", "displacement_ldv", "http://www.arb.ca.gov");
    XmlElement Application_Type = doc.CreateElement("tns", "application_type", "http://www.arb.ca.gov");
    XmlElement Certification_Level = doc.CreateElement("tns", "carb_ftp_std", "http://www.arb.ca.gov");
    XmlElement Weight_Class = doc.CreateElement("tns", "vehicle_class_code", "http://www.arb.ca.gov");
    XmlElement Total_Converter = doc.CreateElement("tns", "total_atd_number", "http://www.arb.ca.gov");
    XmlElement Converter_Type = doc.CreateElement("tns", "catalyst_type_am", "http://www.arb.ca.gov");
    XmlElement Exhaust_Type = doc.CreateElement("tns", "exhaust_type", "http://www.arb.ca.gov");
    XmlElement Conv_Location = doc.CreateElement("tns", "catalyst_location", "http://www.arb.ca.gov");
    XmlElement Monitored_Converter = doc.CreateElement("tns", "monitored_converters", "http://www.arb.ca.gov");

    make.InnerText = dt.Rows[i].ItemArray[2].ToString();
    model.InnerText = dt.Rows[i].ItemArray[3].ToString();
    model_year.InnerText = dt.Rows[i].ItemArray[1].ToString();
    Test_Group_Name.InnerText = dt.Rows[i].ItemArray[5].ToString();
    Engine.InnerText = dt.Rows[i].ItemArray[4].ToString();
    Application_Type.InnerText = dt.Rows[i].ItemArray[15].ToString();
    Certification_Level.InnerText = dt.Rows[i].ItemArray[6].ToString();
    Weight_Class.InnerText = dt.Rows[i].ItemArray[7].ToString();
    Total_Converter.InnerText = dt.Rows[i].ItemArray[10].ToString();
    Converter_Type.InnerText = dt.Rows[i].ItemArray[9].ToString();
    Exhaust_Type.InnerText = dt.Rows[i].ItemArray[8].ToString();
    Conv_Location.InnerText = dt.Rows[i].ItemArray[12].ToString();
    Monitored_Converter.InnerText = dt.Rows[i].ItemArray[11].ToString();


    VEHICLE_INFORMATION.AppendChild(make);
    VEHICLE_INFORMATION.AppendChild(model);
    VEHICLE_INFORMATION.AppendChild(model); // 重复添加model元素
    VEHICLE_INFORMATION.AppendChild(model_year);
    VEHICLE_INFORMATION.AppendChild(BaseVehicleId);
    VEHICLE_INFORMATION.AppendChild(Test_Group_Name);
    VEHICLE_INFORMATION.AppendChild(Engine);
    VEHICLE_INFORMATION.AppendChild(Application_Type);
    VEHICLE_INFORMATION.AppendChild(Certification_Level);
    VEHICLE_INFORMATION.AppendChild(Weight_Class);
    VEHICLE_INFORMATION.AppendChild(Total_Converter);
    VEHICLE_INFORMATION.AppendChild(Converter_Type);
    VEHICLE_INFORMATION.AppendChild(Exhaust_Type);
    VEHICLE_INFORMATION.AppendChild(Conv_Location);
    VEHICLE_INFORMATION.AppendChild(Monitored_Converter);


    dobEle.AppendChild(VEHICLE_INFORMATION);
    break; // 强制终止循环，仅处理一行数据
}
doc.DocumentElement.AppendChild(dobEle);
i += 1;
}

doc.Save(Page.Server.MapPath("~/Data/output.xml"));
Page.Response.Write("Created Successfuly");
Page.Response.Clear();
Page.Response.ContentType = "application/octect-stream";
Page.Response.AppendHeader("Content-Disposition", "filename=Output.xml");
Page.Response.TransmitFile(Page.Server.MapPath("~/Data/Output.xml"));
Page.Response.End();

可行实现方案

1. 修复现有代码的基础问题

先解决代码中的明显错误：

删除VEHICLE_INFORMATION.AppendChild(model);的重复行，避免XML出现冗余节点
移除循环内的break;语句，确保所有Excel行都被处理
将doc.DocumentElement.AppendChild(dobEle);移至循环内部，保证每个<tns:PART>都被添加到文档中

2. 实现核心分组逻辑

从期望XML可以看出，同一个MFR Part Number对应一个<tns:PART>节点，包含多个<tns:VEHICLE_INFORMATION>。需先对Excel数据按零件编号分组：

var groupedParts = dt.AsEnumerable()
    .GroupBy(row => row.Field<string>("MFR Part Number"))
    .ToList();

3. 使用LINQ to XML简化实现（推荐）

相比XmlDocument，LINQ to XML代码更简洁易维护，适合生成结构化XML：

XNamespace tns = "http://www.arb.ca.gov";
XNamespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
string partType = DropDownList1.SelectedItem.Value;
string converterSeries = text2.Text;

XDocument xmlDoc = new XDocument(
    new XElement(tns + "Parts",
        from groupItem in groupedParts
        select new XElement(tns + "PART",
            new XElement(tns + "am_mfr_code", "CONV"),
            new XElement(tns + "part_type", partType),
            new XElement(tns + "CATALYST_INFORMATION",
                new XElement(tns + "converter_series", converterSeries),
                new XElement(tns + "cat_part_type", groupItem.First().Field<string>("Converter Part Type")),
                new XElement(tns + "mfr_part_number", groupItem.Key)
            ),
            from row in groupItem
            select new XElement(tns + "VEHICLE_INFORMATION",
                new XElement(tns + "make", row.Field<string>("Make")),
                new XElement(tns + "model", row.Field<string>("Model")),
                new XElement(tns + "model_year", row.Field<int>("Model Year")),
                new XElement(tns + "BaseVehicleId", new XAttribute(xsi + "nil", "true")),
                new XElement(tns + "test_group_name", row.Field<string>("Test Group Name")),
                new XElement(tns + "displacement_ldv", row.Field<double>("Engine Size (liters)")),
                new XElement(tns + "application_type", row.Field<string>("Application Type")),
                new XElement(tns + "carb_ftp_std", row.Field<string>("Certification Level")),
                new XElement(tns + "vehicle_class_code", row.Field<string>("Weight Class")),
                new XElement(tns + "total_atd_number", row.Field<int>("Total Converter")),
                new XElement(tns + "catalyst_type_am", row.Field<string>("Converter Type")),
                new XElement(tns + "exhaust_type", row.Field<string>("Exhaust Type")),
                new XElement(tns + "catalyst_location", row.Field<string>("Conv. Location")),
                new XElement(tns + "monitored_converters", row.Field<int>("Monitored Converter"))
            )
        )
    )
);

// 保存并输出XML
string outputPath = Page.Server.MapPath("~/Data/output.xml");
xmlDoc.Save(outputPath);

Page.Response.Clear();
Page.Response.ContentType = "application/xml";
Page.Response.AppendHeader("Content-Disposition", "attachment; filename=Output.xml");
Page.Response.TransmitFile(outputPath);
Page.Response.End();

4. Excel读取注意事项

确保Excel数据读取准确：

若使用OleDb，注意连接字符串区分.xls和.xlsx格式
推荐使用EPPlus库读取Excel，避免OleDb的版本兼容性问题

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

基于C#实现Excel转指定格式XML的技术求助

Excel文件结构 #

期望生成的XML格式 #

现有C#代码片段 #

可行实现方案 #

1. 修复现有代码的基础问题 #

2. 实现核心分组逻辑 #

3. 使用LINQ to XML简化实现（推荐） #

4. Excel读取注意事项 #