How to include background colors while exporting excel without any gems in rails?

I saw this article which gives a simple example on how to export data into Excel sheet without using any gem in Rails:

https://makandracards.com/makandra/31567-exporting-to-excel-from-rails-without-a-gem

I believe the above article is based on a RailsCast tutorial on the same topic:

http://railscasts.com/episodes/362-exporting-csv-and-excel?view=asciicast

I would like to know how to include background colors and row borders (and also border colors) please. Please help!

1 answer

  • answered 2018-11-08 06:47 mkrl

    If you are using the "manual" XML approach, a good way to start is an official Microsoft XML Spreadsheet Reference.

    There are many options available, for example a document with borders may look something like this.

    <?xml version="1.0"?>
    <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">
      <Styles> 
          <Style ss:ID="h1" ss:Name="First">
            <Borders>
              <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
              <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
              <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
              <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders> 
          </Style>   
      </Styles> 
    
      <Worksheet ss:Name="Sheet1">
        <Table>
          <Row>
            <Cell ss:StyleID="h1"><Data ss:Type="String">ID</Data></Cell>
            <Cell ss:StyleID="h1"><Data ss:Type="String">Name</Data></Cell>
            <Cell ss:StyleID="h1"><Data ss:Type="String">Release Date</Data>    </Cell>
            <Cell ss:StyleID="h1"><Data ss:Type="String">Price</Data></Cell>
          </Row>
        </Table>
      </Worksheet>
    </Workbook>
    

    You can define different styles at the beginning of the workbook section and then apply them to different cells.