I have a radgrid on my aspx page. I build the grid/columns dynamically in the code behind. I use the NeedDataSource to populate the data and then use the ExportToExcel command to export the grid. I cannot find how to either wrap the column headers and/or format my numeric columns with commas. Below is the code and the aspx page and code-behind. Please help on how to do both.
A button on my page calls the exportSummary() function.
<telerik:RadGrid ID="rgExportSummary" runat="server" OnExcelMLWorkBookCreated="rgExportSummary_ExcelMLWorkBookCreated" OnNeedDataSource="rgExportSummary_NeedDataSource"> <MasterTableView Visible="false" HierarchyDefaultExpanded="true" Font-Names="Arial" Font-Size="8pt" AutoGenerateColumns="false"> <HeaderStyle Font-Names = "Arial" Font-Bold = "true" Font-Size = "8pt" /> <ItemStyle Font-Names="Arial" Font-Bold="false" Font-Size="8pt" /> </MasterTableView> </telerik:RadGrid>
Public Sub exportSummary()
Dim sql As String = ""
Try
sql = "exec ibuy.dbo.WB_sp_PrintBuyByReports_ForMultipleEvents " & buyByReportID
mobjDatabase.subExecuteNonQuery(sql)
Catch ex As SqlException
Throw New Exception(ex.Message & ", SQL: " & sql & ",frmPlanner.exportSummary")
End Try
Dim nsaid As New GridBoundColumn
nsaid.HeaderStyle.Width = Unit.Pixel(50)
nsaid.ItemStyle.Width = Unit.Pixel(50)
nsaid.DataField = "VendorMasterCD"
nsaid.HeaderText = "NSA ID"
nsaid.UniqueName = "VendorMasterCD"
rgExportSummary.MasterTableView.Columns.Add(nsaid)
Dim vendor As New GridBoundColumn
vendor.HeaderStyle.Width = Unit.Pixel(50)
vendor.ItemStyle.Width = Unit.Pixel(50)
vendor.DataField = "VendorName"
vendor.HeaderText = "Vendor"
vendor.UniqueName = "VendorName"
rgExportSummary.MasterTableView.Columns.Add(vendor)
Dim product As New GridBoundColumn
product.HeaderStyle.Width = Unit.Pixel(50)
product.ItemStyle.Width = Unit.Pixel(50)
product.DataField = "VendorProduct"
product.HeaderText = "Product"
product.UniqueName = "VendorProduct"
rgExportSummary.MasterTableView.Columns.Add(product)
Dim productid As New GridBoundColumn
productid.HeaderStyle.Width = Unit.Pixel(50)
productid.ItemStyle.Width = Unit.Pixel(50)
productid.DataField = "ProductID"
productid.HeaderText = "Product ID"
productid.UniqueName = "ProductID"
rgExportSummary.MasterTableView.Columns.Add(productid)
If Not cbBreakoutByProduct.Checked Then
Dim circset As New GridBoundColumn
circset.HeaderStyle.Width = Unit.Pixel(50)
circset.ItemStyle.Width = Unit.Pixel(50)
circset.DataField = "CirculationSet"
circset.HeaderText = "Circ Set"
circset.UniqueName = "CirculationSet"
rgExportSummary.MasterTableView.Columns.Add(circset)
Dim release As New GridBoundColumn
release.HeaderStyle.Width = Unit.Pixel(50)
release.ItemStyle.Width = Unit.Pixel(50)
release.DataField = "Release"
release.HeaderText = "Release"
release.UniqueName = "Release"
rgExportSummary.MasterTableView.Columns.Add(release)
End If
If cbBreakoutByDay.Checked Then
Dim day As New GridBoundColumn
day.HeaderStyle.Width = Unit.Pixel(50)
day.ItemStyle.Width = Unit.Pixel(50)
day.DataField = "DistributionDay"
day.HeaderText = "Day"
day.UniqueName = "DistributionDay"
rgExportSummary.MasterTableView.Columns.Add(day)
End If
If cbBreakoutByDay.Checked And cbIncludeDeliveryCodes.Checked Then
Dim deliveryCodes As New GridBoundColumn
deliveryCodes.HeaderStyle.Width = Unit.Pixel(50)
deliveryCodes.ItemStyle.Width = Unit.Pixel(50)
deliveryCodes.DataField = "DeliveryCode"
deliveryCodes.HeaderText = "Delivery Code"
deliveryCodes.UniqueName = "DeliveryCode"
rgExportSummary.MasterTableView.Columns.Add(deliveryCodes)
End If
If cbBreakoutByDay.Checked And cbIncludeVersions.Checked Then
Dim versions As New GridBoundColumn
versions.HeaderStyle.Width = Unit.Pixel(50)
versions.ItemStyle.Width = Unit.Pixel(50)
versions.DataField = "VersionsDescription"
versions.HeaderText = "Version"
versions.UniqueName = "VersionsDescription"
rgExportSummary.MasterTableView.Columns.Add(versions)
End If
Dim rop As New GridBoundColumn
rop.HeaderStyle.Width = Unit.Pixel(50)
rop.ItemStyle.Width = Unit.Pixel(50)
rop.DataField = "ROPIndValue"
rop.HeaderText = "ROP"
rop.UniqueName = "ROPIndValue"
rgExportSummary.MasterTableView.Columns.Add(rop)
'loop through criteria table to get what is visible
sql = ""
Dim columnDataField As String = ""
Dim columnIndex As Integer = 1
Dim maxProductID As Integer = 0
If cbBreakoutByProduct.Checked Then
Try
sql = "Select productid,count(*) from ibuy.dbo.Tmp_BuyByGeo_SummaryEvents_" & buyByReportID & " group by productid order by 2 desc"
mobjSqlDataReader = mobjDatabase.fnRetrieveData("DATAREADER", sql)
mobjSqlDataReader.Read()
maxProductID = mobjSqlDataReader("productid")
mobjSqlDataReader.Close()
sql = "Select b.* from ibuy.dbo.Tmp_BuyByGeo_SummaryEvents_" & buyByReportID & " a, ibuy.dbo.tmp_WB_BuyByReportsList" & UcHeader.SessionId & " b where a.productid = " & maxProductID & " and a.EventsID = b.EventID and a.EventDistributionPatternID = b.EventDistributionPatternID order by b.EventID, b.EventDistributionPatternID"
mobjSqlDataReader = mobjDatabase.fnRetrieveData("DATAREADER", sql)
Do While mobjSqlDataReader.Read
Dim day As New GridBoundColumn
day.HeaderStyle.Width = Unit.Pixel(150)
day.ItemStyle.Width = Unit.Pixel(150)
columnDataField = "Day" & columnIndex
If mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim <> "" Then
day.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " " & mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim.Replace(">", "").Replace("<", "") & " Day"
Else
day.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " Day"
End If
day.DataField = columnDataField
day.UniqueName = columnDataField
Dim circSet1 As New GridBoundColumn
circSet1.HeaderStyle.Width = Unit.Pixel(150)
circSet1.ItemStyle.Width = Unit.Pixel(150)
columnDataField = "CirculationSet" & columnIndex
If mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim <> "" Then
circSet1.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " " & mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim.Replace(">", "").Replace("<", "") & " Circ Set"
Else
circSet1.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " Circ Set"
End If
circSet1.DataField = columnDataField
circSet1.UniqueName = columnDataField
Dim release1 As New GridBoundColumn
release1.HeaderStyle.Width = Unit.Pixel(150)
release1.ItemStyle.Width = Unit.Pixel(150)
columnDataField = "Release" & columnIndex
If mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim <> "" Then
release1.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " " & mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim.Replace(">", "").Replace("<", "") & " Release"
Else
release1.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " Release"
End If
release1.DataField = columnDataField
release1.UniqueName = columnDataField
Dim circulation As New GridBoundColumn
circulation.HeaderStyle.Width = Unit.Pixel(150)
circulation.ItemStyle.Width = Unit.Pixel(150)
columnDataField = "Circulation" & columnIndex
If mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim <> "" Then
circulation.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " " & mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim.Replace(">", "").Replace("<", "") & " Circulation"
Else
circulation.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " Circulation"
End If
circulation.HeaderStyle.Wrap = True
circulation.DataField = columnDataField
circulation.UniqueName = columnDataField
rgExportSummary.MasterTableView.Columns.Add(day)
rgExportSummary.MasterTableView.Columns.Add(circSet1)
rgExportSummary.MasterTableView.Columns.Add(release1)
rgExportSummary.MasterTableView.Columns.Add(circulation)
columnIndex += 1
Loop
mobjSqlDataReader.Close()
Catch ex As SqlException
Throw New Exception(ex.Message & ", SQL: " & sql & ",frmPlanner.exportZipDetail")
End Try
Else
Try
sql = "Select * from ibuy.dbo.tmp_WB_BuyByReportsList" & UcHeader.SessionId & " order by EventID, EventDistributionPatternID"
mobjSqlDataReader = mobjDatabase.fnRetrieveData("DATAREADER", sql)
Do While mobjSqlDataReader.Read
Dim criteria As New GridBoundColumn
criteria.HeaderStyle.Width = Unit.Pixel(150)
criteria.ItemStyle.Width = Unit.Pixel(150)
columnDataField = "Circulation" & columnIndex
If mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim <> "" Then
criteria.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " " & mobjSqlDataReader("EventDistributionPatternCode").ToString.Trim.Replace(">", "").Replace("<", "") & " Circulation"
Else
criteria.HeaderText = mobjSqlDataReader("EventName").ToString.Trim.Replace(">", "").Replace("<", "") & " Circulation"
End If
criteria.DataField = columnDataField
criteria.UniqueName = columnDataField
rgExportSummary.MasterTableView.Columns.Add(criteria)
columnIndex += 1
Loop
mobjSqlDataReader.Close()
Catch ex As SqlException
Throw New Exception(ex.Message & ", SQL: " & sql & ",frmPlanner.exportZipDetail")
End Try
End If
If columnIndex = 3 Then
Dim difference As New GridBoundColumn
difference.HeaderStyle.Width = Unit.Pixel(150)
difference.ItemStyle.Width = Unit.Pixel(150)
difference.HeaderText = "Difference"
difference.DataField = "Difference"
difference.UniqueName = "Difference"
rgExportSummary.MasterTableView.Columns.Add(difference)
End If
If cbShowMinimumRequirements.Checked Then
Dim sundayMinimum As New GridBoundColumn
sundayMinimum.HeaderStyle.Width = Unit.Pixel(50)
sundayMinimum.ItemStyle.Width = Unit.Pixel(50)
sundayMinimum.DataField = "SundayMinQty"
sundayMinimum.HeaderText = "Sunday Minimum Quantity"
sundayMinimum.UniqueName = "SundayMinQty"
rgExportSummary.MasterTableView.Columns.Add(sundayMinimum)
Dim dailyMinimum As New GridBoundColumn
dailyMinimum.HeaderStyle.Width = Unit.Pixel(50)
dailyMinimum.ItemStyle.Width = Unit.Pixel(50)
dailyMinimum.DataField = "DailyMinQty"
dailyMinimum.HeaderText = "Daily Minimum Quantity"
dailyMinimum.UniqueName = "DailyMinQty"
rgExportSummary.MasterTableView.Columns.Add(dailyMinimum)
Dim sundayCharge As New GridBoundColumn
sundayCharge.HeaderStyle.Width = Unit.Pixel(50)
sundayCharge.ItemStyle.Width = Unit.Pixel(50)
sundayCharge.DataField = "SundayMinCharge"
sundayCharge.HeaderText = "Sunday Minimum Charge"
sundayCharge.UniqueName = "SundayMinCharge"
rgExportSummary.MasterTableView.Columns.Add(sundayCharge)
Dim dailyCharge As New GridBoundColumn
dailyCharge.HeaderStyle.Width = Unit.Pixel(50)
dailyCharge.ItemStyle.Width = Unit.Pixel(50)
dailyCharge.DataField = "DailyMinCharge"
dailyCharge.HeaderText = "Daily Minimum Charge"
dailyCharge.UniqueName = "DailyMinCharge"
rgExportSummary.MasterTableView.Columns.Add(dailyCharge)
End If
rgExportSummary.Rebind()
rgExportSummary.ExportSettings.Excel.Format = DirectCast([Enum].Parse(GetType(GridExcelExportFormat), "ExcelML"), GridExcelExportFormat)
rgExportSummary.ExportSettings.ExportOnlyData = True
rgExportSummary.ExportSettings.OpenInNewWindow = True
rgExportSummary.MasterTableView.ExportToExcel()
End Sub
Protected Sub rgExportSummary_ExcelMLWorkBookCreated(sender As Object, e As Telerik.Web.UI.GridExcelBuilder.GridExcelMLWorkBookCreatedEventArgs) Handles rgExportSummary.ExcelMLWorkBookCreated
Dim index As Integer = 0
For Each row As RowElement In e.WorkBook.Worksheets(0).Table.Rows
If index = 0 Then
For Each cell As CellElement In row.Cells
cell.StyleValue = "Header"
Next
Else
For Each cell As CellElement In row.Cells
cell.StyleValue = "Detail"
Next
End If
index += 1
Next
Dim style As New StyleElement("Detail")
style.FontStyle.Bold = False
style.FontStyle.FontName = "Arial"
style.FontStyle.Size = 8
e.WorkBook.Styles.Add(style)
Dim header As New StyleElement("Header")
header.FontStyle.Bold = True
header.FontStyle.FontName = "Arial"
header.FontStyle.Size = 8
e.WorkBook.Styles.Add(header)
End Sub
Protected Sub rgExportSummary_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles rgExportSummary.NeedDataSource
Dim sSQL As String = ""
Dim objDataTable As New DataTable
If export Then
Try
If cbBreakoutByProduct.Checked Then
sSQL = "select * from iBuy.dbo.Tmp_BuyByGeo_ForMultipleEvents_" & buyByReportID & " order by VendorMasterCD"
Else
sSQL = "select * from iBuy.dbo.Tmp_BuyByGeo_ForMultipleEvents_" & buyByReportID & " order by VendorMasterCD, CirculationSet"
End If
objDataTable = GetDataTable(sSQL)
rgExportSummary.DataSource = objDataTable
Catch ex As SqlException
Throw New Exception(ex.Message & ", SQL: " & sSQL & ",frmPlanner.subLoadStates")
End Try
End If
End Sub
1 Answer, 1 is accepted
0
David
Top achievements
Rank 1
Rank 1
answered on 25 Sep 2015, 03:38 PM
I found both solutions. Below is the code I changed in the ExcelMLWorkBookCreated function:
Dim style As New StyleElement("Detail")style.FontStyle.Bold = Falsestyle.FontStyle.FontName = "Arial"style.FontStyle.Size = 8style.AlignmentElement.Attributes("ss:WrapText") = 1e.WorkBook.Styles.Add(style)Dim numberDetail As New StyleElement("NumberDetail")numberDetail.FontStyle.Bold = FalsenumberDetail.FontStyle.FontName = "Arial"numberDetail.FontStyle.Size = 8numberDetail.NumberFormat.Attributes.Add("ss:Format", "#,###,###,##0")e.WorkBook.Styles.Add(numberDetail)Dim header As New StyleElement("Header")header.FontStyle.Bold = Trueheader.FontStyle.FontName = "Arial"header.FontStyle.Size = 8header.AlignmentElement.Attributes("ss:WrapText") = 1e.WorkBook.Styles.Add(header)