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