Click here to Skip to main content
12,509,899 members (56,593 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET
Not able to Import Data From Excel 2003 to a DataTable
Please Help
Posted 12-Sep-12 21:27pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Comments
_Amy 13-Sep-12 3:34am
   
5'ed! :)
Prasad_Kulkarni 13-Sep-12 3:37am
   
Thank you Amit :D
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

A codeproject article: 9 Solutions to Export Data to Excel for ASP.NET[^]
And A related discussion: import MS Excel to datatable[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

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;
        }
    }
  Permalink  
Comments
Ankit Mishra 21-Oct-13 10:02am
   
what is outtable and what to write for SchemaType.Source
ManojDhobale 22-Oct-13 0:48am
   
outTableName is the name for returning datatable.

and for below method "Source" is one of the SchemaType. dont do anything to it
FillSchema(DataTable, SchemaType)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 9

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("
 

");
        //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>

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;
        }
    }
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 12

I hope the following link will help you:- Import data from excel to datatable in asp.net
  Permalink  
Comments
Deepu S Nair 20-Jan-15 2:32am
   
Why are you answering old questions?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 27 May 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100