using System;

using LinqToExcel;

using System.IO;

using System.Linq;

using System.Xml.Serialization;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Text;

using System.Xml;

using System.Data;

​

public class Program

{

    public static void Main()

    {

        //   FROM @xmlPlanilha.nodes('//Planilha/AbaPlanilha/Registro') AS T(a)

        var caminhoPlanilha = "";

        var arquivoExcel = new LinqToExcel.ExcelQueryFactory(caminhoPlanilha);

        SalvarDadosPlanilha(ExcelParaPlanilha(arquivoExcel));

        Console.WriteLine("Hello World");

    }

​

    public static void SalvarDadosPlanilha(Planilha dadosPlanilha)

    {

        var paramXmlPlanilha = new SqlParameter("@xmlPlanilha", System.Data.SqlDbType.Xml)

        {

        Value = new SqlXml(new MemoryStream(Encoding.UTF8.GetBytes(Serializar(dadosPlanilha, new Dictionary<string, string>()

        {

            {"xs1", "http://www.w3.org/2001/XMLSchema"},

            {"xsi1", "http://www.w3.org/2001/XMLSchema-instance"}

        }))))};

        using (SqlConnection conn = new SqlConnection(""))

        {

            using (var cmd = conn.CreateCommand())

            {

                cmd.CommandText = "PROC";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(paramXmlPlanilha);

                cmd.ExecuteNonQuery();

            }

        }

    }

​

    public static string Serializar(object objeto, Dictionary<string, string> namespaces = null)

    {

        string retorno = null;

        if (objeto != null)

        {

            var xmlSerializer = new XmlSerializer(objeto.GetType());

            using (StringWriter sw = new StringWriter())

            {

                XmlWriter xmlW = XmlWriter.Create(sw, new XmlWriterSettings

                {

                Indent = false, OmitXmlDeclaration = true

                }

​

                );

                XmlSerializerNamespaces xmlNamespaces = new XmlSerializerNamespaces();

                foreach (var x in namespaces)

                {

                    xmlNamespaces.Add(x.Key, x.Value);

                }

​

                xmlSerializer.Serialize(xmlW, objeto, xmlNamespaces);

                retorno = sw.ToString();

            }

        }

​

        return retorno;

    }

​

    private static Planilha ExcelParaPlanilha(ExcelQueryFactory arquivoExcel)

    {

        return new Planilha()

        {

        Arquivo = Path.GetFileName(arquivoExcel.FileName), AbasPlanilhas = arquivoExcel.GetWorksheetNames().Select(nomePlanilha => new AbaPlanilha

        {

        Nome = nomePlanilha, Registros = arquivoExcel.Worksheet(nomePlanilha).Select(RowParaRegistroPlanilha).Where(registro => registro != null).ToList()}

​

        ).ToArray()}

​

        ;

    }

​

    private static RegistroPlanilha RowParaRegistroPlanilha(Row linhaExcel, int idx)

    {

        RegistroPlanilha registro = null;

        foreach (var columnName in linhaExcel.ColumnNames)

        {

            var value = linhaExcel[columnName].Value;

            if (value != null && value != DBNull.Value)

            {

                if (registro == null)

                {

                    registro = new RegistroPlanilha()

                    {

                    Linha = idx + 1, Celulas = new List<CelulaPlanilha>()}

​

                    ;

                }

​

                registro.Celulas.Add(new CelulaPlanilha(columnName, value));

            }

        }

​

        return registro;

    }

}

​

[XmlRoot("Planilha")]

public class Planilha

{

    [XmlElement("AbaPlanilha")]

    public AbaPlanilha[] AbasPlanilhas

    {

        get;

        set;

    }

​

    [XmlAttribute()]

    public string Arquivo

    {

        get;

        set;

    }

}

​

public class AbaPlanilha

{

    [XmlAttribute()]

    public string Nome

    {

        get;

        set;

    }

​

    [XmlElement("Registro")]

    public List<RegistroPlanilha> Registros

    {

        get;

        set;

    }

}

​

public class RegistroPlanilha

{

    [XmlAttribute()]

    public int Linha

    {

        get;

        set;

    }

​

    [XmlElement("Celula")]

    public List<CelulaPlanilha> Celulas

    {

        get;

        set;

    }

}

​

public class CelulaPlanilha

{

    public CelulaPlanilha()

    {

    }

​

    public CelulaPlanilha(string coluna, object valor)

    {

        this.Coluna = coluna;

        this.Valor = valor;

    }

​

    [XmlAttribute()]

    public string Coluna

    {

        get;

        set;

    }

​

    public object Valor

    {

        get;

        set;

    }

}