read excel file according sheet wise

Question

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 7-Mar-13 2:09am

Jagadish M Vadavatti

Updated 7-Mar-13 9:18am

Maciej Los

v2

Add a Solution

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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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