SCROLL DOWN
Styling Excel

Excel cells can be styled using a JSON object parameter while filling up the cell values or with getCell and getRange methods. Let us see working of getCell and getRange methods first. To get a reference of a cell user can use getCell method, the output of this method let user see value of the cell or style the cell. This method can be chained with style method for styling cells. Similar to getCell, oxml.js also provide getRange method which let user style a range of cells. However, this method does not allow let user know the value of cells. We will understand more these method in below example.

For styling user have to use a JSON object with some predefined attributes. This JSON can be used with different methods for filling the value or get cell / range reference. While filling the values this is an additional parameter passed to any of the methods used. Methods supporting styling while filling the values are:

  • cell
  • column
  • row
  • grid
  • sharedFormula
They all take an additional parameter as expalined in below examples for cell and row, although similar implementation can be obtained in all the methods. Appart from passing the JSON object while filling the value, user can also chain a style method just like getCell and getRange to the output of all above methods. The JSON object which can be passed for styling can have following attributes:
  • bold will format the font in cell to bold/emphasize content.
  • italic will format the font in cell to italic content.
  • underline will format the font in cell to underline content.
  • strike will format the font in cell to strike content.
  • fontSize will change the font size of content.
  • fontColor will change the font color of content. This attribute only support RBG values in string format.
  • fontName will change the font name.
  • fontFamily will change the font family.
  • scheme will change the font scheme.
  • numberFormat will format the numeric content. Some of the standard defined numeric formats are "mm-dd-yy"; "m/d/yy h:mm"; "#,##0 ;(#,##0)"; "#,##0 ;[Red](#,##0)"; "#,##0.00;(#,##0.00)"; "#,##0.00;[Red](#,##0.00)"; "mmss.0"; "yyyy-mm-dd". For more number format expressions, refer open xml documentation.
  • border will format the border of the cell. This attribute is again a JSON object. If color and style of border is defined than all the borders of cell will be updated. Appart from this user may provide a specific border for left, right, top, bottom and diagonal with some specific value. eg. border: {color: "ff0000", style: "thick"} will update all the borders and border: {right: {color: "ff0000", style: "thick"}} will update just the right border. User can provide combination of both the methods. Refer below examples for more details.
  • fill will format the fill / color of cell. This attribute is again a JSON object. Fill may be used with pattern or gradient options. For using pattern, user defined pattern attribute, foreColor and backColor. pattern is a string value of pattern used in excel, foreColor and backColor are string values of RGB colors. For using gradient, user define gradient with child attributes degree, bottom, left, right, top, type, and stops. degree, bottom, left, right, top, and type are values of gradient fill used in excel. stops is the array object with defined position and color attribute. color of a stop is again RGB value. Refer open xml documentation for details on providing gradient and pattern fills.

Styling with cell
cell() let user take a reference to cell and update style or get it's value. This method can be chained with style method for styling a cell. This method takes two arguments rowIndex, and columnIndex, which are 1 based index of row and column to select the cell.
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.cell(2, 2, 'Hello World!', {fontColor: 'ff0000'});
worksheet.cell(2, 2).style({ bold: true });
workbook.download('workbook.xslx');

Styling with row
To provide styling user have to pass as optional parameter to row() method. This optional parameter define styling with some predefined properties as explained above.
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.row(1, 1, 'Total of Data', {
 fill: {
  gradient: {
   degree: 90,
   stops: [{
    position: 0,
    color: 'FF92D050'
   },
   {
    position: 1,
    color: 'FF0070C0'
   }]
  }
 },
 fontColor: 'ffffff',
 bold: true,
 underline: true
});
worksheet.row(2, 2, ['Data 1', 'Data 2', { type: 'sharedString', value: 'Total' }], {
 bold: true,
 italic: true,
 underline: true,
 fontName: 'Calibri Light',
 fontColor: '0000ff'
});
worksheet.row(3, 2, [5, 9]);
worksheet.row(4, 2, [7, 3]);
worksheet.row('D3', 'D4', '(B3 + C3)', { bold: true });
workbook.download('workbook.xslx');