ASP.NET with C# and SQL SERVER , some vb.net . asp.net examples, asp.net codes, asp.net programs, c# code, sql server queries, important codes.
Thursday, 18 October 2012
import data from excel to datatable in asp.net with c#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace ImportExcelFileToDataSet
{
class Program
{
public const string EXCEL_97_2003_EXT = ".xls";
public const string EXCEL_2007_EXT = ".xlsx";
private static string GetExcelConnectionString()
{
string excelFileExtention = EXCEL_97_2003_EXT;
string excelFilePath = @"c:\pathToExcelFile.xls";
string excelFileConStr = string.Empty;
switch (excelFileExtention)
{
case EXCEL_97_2003_EXT:
excelFileConStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=Excel 8.0;", excelFilePath);
break;
case EXCEL_2007_EXT:
excelFileConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", excelFilePath);
break;
}
return excelFileConStr;
}
private static DataTable ImportWorkSheetToDataTable(OleDbConnection excelFileConn, string workSheetName)
{
OleDbDataAdapter da = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", workSheetName), excelFileConn);
DataTable dtExcelWorkSheet = new DataTable(workSheetName);
da.Fill(dtExcelWorkSheet);
return dtExcelWorkSheet;
}
static void Main(string[] args)
{
string excelConnectString = GetExcelConnectionString();
using (OleDbConnection oledbExcelConnection = new OleDbConnection(excelConnectString))
{
try
{
oledbExcelConnection.Open();
DataTable excelSheetSchema = oledbExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (excelSheetSchema != null && excelSheetSchema.Rows.Count > 0)
{
DataSet dataSetExcel = new DataSet();
foreach (DataRow workSheet in excelSheetSchema.Rows)
{
string excelWorkSheetName = workSheet["TABLE_NAME"].ToString();
DataTable dtExcelWorkSheet = ImportWorkSheetToDataTable(oledbExcelConnection, excelWorkSheetName);
dataSetExcel.Tables.Add(dtExcelWorkSheet);
}
}
}
catch (Exception ex)
{ throw ex; }
finally
{
if (oledbExcelConnection.State == ConnectionState.Open)
oledbExcelConnection.Close();
}
}
}
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment