Poor Performance

[mikew194]

I am readin gin an excel file and converting to xml.
It is a rather large file and takes several minutes to run.

Any ideas on how to improve the performance?

I suspect it is related to how the excel file is read.

if (this.textBox1.Text.Length <= 5) return;

System.Xml.XmlDocument xmlDoc = InitXMLDocument();

object oNull = Type.Missing;

Excel.Application xl = new Excel.Application();
Excel._Workbook wb =
(Excel._Workbook)xl.Workbooks.Open(this.textBox1.Text,oNull,true,oNull,oNull,oNull,oNull,oNull,oNull,false,false,false,oNull,true,false);
Excel._Worksheet ws = (Excel._Worksheet)wb.Worksheets.get_Item(SHEET);

int nRows = ws.UsedRange.Count;
int r=4;
string vBCellID = "";
string vFCellID = "";
object m_MissingValue = Type.Missing;

try
{
do
{
vBCellID = "B" + r.ToString();
vFCellID = "F" + r.ToString();

Excel.Range range = ws.get_Range(vBCellID, vFCellID);
System.Array myvalues =
(System.Array)range.Cells.get_Value(System.Type.Missing);
string[] strArray = ConvertToStringArray(myvalues);

AddNode(ref xmlDoc, strArray[0], strArray[1], strArray[2],
strArray[3], strArray[0]);

r++;
txtMsgBox.Text = "row processed" + r.ToString();
}
while (r < nRows);
}
catch (SystemException se)
{
string s = se.Message.ToString();
txtMsgBox.Text = s;
}
finally
{

wb.Close(false, oNull, oNull);
wb = null;

xl.Quit();
xl = null;

try
{
xmlDoc.Save(sOutputfile);
}
catch(SystemException se_1)
{
txtMsgBox.Text = se_1.Message.ToString();
}
}

 

[PHV]

Your code is not VBA but VB.Net ?

 

[mikew194]

Sorry,

The problem is with reading excel?

So I didn't know where to post.