Odtnhj

I have three four columns in excelsheet such as Firstname, LastName, Email,Phone no also.

I am using Asp.net with C#.

I need to Import data from Excel sheet to DataTable in asp.net with C#.

I have 100000 records in excel sheet. how to use while upload excel sheet?

If any one knows that, please let me know.

Thanks.

Use the following code:

public static DataTable exceldata(string filePath)
 
 DataTable dtexcel = new DataTable();
 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 [" + sheet3 + "]";
 OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
 dtexcel.Locale = CultureInfo.CurrentCulture;
 daexcel.Fill(dtexcel);
 

 conn.Close();
 return dtexcel;

 

Source: http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data-into-DataTable

You may also refer the following question: Importing Excel into a DataTable Quickly if you wish to import faster.

I'm not sure if this will work in ASP.NET but it works in WPF so maybe there's something you can take from it?

Anyway, at the global scope:

Microsoft.Office.Interop.Excel.Application xls;

Then to select and read a spreadsheet:

 private void readSheet()
 *xls;.xlsx";
 var browsefile = openfile.ShowDialog();

 if (browsefile == true)
 
 string path = openfile.FileName;

 xls = new Microsoft.Office.Interop.Excel.Application();

 // Dynamic File Using Uploader... Note the readOnly flag is true
 Workbook excelBook = xls.Workbooks.Open(path, 0, true, 5, "", "", true, XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
 Worksheet excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1); ;
 Range excelRange = excelSheet.UsedRange;

 // Make default cell contents
 string strCellData = String.Empty;
 double douCellData;

 // Initialise row and column
 int rowCnt, colCnt = 0;

 // Initialise DataTable
 System.Data.DataTable dt = new System.Data.DataTable();

 // Loop through first row of columns to make header
 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 
 string strColumn = "";
 strColumn = Convert.ToString((excelRange.Cells[1, colCnt] as Range).Value2);
 var Column = dt.Columns.Add();
 Column.DataType = Type.GetType("System.String");

 // Check & rename for duplicate entries
 if (dt.Columns.Contains(strColumn))
 Column.ColumnName = (strColumn + ", " + colCnt);
 else
 Column.ColumnName = strColumn;
 
 dt.AcceptChanges();

 // Fill in the rest of the cells
 for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
 
 string strData = "";
 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 
 try
 ";
 
 catch (Exception ex)
 
 douCellData = (excelRange.Cells[rowCnt, colCnt] as Range).Value2;
 strData += douCellData.ToString() + "
 
 strData = strData.Remove(strData.Length - 1, 1);
 dt.Rows.Add(strData.Split('

 dtGrid.ItemsSource = dt.DefaultView;
 try
 
 excelBook.Close(true, null, null);
 
 catch (System.Runtime.InteropServices.COMException comEX)
 
 Console.Write("COM Exception: " + comEX.ToString());
 
 xls.Quit();