Read Data From Excel Sheet In C#.

public ActionResult ReadExcelData()

{

var sourceFile="C:\\Documents and

                Settings\\Administrator\\Desktop\\Taxonomy_Read.xls";

 

string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

        +sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";

string srcQuery = "Select * from [" + GetExcelSheetNames(

                                   sourceFile)[0] + "]";

OleDbConnection srcConn = new OleDbConnection( srcConnString);

srcConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);

OleDbDataReader readerExcel = objCmdSelect.ExecuteReader(

                              CommandBehavior.CloseConnection);

 

while (readerExcel.Read())

{

var i = 0;

while (i <>

{

var data = readerExcel[i].ToString();

i++;

}

}

readerExcel.Close();

return View("About");

}

 

 

static String[] GetExcelSheetNames(string excelFile)

{

OleDbConnection objConn = null;

System.Data.DataTable dt = null;

try

{

// Connection String. Change the excel file to the file you

// will search.

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

  "Data Source=" + excelFile + ";

   Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.

objConn = new OleDbConnection(connString);

// Open connection with the database.

objConn.Open();

// Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)

{

return null;

}

String[] excelSheets = new String[dt.Rows.Count];

int i = 0;

// Add the sheet name to the string array.

foreach (DataRow row in dt.Rows)

{

excelSheets[i] = row["TABLE_NAME"].ToString();

i++;

}

return excelSheets;

}

catch (Exception ex)

{

return null;

}

finally

{

// Clean up.

if (objConn != null)

{

objConn.Close();

objConn.Dispose();

}

if (dt != null)

{

dt.Dispose();

}

}

}

 

 

 

 

//-----OR Can Use Another Method Below But it requires 2 Dll  -----//

            >> Microsoft.Office.Interop.Excel.dll

          >>Office.dll

public ActionResult ReadExcelData()

{

//Request.Files["EditImgFile"];

Microsoft.Office.Interop.Excel.Application ExcelObj = null;

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

if (ExcelObj == null)

{

//MessageBox.Show("ERROR: EXCEL couldn't be started!");

//System.Windows.Forms.Application.Exit();

}

var sourceFile="C:\\Documents and

                Settings\\Administrator\\Desktop\\Taxonomy_Read.xls";

 

Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

int Ano = 0;

List<string> ParentList = new List<string>();

for(int x = 1; x <= 29; x++)

{

Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+x.ToString(), "I" + x.ToString());

System.Array myvalues = (System.Array)range.Cells.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);

string[] strArray = ConvertToStringArray(myvalues);

for(int i=0;i<=strArray.Length;i++)

{

if (!string.IsNullOrEmpty(strArray[i])&&i==0)

{

ParentList.Clear();

Ano = 0;

// //insert Main Menu

//var Id = insertTaxonomy(strArray[i],-1);

// //Add return id to ParentList

//ParentList.Add(Id.Tostring());

break;

}

if (!string.IsNullOrEmpty(strArray[i]))

{

////insert sub menu

//var Id = insertTaxonomy(strArray[i],ParentList.ToArray()[Ano-1]);

// //Add return id to ParentList

//ParentList.Insert(Ano, Id.Tostring());

ParentList.RemoveRange(Ano + 1, ParentList.Count-Ano);

Ano = 0;

break;

}

Ano++;

}

}

theWorkbook.Close(false, hpf.FileName, false);

return View("About");

}

 

 

public static string[] ConvertToStringArray(System.Array values)

{

// create a new string array

string[] theArray = new string[values.Length];

// loop through the 2-D System.Array and populate the 1-D String Array

for (int i = 1; i <= values.Length; i++)

{

if (values.GetValue(1, i) == null)

theArray[i - 1] = "";

else

theArray[i - 1] = (string)values.GetValue(1, i).ToString();

}

return theArray;

}