1

I tried to convert excel file to XML file. And I did it successfully. However when I run the APP on another PC (where I dont have SQL server manager), it is not working anymore. I receive this error

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine

which I tried to solve with every solved question here (installing Microsoft Access Database Engine 2016 Redistributable, converting app into x86, etc...) and nothing worked.

But since I don't really need (or at least I think so) Access, is there any other way, how to convert excel file to xml?

Here is to code

XmlDocument doc = new XmlDocument();

try
{               
    bool test = Environment.Is64BitProcess;
    FileName.Content = filelocation.Substring(filelocation.LastIndexOf(@"\")+1);

    if (!test)
    {
        connStr = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";                    
    }               

    OleDbConnection conn = new OleDbConnection(connStr);
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataTable dt = new DataTable();

To here it is working, here is the issue

    conn.Open();
    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string sheetName = dtSheet.Rows[0]["table_name"].ToString();
    cmd.CommandText = "select * from [" + sheetName + "]";
    da.SelectCommand = cmd;
    da.Fill(dt);
    conn.Close();

And then I continue...

    DataSet ds = new DataSet();                
    ds.Tables.Add(dt);                
    oItem = dt.Rows.Count;
    oItem -= 1;

    XmlDeclaration declaire = doc.CreateXmlDeclaration("1.0", "utf-8", null);
    declaire.Standalone = "yes";                
    XmlElement rootnode = doc.CreateElement("Wiring");                    
    doc.InsertBefore(declaire, doc.DocumentElement);
    doc.AppendChild(rootnode);                

    while (i <= oItem)
    {
        XmlElement dobEle = doc.CreateElement("Machine");

        do
        {...

Is there any other way how to solve the problematic code?

EDIT I have installed Office on both PCs. And I have tried .Ace.OLEDB.x (4-23 [just to be sure]) and on PC where I develop the APP v.12 and v.16 works. On the PC where I deployed the app none of them works

Edit 2 So I just found out I have office 365 on the 2nd computer but not fully licensed Access (for whatever reason). Could someone confirm, if that's the reason why it doesn't work?

Iksd
  • 13
  • 4
  • Microsoft Excel Interop (https://www.dotnetperls.com/excel) or try one of the many .Net libraries that are available for reading Excel files. https://stackoverflow.com/questions/5852678/net-excel-library-that-can-read-write-xls-files – PaulF Sep 13 '22 at 09:13
  • DO NOT USE INTEROP. It is very slow and hard to use. ACE should work. As gets put into the Program Files folder and needs to be same version as Office installed or the version of ACE installed. This need to match the version : Provider=Microsoft.ACE.OLEDB.16.0 SQL Server is a different database from Excel. When you deploy app on another computer it either has to have OFFICE installed or ACE. Office automatically has ACE installed. But the version of ACE has to match the connection string. – jdweng Sep 13 '22 at 09:25
  • Thank you for your replies. Just checked version on the PC and it says Version 2202 which when I tried to look up is supported by .ACE.OLEDB.16.0 and 12.0 aswell. – Iksd Sep 13 '22 at 09:55

1 Answers1

-1

simple and easy excel file reader. https://github.com/ExcelDataReader/ExcelDataReader

i believe u can also add it from nuget

antikern
  • 48
  • 6