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();
        }
      }
    }
  }
}
   

No comments:

Post a Comment