How Load Excel File With Password In C#

Question

I Work On C# Project

I Use Below Code For Import XLS Or XLSX File On DataSet.

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn;
    if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
    {
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
    }
    else
    {
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
    }

    DataSet output = new DataSet();

    using (OleDbConnection conn = new OleDbConnection(strConn))
    {
        conn.Open();

        DataTable schemaTable = conn.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        foreach (DataRow schemaRow in schemaTable.Rows)
        {
            string sheet = schemaRow["TABLE_NAME"].ToString();

            if (!sheet.EndsWith("_"))
            {
                try
                {
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;

                    sheet = sheet.Replace("$", "");
                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                }
            }
        }
    }
    return output;
}

This Code Work Correctly For Load Excel File Without Password. But When Run For Load a File With password Show Under error:

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Now How Change My Code For Load Encrypt File?

You can't open a password-protected Excel workbook in OleDb without first opening it using another method. Have a look here. — Sid Holland
– Sid Holland, Commented Nov 30, 2012 at 16:35

Picrofo Software · Accepted Answer · 2012-11-30 12:27:25Z

1

Please : use this lib:

using Microsoft.Office.Interop.Excel

Please , provide password :

WorkbookObject.Password = password;

Please, Change ConnString:

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + s + ";Password=password;Extended Properties='Excel 8.0;HDR=YES'";

Here, your answer is:

 if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
 {
     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Password=password;Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
 }
 else
 {
     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Password=password;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
 }

edited Nov 30, 2012 at 12:27

Picrofo Software

5,6073 gold badges26 silver badges38 bronze badges

answered Nov 30, 2012 at 12:25

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mahdi Radi Over a year ago

how connect WorkbookObject object to my connection string?

Sid Holland Over a year ago

This answer is using two totally different techniques for access Excel: Interop and OleDb. They don't work together particularly well and you certainly won't be able to set a password in Interop by only connecting to a workbook through OleDb.

Anto S · Accepted Answer · 2016-12-15 07:41:27Z

0

Try for this code:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Password=password;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

but still the exception popped:

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

edited Dec 15, 2016 at 7:41

Anto S

2,4576 gold badges35 silver badges52 bronze badges

answered Dec 15, 2016 at 7:21

High Templar potato

11

Collectives™ on Stack Overflow

How Load Excel File With Password In C#

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related