This is a migrated thread and some comments may be shown as answers.

Column Header Wrapping and Cell Formatting with ExcelToExport

1 Answer 128 Views
Grid
This is a migrated thread and some comments may be shown as answers.
David
Top achievements
Rank 1
David asked on 25 Sep 2015, 12:07 AM

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

Sort by
0
David
Top achievements
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 = False
style.FontStyle.FontName = "Arial"
style.FontStyle.Size = 8
style.AlignmentElement.Attributes("ss:WrapText") = 1
e.WorkBook.Styles.Add(style)
 
Dim numberDetail As New StyleElement("NumberDetail")
numberDetail.FontStyle.Bold = False
numberDetail.FontStyle.FontName = "Arial"
numberDetail.FontStyle.Size = 8
numberDetail.NumberFormat.Attributes.Add("ss:Format", "#,###,###,##0")
e.WorkBook.Styles.Add(numberDetail)
 
Dim header As New StyleElement("Header")
header.FontStyle.Bold = True
header.FontStyle.FontName = "Arial"
header.FontStyle.Size = 8
header.AlignmentElement.Attributes("ss:WrapText") = 1
e.WorkBook.Styles.Add(header)
Tags
Grid
Asked by
David
Top achievements
Rank 1
Answers by
David
Top achievements
Rank 1
Share this question
or