如何从excel读取数据，转换为数据表或数据集？ [英] How to read data from excel, convert to datatable or dataset?

问题描述

http://csharpcorner.mindcrackerinc.netdna-cdn.com/forums/uploadfile/50cef0/11092017011035AM/ipmsgclip_s_1510206908_0.png

http://csharpcorner.mindcrackerinc.netdna-cdn.com/forums/uploadfile/50cef0/11092017011035AM/ipmsgclip_s_1510206908_0.png

when i open the excel file above error is occurs, and another error inside the excel opening(Excel was able to open the file by repairing or removing the unreadable content) i click the yes and save as another file and upload the excel.but after the upload many lines are ignored how to fix it.

我的尝试：

What I have tried:

i follow some online solutions and apply but not working.
if i copy the data and paste to new excel doc and upload no problem is occurs successfully uploaded.

推荐答案

DataTable dtResult = null;

int totalSheet = 0; // excel文件中没有工作表

使用（OleDbConnection objConn = new OleDbConnection（@

Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ FileName +;扩展属性='Excel 12.0; HDR = YES; IMEX = 1;';））

{

objConn.Open（）;

OleDbCommand cmd = new OleDbCommand（）;

OleDbDataAdapter oleda = new OleDbDataAdapter（）;

DataSet ds = new DataSet（）;

DataTable dt = objConn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）;

string sheetName = string.Empty;

if（dt！= null）

{

var tempDataTable =（来自dataRow in dt.AsEnumerable（）

where！dataRow [TABLE_NAME]。ToString（）。包含（FilterDatabase）

select dataRow）.CopyToDataTable（）;

dt = tempDataTable;

totalSheet = dt.Rows.Count;

sheetName = dt.Rows [0] [TABLE_NAME]。ToString（）;

}

cmd.Connection = objConn;

cmd.CommandType = CommandType.Text;

cmd.CommandText =SELECT * FROM [+ sheetName +];

oleda = new OleDbDataAdapter（cmd）;

oleda.Fill（ds，excelData）;

dtResult = ds.Tables [excelData];

objConn.Close（）;

返回dtResult; //返回数据表

}

DataTable dtResult = null;
int totalSheet = 0; //No of sheets on excel file
using(OleDbConnection objConn = new OleDbConnection(@
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where!dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
dtResult = ds.Tables["excelData"];
objConn.Close();
return dtResult; //Returning Dattable
}

这篇关于如何从excel读取数据，转换为数据表或数据集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！