从c＃中读取Excel文件 [英] Read Excel file from c#

问题描述

这是我的代码，我可以从中读取没有受保护视图的Excel数据。但是我无法从c＃中读取受保护视图中的excel文件，请给我一些可以在受保护视图中读取excel的工作代码。

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();

推荐答案

））
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（）;

")) 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();

OLEDB; Provider = Microsoft.Jet.OLEDB.4.0; Data Source = e ：\\claimtable1.xls;

扩展属性=Excel 8.0; HDR =是; IMEX = 1;



其实如果你没有手动指定IMEX，默认的IMEX行为是MajorityType。所以如果在列中我们有前几行的数值，那么同一列的字符串值，它将列视为数字&当它遇到字符串值时，它可能会抛出格式异常。

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.

这篇关于从c＃中读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！