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