C# Programming
08-24-2009, 04:41 PM
Hi all,
I've been given source code for a program that created a form post from values within an excel document, however I keep getting the following errors:
Property, indexer, or event 'Value' is not supported by the ********; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'
I've had a root through the MSDN site, and cant seem to work out whats wrong:
private void btnBatchExecute_Click(object sender, System.EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
string method;
string postString;
string[] values;
string returnString;
XmlDocument xDoc = new XmlDocument();
try
{
//Start Excel and get Application object.
xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
string thisFileName = txtFileName.Text;
xlWorkbook = xlApp.Workbooks.Open(thisFileName,
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);
xlWorkSheet = (Excel.Worksheet)xlWorkbook.ActiveSheet;
//int i = 5;
for(int i = 5;;i++)
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,2],xlWorkSheet.Cells[i,52]);
values = ConvertToStringArray((System.Array)xlRange.Cells.Value);
if(values[0]=="") break;
postString = "ESERIES_FORM_ID=AUTHENTICATE_3&CTRL_TYPEINDICATOR=C&CTRL_SEARCHCONSENT=Y&CTRL_CHANNEL=I&CTRL_CALLTYPE=I";
// Main App Name
postString+="&NAME_TITLE="+values[0];
postString+="&NAME_FORENAME="+values[1];
postString+="&NAME_INITIALS="+values[2];
postString+="&NAME_SURNAME="+values[3];
postString+="&NAME_SUFFIX="+values[4];
postString+="&NAME_DATEOFBIRTH_DD="+("0"+values[5]).Substring(("0"+values[5]).Length-2);
postString+="&NAME_DATEOFBIRTH_MM="+("0"+values[6]).Substring(("0"+values[6]).Length-2);
postString+="&NAME_DATEOFBIRTH_CCYY="+values[7];
// postString+="&NAME_DATEOFBIRTH="+values[7]+("0"+values[6]).Substring(("0"+values[6]).Length-2)+("0"+values[5]).Substring(("0"+values[5]).Length-2);
// Alias - need to check if title supplied first
if(values[8]!="")
{
postString+="&NAME_TITLE="+values[8];
postString+="&NAME_FORENAME="+values[9];
postString+="&NAME_INITIALS="+values[10];
postString+="&NAME_SURNAME="+values[11];
postString+="&NAME_SUFFIX="+values[12];
postString+="&NAME_DATEOFBIRTH_DD="+("0"+values[5]).Substring(("0"+values[5]).Length-2);
postString+="&NAME_DATEOFBIRTH_MM="+("0"+values[6]).Substring(("0"+values[6]).Length-2);
postString+="&NAME_DATEOFBIRTH_CCYY="+values[7];
}
// Current Address
postString+="&ADDR_FLAT="+values[15];
postString+="&ADDR_HOUSENAME="+values[14];
postString+="&ADDR_HOUSENUMBER="+values[13];
postString+="&ADDR_STREET="+values[16];
postString+="&ADDR_DISTRICT="+values[17];
postString+="&ADDR_TOWN="+values[18];
postString+="&ADDR_COUNTY="+values[19];
postString+="&ADDR_POSTCODE="+values[20];
postString+="&RESY_DATEFROM_CCYY="+values[28];
postString+="&RESY_DATEFROM_MM="+("0"+values[27]).Substring(("0"+values[27]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[26]).Substring(("0"+values[26]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[31];
postString+="&RESY_DATETO_MM="+("0"+values[30]).Substring(("0"+values[30]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[29]).Substring(("0"+values[29]).Length-2);
// postString+="&RESY_DATEFROM="+values[28]+("0"+values[27]).Substring(("0"+values[27]).Length-2)+("0"+values[26]).Substring(("0"+values[26]).Length-2);
// postString+="&RESY_DATETO="+values[31]+("0"+values[30]).Substring(("0"+values[30]).Length-2)+("0"+values[29]).Substring(("0"+values[29]).Length-2);
postString+="&RESY_DATEFROM_CCYY="+values[28];
postString+="&RESY_DATEFROM_MM="+("0"+values[27]).Substring(("0"+values[27]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[26]).Substring(("0"+values[26]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[31];
postString+="&RESY_DATETO_MM="+("0"+values[30]).Substring(("0"+values[30]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[29]).Substring(("0"+values[29]).Length-2);
// Previous address
if(values[39]!="")
{
postString+="&ADDR_FLAT="+values[34];
postString+="&ADDR_HOUSENAME="+values[33];
postString+="&ADDR_HOUSENUMBER="+values[32];
postString+="&ADDR_STREET="+values[35];
postString+="&ADDR_DISTRICT="+values[36];
postString+="&ADDR_TOWN="+values[37];
postString+="&ADDR_COUNTY="+values[38];
postString+="&ADDR_POSTCODE="+values[39];
postString+="&RESY_DATEFROM_CCYY="+values[47];
postString+="&RESY_DATEFROM_MM="+("0"+values[46]).Substring(("0"+values[46]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[45]).Substring(("0"+values[45]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[50];
postString+="&RESY_DATETO_MM="+("0"+values[49]).Substring(("0"+values[49]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[48]).Substring(("0"+values[48]).Length-2);
postString+="&RESY_DATEFROM_CCYY="+values[47];
postString+="&RESY_DATEFROM_MM="+("0"+values[46]).Substring(("0"+values[46]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[45]).Substring(("0"+values[45]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[50];
postString+="&RESY_DATETO_MM="+("0"+values[49]).Substring(("0"+values[49]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[48]).Substring(("0"+values[48]).Length-2);
}
txtReturn.Lines = values;
if (rdoPost.Checked == true)
method = "POST";
else
method = "GET";
returnString = webRequest(txtAddress.Text, method, "HTML", postString);
txtReturn.Text = returnString;
txtReturn.*******();
returnString = Regex.Replace(returnString,@"&[^(amp;)]","&");
//returnString = returnString.Replace(" & "," & ");
xDoc.LoadXml(returnString);
if(xDoc.SelectNodes("//ERR1").Count>0)
{
// Error, can't do much here
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,68],xlWorkSheet.Cells[i,68]);
xlRange.Value="Error! - " + xDoc.SelectSingleNode("//ERR1").InnerText;
}
else
{
int cellPos;
if("CHAM"==xDoc.SelectSingleNode("//AU3E/TEST_GROUP").InnerText)
{
cellPos=54;
}
else
{
cellPos=61;
}
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=xDoc.SelectSingleNode("//AU3E/AUTH_INDEX").InnerText;
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=xDoc.SelectSingleNode("//AU3E/PST_OUTCOME").InnerText;
if("0"==xDoc.SelectSingleNode("//AU3E/POLICY_RULE_COUNT").InnerText)
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value="No Rules Hit!";
}
else
{
int counter=1;
foreach(XmlNode node in xDoc.SelectNodes("//AU3E/POLICYRULES/POLICY_RULE"))
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=node.InnerText;
if(counter++>5)break;
}
}
}
}
xlApp.Quit();
}
catch( Exception ex )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, ex.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, ex.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
Any Ideas?
I've been given source code for a program that created a form post from values within an excel document, however I keep getting the following errors:
Property, indexer, or event 'Value' is not supported by the ********; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'
I've had a root through the MSDN site, and cant seem to work out whats wrong:
private void btnBatchExecute_Click(object sender, System.EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
string method;
string postString;
string[] values;
string returnString;
XmlDocument xDoc = new XmlDocument();
try
{
//Start Excel and get Application object.
xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
string thisFileName = txtFileName.Text;
xlWorkbook = xlApp.Workbooks.Open(thisFileName,
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);
xlWorkSheet = (Excel.Worksheet)xlWorkbook.ActiveSheet;
//int i = 5;
for(int i = 5;;i++)
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,2],xlWorkSheet.Cells[i,52]);
values = ConvertToStringArray((System.Array)xlRange.Cells.Value);
if(values[0]=="") break;
postString = "ESERIES_FORM_ID=AUTHENTICATE_3&CTRL_TYPEINDICATOR=C&CTRL_SEARCHCONSENT=Y&CTRL_CHANNEL=I&CTRL_CALLTYPE=I";
// Main App Name
postString+="&NAME_TITLE="+values[0];
postString+="&NAME_FORENAME="+values[1];
postString+="&NAME_INITIALS="+values[2];
postString+="&NAME_SURNAME="+values[3];
postString+="&NAME_SUFFIX="+values[4];
postString+="&NAME_DATEOFBIRTH_DD="+("0"+values[5]).Substring(("0"+values[5]).Length-2);
postString+="&NAME_DATEOFBIRTH_MM="+("0"+values[6]).Substring(("0"+values[6]).Length-2);
postString+="&NAME_DATEOFBIRTH_CCYY="+values[7];
// postString+="&NAME_DATEOFBIRTH="+values[7]+("0"+values[6]).Substring(("0"+values[6]).Length-2)+("0"+values[5]).Substring(("0"+values[5]).Length-2);
// Alias - need to check if title supplied first
if(values[8]!="")
{
postString+="&NAME_TITLE="+values[8];
postString+="&NAME_FORENAME="+values[9];
postString+="&NAME_INITIALS="+values[10];
postString+="&NAME_SURNAME="+values[11];
postString+="&NAME_SUFFIX="+values[12];
postString+="&NAME_DATEOFBIRTH_DD="+("0"+values[5]).Substring(("0"+values[5]).Length-2);
postString+="&NAME_DATEOFBIRTH_MM="+("0"+values[6]).Substring(("0"+values[6]).Length-2);
postString+="&NAME_DATEOFBIRTH_CCYY="+values[7];
}
// Current Address
postString+="&ADDR_FLAT="+values[15];
postString+="&ADDR_HOUSENAME="+values[14];
postString+="&ADDR_HOUSENUMBER="+values[13];
postString+="&ADDR_STREET="+values[16];
postString+="&ADDR_DISTRICT="+values[17];
postString+="&ADDR_TOWN="+values[18];
postString+="&ADDR_COUNTY="+values[19];
postString+="&ADDR_POSTCODE="+values[20];
postString+="&RESY_DATEFROM_CCYY="+values[28];
postString+="&RESY_DATEFROM_MM="+("0"+values[27]).Substring(("0"+values[27]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[26]).Substring(("0"+values[26]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[31];
postString+="&RESY_DATETO_MM="+("0"+values[30]).Substring(("0"+values[30]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[29]).Substring(("0"+values[29]).Length-2);
// postString+="&RESY_DATEFROM="+values[28]+("0"+values[27]).Substring(("0"+values[27]).Length-2)+("0"+values[26]).Substring(("0"+values[26]).Length-2);
// postString+="&RESY_DATETO="+values[31]+("0"+values[30]).Substring(("0"+values[30]).Length-2)+("0"+values[29]).Substring(("0"+values[29]).Length-2);
postString+="&RESY_DATEFROM_CCYY="+values[28];
postString+="&RESY_DATEFROM_MM="+("0"+values[27]).Substring(("0"+values[27]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[26]).Substring(("0"+values[26]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[31];
postString+="&RESY_DATETO_MM="+("0"+values[30]).Substring(("0"+values[30]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[29]).Substring(("0"+values[29]).Length-2);
// Previous address
if(values[39]!="")
{
postString+="&ADDR_FLAT="+values[34];
postString+="&ADDR_HOUSENAME="+values[33];
postString+="&ADDR_HOUSENUMBER="+values[32];
postString+="&ADDR_STREET="+values[35];
postString+="&ADDR_DISTRICT="+values[36];
postString+="&ADDR_TOWN="+values[37];
postString+="&ADDR_COUNTY="+values[38];
postString+="&ADDR_POSTCODE="+values[39];
postString+="&RESY_DATEFROM_CCYY="+values[47];
postString+="&RESY_DATEFROM_MM="+("0"+values[46]).Substring(("0"+values[46]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[45]).Substring(("0"+values[45]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[50];
postString+="&RESY_DATETO_MM="+("0"+values[49]).Substring(("0"+values[49]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[48]).Substring(("0"+values[48]).Length-2);
postString+="&RESY_DATEFROM_CCYY="+values[47];
postString+="&RESY_DATEFROM_MM="+("0"+values[46]).Substring(("0"+values[46]).Length-2);
postString+="&RESY_DATEFROM_DD="+("0"+values[45]).Substring(("0"+values[45]).Length-2);
postString+="&RESY_DATETO_CCYY="+values[50];
postString+="&RESY_DATETO_MM="+("0"+values[49]).Substring(("0"+values[49]).Length-2);
postString+="&RESY_DATETO_DD="+("0"+values[48]).Substring(("0"+values[48]).Length-2);
}
txtReturn.Lines = values;
if (rdoPost.Checked == true)
method = "POST";
else
method = "GET";
returnString = webRequest(txtAddress.Text, method, "HTML", postString);
txtReturn.Text = returnString;
txtReturn.*******();
returnString = Regex.Replace(returnString,@"&[^(amp;)]","&");
//returnString = returnString.Replace(" & "," & ");
xDoc.LoadXml(returnString);
if(xDoc.SelectNodes("//ERR1").Count>0)
{
// Error, can't do much here
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,68],xlWorkSheet.Cells[i,68]);
xlRange.Value="Error! - " + xDoc.SelectSingleNode("//ERR1").InnerText;
}
else
{
int cellPos;
if("CHAM"==xDoc.SelectSingleNode("//AU3E/TEST_GROUP").InnerText)
{
cellPos=54;
}
else
{
cellPos=61;
}
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=xDoc.SelectSingleNode("//AU3E/AUTH_INDEX").InnerText;
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=xDoc.SelectSingleNode("//AU3E/PST_OUTCOME").InnerText;
if("0"==xDoc.SelectSingleNode("//AU3E/POLICY_RULE_COUNT").InnerText)
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value="No Rules Hit!";
}
else
{
int counter=1;
foreach(XmlNode node in xDoc.SelectNodes("//AU3E/POLICYRULES/POLICY_RULE"))
{
xlRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[i,cellPos],xlWorkSheet.Cells[i,cellPos++]);
xlRange.Value=node.InnerText;
if(counter++>5)break;
}
}
}
}
xlApp.Quit();
}
catch( Exception ex )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, ex.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, ex.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
Any Ideas?