按照表格明智地阅读excel文件 [英] read excel file according sheet wise

问题描述

大家好。



我在阅读excel文件时遇到问题..



我提供Intranet excel文件路径，如



http：//mywebsite/documents/file/excel/myexcelfile.xlsx



文件存在的位置。我想获取excel文件中的所有工作表，并将每个excel工作表数据放入C＃中的不同DataTable中。



此处服务器不是公共服务器。它是Intranet服务器，只在本地我可以访问这个文件。



请帮我解决这个问题。





[从评论中转移]

i我正在使用此代码

Hi to all.,

I have a problem with reading the excel file..

I am providing the Intranet excel file path like

http://mywebsite/documents/file/excel/myexcelfile.xlsx

where the file is existing. I want to get all the sheets in the excel file and get the each excel sheet data into different DataTable in C#.

Here server is not a public server. It is Intranet server, only locally i can access this file.

Please help me out to get this problem.


[MOVED FROM COMMENT]
i am using this code

string XLfile = "http://mywebsite/documents/file/excel/myexcelfile.xlsx";
String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLfile + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
// String sConnectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + XLfile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLfile + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
// String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + XLfile + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = "";
string nam = "";
String[] excelSheets = new String[dt.Rows.Count];
int kk = 0;
foreach (DataRow row in dt.Rows)
{
	nam = row["TABLE_NAME"].ToString();
	nam = nam.Substring(0, nam.Length - 1);
	excelSheets[kk] = nam;
	kk++;
	sheetname = sheetname + ", " + nam;
}
sheetname = sheetname.Substring(1);
objConn.Close();
string sheet = "";
string df = "";
string shetnam = "";
string status;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
// creating new dynamic dataTable
DataTable dt_New = new DataTable();
DataColumn dc; dc = new DataColumn("Numbers");
dt_New.Columns.Add(dc);
dc = new DataColumn("Message");
dt_New.Columns.Add(dc);
for (int j = 0; j < excelSheets.Length; j++)
{
	status = "0";
	selectCommand.CommandText = "SELECT * FROM [" + excelSheets[j] + "$]";
	DbConnection connection = factory.CreateConnection();
	connection.ConnectionString = sConnectionString1;
	selectCommand.Connection = connection;
	adapter.SelectCommand = selectCommand;
	DataSet objDataset11 = new DataSet();
	DataTable dtNumbs = new DataTable();
	adapter.Fill(dtNumbs);
}

如果服务器是公共的，这项工作。但现在它给错误cos服务器不公开。它是一个Intranet服务器。

[/ END]

this workis if the server is public. But now it is giving the error cos server is not public. It is a Intranet Server.
[/END]

推荐答案

;
DbConnection连接=工厂。 CreateConnection（）;
connection.ConnectionString = sConnectionString1;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataSet objDataset11 = new DataSet（）;
DataTable dtNumbs = new DataTable（）;
adapter.Fill（dtNumbs）;
}

"; DbConnection connection = factory.CreateConnection(); connection.ConnectionString = sConnectionString1; selectCommand.Connection = connection; adapter.SelectCommand = selectCommand; DataSet objDataset11 = new DataSet(); DataTable dtNumbs = new DataTable(); adapter.Fill(dtNumbs); }

如果服务器是公共的，这项工作就可以了。但是现在它提供的错误是cos服务器不公开。它是一个Intranet服务器。

[/ END]

this workis if the server is public. But now it is giving the error cos server is not public. It is a Intranet Server.
[/END]

这篇关于按照表格明智地阅读excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！