Click here to Skip to main content
14,865,088 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
C#
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);
}

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]
Posted
Updated 7-Mar-13 9:18am
v2
Comments
phil.o 7-Mar-13 8:10am
   
And what is the problem exactly ? Can you post some relevant code ?
Jagadish M Vadavatti 7-Mar-13 8:26am
   
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);
}

this workis if the server is public. But now it is giving the error cos server is not public. It is a Intranet Server.
ZurdoDev 7-Mar-13 8:17am
   
What exactly is the problem? You can't access the Sheets or you can't access the file?
Jagadish M Vadavatti 7-Mar-13 8:22am
   
ya i cant access the file cos of the sever where the file is existing, that server is not public server, it is intranet server.. So for that i need code.
ZurdoDev 7-Mar-13 8:26am
   
I'm confused. When you open a file it doesn't matter where it is physically as long as you can access it. So, if it is on the intranet just open it from there. What code are you using to open the file?
Jagadish M Vadavatti 7-Mar-13 8:27am
   
dont get confuse see the code i m using


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

this works if the server is public. But now it is giving the error cos server is not public. It is a Intranet Server.
ZurdoDev 7-Mar-13 8:28am
   
Is it on the intranet that your code is executing from?
Jagadish M Vadavatti 7-Mar-13 8:31am
   
ya the code also executes in intranet only.
ZurdoDev 7-Mar-13 8:32am
   
So, just change the path to be the intranet path.
Jagadish M Vadavatti 7-Mar-13 8:34am
   
ya path also intranet path only..
ZurdoDev 7-Mar-13 8:36am
   
So, what's the problem? I'm sorry, but you are not making any sense. If it is on an intranet, then just supply the intranet path. Where's the issue?
Jagadish M Vadavatti 7-Mar-13 8:37am
   
ok
Maciej Los 7-Mar-13 15:19pm
   
Next time use "Improve question" widget, OK?
ZurdoDev 7-Mar-13 8:27am
   
If you can't access it, you can't access it. There's no code to get around that.
Jagadish M Vadavatti 7-Mar-13 8:28am
   
it is giving the Invalid Internet address error. so

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