{"id":468,"date":"2015-08-22T00:18:33","date_gmt":"2015-08-21T23:18:33","guid":{"rendered":"http:\/\/www.pixnbgames.com\/blog\/?p=468"},"modified":"2015-08-25T08:04:17","modified_gmt":"2015-08-25T07:04:17","slug":"apache-poi-how-to-define-styles-for-excel-files","status":"publish","type":"post","link":"https:\/\/www.pixnbgames.com\/blog\/excel\/apache-poi-how-to-define-styles-for-excel-files\/","title":{"rendered":"Apache POI: How to define styles for excel files"},"content":{"rendered":"

In the previous entry about apache POI, we saw how to create excel files with Java and Apache POI<\/a>. This time, we’ll see how to customize the style of these excel files.<\/p>\n

How to create a style for a cell<\/h2>\n

The class that represent the style for a cell is HSSFCellStyle<\/code>. It allow us to set properties specific of a cell: border type, border color, content align, background color, etc. To create a cell style, we only need a reference to the excel workbook:<\/p>\n

HSSFWorkbook workbook = new HSSFWorkbook();\r\nHSSFCellStyle style = workbook.createCellStyle();\r\n<\/pre>\n
Once style is created, we can call its set...()<\/code> methods in order to set the style properties. The class HSSFCellStyle<\/code> has a serie of constants that will help us to configurate the style. Similarly, the class that allow us to manage colors is HSSFColor<\/code>:<\/p>\n
\/\/ Sets the cell background\r\nstyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);\r\nstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);\r\n<\/pre>\n
Probably, we also want to modify the properites of the text that cell contains. To do so, we must create a font style (below).<\/p>\n
How to create a font style<\/h2>\n
Font styles are represented by class HSSFFont<\/code>. This class give us the control over the typography used font: text color, bold, italic, etc. Same to cell styles, we create the fonts from the excel workbook:<\/p>\n
HSSFFont font = workbook.createFont();\r\n<\/pre>\n
Once we have the font, we need to assign it to a cell style:<\/p>\n
style.setFont(font);<\/pre>\n
<\/h1>\n
Complete example<\/h1>\n
As example, we are going to generate an excel file like this:<\/p>\n
\"final_excel_with_style\"<\/a>We use an auxiliary class, called FakeDataProvider<\/code>, that simple give us some test data to fill our excel table:<\/p>\n
public final class FakeDataProvider {\r\n    \r\n    \/** Return the columns name for the table *\/\r\n    public static List getTableHeaders() {\r\n        List tableHeader = new ArrayList();\r\n        tableHeader.add(\"Name\");\r\n        tableHeader.add(\"Address\");\r\n        tableHeader.add(\"Phone\");\r\n        \r\n        return tableHeader;\r\n    }\r\n\r\n    \r\n    \/**\r\n     * Return values for the table\r\n     * \r\n     * @param numberOfRows Number of rows we want to receive\r\n     * \r\n     * @return Values\r\n     *\/\r\n    public static List> getTableContent(int numberOfRows) {\r\n        if (numberOfRows <= 0) {\r\n            throw new IllegalArgumentException(\"The number of rows must be a positive integer\");\r\n        }\r\n        \r\n        List> tableContent = new ArrayList>();\r\n\r\n        List row = null;\r\n        for (int i = 0; i < numberOfRows; i++) {\r\n            tableContent.add(row = new ArrayList());\r\n            row.add(\"my name is \" + i);\r\n            row.add(\"my address is \" + i);\r\n            row.add(\"my phone is \" + i);\r\n        }\r\n        \r\n        return tableContent;\r\n    }\r\n    \r\n}\r\n<\/pre>\n
The class responsible of the excel generation is ExcelGenerator<\/code>. It has an only one public method, generateExcel()<\/code>, that returns the generated workbook:<\/p>\n
public class ExcelGenerator {\r\n\t\r\n\t\/\/ Excel work book\r\n\tprivate HSSFWorkbook workbook;\r\n\t\r\n\t\/\/ Fonts\r\n\tprivate HSSFFont headerFont;\r\n\tprivate HSSFFont contentFont;\r\n\t\r\n\t\/\/ Styles\r\n\tprivate HSSFCellStyle headerStyle;\r\n\tprivate HSSFCellStyle oddRowStyle;\r\n\tprivate HSSFCellStyle evenRowStyle;\r\n\t\r\n\t\/\/ Integer to store the index of the next row\r\n\tprivate int rowIndex;\r\n\t\r\n\r\n\t\/**\r\n\t * Make a new excel workbook with sheet that contains a stylized table\r\n\t * \r\n\t * @return\r\n\t *\/\r\n\tpublic HSSFWorkbook generateExcel() {\r\n\t\t\r\n\t\t\/\/ Initialize rowIndex\r\n\t\trowIndex = 0;\r\n\t\t\r\n\t\t\/\/ New Workbook\r\n\t\tworkbook = new HSSFWorkbook();\r\n\t\t\r\n\t\t\/\/ Generate fonts\r\n\t\theaderFont  = createFont(HSSFColor.WHITE.index, (short)12, true);\r\n\t\tcontentFont = createFont(HSSFColor.BLACK.index, (short)10, false);\r\n\t\t\r\n\t\t\/\/ Generate styles\r\n\t\theaderStyle  = createStyle(headerFont,  HSSFCellStyle.ALIGN_CENTER, HSSFColor.BLUE_GREY.index,       true, HSSFColor.WHITE.index);\r\n\t\toddRowStyle  = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT,   HSSFColor.GREY_25_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index);\r\n\t\tevenRowStyle = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT,   HSSFColor.GREY_40_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index);\r\n\t\t\r\n\t\t\/\/ New sheet\r\n\t\tHSSFSheet sheet = workbook.createSheet(\"Very Cool Sheet\");\r\n\t\t\r\n\t\t\/\/ Table header\r\n\t\tHSSFRow      headerRow    = sheet.createRow( rowIndex++ );\r\n\t\tList headerValues = FakeDataProvider.getTableHeaders();\r\n\t\t\r\n\t\tHSSFCell headerCell = null;\r\n\t\tfor (int i = 0; i < headerValues.size(); i++) {\r\n\t\t\theaderCell = headerRow.createCell(i);\r\n\t\t\theaderCell.setCellStyle(headerStyle);\r\n\t\t\theaderCell.setCellValue( headerValues.get(i) );\r\n\t\t}\r\n\t\t\r\n\t\t\r\n\t\t\/\/ Table content\r\n\t\tHSSFRow  contentRow  = null;\r\n\t\tHSSFCell contentCell = null;\r\n\t\t\r\n\t\t\/\/ Obtain table content values\r\n\t\tList> contentRowValues = FakeDataProvider.getTableContent(20);\r\n\t\tfor (List rowValues : contentRowValues) {\r\n\t\t\t\r\n\t\t\t\/\/ At each row creation, rowIndex must grow one unit\r\n\t\t\tcontentRow = sheet.createRow( rowIndex++ );\r\n\t\t\tfor (int i = 0; i < rowValues.size(); i++) {\r\n\t\t\t\tcontentCell = contentRow.createCell(i);\r\n\t\t\t\tcontentCell.setCellValue( rowValues.get(i) );\r\n\t\t\t\t\r\n\t\t\t\t\/\/ Style depends on if row is odd or even\r\n\t\t\t\tcontentCell.setCellStyle( rowIndex % 2 == 0 ? oddRowStyle : evenRowStyle );\r\n\t\t\t}\r\n\t\t}\r\n\t\t\r\n\t\t\r\n\t\t\/\/ Autosize columns\r\n\t\tfor (int i = 0; i < headerValues.size(); sheet.autoSizeColumn(i++));\r\n\t\t\r\n\t\treturn workbook;\r\n\t}\r\n\t\r\n\t\r\n\t\/**\r\n\t * Create a new font on base workbook\r\n\t * \r\n\t * @param fontColor       Font color (see {@link HSSFColor})\r\n\t * @param fontHeight      Font height in points\r\n\t * @param fontBold        Font is boldweight (true<\/code>) or not (false<\/code>)\r\n\t * \r\n\t * @return New cell style\r\n\t *\/\r\n\tprivate HSSFFont createFont(short fontColor, short fontHeight, boolean fontBold) {\r\n\t\t\r\n\t\tHSSFFont font = workbook.createFont();\r\n\t\tfont.setBold(fontBold);\r\n\t\tfont.setColor(fontColor);\r\n\t\tfont.setFontName(\"Arial\");\r\n\t\tfont.setFontHeightInPoints(fontHeight);\r\n\t\t\r\n\t\treturn font;\r\n\t}\r\n\t\r\n\t\r\n\t\/**\r\n\t * Create a style on base workbook\r\n\t * \r\n\t * @param font            Font used by the style\r\n\t * @param cellAlign       Cell alignment for contained text (see {@link HSSFCellStyle})\r\n\t * @param cellColor       Cell background color (see {@link HSSFColor})\r\n\t * @param cellBorder      Cell has border (true<\/code>) or not (false<\/code>)\r\n\t * @param cellBorderColor Cell border color (see {@link HSSFColor})\r\n\t * \r\n\t * @return New cell style\r\n\t *\/\r\n\tprivate HSSFCellStyle createStyle(HSSFFont font, short cellAlign, short cellColor, boolean cellBorder, short cellBorderColor) {\r\n\r\n\t\tHSSFCellStyle style = workbook.createCellStyle();\r\n\t\tstyle.setFont(font);\r\n\t\tstyle.setAlignment(cellAlign);\r\n\t\tstyle.setFillForegroundColor(cellColor);\r\n\t\tstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);\r\n\t\t\r\n\t\tif (cellBorder) {\r\n\t\t\tstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);\r\n\t\t\tstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);\r\n\t\t\tstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);\r\n\t\t\tstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);\r\n\t\t\t\r\n\t\t\tstyle.setTopBorderColor(cellBorderColor);\r\n\t\t\tstyle.setLeftBorderColor(cellBorderColor);\r\n\t\t\tstyle.setRightBorderColor(cellBorderColor);\r\n\t\t\tstyle.setBottomBorderColor(cellBorderColor);\r\n\t\t}\r\n\t\t\r\n\t\treturn style;\r\n\t}\r\n}\r\n<\/pre>\n
From our main class, we only need to use the ExcelGenerator.generateExcel()<\/code> method to obtain the workbook, and then we write it to disk:<\/p>\n
public class App {\r\n\r\n    public static void main(String[] args) {\r\n        \r\n        HSSFWorkbook workbook = new ExcelGenerator().generateExcel();\r\n        \r\n        \/\/ Writing the excel to output file\r\n        try {\r\n            OutputStream out = new FileOutputStream(\"src\/main\/resources\/ExcelWithStyles.xls\");\r\n            workbook.write(out);\r\n            workbook.close();\r\n            out.flush();\r\n            out.close();\r\n        }\r\n        catch (IOException e) {\r\n            System.err.println(\"Error at file writing\");\r\n            e.printStackTrace();\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
<\/h2>\n
Style with user name<\/h2>\n
If we set an user name to the style, when we the file is open on Microsoft Excel, the style appears with the other predefined styles. For example, if we had done this:<\/p>\n
headerStyle.setUserStyleName(\"Header\");<\/pre>\n
The style had been shown like this:<\/p>\n
\"custom_user_styles\"<\/a><\/p>\n
Row styles<\/h2>\n
Same to cells, rows (HSSFRow<\/code>) have a method setRowStyle()<\/code>. If we apply a cell style to a row, the style is only applied to empty cells of the row. So if we create a cell on this row (row.createCell()<\/code>), this cell will not use the row style.<\/p>\n
<\/h1>\n
Optimization<\/h1>\n
Cell styles (HSSFCellStyle<\/code>) and fonts (HSSFFont<\/code>) are both created on the workbook.<\/p>\n
Is important to keep it in mind because if we need to apply the same style more than once, the right thing is to create it once and apply it to all required cells. If instead of doing that, we create the style every time we need to apply it to a cell, we will multiply the style as many times as cells are there.<\/p>\n
On the same way, if we need to apply the same font style to more than one style, the right thing is to create it once, and then assign it to all required cell styles.<\/p>\n
<\/h1>\n
Source code<\/h1>\n
You can get the source code of the example from my GitHub repository<\/a>. The shown example is Poi-ExcelWithStyles<\/em>.<\/p>\n
Saludos,<\/p>\n","protected":false},"excerpt":{"rendered":"
In the previous entry about apache POI, we saw how to create excel files with Java and Apache POI. This time, we’ll see how to customize the style of these excel files. How to create a style for a cell Seguir leyendo   Apache POI: How to define styles for excel files<\/span>→<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[38,37],"tags":[40,41,39],"language":[4],"_links":{"self":[{"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/posts\/468"}],"collection":[{"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/comments?post=468"}],"version-history":[{"count":3,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/posts\/468\/revisions"}],"predecessor-version":[{"id":491,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/posts\/468\/revisions\/491"}],"wp:attachment":[{"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/media?parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/categories?post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/tags?post=468"},{"taxonomy":"language","embeddable":true,"href":"https:\/\/www.pixnbgames.com\/blog\/wp-json\/wp\/v2\/language?post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}