0
Reply

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

Abhay Krishna

Abhay Krishna

Nov 14 2014 1:39 AM
734
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");
}