Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This is my code from which i am able to read excel data without protected view. But I am not able to read the excel file which is in protected view from c# please get me some working code which can read excel in protected view.

DataSet ds = new DataSet();
OleDbConnection conn;
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter adapter;
conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" +
"Data Source= e:\\claimtable1.xls;" +
"Extended Properties=Excel 8.0");
conn.Open();
var zz = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cmd.Connection = conn;

foreach (DataRow dr in dtSheet.Rows)
{
    string sheetName = dr["TABLE_NAME"].ToString();
    if (!sheetName.EndsWith("$"))
        continue;
    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
    DataTable dt = new DataTable();
    dt.TableName = sheetName;
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    da.Fill(dt);
    ds.Tables.Add(dt);

    string Number = dt.Columns[1].ToString();
}
cmd = null;
conn.Close();
Posted
Comments
Richard MacCutchan 17-Dec-14 3:45am    
What do you mean by "read excel in protected view"?
[no name] 17-Dec-14 6:30am    
When I am opening the excel its opening in Protected View because of that I am not able to read data from c#
Richard MacCutchan 17-Dec-14 6:53am    
You still have not explained what you mean by "protected view". You are just reading the file through OleDB, so what error are you seeing?
[no name] 17-Dec-14 8:01am    
When I am trying to read excel file from c# I am getting this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: External table is not in the expected format.
Richard MacCutchan 17-Dec-14 12:16pm    
Why do you believe that means "protected view"? You probably need to step through your code with the debugger to find out exactly where the failure occurs, and what values cause it.

1 solution

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\claimtable1.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Actually if you do not specify IMEX manually, default IMEX behavior is MajorityType. so if in a column we have numeric values for first few rows, then string value for the same column, It treats the column as Numeric & when it encounters string value,it may throw format exception.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900