Export data to excel with color

  1. #1

    Export data to excel with color

    Hello !!

    I have a GRID on my page, and one of the columns it has color.

    For example:

    If the field is equal to "24" the cell is green, if the field is "36 Hours" yellow.

    I wonder how I export the Grid to excel with these colors according to the cell value.

    And when I import to excel one column called Value with negative values ​​that do not belong to my Store appears.

    Follows the PRINT GRID and Excel file

    Click image for larger version. 

Name:	GRID.png 
Views:	17 
Size:	73.9 KB 
ID:	15752Click image for larger version. 

Name:	File_Excel.png 
Views:	14 
Size:	76.1 KB 
ID:	15761

    I thank any help.

    Button of Export
    protected void StoreFechadosRTBCapital_Submit(object sender, StoreSubmitDataEventArgs e)
            {
                string format = this.FormatType.Value.ToString();
                XmlNode xml = e.Xml;
                this.Response.ContentType = "application/vnd.ms-excel";
                this.Response.AddHeader("Content-Disposition", "attachment; filename=FechadosRTBCapital_" + DateTime.Now + ".xls");
                XslCompiledTransform xtExcel = new XslCompiledTransform();
                xtExcel.Load(Server.MapPath("~/App_Themes/CSS/Excel.xsl"));
                xtExcel.Transform(xml, null, Response.OutputStream);
                this.Response.End();
     
            }
    Store
     <ext:JsonReader IDProperty="Value">
                    <Fields>
                       <ext:RecordField Name="HISREPETIDA" SubmitEmptyValue="EmptyString"  />
                        <ext:RecordField Name="LOCESCRITORIO" SubmitEmptyValue="EmptyString"  />
                        <ext:RecordField Name="LOCAREA" SubmitEmptyValue="EmptyString"  />
                        <ext:RecordField Name="FRACODSC" SubmitEmptyValue="EmptyString"  />
                        <ext:RecordField Name="GESRESPONSABLE" SubmitEmptyValue="EmptyString"  />
                        <ext:RecordField Name="LOCNOTERMINAL" SubmitEmptyValue="EmptyString" />
                        <ext:RecordField Name="DT_RECLAMACAO" Type="Date" SubmitEmptyValue="EmptyString">
                         <Convert Handler="return Ext.util.Format.date(value,'d/m/Y H:i:s')" />
                        </ext:RecordField>
                        <ext:RecordField Name="DT_PRE_BAIXA" Type="Date" SubmitEmptyValue="EmptyString">
                        <Convert Handler="return Ext.util.Format.date(value,'d/m/Y H:i:s')" />
                        </ext:RecordField>
                        <ext:RecordField Name="HORAS" Type="Float" SubmitEmptyValue="EmptyString">
                        
                        </ext:RecordField>
                        <ext:RecordField Name="PRAZO" SubmitEmptyValue="EmptyString"/>
                    </Fields>
                </ext:JsonReader>
    Excel.xsl

    <xsl:stylesheet version="1.0"
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
     xmlns:msxsl="urn:schemas-microsoft-com:xslt"
     xmlns:user="urn:my-scripts"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> 
     
    <xsl:template match="/">
      <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
        <xsl:apply-templates/>
      </Workbook>
    </xsl:template>
    
    
    <xsl:template match="/*">
      <Worksheet>
      <xsl:attribute name="ss:Name">
      <xsl:value-of select="local-name(/*/*)" />
      </xsl:attribute>
        <Table x:FullColumns="1" x:FullRows="1">
          <Row>
            <xsl:for-each select="*[position() = 1]/*">
              <Cell><Data ss:Type="String">
              <xsl:value-of select="local-name()" />
              </Data></Cell>
            </xsl:for-each>
          </Row>
          <xsl:apply-templates/>
        </Table>
      </Worksheet>
    </xsl:template>
    
    
    <xsl:template match="/*/*">
      <Row>
        <xsl:apply-templates/>
      </Row>
    </xsl:template>
    
    
    <xsl:template match="/*/*/*">
      <Cell><Data ss:Type="String">
        <xsl:value-of select="." />
      </Data></Cell>
    </xsl:template>
      
      
    
    
    </xsl:stylesheet>
  2. #2
    Hi @Cris,

    I wonder how I export the Grid to excel with these colors according to the cell value.
    I think it might be possible to achieve in the Excel.xsl file. This thread looks promise.
    http://stackoverflow.com/questions/1...ting-2-columns

    And when I import to excel one column called Value with negative values ​​that do not belong to my Store appears.
    I think it is because of the IDProperty="Value" setting on JsonReader. Is there "Value" in the data that you bind to the Store?
  3. #3
    Thanks Daniil,

    But it's still not working, I tried to adapt the code to my need, but do not know what I'm doing wrong.

    Now only the header appears.

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" indent="yes"/>
    
      <xsl:template match="/">
        <table border="1">
          <tr bgcolor="lightgrey" cellspacing="right">
            <td>HISREPETIDA</td>
            <td>LOCESCRITORIO</td>
            <td>LOCAREA</td>
            <td>FRACODSC</td>
            <td>GESRESPONSABLE</td>
            <td>LOCNOTERMINAL</td>
            <td>DT_RECLAMACAO</td>
            <td>DT_PRE_BAIXA</td>
            <td>HORAS</td>
            <td>PRAZO</td>
          </tr>
          
          
          <xsl:for-each select="xml">
            <tr bgcolor="lightyellow">
              <xsl:for-each select="./*">
                <td>
                  <xsl:attribute name="bgcolor">
                    <xsl:choose>
                      <xsl:when test="PRAZ0 = '24 HORAS'">
                        <xsl:text>F08080</xsl:text>
                      </xsl:when>
                      <xsl:when test="PRAZ0 = '36 HORAS'">
                        <xsl:text>LightGreen</xsl:text>
                      </xsl:when>
                    </xsl:choose>
                  </xsl:attribute>
                  <xsl:value-of select="."/>
                </td>
              </xsl:for-each>
            </tr>
          </xsl:for-each>
        </table>
      </xsl:template>
    </xsl:stylesheet>
    Attached Thumbnails Click image for larger version. 

Name:	File_Excel_2.png 
Views:	9 
Size:	24.1 KB 
ID:	15781  
  4. #4
    I am not quite experienced in XSL, so, I am afraid I cannot help you. It might be better to ask that on some more specialized Excel-related forum.
  5. #5
    Thanks for Help.

Similar Threads

  1. [CLOSED] Export of Grid data to Excel
    By jesperhp in forum 2.x Legacy Premium Help
    Replies: 3
    Last Post: May 14, 2014, 3:32 PM
  2. [CLOSED] Export data to excel
    By immenso in forum 2.x Legacy Premium Help
    Replies: 4
    Last Post: Dec 16, 2013, 4:42 AM
  3. Export grid panel data to excel
    By sumesh in forum 1.x Help
    Replies: 1
    Last Post: Sep 23, 2013, 9:07 AM
  4. MVC Razor - export data to excel or csv
    By Ishrath in forum 2.x Help
    Replies: 4
    Last Post: Nov 14, 2012, 11:39 AM
  5. How can Export data from GridPanel to Excel
    By delta in forum 2.x Help
    Replies: 2
    Last Post: Nov 14, 2012, 1:07 AM

Tags for this Thread

Posting Permissions