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! ^_^