Export Gridview In Excel and format cell RSS

• • Reply
  

  fab77500

  0 Points

  5 Posts

  Export Gridview In Excel and format cell

  Dec 24, 2012 02:18 PM|LINK

  Hello,

  I try to format data from a gridview to an excel file.
  I can easily modify the color of the text in each cells but how can I center the data ?.
  And how can apply it only for the cells with data and not for all the rows ?

  Thanks for yours help.
  fabrice

  Here is my code :

  Dim GridView1 As New GridView()
   GridView1.AllowPaging = False
   GridView1.DataSource = lds
  
  GridView1.DataBind()
  
  
   'Format Header
   GridView1.HeaderStyle.Font.Bold = True
   For i As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
    GridView1.HeaderRow.Cells(i).Style.Add("background-color", "#BDCFE7")
   Next
  
  
   Dim sw As New StringWriter()
   Dim hw As New HtmlTextWriter(sw)
    
   For i As Integer = 0 To GridView1.Rows.Count - 1
    'Apply text style to each Row (ligne)
    'Text Color
    GridView1.Rows(i).ForeColor =System.Drawing.Color.Black   <------- forecolor
   Next
   GridView1.RenderControl(hw)
  
  'Write the rendered content to a file.
   Dim renderedGridView As String = sw.ToString()
   System.IO.File.WriteAllText(System.Configuration.ConfigurationSettings.AppSettings.Item("ExportXlsPath") & lMyFileXlsName , renderedGridView)
   End Sub 'SetDataTable_To_Excel

  • Edited by XIII on Dec 24, 2012 02:37 PM
  • Moved by XIII on Dec 24, 2012 02:38 PM
• • Reply
  

  oned_gk

  All-Star

  36226 Points

  7377 Posts

  Re: Export Gridview In Excel and format cell

  Dec 24, 2012 02:53 PM|LINK

  if you wanna change color without conditional. I think the simple way by changing itemstyle/rowstyle etc directly in html tag.
  Programming to simplify, dont look for hard way
  Suwandi - Non Graduate Programmer
• • Reply
  

  sarathi125

  Star

  13599 Points

  2691 Posts

  Re: Export Gridview In Excel and format cell

  Dec 24, 2012 03:05 PM|LINK

  Hi,

  Try like the following,

  e.Row.Cells(4).HorizontalAlign = HorizontalAlign.Center

  Parthasarathi,
  Remember to click Mark as Answer on the post that helps to others.
  
  My Blog :MyAspSnippets
• • Reply
  

  oned_gk

  All-Star

  36226 Points

  7377 Posts

  Re: Export Gridview In Excel and format cell

  Dec 24, 2012 03:35 PM|LINK

  use design mode and modify itemstyle each column.
  Programming to simplify, dont look for hard way
  Suwandi - Non Graduate Programmer
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Export Gridview In Excel and format cell

  Dec 25, 2012 05:18 AM|LINK

  fab77500

  fab77500

  Hi,

  You can first try to set some styles to the cells by applying the HeaderStyle, ItemStyle properties, and then do this:

  http://www.highoncoding.com/Articles/197_Extensive_Study_of_GridView_Export_to_Excel.aspx

• • Reply
  

  fab77500

  0 Points

  5 Posts

  Re: Export Gridview In Excel and format cell

  Dec 29, 2012 02:57 PM|LINK

  Hello,
  
  Thanks for your help.
  Here is my solution.
  
  'Programmatically Register The event-handling method "GridView1_RowDataBound"
  AddHandler GridView1.RowDataBound, AddressOf GridView1_RowDataBound
  
  
  and the "GridView1_RowDataBound"
  
  Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
  'Cells Format With Event RowDataBound
  'Format par identification
                  If e.Row.RowType = DataControlRowType.DataRow Then
                   'Cells(1) REFCEA: Display in italics & Alignement CENTER
                        e.Row.Cells(1).Text = "<i>" & e.Row.Cells(1).Text & "</i>"
                                    e.Row.Cells(1).HorizontalAlign = HorizontalAlign.Center
  
                                 'Cells(2) CODPAYS: Alignement CENTER
                                   e.Row.Cells(2).HorizontalAlign = HorizontalAlign.Center
  
                                 'Cells(3) CODTYP: Alignement CENTER
                                   e.Row.Cells(3).HorizontalAlign = HorizontalAlign.Center
                                   
                                   'Cells(4) INDICE: Format Text et Alignement CENTER
                                   e.Row.Cells(4).Attributes.Add("style", "mso-number-format:\@")
                                    e.Row.Cells(4).HorizontalAlign = HorizontalAlign.Center
  
                                   'Cells(5) CODCAB: Alignement CENTER
                                   e.Row.Cells(5).HorizontalAlign = HorizontalAlign.Center
  
                                   'Cells(6) LIBNH: Alignement CENTER
                                   e.Row.Cells(6).HorizontalAlign = HorizontalAlign.Left
  
                                   'Cells(7) NUMNH: Format text  & Alignement CENTER
                                   e.Row.Cells(7).Attributes.Add("style", "mso-number-format:\@")
                                    e.Row.Cells(7).HorizontalAlign = HorizontalAlign.Center
                                   
                                   'Cells(8) DATNH: Alignement CENTER
                                   e.Row.Cells(8).HorizontalAlign = HorizontalAlign.Center
                                    
                                   'Cells(9) QUOTEPART: Alignement CENTER
                                   e.Row.Cells(9).HorizontalAlign = HorizontalAlign.Center
                                   
                  End If
  End Sub 'GridView1_RowDataBound

   

  Fabrice

  • Marked as answer by decker dong - msft on Dec 30, 2012 03:15 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Export Gridview In Excel and format cell

  Dec 30, 2012 12:37 AM|LINK

  fab77500

  fab77500

  Hi，

  If your problem is solved，I'll mark it as an answer to close your issue.

  Anything important, please feel free to feedback by creating another thread.