Click here to Skip to main content
Rate this: bad
good
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 22: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 at 13-Sep-12 3:34am
   
5'ed! :)
Prasad_Kulkarni at 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 at 21-Oct-13 10:02am
   
what is outtable and what to write for SchemaType.Source
ManojDhobale at 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 7

Below article can export excel (97-2003, 2007, 2010)to datatable, see below code:
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\michelle\my file\FandH.xlsx”);
            Worksheet sheet = workbook.Worksheets[0];
            this.dataGridView1.DataSource = sheet.ExportDataTable();
 
See details here:
import MS Excel to datatable[^]
Excel to Datatable and Datatable to Excel[^]
  Permalink  
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>");
        }
        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();
   }
 
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 at 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
0 OriginalGriff 288
1 ProgramFOX 165
2 Peter Leow 130
3 Sergey Alexandrovich Kryukov 95
4 Fredrik Bornander 85
0 OriginalGriff 288
1 ProgramFOX 165
2 Peter Leow 130
3 Sergey Alexandrovich Kryukov 95
4 Fredrik Bornander 85


Advertise | Privacy | Mobile
Web03 | 2.8.150301.1 | Last Updated 20 Jan 2015
Copyright © CodeProject, 1999-2015
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