Jorrell Ang

Jorrell Ang

  • NA
  • 3
  • 0

Need Help in Excel Interop

Feb 8 2006 10:25 PM
Hi guys! I really need help from this one.

I created a desktop application that connects to the ms sql database and transfer the data to excel.... its working.

But when I transfered the codes to a web application, it is still working but the excel won't show up. I tried to look into the Task Manager and Excel was succesfully opened but it won't show up.

I really don't know why is this happening.... please help. Here's the code...

------------------------------------

try

{

SqlConnection objConnection = new SqlConnection();

objConnection.ConnectionString = "Server=(local);Database=MyDatabase;uid=myid;pwd=mypassword;Connect Timeout=300";

objConnection.Open();

//this.txtDebug.Text = txtDebug.Text + "Database connected.\r\n\r\n";

SqlCommand thisCommand = objConnection.CreateCommand();

string query = "select i.invoiceid, i.invoiceno, p.projectname, i.startdate, i.enddate, " +

" billingitem, amount, " +

" lastname + ', ' + firstname + ' ' + middlename as employee, " +

" ep.startdate as emp_startdate, ep.enddate as emp_enddate "+

" from invoice i " +

" inner join invoicedetail ind on i.invoiceid = ind.invoiceid " +

" inner join billingitem b on ind.billingitemid = b.billingitemid " +

" inner join EmployeeInvoiceDetail eid on ind.invoicedetailid = eid.invoicedetailid " +

" inner join project p on i.projectid = p.projectid " +

" inner join employee e on eid.employeeid = e.employeeid " +

" inner join employeeproject ep on e.employeeid = ep.employeeid and p.projectid = ep.projectid " +

" where i.startdate between '01/1/2005' and '12/31/2005' and i.enddate between '01/1/2005' and '12/31/2005' and i.projectid='10'";

//this.txtDebug.Text = txtDebug.Text + "This is the query \r\n " + query;

thisCommand.CommandText = query;

SqlDataReader thisReader = thisCommand.ExecuteReader();

 

Excel.Application ExcelObj = null;

ExcelObj = new Excel.Application();

ExcelObj.Visible = true;

 

 

Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(

@"C:\Excel_Pivot.xls", 0, true, 5,

"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,

0, true, false, false);

Excel.Sheets sheet = theWorkbook.Worksheets;

Excel.Worksheet worksheet = (Excel.Worksheet)sheet.get_Item(2);

int ctr = 1;

while (thisReader.Read())

{

ctr++;

Range invoiceid = (Range)worksheet.Cells[ctr,"A"];

invoiceid.Value2 = thisReader["invoiceid"];

Range invoiceno = (Range)worksheet.Cells[ctr,"B"];

invoiceno.Value2 = thisReader["invoiceno"];

Range projectname = (Range)worksheet.Cells[ctr,"C"];

projectname.Value2 = thisReader["projectname"];

Range startdate = (Range)worksheet.Cells[ctr,"D"];

startdate.Value2 = thisReader["startdate"];

Range enddate = (Range)worksheet.Cells[ctr,"E"];

enddate.Value2 = thisReader["enddate"];

Range billingitem = (Range)worksheet.Cells[ctr,"F"];

billingitem.Value2 = thisReader["billingitem"];

Range amount = (Range)worksheet.Cells[ctr,"G"];

amount.Value2 = thisReader["amount"];

Range employee = (Range)worksheet.Cells[ctr,"H"];

employee.Value2 = thisReader["employee"];

Range emp_startdate = (Range)worksheet.Cells[ctr,"I"];

emp_startdate.Value2 = thisReader["emp_startdate"];

Range emp_enddate = (Range)worksheet.Cells[ctr,"J"];

emp_enddate.Value2 = thisReader["emp_enddate"];

 

 

}

//if(!thisReader.Read())

//{

//}

thisReader.Close();

objConnection.Close();

//this.txtDebug.Text = txtDebug.Text + "\r\n\r\n\r\nExcel populated.";

}

catch (Exception ex)

{

string x;

x = ex.Message;

//this.txtDebug.Text = txtDebug.Text + ex.Message;

}
------------------------------------


I really need this working, please help. Thanks! ^_^


Answers (1)