读取Excel默认工作表导出XML

Excel导出XML比较简单，但是在不修改原Excel文件的情况下，自定义XML 元素，将数据导出，

1.我拿到的Excel 文件中的数据的第一、二、三行数据是要移除了

2.Xml元素要具有灵活性。可以自定义

3.默认读取Excel第一张工作簿，Excel工作簿可以重命名，这个问题要考虑到。

 

1.首先自定义XSD，用来定义要导出XML的文档结构。

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Data" msdata:IsDataSet="true" msdata:UseCurrentLocal="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Row">
<xs:complexType>
<xs:sequence>
<xs:element name="UseraID" type="xs:string" minOccurs="0"/>
<xs:element name="First_Name" type="xs:string" minOccurs="0"/>
<xs:element name="LastName" type="xs:string" minOccurs="0"/>
<xs:element name="Title" type="xs:string" minOccurs="0"/>
<xs:element name="Email" type="xs:string" minOccurs='0'/>
<xs:element name="Mobile" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

2.Excel 数据读取，和Xml导出

读取

namespace ExcelToXML.Util
{
    public static class ExcelToDataSet
    {
        public static DataSet ExcelToDataset(string fileName)
        {
            DataSet dataSet = new DataSet();
            DataSet  ds= new DataSet("Data"); ;
            string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Extended Properties=Excel 8.0;" +
                        "data source=" + fileName;
            dataSet.ReadXmlSchema(AppDomain.CurrentDomain.BaseDirectory + @"Util\XMLSchema.xsd");
            string tableName = GetFirstSheetNameFromExcelFileName(fileName,1);
            using (OleDbConnection oleConnection = new OleDbConnection(strconn))
            {
                try
                {
                    string strCommand = "SELECT * FROM  [" + tableName + "$] "; ;
                    oleConnection.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strCommand, oleConnection);

                    adapter.Fill(ds, "Row");
                  //  ds.Tables[0].Rows.RemoveAt(0);
                  //  ds.Tables[0].Rows.RemoveAt(0);

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        dataSet.Tables[0].Rows.Add(ds.Tables[0].Rows[i].ItemArray);
                    }
                    oleConnection.Close();
                }
                catch (Exception ex) { }
            }
            return dataSet;
        }

        public static string GetFirstSheetNameFromExcelFileName(string filepath, int numberSheetID)
        {
            
            if (!System.IO.File.Exists(filepath))
            {
                return "FileNotFound";
            }
            if (numberSheetID <= 1) { numberSheetID = 1; }
            try
            {
                Microsoft.Office.Interop.Excel.Application obj = default(Microsoft.Office.Interop.Excel.Application);
                Microsoft.Office.Interop.Excel.Workbook objWB = default(Microsoft.Office.Interop.Excel.Workbook);
                string strFirstSheetName = null;

                obj = (Microsoft.Office.Interop.Excel.Application)Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
                objWB = obj.Workbooks.Open(filepath, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                strFirstSheetName = ((Microsoft.Office.Interop.Excel.Worksheet)objWB.Worksheets[1]).Name;

                objWB.Close(Type.Missing, Type.Missing, Type.Missing);
                objWB = null;
                obj.Quit();
                obj = null;
                return strFirstSheetName;
            }
            catch (Exception Err)
            {
                return Err.Message;
            }
        }

    }

导出

            if (string.IsNullOrEmpty(FileName))
            {
                MessageBox.Show("请选择要转换的文件.");
                return;
            }

            string strFileName = System.IO.Path.GetFileNameWithoutExtension(FileName);
            string strFilePath = System.IO.Path.GetDirectoryName(FileName);
            if (string.IsNullOrEmpty(FileName))
            {
                MessageBox.Show("请选择文件");
            }
            else
            {
                DataSet ds = Util.ExcelToDataSet.ExcelToDataset(FileName);
                XmlWriterSettings setting=new XmlWriterSettings();
                setting.Indent = true;//设置缩进
                XmlWriter writer = XmlWriter.Create(strFilePath + @"\" + strFileName + ".xml",setting);
                writer.WriteStartDocument(false);

                writer.WriteStartElement("Data");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    writer.WriteStartElement("Row");
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        writer.WriteStartElement(ds.Tables[0].Columns[j].ToString());
                        writer.WriteCData(ds.Tables[0].Rows[i][j].ToString());
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
                writer.WriteEndElement();
                writer.Flush();
                writer.Close();
                MessageBox.Show("生成成功.");