public ActionResult categoryimp()
{
_Workbook workBook = null;
_Worksheet workSheet = null;
Range cellsRange = null;
Range columnRange = null;
Range rowRange = null;
int numberOfColumns = 0;
int numberOfRows = 0;
var excelApp = new Application();
List<Categorydescription> obj = new List<Categorydescription>();
List<Categorydescription> items = new List<Categorydescription>();
try
{
workBook = excelApp.Workbooks.Open("D:/excelnew", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
workSheet = (Worksheet)workBook.Worksheets.get_Item(1);
cellsRange = workSheet.Cells;
columnRange = cellsRange.Columns;
rowRange = cellsRange.Rows;
numberOfColumns = columnRange.Count;
numberOfRows = rowRange.Count;
Range objRange = null;
object[] data = null;
int totalColumns = workSheet.UsedRange.Cells.Columns.Count + 1;
//Iterating from row 2 because first row contains HeaderNames
Categorydescription item = new Categorydescription();
for (int row = 2; row < workSheet.UsedRange.Cells.Rows.Count; row++)
{
item = new Categorydescription();
data = new object[totalColumns - 1];
for (int col = 1; col < totalColumns; col++)
{
objRange = workSheet.Cells[row, col];
if (objRange.MergeCells)
{
data[col - 1] = Convert.ToString(((Range)objRange.MergeArea[1, 1]).Text).Trim();
}
else
{
data[col - 1] = Convert.ToString(objRange.Text).Trim();
}
if (col == 1)
{
item.city= Convert.ToString(objRange.Text).Trim();
}
if (col == 2)
{
item.belts= Convert.ToString(objRange.Text).Trim();
}
if (col == 3)
{
item.Metadescription = Convert.ToString(objRange.Text).Trim();
}
}
items.Add(item);
}
string s = String.Format(
"There are {0} columns and {1} rows in the Worksheet",
numberOfColumns, numberOfRows);
}
finally
{
}
CategorydescDataContext db = new CategorydescDataContext();
db.Categorydescriptions.InsertAllOnSubmit(items);
db.SubmitChanges();
return View();
}
}
}