How to write data from an excel to an xml file

Question

How to write data from an excel to an xml file

Navigation

#1 by (1 votes)

1

I would like to develop a program that has to read excel files and write to an xml file but I'm not sure 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");

c# excel xml

asked by anonymous 27.09.2017 / 17:07

1 answer

How to open PDF in Delphi WebBrowser? I need to add captions to my chart in R

score 1 · Accepted Answer

//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;
}