What methods can export excel file from stored procedure with parameters which input from web form?

[ABC]

What methods can export excel file from stored procedure with parameters
which input from web form?

 

[Jason Hales]

You're going to have to do a lot of this yourself - there's nothing
built into the Frawework to support this althought here are many third
party components that can help with creating Excel files

I've used the Microsoft.Office.Interop.Excel namespace inside
Microsoft.Office.Interop.Excel.dll.
You'll need to download and install Office Primary Interop Assemblies.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp

I've used it in a Windows app to open Excel and write to cells.

The trouble is these aren't meant to be used as server side components.
Office 2006 contains Excel object model that is decoupled from the UI
and intended for server-side use.

 

[Guest]

one method to do it...

bring the parametrized data back into a datagrid and add a 'save to excel'
button on your form

the following code in your webform:
(you have to use your own datagrid's name in the top btnExportToExcel_Click,
the ClearControl method takes out troublesome controls from the datagrid (in
memory) and when it's done it calls the datagrid's RenderControl Method, a
File save as dialog is thrown up and you're there)

if your dataset is too large, this might not be an ideal method, but it
works fine for exporting datagrid data from a web page



private void btnExportToExcel_Click(object sender, System.EventArgs e)
{
//export to excel

Response.Clear();
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([enter your datagrid's name here]);
[Enter your datagrid's name here].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);
}

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;
}


good luck
CharlesA

 

[Steve C. Orr [MVP, MCSD]]

There are a variety of ways to export data to Excel, most of which are
detailed here:
http://SteveOrr.net/articles/ExcelExport.aspx

If you want a more automatic approach, you might try one of these
components:
http://SteveOrr.net/articles/ExportPanel.aspx
http://SteveOrr.net/reviews/AsposeExcel.aspx
http://SteveOrr.net/reviews/OfficeWriter.aspx

You could also consider Crystal Reports or the new VSTO:
http://msdn.microsoft.com/office/understanding/vsto/