Hello,
I recently upgraded from NetAdvantage for .NET 2008 Vol. 1 CLR 2.0 to NetAdvantage for .NET 2008 Vol. 2 CLR 2.0. I am using Visual Studio 2005 with VB.
Before I upgraded, I was able to have a couple of merged regions along the top of my worksheets in Excel as report headers and then start the actual datagrid in the next row down. Since I upgraded, the same code does merge the cells but puts the column headers for the grid in these merged cells so that the actual grid has no column headers. If I remark out the merged cell regions, the grid comes across fine. Is there any way to tell it which row to start the datagrid in? Has there been a change in syntax between the versions that I need to know? I am not getting any errors, it just doesn't look right when it exports.
My code is below:
Dim ds11 As DataSet
Dim sheet1 As Worksheet
Dim sheet3 As Worksheet
Dim rgn As WorksheetMergedCellsRegion
'EE1 is the name of the Infragistics UltraGrid Excel Exporter.
'DG9 is the name of the datagrid
Me.EE1.DownloadName = "SalesByPart_Quantity.xls"
wb.WindowOptions.SelectedWorksheet = sheet1
rgn = sheet1.MergedCellsRegions.Add(0, 0, 0, 17)
rgn.Value = "Program Name"
rgn.CellFormat.FillPatternForegroundColor = System.Drawing.Color.Navy
rgn.CellFormat.FillPattern = FillPatternStyle.Solid
rgn.CellFormat.Alignment = HorizontalCellAlignment.Center
rgn.CellFormat.Font.Name = "Verdana"
rgn.CellFormat.Font.Color = Color.White
rgn.CellFormat.Font.Bold = ExcelDefaultableBoolean.True
rgn.CellFormat.Font.Height = 240
rgn = sheet1.MergedCellsRegions.Add(1, 0, 1, 17)
rgn.Value = "Date Report Generated -- " & FormatDateTime(Now(), DateFormat.ShortDate) & " " & "Year:" & " " & Me.cboOrderYear.SelectedItem.Value & " " & "Territory -- " & Me.cboTerritory.SelectedItem.Value
rgn = sheet1.MergedCellsRegions.Add(2, 0, 2, 17)
rgn.Value = "Sales By Part - Quantity"
wb.WindowOptions.SelectedWorksheet.Rows(3).CellFormat.Font.Bold = ExcelDefaultableBoolean.True
wb.WindowOptions.SelectedWorksheet.Rows(3).CellFormat.WrapText = ExcelDefaultableBoolean.True
wb.WindowOptions.SelectedWorksheet.Columns(6).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(7).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(8).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(9).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(10).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(11).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(12).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(13).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(14).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(15).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(16).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(17).CellFormat.FormatString = "###,###,##0"
wb.WindowOptions.SelectedWorksheet.Columns(18).CellFormat.FormatString = "###,###,##0"
If Me.cboTerritory.SelectedItem.Value <> "All" Then
DG9.DataSource = ds11
DG9.DataBind()
Else
wb.WindowOptions.SelectedWorksheet.Rows(3).Height = 1539
wb.WindowOptions.SelectedWorksheet.Columns(0).Width = 1793
wb.WindowOptions.SelectedWorksheet.Columns(1).Width = 1793
wb.WindowOptions.SelectedWorksheet.Columns(2).Width = 5689
wb.WindowOptions.SelectedWorksheet.Columns(3).Width = 6689
wb.WindowOptions.SelectedWorksheet.Columns(4).Width = 8689
wb.WindowOptions.SelectedWorksheet.Columns(4).CellFormat.WrapText = ExcelDefaultableBoolean.True
wb.WindowOptions.SelectedWorksheet.Columns(5).Width = 8689
wb.WindowOptions.SelectedWorksheet.Columns(5).CellFormat.WrapText = ExcelDefaultableBoolean.True
wb.WindowOptions.SelectedWorksheet.Columns(6).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(6).CellFormat.WrapText = ExcelDefaultableBoolean.True
wb.WindowOptions.SelectedWorksheet.Columns(7).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(8).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(9).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(10).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(11).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(12).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(13).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(14).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(15).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(16).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(17).Width = 3689
wb.WindowOptions.SelectedWorksheet.Columns(18).Width = 3689
'Write the in memory object to our Custom Stream
'BIFF8Writer.WriteWorkbookToStream(wb, objStream)
wb.Save(objStream)
'wb.Save(wb, System.IO.StreamWriter)
'Create a Byte Array to contain the stream
objStream.Position = 0 'Set Stream Starting point
objStream.Read(byteArr, 0, CType(objStream.Length, Integer)) 'Read Stream into Byte Array
objStream.Close() 'Close the stream
Response.Clear() 'Clear the response object
Response.AddHeader("content-disposition", "attachment; filename=SalesByPartQuantity.xls") 'Set the Content Header
Response.BinaryWrite(byteArr) 'Write the byte array to the output stream
Response.End() 'End the response
Any help would be greatly appreciated.
Thanks,
K
Thank you. That was very helpful. It is doing what I want it to now.
There should be two ways of accomplishing this. Either you can set the ExcelStartRow property of the WebGrid ExcelExporter control up near the beginning to the index of the first row you would like your WebGrid exported to (ie, an index after your merged cells header region appears):
Me.EE1.DownloadName = "SalesByPart_Quantity.xls"Me.EE1.ExcelStartRow = 5
or you can use one of the Export sub overloads that let you pass in starting row and starting column arguments as value parameters:
Me.EE1.Export(Me.DG9, wb, 5, 1) ' start at row 5, column A
HTH,