Wednesday, 26 November 2014

Create Xml from Excel

        private const string FileName = @"C:\myFile.xls";
        private const string XmlFileName = @"c:\\myFile.xml";
private static void CreateXmlFile()
        {           

            DataTable data = new ExcelReader().ReadFirstSheet(FileName);

            var xdoc = new XDocument();

            var root = new XElement("ReportEntitlementMappings");

            foreach (DataRow row in data.Rows)
            {
                var mapping = new XElement("Mapping");
                mapping.SetAttributeValue("From", row[0]);
                mapping.SetAttributeValue("To", row[1]);
                root.Add(mapping);
            }

            xdoc.Add(root);

            xdoc.Save(XmlFileName);
        }

 public interface IExcelReader
    {
        DataTable ReadFirstSheet(string fileName);
        DataTableCollection ReadAllSheets(string fileName);
    }

    public class ExcelReader : IExcelReader
    {
        const string ConnectionStringFormat = "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;";

        public DataTable ReadFirstSheet(string fileName)
        {
           return ReadAllSheets(fileName)[0];
        }

        public DataTableCollection ReadAllSheets(string fileName)
        {
            var adapter = new OleDbDataAdapter("SELECT * FROM [Mappings$]", ConnectionStringFormat.FormatWith(fileName));

            var ds = new DataSet();

            adapter.Fill(ds);

            return ds.Tables;
        }
    }

EPPLUS tool supports reading from Excel 2007 and 2010 files: http://epplus.codeplex.com/

No comments: