How to write data from an excel to an xml file

Question

I would like to be developing a program that has to read excel files and write to an xml file but I don’t know how to write the data to the xml file.

I’m reading the excel file like this :

var fileName = @"C:\Users\HP8200\Desktop\test.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;

var conexao = new System.Data.OleDb.OleDbConnection(connectionString);

var sql = "SELECT * FROM [PARAC1$]";

var cmm = new System.Data.OleDb.OleDbCommand(sql, conexao);
var dt = new System.Data.DataTable();

conexao.Open();

System.Data.OleDb.OleDbDataReader dr = cmm.ExecuteReader();
dt.Load(dr);

conexao.Close();

and I’m writing the xml file like this :

XmlDocument doc = new XmlDocument();
XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
doc.AppendChild(docNode);

XmlNode ejournal = doc.CreateElement("EJournal");
doc.AppendChild(ejournal);

XmlNode dealer = doc.CreateElement("Dealer");
XmlAttribute valueID = doc.CreateAttribute("ID");
valueID.Value = dealerID;
dealer.Attributes.Append(valueID);
ejournal.AppendChild(dealer);

XmlNode PeriodBegin = doc.CreateElement("PeriodBegin");
PeriodBegin.AppendChild(doc.CreateTextNode(data));
dealer.AppendChild(PeriodBegin);

XmlNode PeriodEnd = doc.CreateElement("PeriodEnd");
PeriodEnd.AppendChild(doc.CreateTextNode(data));
dealer.AppendChild(PeriodEnd);

XmlNode Transaction = doc.CreateElement("Transaction");
dealer.AppendChild(Transaction);

//inicio da transaction\\

XmlNode CardNumber = doc.CreateElement("CardNumber");
CardNumber.AppendChild(doc.CreateTextNode("7710007007379"));
Transaction.AppendChild(CardNumber);
doc.Save(@"C:\Users\HP8200\Desktop\pedro1123.xml");

Answer 1

//using System.Xml;
//using System.Data;
//using System.Data.OleDb;

private void ExcelToXML()
{

    var fileNameExcel = @"C:\Desenv\Pasta1.xlsx";
    var fileNameXML = @"C:\Desenv\Pasta1.xml";

    var dt = LerPlanilhaExcel(fileNameExcel,"[Plan1$]");

    using (XmlWriter writer = XmlWriter.Create(fileNameXML))
    {
        writer.WriteStartDocument();
        writer.WriteStartElement("Plan1");

        foreach (DataRow row in dt.Rows)
        {
            writer.WriteStartElement("row");

            foreach (DataColumn col in dt.Columns)
            {
                writer.WriteElementString(col.ColumnName, row[col.Ordinal].ToString());
            }

            writer.WriteEndElement();
        }

        writer.WriteEndElement();
        writer.WriteEndDocument();
    }               
}


private DataTable LerPlanilhaExcel(string fileNameExcel, string workbook)
{
    var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileNameExcel + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\""; ;

    var conexao = new OleDbConnection(connectionString);

    var sql = "SELECT * FROM " + workbook;

    var cmm = new OleDbCommand(sql, conexao);
    var dt = new DataTable();

    conexao.Open();

    OleDbDataReader dr = cmm.ExecuteReader();
    dt.Load(dr);

    conexao.Close();
    conexao.Dispose();

    return dt;
}