how to Convert excel sheet into Dataset in Asp.net with C#

Posted by Johnseelan under ASP.NET on 11/22/2012 | Points: 10 | Views : 14229 | Status : [Member] | Replies : 12
Hi Friends,

i am having data in Excel sheet,
using file upload control i am upload the excel sheet,
actually now i am getting that excel sheet from that upload control and store in to d derive.
after that i am using that path read that excel data and convert to data set.

but my doubt is
with out storing the excel sheet how to convert the excel sheet in to dataset
actually after upload the excel we need to convert the excel sheet in to dataset with out storing excel in local path

it is very urgent so please help me any one.
thanks
john




Responses

Posted by: Kamalakanta.Nayak09 on: 11/22/2012 [Member] Starter | Points: 25

Up
0
Down
public static DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable();
string extention = Path.GetExtension(filePath);
if (extention == ".xls")
{

bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//Looping Total Sheet of Xl File
/*foreach (DataRow schemaRow in schemaTable.Rows)
{
}*/
//Looping a first Sheet of Xl File
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheet + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
}
//conn.Close();
return dtexcel;

}

Thanks,
K.K

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Johnseelan on: 11/22/2012 [Member] Starter | Points: 25

Up
0
Down
hi kamal ,
thanks for ur reply,

do u read my doubt.
actually after upload the excel we need to convert the excel sheet in to dataset with out storing excel in local path

thanks
john

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somu475 on: 11/23/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
I Think it is not possible without copying excel sheet to local path.

Because, Data Source of "Provider=Microsoft.Jet.OLEDB.4.0" requires full path of the uploaded excel file and also Upload control will not provide the full path.

So it is required to store the Excel file in local path before using it...

If I'm wrong, plz let me know as I'm new to Dotnet.

-Somu

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bageshkumarbagi on: 11/24/2012 [Member] Starter | Points: 25

Up
0
Down
hi john
see this code
http://www.dotnetfunda.com/codes/code3340-reading-the-data-from-excel-sheet-in-aspnet.aspx

Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sebas on: 3/1/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,
You can convert Excel file to datatable from stream using this C# Excel Reader:
http://www.eleriumsoft.com/Excel_NET/ExcelReader/Default.aspx
But it is not free. Look at this code:

ExcelWorkbook wb2 = ExcelWorkbook.ReadXLS(FileUploader.PostedFile.InputStream);

DataTable dt = new DataTable();
dt = wb2.Worksheets[0].WriteToDataTable();


Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 7/13/2013 [Member] Starter | Points: 25

Up
0
Down
hello Friend,
i am having data in Excel sheet,
using file upload control i am upload the excel sheet,
actually now i am getting that excel sheet from that upload control and store in to d derive.
after that i am using that path read that excel data and convert to data set.

but my doubt is
with out storing the excel sheet how to convert the excel sheet in to application form

it is very urgent so please help me any one.
thank you
Priya



Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 7/24/2013 [Member] Starter | Points: 25

Up
0
Down
hello friend,
code to read data excel file in C#.net in datatable

Regard
Priya

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thackeray on: 5/14/2014 [Member] Starter | Points: 25

Up
0
Down
Great! This expands my knowledge on excel conversion. My excel converting application supports to conevrt excel to other image and document file formats. It is necessary for me to add more features to it.

http://www.rasteredge.com/how-to/vb-net-imaging/excel-converting/

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lethalmarc on: 10/20/2014 [Member] Starter | Points: 25

Up
0
Down
Try this one...c# using oledb to read from excel to datatable

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm

Lethal


Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oliver32 on: 10/23/2014 [Member] Starter | Points: 25

Up
0
Down
refer this bolg post, it gives a WinForm sample to export data from Excel to datatable, it works fine for me.Hope it can do someone a bit favor as well.
http://janewdaisy.wordpress.com/2011/11/29/c-export-data-from-excel-to-datatable/

regards,
oliver


Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Loisgalvez on: 10/27/2014 [Member] Starter | Points: 25

Up
0
Down

Excel Recovery Software is a powerful tool and embedded with a strong algorithm which can recover your data from damaged, inaccessible and corrupted excel files (.xls and .xlsx ).

Download:- http://www.recoverydeletedfiles.com/excel-file-recovery-tool.html


Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thomasaguilar on: 9/18/2015 [Member] Starter | Points: 25

Up
0
Down
Advanced and graphical user interface tool to recover corrupt or damaged Excel (.xls & .xlsx) files. Its scan corrupt or inaccessible excel files and recover your spreadsheets data.

Try:- http://www.mannatsoftware.com/stellar-phoenix-excel-repair.html


Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response