I need suggestions on how to get rid of web page navigation

May 14, 2010 at 2:21 PM
I am generation an Excel workbook from an aspx page that uses a master page. When I generate the workbook the stream is including the entire master page and other html code. Any ideas on how to get rid of this? It is causing the following set of errors: Excel found unreadable content in 'Filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes. <Yes> This file cannot be opened by using Microsoft Excel. Do you want to search the Microsoft Office Online Web site for a converter that can open the file? <No> Excel was able to open the file by repairing or removing the unreadable content. Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Editor
May 14, 2010 at 2:37 PM
Edited May 14, 2010 at 2:38 PM

Execute a Response.clear() before send your stream back to client.

 

       Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                    
                    Response.BinaryWrite(epplus.GetAsByteArray());
                    Response.End();

May 14, 2010 at 4:36 PM
Edited May 14, 2010 at 4:42 PM
Thanks but that didn't work. My code is VB so it may need a little translation. I'm posting it so you can see what I'm doing to give ideas as to what else to try.

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
'dtBalanceSheet should have the needed balancesheet data by now because it was filled on Page_Load
Dim drBalanceSheet As DataRow
' Dim dcBalanceSheet As DataColumn
Dim iRowNumber As Integer = 0
Dim iColumnNumber As Integer = 0
Dim pck As New ExcelPackage()
Dim ws = pck.Workbook.Worksheets.Add("BalanceSheet")
Dim iRemainder As Integer = 0
' Response.Clear() 'added here to see if it would get rid of html from master page
'Add data to cells
'Build header of worksheet
If ddlYears.Text = "10" Then
ws.Cells(1, 1, 1, 12).Merge = True
ws.Cells(1, 1, 1, 12).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
ws.Cells(1, 1, 1, 12).Value = "Balance Sheet"
ws.Cells(1, 1, 1, 12).Style.Font.Bold = True
ws.Cells(1, 1, 1, 12).Style.Font.Name = "Times New Roman"
'Can't set color when patterntype is not set.
ws.Cells(1, 1, 1, 12).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(2, 1, 2, 12).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(1, 1, 1, 12).Style.Fill.BackgroundColor.SetColor(Color.LightBlue)
ws.Cells(2, 1, 2, 12).Style.Fill.BackgroundColor.SetColor(Color.LightCyan)
ws.Cells(2, 3).Value = iStartYear
ws.Cells(2, 4).Value = iStartYear + 1
ws.Cells(2, 5).Value = iStartYear + 2
ws.Cells(2, 6).Value = iStartYear + 3
ws.Cells(2, 7).Value = iStartYear + 4
ws.Cells(2, 8).Value = iStartYear + 5
ws.Cells(2, 9).Value = iStartYear + 6
ws.Cells(2, 10).Value = iStartYear + 7
ws.Cells(2, 11).Value = iStartYear + 8
ws.Cells(2, 12).Value = iStartYear + 9
Else
ws.Cells(1, 1, 1, 8).Merge = True
ws.Cells(1, 1, 1, 8).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
ws.Cells(1, 1, 1, 8).Value = "Balance Sheet"
ws.Cells(1, 1, 1, 8).Style.Font.Bold = True
ws.Cells(1, 1, 1, 8).Style.Font.Name = "Times New Roman"
ws.Cells(1, 1, 1, 8).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(2, 1, 2, 8).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(1, 1, 1, 8).Style.Fill.BackgroundColor.SetColor(Color.LightBlue)
ws.Cells(2, 1, 2, 8).Style.Fill.BackgroundColor.SetColor(Color.LightCyan)
ws.Cells(2, 3).Value = iStartYear
ws.Cells(2, 4).Value = iStartYear + 1
ws.Cells(2, 5).Value = iStartYear + 2
ws.Cells(2, 6).Value = iStartYear + 3
ws.Cells(2, 7).Value = iStartYear + 4
ws.Cells(2, 8).Value = iStartYear + 5
End If
'If ddlYears.Text = "10" Then
iRowNumber = 3
For Each drBalanceSheet In dtBalanceSheet.Rows 'Loop through rows
'Add the row to the worksheet
If Not (iRowNumber Mod 2 = 0) Then
If ddlYears.Text = "10" Then
ws.Cells(iRowNumber, 1, iRowNumber, 12).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(iRowNumber, 1, iRowNumber, 12).Style.Fill.BackgroundColor.SetColor(Color.White)
Else
ws.Cells(iRowNumber, 1, iRowNumber, 8).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(iRowNumber, 1, iRowNumber, 8).Style.Fill.BackgroundColor.SetColor(Color.White)
End If
Else
If ddlYears.Text = "10" Then
ws.Cells(iRowNumber, 1, iRowNumber, 12).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(iRowNumber, 1, iRowNumber, 12).Style.Fill.BackgroundColor.SetColor(Color.AliceBlue)
Else
ws.Cells(iRowNumber, 1, iRowNumber, 8).Style.Fill.PatternType = ExcelFillStyle.Solid
ws.Cells(iRowNumber, 1, iRowNumber, 8).Style.Fill.BackgroundColor.SetColor(Color.AliceBlue)
End If
End If
' For Each dcBalanceSheet In drBalanceSheet.Table.Columns 'Loop through columns
For iColumnNumber = 2 To drBalanceSheet.ItemArray.Count - 1
'Add the field to the worksheet
' Name,ID,Text,Year1,Year2,Year3,Year4,Year5,Year6,Year7,Year8,Year9,Year10,Calculation
If iColumnNumber > 3 Then
If Not IsDBNull(drBalanceSheet.Item(iColumnNumber - 1)) Then
ws.Cells(iRowNumber, iColumnNumber - 1).Value = CInt(drBalanceSheet.Item(iColumnNumber - 1))
ws.Cells(iRowNumber, iColumnNumber - 1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right
ws.Cells(iRowNumber, iColumnNumber - 1).Style.Numberformat.Format = "#,##0"
Else
ws.Cells(iRowNumber, iColumnNumber - 1).Value = " "
End If
Else
If iColumnNumber = 2 Then
If Not IsDBNull(drBalanceSheet.Item(iColumnNumber - 1)) And drBalanceSheet.Item(iColumnNumber - 1) <> "" Then
ws.Cells(iRowNumber, iColumnNumber - 1).Value = CInt(drBalanceSheet.Item(iColumnNumber - 1))
End If
Else
ws.Cells(iRowNumber, iColumnNumber - 1).Value = drBalanceSheet.Item(iColumnNumber - 1)
End If
End If
Next
'Add formatting to cells
'Rows to underline 2nd column 3,21
'Rows to bold 2nd column = 3,4,9,10,11,12,19,20,21,22,30,35,36,37,38
Select Case iRowNumber
Case 3
ws.Cells(3, 2).Style.Font.Bold = True
ws.Cells(3, 2).Style.Font.UnderLine = True
Case 4
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 9
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 10
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 11
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 12
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 19
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 20
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 21
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
ws.Cells(iRowNumber, 2).Style.Font.UnderLine = True
Case 22
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 30
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 35
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 36
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 37
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case 38
ws.Cells(iRowNumber, 2).Style.Font.Bold = True
Case Else
End Select
iRowNumber = iRowNumber + 1
Next
'Add borders to cells
If ddlYears.Text = "10" Then
ws.Cells("A1:L38").Style.Border.Top.Style = ExcelBorderStyle.Thin
ws.Cells("A1:L38").Style.Border.Right.Style = ExcelBorderStyle.Thin
ws.Cells("A1:L38").Style.Border.Left.Style = ExcelBorderStyle.Thin
ws.Cells("A1:L38").Style.Border.Bottom.Style = ExcelBorderStyle.Thin
Else
ws.Cells("A1:H38").Style.Border.Top.Style = ExcelBorderStyle.Thin
ws.Cells("A1:H38").Style.Border.Right.Style = ExcelBorderStyle.Thin
ws.Cells("A1:H38").Style.Border.Left.Style = ExcelBorderStyle.Thin
ws.Cells("A1:H38").Style.Border.Bottom.Style = ExcelBorderStyle.Thin
End If
'Output the new Excel Workbook to the client
Response.Clear() 'Just added but it did nothing
pck.SaveAs(Response.OutputStream)
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment; filename=BalanceSheet" & iCase & ".xlsx")
End Sub
May 14, 2010 at 6:34 PM
Ok! It did work after all. I just had to re-arrange a little. I went from this: Response.Clear() pck.SaveAs(Response.OutputStream) Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Response.AddHeader("content-disposition", "attachment; filename=BalanceSheet" & iCase & ".xlsx") To this: Response.Clear() Response.BinaryWrite(pck.GetAsByteArray()) Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Response.AddHeader("content-disposition", "attachment; filename=BalanceSheet" & iCase & ".xlsx") Response.End() Thank you very much!! My clients will be very happy that the old way we did this with the error message and no formatting is being replaced with formatted workbooks and no error messages!