i am going to upload excel data into list in sharepoint.

Question

Index was out of range. Must be non-negative and less than the size of the collection.

 

Parameter name: index

 

i have got this error.please tell me solution...

 this is my sheet template code...............

 

private void btnvehiclenewsheet_Click(object sender, RibbonControlEventArgs e)

{

try

{

MyExcel.Range readRange;

MyExcel.Application app = null;

app = Globals.ThisAddIn.Application;

Microsoft.Office.Interop.Excel.Workbook theWorkbook;

theWorkbook = app.ActiveWorkbook;

MyExcel.Worksheet sheet = app.ActiveWorkbook.Worksheets.Add();

sheet.Name = "MyAssetSheet";

Microsoft.Office.Interop.Excel.Worksheet theWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)theWorkbook.Worksheets["MyAssetSheet"];

readRange = theWorksheet.UsedRange;

MyExcel.Range headerRange1 = sheet.get_Range("A1");

headerRange1.Value = "Category";

MyExcel.Range headerRange2 = sheet.get_Range("B1");

headerRange2.Value = "AssetID";

MyExcel.Range headerRange3 = sheet.get_Range("C1");

headerRange3.Value = "AssetType";

MyExcel.Range headerRange4 = sheet.get_Range("D1");

headerRange4.Value = "IssuedON";

MyExcel.Range headerRange5 = sheet.get_Range("E1");

headerRange5.Value = "Description";

}

catch (Exception ex)

{

}

}

 

 

 

this is my excel upload code....................

 

using (ClientContext client = new ClientContext(url))

{

client.Credentials = s;

Web web = client.Web;

client.Load(web);

client.ExecuteQuery();

SP.List oList = client.Web.Lists.GetByTitle("Vehicle_Admin");

client.Load(oList);

client.ExecuteQuery();

for (int i = 2; i <= count; i++)

{

assetid = Convert.ToString((readRange.Cells[i, 2] as MyExcel.Range).Value2);

CamlQuery query1 = new CamlQuery();

query1.ViewXml = "<View>"

+ "<Query>"

+ "<Where><Eq><FieldRef Name='AssetID' /><Value Type='Text'>assetid</Value></Eq></Where>"

+ "</Query>"

+ "</View>";

ListItemCollection listItems = oList.GetItems(query1);

client.Load(listItems);

client.ExecuteQuery();

if (listItems.Count> 0)

{

System.Windows.Forms.MessageBox.Show("'" + i + "'th Row having Duplicate Value");

 

}

}

for (int i = 2; i <= (readRange.Rows.Count); i++)

{

category = Convert.ToString((readRange.Cells[i, 1] as MyExcel.Range).Value2);

assetid = Convert.ToString((readRange.Cells[i, 2] as MyExcel.Range).Value2);

assettype = Convert.ToString((readRange.Cells[i, 3] as MyExcel.Range).Value2);

issuedon = DateTime.FromOADate((readRange.Cells[i, 4] as MyExcel.Range).Value2);

description = Convert.ToString((readRange.Cells[i, 5] as MyExcel.Range).Value2);

ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();

ListItem oListItem = oList.AddItem(itemCreateInfo);

oListItem["Category"] = category;

oListItem["AssetID"] = assetid;

oListItem["AssetType"] = assettype;

oListItem["IssuedON"] = issuedon;

oListItem["Description"] = description;

}

System.Windows.Forms.MessageBox.Show("Data Uploaded Successfully");

}

}

catch (Exception ex)

{

//Console.WriteLine("Duplicate Data is not allowed");

}