Magic with JExcel

            Most of the automation Frameworks deal with generating reports for various situations. Usually such documents should be designed for reviewing by people who are not closely familiar with different technologies and prefer to obtain reports in a usual format. For Microsoft Windows users, the most common office solution is Microsoft Office, and when talking about electronic worksheets, it is undoubtedly Microsoft Excel.

           The JExcel library allows Java programmers for Windows to easily create, modify or display Microsoft Excel files. Using this library, you can work with workbook files, print a workbook, worksheet or even a specific range of cells, and modify cell values, cell number formats, etc. Also, there is an ability to customize cell appearance, including text font, cell background and border, text alignment and orientation, etc.

           JExcel can be used for solving different tasks from simple display of a workbook in a standalone Excel application to handling Excel events and embedding a workbook into Java Swing applications. The use of the library is very simple and doesn't require from a Java programmer specific knowledge of Microsoft Office programming.

           Many of us knows that JExcel can be used to read and write the excel sheets. But, in this post we will explore various options available for customizing cells in Excel Sheets.

1. Changing a Number Format:
            The range number format reflects the way of displaying numeric data. For instance, a double value can be represented as an integer or as a date. A number format  in Excel is set by a string pattern in a specific locale-dependent format. For detailed specification of the number format, please refer to appropriate articles  on Excel.
         //Getting a range number format
         Range range = worksheet.getRange("A1:A3");
         String numberFormat = range.getNumberFormat();
         System.out.println("A1:A3 number format is " + numberFormat);

  The Range.setNumberFormat(String) method changes the number format of a range:
         //Setting a custom number format
         String newNumberFormat = "0,00%";
         range.setNumberFormat(newNumberFormat);
         System.out.println("A1:A3 new number format is " + range.getNumberFormat());

2. Changing Text Alignment:            

               JExcel provides the TextAlignment class, which is an enumeration of all alignment types supported by Excel. The Range (Cell) class enables you to obtain and modify both horizontal and vertical text alignment.

             The Range.getHorizontalAlignment() method returns the current horizontal text alignment as an instance of the TextAlignment class. The possible values are TextAlignment.CENTER,TextAlignment.DISTRIBUTED, TextAlignment.FILL,TextAlignment.GENERAL,TextAlignment.JUSTIFY, TextAlignment.LEFT and TextAlignment.RIGHT. If the range cells have mixed horizontal alignment, the return value is null. To change horizontal alignment of a range, call the Range.setHorizontalAlignment() method:

       //Setting custom horizontal text alignment
        range.setHorizontalAlignment(TextAlignment.RIGHT);

        //Checking horizontal text alignment
        if (range.getHorizontalAlignment().equals(TextAlignment.RIGHT))
        {
            System.out.println("A1:A3 range: new horizontal text alignment was applied successfully.");
        }
        else
        {
            System.out.println("Horizontal text alignment failed to be applied.");
        }

3. Changing the Font Size:              

                 The Font class from the com.jniwrapper.win32.jexcel package provides the ability to customize fonts in Excel.

 The following font attributes can be obtained or modified:

 

• Font name  

       Call the Font.getName() method to get the font name, and the Font.setName(String) method to specify the font name.

   

• Font size
     Call the Font.getSize() method to get the font size, and the Font.setSize() method to specify the font size.

 

• General font styles
    The Font class allows you to specify whether the font is bold, italic or strike-through.

 

• Font underline style
    The Font.UnderlineStyle class is an enumeration of five underline styles supported by Excel: UnderlineStyle.NONE, UnderlineStyle.SINGLE, 

UnderlineStyle.SINGLEACCOUNTING,UnderlineStyle.DOUBLE,and UnderlineStyle.DOUBLEACCOUNTING.The Font.getUnderlineStyle() method returns the current   underline style as an instance of the Font.UnderlineStyle class. The return value can be one of the predefined values listed above. To change an   underline style, call the Font.setUnderlineStyle() method.

 

• Font color
    Call the Font.getColor() method to get the currently set font color, and the Font.setColor() method to specify the font color. A color value in both   functions is an instance of the java.awt.Color class.

 

• Font alignment style
    The Font class allows you to specify whether the text is normally aligned, subscript or superscript using the Font.setAlignment() method. This method 

takes one of the three predefined instances of the Font.Alignment class: Alingment.NORMAL, Alignment.SUBSCRIPT or Alignment.SUPERSCRIPT. To obtain the   current font alignment, call the Font.getAlignment() method.

 The following sample demonstrates the technique of changing text font:
       

   //Creating a new instance of the  om.jniwrapper.win32.jexcel.Font class
        Font font = new Font();

        //Changing font name
        font.setName("Courier New");

        //Changing font styles
        font.setBold(true);
        font.setStrikethrough(true);
        font.setUnderlineStyle(Font.UnderlineStyle.DOUBLE);

        //Changing font color
        font.setColor(Color.ORANGE);

        //Applying new font setting
        range.setFont(font);

4. Customizing cell borders:
            To customize a range or cell border, you need to specify the kind of border to work with. There are several standard kinds of borders which are provided in  JExcel as instances of the Border.Kind class. For example, Border.Kind.EDGELEFT or Border.Kind.EDGETOP.

 

         The Range.getBorder() method allows you to obtain an instance of the Border class that corresponds to some border kind. The Border class resides in the  com.jniwrapper.win32.jexcel package.

       The Border class provides functionality for working with border color, line style and line weight. The line style is set using the constants from the  Border.LineStyle class, such as LineStyle.CONTINUOUS, LineStyle.DASHDOT, etc. The line weight is set using the constants from the Border.LineWeight class:  LineWeight.HAIRLINE, LineWeight.MEDIUM, etc.

The following sample demonstrates the technique of customizing the range border:
         //Getting the top border
         Border topBorder = range.getBorder(Border.Kind.EDGETOP);

         //Getting the border style
         java.awt.Color borderColor = topBorder.getColor();
         Border.LineStyle lineStyle = topBorder.getLineStyle();
         Border.LineWeight lineWeight = topBorder.getWeight();

         //Setting new border style
         Border border = new Border();
         border.setColor(Color.CYAN);
         border.setLineStyle(Border.LineStyle.DASHDOT);
         border.setWeight(Border.LineWeight.MEDIUM);

         //Applying the border settings to the top border
         range.setBorder(Border.Kind.EDGETOP, border);

Below is the consolidated example to coustomize the cells:

  public static void updateExcel(String strFilePath, ArrayList arrValues) throws IOException, BiffException, WriteException {
   try {
    jxl.Workbook workbook = jxl.Workbook.getWorkbook(new File(strFilePath));
    WritableWorkbook writableWorkbook = jxl.Workbook.createWorkbook(new File(strFilePath), workbook);
    WritableSheet writableSheet = writableWorkbook.getSheet(strGlobalSheetName);
    WritableCellFormat cfwithColor = new WritableCellFormat(getCellFormat(Colour.RED, Pattern.SOLID,Border.ALL,BorderLineStyle.THIN));
    WritableCellFormat cfwithoutColor = new WritableCellFormat(getCellFormat(Colour.RED, Pattern.NONE,Border.ALL,BorderLineStyle.THIN));
    // Creates Label and writes data to one cell of sheet
    Label label;
    CellView cellView;
    intOutputRowCntr = intOutputRowCntr + 1;
    for (int intColHdrCntr = 0; intColHdrCntr < arrValues.size(); intColHdrCntr++) {
     if(arrValues.get(intColHdrCntr).toString().contains("~")){
      String strTemp=arrValues.get(intColHdrCntr).toString();
      strTemp=strTemp.substring(0, strTemp.length()-1);
      label = new Label(intColHdrCntr, intOutputRowCntr, strTemp, cfwithColor);
     }
     else
      label = new Label(intColHdrCntr, intOutputRowCntr, arrValues.get(intColHdrCntr).toString(), cfwithoutColor);
     writableSheet.addCell(label);
     cellView = writableSheet.getColumnView(intColHdrCntr);
     cellView.setSize(arrValues.get(intColHdrCntr).toString().length()*256+100);
     writableSheet.setColumnView(intColHdrCntr, cellView);
    }
    writableWorkbook.write();
    writableWorkbook.close();
    workbook.close();
   } catch (Exception e) {
    //gulp the exception
   }
  }

private static WritableCellFormat getCellFormat(Colour colour, Pattern pattern,Border border, BorderLineStyle borderLineStyle) throws WriteException {
                         WritableFont cellFont = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD);
                         WritableCellFormat cellFormat = new WritableCellFormat(cellFont);
                         cellFormat.setBackground(colour, pattern);
                         cellFormat.setBorder(border, borderLineStyle);
                         return cellFormat;
  }

Below is the example display with the above code