Output to Excel

Using this code:

Response.ContentType = "application/ms-excel"
Response.AppendHeader("Content-Disposition", "attachment; filename=worksheet.xls")
Response.Flush()

...I'm getting an "unable to read file" error when I try to open the file in Excel. It seems to be saving it, but I'm not convinced in a readable format. Does anyone have any suggestions regarding this or other methods of sending an aspx page to Excel from the web form?

Thanks...Ooogs

Hello,

This can be a problem, specially if you want it to in a preformatted readable format. I had the same problem, and it would mess up the data in certain columns and do all types of crazy stuff.

What I had to do.

If you are using SQL Server, You can build DTS Package that runs daily or when ever you want it to, and have it build a the excel files.

Then just have a button on your web page that will open up the already created file that is located on your server somewhere.

If you do not have SQL Server, not sure what to tell you.

Give me more details about your problems and I will try to help you further, with greater details.

Output to Excel

Basically, I'm using a datagrid on a nice colorful page to display the results of a query. I have a link on that page to a "Printer-friendly version", which is another aspx page with the same datagrid re-done in black and white (not "fancy"). That part works just great.

Unfort, my boss wants me to add an option for output to Excel so our employees can sort and format away before they print. The code I used (and included before), worked awesome a couple times, then stopped....???

Any ideas? :confused:

Thanks....Ooogs

I looked at some code that works of mine, and the only thing different that I see is that I have:

Response.clear

Then what you had.

Kenny

Kenny - humor me and post your code if you don't mind. I'd like to see if the problem is systematic or programatic. I'm still having no luck.

Thanks...Ooogs

in this page, I am only opening an existing excel file

<%@ Import namespace ="System.Data.SqlClient" %>
<%@ Import namespace ="System.Data" %>
<%@ Import namespace ="System.Web.Security" %>
<%@ Import Namespace="System.Web" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>test</title>
<meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
<meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<h2 style="FONT-FAMILY: Arial">Test Summary Report</h2>
<font style="FONT-WEIGHT: bold; FONT-FAMILY: Arial">Report Data was Updated: </font>
<form id="Form1" method="post" runat="server">
<asp:DataGrid ID="dgUpdated" Runat="server" ShowHeader="False" BorderWidth="0" Font-Names="Arial" Font-Bold="True" style="FONT-WEIGHT: bold; FONT-FAMILY: Arial" Width="227px" Height="10px"></asp:DataGrid>
<asp:DataGrid id="dgNameList" style="Z-INDEX: 101; LEFT: 7px; POSITION: absolute; TOP: 160px" runat="server" BorderWidth="1px" AutoGenerateColumns="False" BorderColor="#999999" BackColor="White" HorizontalAlign="Left" BorderStyle="Groove" CellPadding="3" GridLines="Vertical">
<FooterStyle ForeColor="Black" BackColor="Silver"></FooterStyle>
<HeaderStyle Font-Size="10pt" Font-Names="arial" Font-Bold="True" Wrap="False" ForeColor="White" BackColor="DimGray"></HeaderStyle>
<PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>
<SelectedItemStyle Font-Bold="True" Wrap="False" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
<AlternatingItemStyle BorderStyle="Groove" BackColor="Gainsboro"></AlternatingItemStyle>
<ItemStyle Font-Size="9pt" Font-Names="arial" Wrap="False" ForeColor="Black" BackColor="White"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="eventdate" SortExpression="eventdate" HeaderText="Date"></asp:BoundColumn>
<asp:BoundColumn DataField="sales" SortExpression="sales" HeaderText="Sales"></asp:BoundColumn>
<asp:BoundColumn DataField="dr_sales" SortExpression="dr_sales" HeaderText="DR Sales" ItemStyle-Wrap="false" HeaderStyle-Wrap="false"></asp:BoundColumn>
<asp:BoundColumn DataField="net_sales" SortExpression="net_sales" HeaderText="Net Sales"></asp:BoundColumn>
<asp:BoundColumn DataField="tot_ref_fee" SortExpression="tot_ref_fee" HeaderText="Total Referral Fee"></asp:BoundColumn>
<asp:BoundColumn DataField="pct_tot" SortExpression="pct_tot" HeaderText="Referral Fee Pct."></asp:BoundColumn>
<asp:BoundColumn DataField="sbs_contribution" SortExpression="sbs_contribution" HeaderText="SBS Contribution"></asp:BoundColumn>
<asp:BoundColumn DataField="pct_sbs" SortExpression="pct_sbs" HeaderText="SBS Contribution Pct."></asp:BoundColumn>
<asp:BoundColumn DataField="dist_contribution" SortExpression="dist_contribution" HeaderText="Distr Referral Fee Exp."></asp:BoundColumn>
<asp:BoundColumn DataField="pct_dist" SortExpression="pct_dist" HeaderText="Distr Referral Fee Pct."></asp:BoundColumn>
<asp:BoundColumn DataField="act_ref_sources" SortExpression="act_ref_sources" HeaderText="Active Referral Sources"></asp:BoundColumn>
<asp:BoundColumn DataField="rid" SortExpression="rid" HeaderText="Sources Referring"></asp:BoundColumn>
<asp:BoundColumn DataField="ncid" SortExpression="ncid" HeaderText="New Customers"></asp:BoundColumn>
<asp:BoundColumn DataField="new_sales" SortExpression="new_sales" HeaderText="New Sales"></asp:BoundColumn>
<asp:BoundColumn DataField="rep_sales" SortExpression="rep_sales" HeaderText="Repeat Sales"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
<asp:Button id="btnOpenExcel" runat="server" Text="View in Excel" OnClick="OpenExcel" ToolTip="Opens the Report in Excel." onmouseover="this.style.fontWeight='bold'" onmouseout="this.style.fontWeight='normal'" style="Z-INDEX: 103; LEFT: 455px; POSITION: absolute; TOP: 59px"></asp:Button>
</form>
<script language="vb" runat="server">

Sub Page_Load(Source As Object, E As EventArgs)

Dim objConnection As sqlConnection = New sqlConnection(ConfigurationSettings.AppSettings("test"))

Dim strSQLupdate As String = "Select min(last_updated) from test"
Dim objCommand As New SqlCommand(strSQLupdate, objConnection)
objConnection.Open()
Dim objReader As sqlDataReader = objCommand.ExecuteReader()

dgUpdated.DataSource = objReader
dgUpdated.DataBind()
objReader.Close()

Dim strSQL As String = "Select * from test order by eventdate"
objCommand.CommandText = strSQL
objReader = objCommand.ExecuteReader()
dgNameList.DataSource = objReader
dgNameList.DataBind()

objConnection.Close()

End Sub
Sub OpenExcel(Source As Object, E As EventArgs)

Dim FileName As String = "test.xls"
Dim FileAndPath As String = "C:\" & FileName

'This opens the download dialoge box and allows the user'
'to download the excel sheet from the server'
Response.Clear()
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName)
Response.Flush()
'This command actually transfers the file'
Response.WriteFile(FileAndPath)
End Sub

</script>
</FONT>
<HR style="Z-INDEX: 102; LEFT: 17px; WIDTH: 162.76%; POSITION: absolute; TOP: 43px; HEIGHT: 3px; BACKGROUND-COLOR: silver" width="162.76%" SIZE="3">
</body>
</HTML>

We're so close...

So far this is what I've got:

Private Sub cmdExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExcel.Click
Try
Dim FileName As String = "PendingSteps.xls"
Dim FileAndPath As String = "C:\My Documents\" & FileName
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("Content-Disposition", "attachment; filename=" & FileName)
Response.Flush()
Response.WriteFile(FileAndPath)
Catch ex As OleDb.OleDbException
lblError.Text = "There has been an error. Please advise MIS of this error message: " & ex.Message
End Try
End Sub

It is finally making an Excel worksheet, but it's 0 bytes (meaning it's not sending the datagrid, just a blank file) How would I make sure the datagrid is going too?

Thanks again for all the help Ken...

Ooogs

have to referece OWC

Sub Export(Source As Object, E As EventArgs)

Dim objExcel As New OWC.Spreadsheet() 'Referencing Microsoft Office Web Compenents 9.0'
Dim myRow As DataRow
Dim myColumn As New DataColumn()
Dim RowCount As Object
Dim ColumnCount As Object
Dim FileName As String = "Example.xls"
Dim FileAndPath As String = "C:\" & FileName

'Add the column headers to the excel sheet'
For Each myColumn In myTable.Columns
ColumnCount += 1
objExcel.ActiveSheet.Cells(1, ColumnCount) = myColumn.ColumnName
Next

ColumnCount = 0
RowCount = 0

'Add the data to the excel sheet'
For Each myColumn In myTable.Columns
ColumnCount += 1
RowCount = 1
For Each myRow In myTable.Rows
RowCount += 1
objExcel.ActiveSheet.Cells(RowCount, ColumnCount) = CStr(myRow(myColumn))
Next
Next

ColumnCount = 0

'Autofit the columns to make them look pretty'
For Each myColumn In myTable.Columns
ColumnCount += 1
objExcel.ActiveSheet.Columns(ColumnCount).EntireColumn.AutoFitColumns()
Next

'Do any formatting you wish.....'
'objExcel.ActiveSheet.Columns.InsertColumns(0)
objExcel.ActiveSheet.Rows.InsertRows(2)
objExcel.ActiveSheet.Cells(1, 1) = "Test Report"
objExcel.ActiveSheet.Rows(1).Font.Bold = True
objExcel.ActiveSheet.Rows(3).Font.Bold = True

'This saves the excel file to C:\Example.xls on the server'
objExcel.ActiveSheet.Export(FileAndPath, OWC.SheetExportActionEnum.ssExportActionNone)

'This opens the download dialoge box and allows the user'
'to download the excel sheet from the server'
Response.Clear()
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName)
Response.Flush()
'This command actually transfers the file'
Response.WriteFile(FileAndPath)

End Sub

Sub Page_Load(Source As Object, E As EventArgs)

'Set your connection and commandstring'
'I am using the Northwind database'
Dim myConnection As New SqlClient.SqlConnection("server=test;uid=test;pwd=test;database=test")
Dim myCommand As New SqlClient.SqlCommand("Select * From test", myConnection)
Dim myDataAdapter As New SqlClient.SqlDataAdapter(myCommand)

'Clear out the public datatable'
'Dim myTable As New DataTable()
myTable = New DataTable()

'Open connection'
myConnection.Open()
'Fill the public datatable'
myDataAdapter.Fill(myTable)
'Close connection'
myConnection.Close()

'Autogeneratecolumns = false, so you have to'
'create the datagrid through code based on the'
'data in the public datatable'
FormatDataGrid(myTable)

'Set the datagrids datasource and bind the grid'
grdMain.DataSource = myTable
grdMain.DataBind()

End Sub

KMG -

Thanks much for the help. I do appreciate it. A quick question: I don't see the call to the "SUB Export" procedure in the page_load handler. Should it be in there, or is it called at some other point?

Ooogs

That is because I used a button that the user could click to view in excel.

So, the click event handler for the button calls the "Sub Export" procedure, correct?

yes

<asp:Button id="btnOpenExcel" runat="server" Text="View in Excel" OnClick="OpenExcel" ToolTip="Opens the Report in Excel." onmouseover="this.style.fontWeight='bold'" onmouseout="this.style.fontWeight='normal'" ></asp:Button>