Problem long running process

[philky001]

This code is in ASP.net but it creates a new sheet in an exisiting excel. The issue is that it is taking quite long to run. 7000 rows it has to read and copy to the new sheet, from A to Q, and also does one data lookup on SQL Server for each row. It gets the drug description and populates a new column. But it take about 30 minutes to run. any ideas why it would take so long. I was thinking 3-4 min or so it should be.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace ExcelReader
{
public partial class Form1 : Form
{
SqlConnection con;
SqlDataReader rdr = null;
SqlCommand cmd = null;
public Form1()
{

InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog1.ShowDialog();
con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = "Data Source=SRVORDERS;Initial Catalog=PRO2;Integrated Security=True;Persist Security Info=True;MultipleActiveResultSets=True";


con.Open();

Excel.Application excel = null;
Excel.Workbook wkb = null;
if (result == DialogResult.OK) // Test result.
{
this.Cursor = Cursors.WaitCursor;

string file = openFileDialog1.FileName;
try
{
List<string> headerList = new List<string>();
excel = new Excel.Application();
wkb = this.OpenBook(excel, file, true, false, false);
Excel.Worksheet sheet = wkb.Sheets["DRUG ORDERS"] as Excel.Worksheet;
int startRow = 2;
bool hasContent = false;

for (int i = 0; i < 18; i++)
{
var headercell = (Excel.Range)sheet.Cells[1, i + 1];
headerList.Add(headercell.Value2);

if (i == 7)
headerList.Add("Drug-Table");
}

List<DrugOrder> listDrugOrder = new List<DrugOrder>();
int row = startRow;
do
{
DrugOrder drugoder = new DrugOrder();

var rescell = (Excel.Range)sheet.Cells[row, 1];



var stationcell = (Excel.Range)sheet.Cells[row, 2];
drugoder.STATION = stationcell.Value2;

var datecell = (Excel.Range)sheet.Cells[row, 3];
if (datecell.Value2 != null)
{

drugoder.DATE = DateTime.FromOADate(datecell.Value2);
}
var drugcell = (Excel.Range)sheet.Cells[row, 4];
drugoder.DRUG = drugcell.Value2;

//var ndccell = (Excel.Range)sheet.Cells[row, 5];
var updosecell = (Excel.Range)sheet.Cells[row, 6];
drugoder.UNITS_PER_DOSE = updosecell.Value2;

var formcell = (Excel.Range)sheet.Cells[row, 7];
drugoder.FORM = formcell.Value2;

var routcell = (Excel.Range)sheet.Cells[row, 8];
drugoder.ROUTE = routcell.Value2;

// var drugadlcell = (Excel.Range)sheet.Cells[row, 10];
var freqcell = (Excel.Range)sheet.Cells[row, 10];
drugoder.FREQUENCY = freqcell.Value2;

var emptycell = (Excel.Range)sheet.Cells[row, 11];
drugoder.CLOVE_LAKE_ORDERS = emptycell.Value2;


var hoacell = (Excel.Range)sheet.Cells[row, 12];
drugoder.HOA = hoacell.Value2;


var instructioncell = (Excel.Range)sheet.Cells[row, 13];
drugoder.INSTRUCTIONS = instructioncell.Value2;


var diagncell = (Excel.Range)sheet.Cells[row, 14];
drugoder.DIAGNOSIS = diagncell.Value2;

var diagncodecell = (Excel.Range)sheet.Cells[row, 15];
drugoder.DIAGNOSIS_CODES = diagncodecell.Value2;


var marcell = (Excel.Range)sheet.Cells[row, 16];
drugoder.MAR = marcell.Value2;


var tarcell = (Excel.Range)sheet.Cells[row, 17];
drugoder.TAR = tarcell.Value2;

var drugalertell = (Excel.Range)sheet.Cells[row, 18];
drugoder.DRUG_ALERT = drugalertell.Value2;


var ndccell = (Excel.Range)sheet.Cells[row, 5];


if (rescell.Value2 != null)
{
drugoder.RES_ID = rescell.Value2;
hasContent = true;
}
else
{
hasContent = false;
}

if (rescell.Value2 != null)
{
hasContent = true;
}
else
{
hasContent = false;
}
if (ndccell.Value2 != null)
{


drugoder.NDC = ndccell.Value2;

string CommandText = "SELECT distinct [LABEL_NAME]" +
" FROM [OREN].[REN_M_NDC]" +
" WHERE ([NDC_CODE]= @Find)";

cmd = new SqlCommand(CommandText);
cmd.Connection = con;

cmd.Parameters.Add(
new SqlParameter(
"@Find", // The name of the parameter to map
System.Data.SqlDbType.NVarChar, // SqlDbType values
10, // The width of the parameter
"NDC_CODE")); // The name of the source column
cmd.Parameters["@Find"].Value = ndccell.Value2;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
drugoder.CLOVE_LAKE_ORDERS =rdr["LABEL_NAME"].ToString();
}
rdr.Close();
}
drugoder.RowNo = row;
row++;
listDrugOrder.Add(drugoder);
}
while (hasContent);



//bool updateResult = this.HotSheetRanking.UpdateHotSheetListByName(hotSheetsList);
//if (updateResult)
//{
// MessageBox.Show("Updated Successfully!");
//}
//else
//{
// MessageBox.Show("Error!");
//}
AddWorksheetToExcelWorkbook(file, "Drug table", headerList, listDrugOrder);
this.Cursor = Cursors.Arrow;

}
catch (Exception ex)
{
this.Cursor = Cursors.Arrow;
MessageBox.Show(ex.StackTrace);
}
wkb.Close();

con.Close();
MessageBox.Show("Done");
}
}

private void AddWorksheetToExcelWorkbook(string fullFilename, string worksheetName, List<string> headerList, List<DrugOrder> listDrugOrder)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;

try
{
FileInfo file = new FileInfo(fullFilename);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

sheets = workbook.Sheets;

//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);

System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}

newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = worksheetName;

for (int i = 0; i < headerList.Count; i++)
{
newSheet.Cells[1, i + 1] = headerList;
Range headerRange = newSheet.Cells[1, headerList.Count]; ;
headerRange.Font.Bold = true;
}
for (int i = 0; i < listDrugOrder.Count; i++)
{
DrugOrder drugorder = listDrugOrder;

newSheet.Cells[i + 2, 1] = drugorder.RES_ID;
newSheet.Cells[i + 2, 2] = drugorder.STATION;
newSheet.Cells[i + 2, 3] = drugorder.DATE;
newSheet.Cells[i + 2, 4] = drugorder.DRUG;
newSheet.Cells[i + 2, 5] = drugorder.NDC;
newSheet.Cells[i + 2, 6] = drugorder.UNITS_PER_DOSE;
newSheet.Cells[i + 2, 7] = drugorder.FORM;
newSheet.Cells[i + 2, 8] = drugorder.ROUTE;

newSheet.Cells[i + 2, 10] = drugorder.FREQUENCY;
newSheet.Cells[i + 2, 11] = drugorder.CLOVE_LAKE_ORDERS;
newSheet.Cells[i + 2, 12] = drugorder.HOA;
newSheet.Cells[i + 2, 13] = drugorder.INSTRUCTIONS;
newSheet.Cells[i + 2, 14] = drugorder.DIAGNOSIS;
newSheet.Cells[i + 2, 15] = drugorder.DIAGNOSIS_CODES;
newSheet.Cells[i + 2, 16] = drugorder.MAR;
newSheet.Cells[i + 2, 17] = drugorder.TAR;
newSheet.Cells[i + 2, 18] = drugorder.DRUG_ALERT;

}

workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

newSheet = null;
sheets = null;
workbook = null;
excelApp = null;

GC.Collect();
}

}


public Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable, bool updateLinks)
{
Excel.Workbook book = excelInstance.Workbooks.Open(fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


return book;
}

public static void ReleaseRCM(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally { o = null; }
}

}
}

 

[jimrward]

can you describe the process in non programming terms, so we can understand how the process works, especially the reading one line at a time, how many tables involved and why php and not VBA

 

[tweedle]

I'm going to lean towards 7000 connect+query is likely prohibitor.
might be a better strategy to pull the table elements you need to a more 'localized' recordset and seek/find off of that; OR
push those parts into Excel and give Excel a simple vlookup to pull back the description to your working sheets.

 

[philky001]

Thanks the localized idea is where we are heading. It's in C# as we try to do most things in that here. AS I come from an old background, I found it easier to use the one language as my main.