Solved

import excel to .net C#

Posted on 2013-01-26
5
302 Views
Last Modified: 2013-02-06
Hello,
I would like to import an excel document. The excel document contains 3 columns (Id, Name, Description).  I would like to show the data in a GridView.  I am using C#. Can you show an example how this is done?
0
Comment
Question by:johnkainn
5 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 38822151
Hi John,
What kind of gridview? winforms, wpf, asp?

If ASP (I am just guessing), you can read: http://www.geekcamp.us/articles/import_data_from_excel_into_gridview_using_aspnet.aspx
0
 
LVL 6

Expert Comment

by:esolve
ID: 38822706
Sorry I am not at my development PC now but this should get you started.

http://social.msdn.microsoft.com/Forums/da-DK/csharpgeneral/thread/45141eb3-9010-455c-a095-e4f4f709ca08
http://forums.asp.net/t/1536177.aspx


Just ensure that you're using the correct connectionstring.
"provider=Microsoft.ACE.OLEDB.12.0;data source=" + filepath + ";extended properties='Excel 12.0;HDR=YES;'";
0
 

Assisted Solution

by:azizoglua
azizoglua earned 166 total points
ID: 38823980
try this for winforms, for web just open last comment (dataGridView1.DataBindind();)

string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\excel.xlsx;Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT Id, Name, Description FROM [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
// dataGridView1.DataBindind();
0
 
LVL 10

Assisted Solution

by:Monica P
Monica P earned 167 total points
ID: 38825897
Have excel sheet with following headings UserName,Education,Location with data as below

UserName],[Education],[Location]
aaaa              MBA           chennai
bbbb             MCA            bangalore


Asp code

<asp:FileUpload ID="fileuploadExcel" runat="server" />&nbsp;&nbsp;
<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />
<asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>

Open in new window



protected void btnImport_Click(object sender, EventArgs e)
{
string connString = "";
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
string path = fileuploadExcel.PostedFile.FileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
grvExcelData.DataSource = ds.Tables[0];
grvExcelData.DataBind();
da.Dispose();
conn.Close();
}

Open in new window

0
 
LVL 13

Accepted Solution

by:
jonnidip earned 167 total points
ID: 38834412
I would STRONGLY suggest you to take a look at: ExcelDataReader.
It can be used as any IDataReader and also lets you read any xls/xlsx format into DataTable.

I am sure you will not be disappointed!

Regards.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now