<script data-ad-client="ca-pub-7202621014932746" async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
Below code will help you to reading data rows from excel sheet and fetch data from CRM .
Reading data from Microsoft Excel sheet using C#.net code
sample excel data
//Name space need to use
using Excel = Microsoft.Office.Interop.Excel;
public void ReadInvoiceFromExcelAndUpdate()
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\MyData\Sample.xlsx");
try
{
AppLogger logger = new AppLogger();
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = (Excel.Range)xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
// Excel rows count will start form 1 , not from zero (0) , 1 will be header
for (int i = 2; i <= rowCount; i++)
{
logger.LogInfo(i.ToString());
string invoieID = ((Excel.Range)xlWorksheet.Cells[i, 1]).Value;
if (invoieID != null)
{
UpdateInvoiceFromExcel(invoieID);
}
}
}
catch (Exception ex)
{
string err = ex.Message;
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
xlWorkbook.Close();
//quit and release
xlApp.Quit();
}
}
//Fetch invoice data from CRM
public void UpdateInvoiceFromExcel(string invoiceID)
{
try
{
QueryExpression qe = new QueryExpression("invoice")
{
ColumnSet = new ColumnSet("name", "statuscode", "statecode"),
};
qe.Criteria.AddCondition("invoicenumber", ConditionOperator.Equal, invoiceID);
var result = _CrmService.RetrieveMultiple(qe);
if (result.Entities != null && result.Entities.Count > 0)
{
//result.Entities[0];
int invoiceStatusValue = result.Entities[0].GetAttributeValue<OptionSetValue>("statuscode").Value;
int invoiceStateValue = result.Entities[0].GetAttributeValue<OptionSetValue>("statecode").Value;
Guid _invoiceID = result.Entities[0].Id;
if (invoiceStatusValue == 100001 && invoiceStateValue == 2)
{
SetStateRequest request = new SetStateRequest()
{
EntityMoniker = new EntityReference
{
Id = _invoiceID,
LogicalName = "invoice",
},
State = new OptionSetValue(0),
Status = new OptionSetValue(1),
};
_CrmService.Execute(request);
// end set invoice active
//
//
Entity invoiceToUpdate = new Entity("invoice");
invoiceToUpdate.Id = result.Entities[0].Id;
invoiceToUpdate["new_issend"] = true; //has_issend //new_issend
_CrmService.Update(invoiceToUpdate);
//
// set back invoice status
SetStateRequest invocieRequest = new SetStateRequest()
{
EntityMoniker = new EntityReference
{
Id = _invoiceID,
LogicalName = "invoice",
},
State = new OptionSetValue(invoiceStateValue), // State = new OptionSetValue(2),
Status = new OptionSetValue(invoiceStatusValue), //Status = new OptionSetValue(100001),
};
_CrmService.Execute(invocieRequest);
}
else
{
Entity invoiceToUpdate = new Entity("invoice");
invoiceToUpdate.Id = _invoiceID;
invoiceToUpdate["new_issend"] = true;
_CrmService.Update(invoiceToUpdate);
}
}
}
catch (Exception ex)
{
// log.Error(ex);
//throw;
}
}
Reading data from Microsoft Excel sheet using C#.net code
sample excel data
//Name space need to use
using Excel = Microsoft.Office.Interop.Excel;
public void ReadInvoiceFromExcelAndUpdate()
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\MyData\Sample.xlsx");
try
{
AppLogger logger = new AppLogger();
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = (Excel.Range)xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
// Excel rows count will start form 1 , not from zero (0) , 1 will be header
for (int i = 2; i <= rowCount; i++)
{
logger.LogInfo(i.ToString());
string invoieID = ((Excel.Range)xlWorksheet.Cells[i, 1]).Value;
if (invoieID != null)
{
UpdateInvoiceFromExcel(invoieID);
}
}
}
catch (Exception ex)
{
string err = ex.Message;
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
xlWorkbook.Close();
//quit and release
xlApp.Quit();
}
}
//Fetch invoice data from CRM
public void UpdateInvoiceFromExcel(string invoiceID)
{
try
{
QueryExpression qe = new QueryExpression("invoice")
{
ColumnSet = new ColumnSet("name", "statuscode", "statecode"),
};
qe.Criteria.AddCondition("invoicenumber", ConditionOperator.Equal, invoiceID);
var result = _CrmService.RetrieveMultiple(qe);
if (result.Entities != null && result.Entities.Count > 0)
{
//result.Entities[0];
int invoiceStatusValue = result.Entities[0].GetAttributeValue<OptionSetValue>("statuscode").Value;
int invoiceStateValue = result.Entities[0].GetAttributeValue<OptionSetValue>("statecode").Value;
Guid _invoiceID = result.Entities[0].Id;
if (invoiceStatusValue == 100001 && invoiceStateValue == 2)
{
SetStateRequest request = new SetStateRequest()
{
EntityMoniker = new EntityReference
{
Id = _invoiceID,
LogicalName = "invoice",
},
State = new OptionSetValue(0),
Status = new OptionSetValue(1),
};
_CrmService.Execute(request);
// end set invoice active
//
//
Entity invoiceToUpdate = new Entity("invoice");
invoiceToUpdate.Id = result.Entities[0].Id;
invoiceToUpdate["new_issend"] = true; //has_issend //new_issend
_CrmService.Update(invoiceToUpdate);
//
// set back invoice status
SetStateRequest invocieRequest = new SetStateRequest()
{
EntityMoniker = new EntityReference
{
Id = _invoiceID,
LogicalName = "invoice",
},
State = new OptionSetValue(invoiceStateValue), // State = new OptionSetValue(2),
Status = new OptionSetValue(invoiceStatusValue), //Status = new OptionSetValue(100001),
};
_CrmService.Execute(invocieRequest);
}
else
{
Entity invoiceToUpdate = new Entity("invoice");
invoiceToUpdate.Id = _invoiceID;
invoiceToUpdate["new_issend"] = true;
_CrmService.Update(invoiceToUpdate);
}
}
}
catch (Exception ex)
{
// log.Error(ex);
//throw;
}
}