Import and Export Data From Excel to DataTable in Asp.net C# 2.0

Question

1.00/5 (1 vote)

See more:

ASP.NET

Not able to Import Data From Excel 2003 to a DataTable
Please Help

Posted 12-Sep-12 21:27pm

agnelraj

Add a Solution

6 solutions

Add a Solution

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)

Prasad_Kulkarni · Answer 1 · 2012-09-12T21:30:00

Solution 1

Have a look:
Excel Data into datatable[^]
import MS Excel to datatable[^]

Export a DataTable to Excel in ASP.NET[^]

Following link shows how to import or export DataTable to Excel or HTML files by using GemBox.Spreadsheet .NET component.
Import or Export DataTable to Excel[^]

Posted 12-Sep-12 21:30pm

Prasad_Kulkarni

Comments

_Amy 13-Sep-12 3:34am

5'ed! :)

Prasad_Kulkarni 13-Sep-12 3:37am

Thank you Amit :D

Pandvi · Answer 2 · 2012-09-12T21:32:00

Solution 2

A codeproject article: 9 Solutions to Export Data to Excel for ASP.NET[^]
And A related discussion: import MS Excel to datatable[^]

Posted 12-Sep-12 21:32pm

Pandvi

_Amy · Answer 3 · 2012-09-12T21:34:00

Refer the links below:
DataSet to Excel File Conversion using ExcelLibrary[^]
Import Data from Excel to DataTable[^]
This discussion[^] might also be of some interest to you.

View the similar threads:
How to import Data from a Excel to dataset in asp.net page in c[^]
Export DataTable to Excel File in asp.net[^]

--Amit

ManojDhobale · Answer 4 · 2012-12-04T01:26:00

Simple code to do this

public DataTable GetTable(string filename, string SheetName, string outTableName)
    {
        try
        {
            string Con = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                        @"Data Source=" + filename + ";" +
                        @"Extended Properties=" + Convert.ToChar(34).ToString() +
                        @"Excel 8.0;" + "Imex=2;" + "HDR=Yes;" + Convert.ToChar(34).ToString();
            OleDbConnection oleConn = new OleDbConnection(Con);
            oleConn.Open();
            OleDbCommand oleCmdSelect = new OleDbCommand();
            oleCmdSelect = new OleDbCommand(
                    @"SELECT * FROM ["
                    + SheetName
                    + "$" + "]", oleConn);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
            oleAdapter.SelectCommand = oleCmdSelect;
            DataTable dt = new DataTable(outTableName);
            oleAdapter.FillSchema(dt, SchemaType.Source);
            oleAdapter.Fill(dt);
            oleCmdSelect.Dispose();
            oleCmdSelect = null;
            oleAdapter.Dispose();
            oleAdapter = null;
            oleConn.Dispose();
            oleConn = null;
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

ManojDhobale · Answer 5 · 2013-01-15T21:19:00

C#

public void DtToExcel(DataTable table, string filename)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.Write(@");
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename="+filename+".xls");
        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
        //sets font
        HttpContext.Current.Response.Write("<font style="font-size:10.0pt; font-family:Calibri;">");
        HttpContext.Current.Response.Write("<br><br><br>");
        //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
        HttpContext.Current.Response.Write("<table border="1" bgcolor="#FFFFFF" hold=" />          " bordercolor="#000000" cellspacing="0" cellpadding="0" style="font-size:10.0pt; font-family:Calibri; background:white;"> <tr>");
       
        for (int j = 0; j < table.Columns.Count; j++)
        {      //write in new column
            HttpContext.Current.Response.Write("<td>");
            //Get column headers  and make it as bold in excel columns
            HttpContext.Current.Response.Write("");
HttpContext.Current.Response.Write(table.Columns[j].ColumnName.ToString());
            HttpContext.Current.Response.Write("");
            HttpContext.Current.Response.Write("</td>");
        }
        HttpContext.Current.Response.Write("</tr>");
        foreach (DataRow row in table.Rows)
        {//write in new row
            HttpContext.Current.Response.Write("<tr>");
            for (int i = 0; i < table.Columns.Count; i++)
            {
                HttpContext.Current.Response.Write("<td>");
                HttpContext.Current.Response.Write(row[i].ToString());
                HttpContext.Current.Response.Write("</td>");
            }
            HttpContext.Current.Response.Write("</tr>");
        }
        HttpContext.Current.Response.Write("</table>");
        HttpContext.Current.Response.Write("</br></br></br></font>");
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
   }

C#

public DataTable GetTable(string filename, string SheetName, string outTableName)
    {
        try
        {
            string Con = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                        @"Data Source=" + filename + ";" +
                        @"Extended Properties=" + Convert.ToChar(34).ToString() +
                        @"Excel 8.0;" + "Imex=2;" + "HDR=Yes;" + Convert.ToChar(34).ToString();
            OleDbConnection oleConn = new OleDbConnection(Con);
            oleConn.Open();
            OleDbCommand oleCmdSelect = new OleDbCommand();
            oleCmdSelect = new OleDbCommand(
                    @"SELECT * FROM ["
                    + SheetName
                    + "$" + "]", oleConn);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
            oleAdapter.SelectCommand = oleCmdSelect;
            DataTable dt = new DataTable(outTableName);
            oleAdapter.FillSchema(dt, SchemaType.Source);
            oleAdapter.Fill(dt);
            oleCmdSelect.Dispose();
            oleCmdSelect = null;
            oleAdapter.Dispose();
            oleAdapter = null;
            oleConn.Dispose();
            oleConn = null;
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

neethumohan2015 · Answer 6 · 2015-01-19T20:30:00

Solution 12

I hope the following link will help you:- Import data from excel to datatable in asp.net

Posted 19-Jan-15 20:30pm

neethumohan2015

Comments

Deepu S Nair 20-Jan-15 2:32am

Why are you answering old questions?