Link to home
Start Free TrialLog in
Avatar of vinuramdas
vinuramdas

asked on

export to excel

I have a ASP.Net application and I am exporting the report into excel. It is working fine in my development system which has IIS 5.1. But when I deploy this in the production which has IIS 6.0, it is not showing the excel page. So i created a sample code and deployed in the production and this is also not working.  Here is my code

public void DynamicExcel()
{
Excel.Application excel = new Excel.Application();
excel.Visible = true;
response.write("Visible: " + excel.Visible);
}

I am calling DynamicExcel function on click of button. In local system out of response.write "Visible: True" but in production it is "Visible: False". I think this is the reason why excel page is not displaying. Is there any do be done to display the excel page?

Thanks in advance
Avatar of MogalManic
MogalManic
Flag of United States of America image

I think we need more information.  Is DynamicExcel a comercial product?

Does it need any custom install or setup in the GAC?

What shows up on the HTML page when you view source?
Avatar of vinuramdas
vinuramdas

ASKER

Thanks for the reply
DynamicExcel is a function which I create and it will be called when I click on a button.
No custom installation is required provided you should have micrsoft excel in your system.
I am not clear about the third question. If you mean about the web page, I could see the upt as "Visible: FalseDone" which is supposed to be "Visible: TrueDone".
 
Try this instead of Response.Write:
  In your page, add the following:
    <asp:Label id='ExcelVisible' runat='Server' text='Visible: Unknown'/>  <!--This could also be initialy Text=''-->
  In your DynamicExcel function:
    public void DynamicExcel()
   {
      Excel.Application excel = new Excel.Application();
      excel.Visible = true;
      ExcelVisible.Text="Visible: " + excel.Visible;
   }


So you are using Excel .Net interop to talk directly with Excel.   correct?
You should not use Excel .net Interop this is not recomend or supported by Microsoft. There are many complications with using interop in this situation and it is very un-scallable.

What are you trying to achieve?

if you want to export a GridView to export I can supply some sample code.
I am exporting a chart to excel. I am using DotNetchart (third party control) control to draw chart in web page and I wanted to export this into excel. Since the chart is an image i cannot bind it to excel. So I open an excel file and write values in the cell and draw the graph according to the values in the cell.

This is working fine in my local system which has IIS 5.1 but in production (IIS 6.0) it is not opening excel file. One thing I noticed is even after setting excel.Visible = true; excel.Visible is always false. Please let me know if I need to do any specific changes for IIS 6.0

Trust me, I know from experience, this will not be the first problem you will face if you continue to impliment your solution using this technque. No attempt has been made by microsoft for Excel to be used in an unnatended or non-interacive client.

Quote from MS Site:
"Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment."

Visit here for a detailed explantion of why it is a bad Idea!
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757
If you want to create docs server side, you will need to find a .NET component for Server side cretion of office documents. Some companys with suitable products are: -

Aia Software B.V. - http://www.aia-itp.com
Polar - http://www.polarsoftware.com
SoftArtisans - http://www.softartisans.com
SyncFusion- http://www.syncfusion.com
Keylogix - http://www.activedocs.com
If all of your clients will have Excel and IE installed on there client machines you coulld try using  (VBScript) client-side code that launches and automates Microsoft Office Excel

http://support.microsoft.com/kb/198703/
Do your users need to be able to edit the figures that generate the Chart?

If not why not use a PDF writer to create a PDF report, this is my favorite solution to your problem!
can you share the code which can be used to export to PDF?
User doesn;t make any change in the report.
I don't currently use PDF as a report output, so don't have any code samples, but Look here for a PDF library that comes highly recomend by those who do.

http://itextsharp.sourceforge.net/

My Users are happy to have HTML reports, which is obviously the simplist solution, but could sometimes be considered less professional
I've used itextsharp to create text based reports and it works fairly well.  I just created a ASHX page that processes the request.  The processRequest method for the ASHX page looks like this:
    public void ProcessRequest (HttpContext context) {
           
        MemoryStream m = new MemoryStream();
        Document document = CreateDocument();        //Creates IText Document object
       
        DocWriter writer = PdfWriter.GetInstance(document, m);

        BuildDocument(document, writer);  //Builds PDF document

        context.Response.Clear();

        string contentType = "application/pdf";
        //Process PDF as an "Attachment"
        context.Response.AddHeader("Content-Disposition", "filename=" + AttachmentName + ".pdf");

        context.Response.ContentType = contentType;
        context.Response.AddHeader("Content-Length", m.GetBuffer().Length.ToString());
        context.Response.OutputStream.Flush();
       
        context.Response.OutputStream.Write(m.GetBuffer(), 0, m.GetBuffer().Length);

        context.Response.OutputStream.Flush();
        context.Response.OutputStream.Close();
        context.Response.End();
    }

You can find examples on how to create a PDF document at
http://itextdocs.lowagie.com/tutorial/  - (tutorial is for Java version of iText but API is the same)
I am using in the following way to export my datagrid to excel .Its working fine.
I ma calling following code on a button click event of the page.

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=StoreRecallInformation.xls");
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(dgGrid);
dgGrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();

private void ClearControls(Control control)
{
      for (int i=control.Controls.Count -1; i>=0; i--)
      {
      ClearControls(control.Controls[i]);
      }
      if (!(control is TableCell))
      {
      if (control.GetType().GetProperty("SelectedItem") != null)
      {
      LiteralControl literal = new LiteralControl();
            control.Parent.Controls.Add(literal);
      try
      {
      literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
      }
      catch
      {
      }
      control.Parent.Controls.Remove(control);
      }
      else
      if (control.GetType().GetProperty("Text") != null)
      {
      LiteralControl literal = new LiteralControl();
      control.Parent.Controls.Add(literal);
      literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
      control.Parent.Controls.Remove(control);
      }
      }
      return;
}

I hope this will help you.

ASKER CERTIFIED SOLUTION
Avatar of McExp
McExp
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial