Hssfworkbook Header Example For Essay

If you are building a software for HR or finance domain, there is usually requirement for generating excel reports which are usually across management levels. Apart from reports, you can expect input data for application coming in form of excel sheets and application is expected to support it. These are many open source APIs to handle such scenarios.

Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.

In this post, I am discussing some common activities required to do in real life application.

Sections in this post:Apache POI runtime dependenciesSome useful common classesWriting an excel fileReading an excel fileUsing formulas in excel sheetFormatting the cellsSourcecode download

Apache POI runtime dependencies

If you are working on a maven project, you can include the POI dependency in pom.xml file using this:

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>

If you are not using maven, then you can download maven jar files from POI download page. Include following jar files minimum to run the sample code:

  • dom4j-1.6.1.jar
  • poi-3.9-20121203.jar
  • poi-ooxml-3.9-20121203.jar
  • poi-ooxml-schemas-3.9-20121203.jar
  • xmlbeans-2.3.0.jar

Some useful POI classes

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

  • HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.
  • XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.
  • SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.

Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.

Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.

A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting in a sheet, mostly based on some rules.

We will see the usage of above classes in coming examples.

Writing an excel file

I am taking this example first so that we can reuse the excel sheet created by this code to read back in next example.

Writing a file using POI is very simple and involve following steps:

  1. Create a workbook
  2. Create a sheet in workbook
  3. Create a row in sheet
  4. Add cells in sheet
  5. Repeat step 3 and 4 to write more data

It seems very simple, right? Lets have a look at the code doing these steps:

package com.howtodoinjava.demo.poi; //import statements public class WriteExcelDemo { public static void main(String[] args) { //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Employee Data"); //This data needs to be written (Object[]) Map<String, Object[]> data = new TreeMap<String, Object[]>(); data.put("1", new Object[] {"ID", "NAME", "LASTNAME"}); data.put("2", new Object[] {1, "Amit", "Shukla"}); data.put("3", new Object[] {2, "Lokesh", "Gupta"}); data.put("4", new Object[] {3, "John", "Adwards"}); data.put("5", new Object[] {4, "Brian", "Schultz"}); //Iterate over data and write to sheet Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object [] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if(obj instanceof String) cell.setCellValue((String)obj); else if(obj instanceof Integer) cell.setCellValue((Integer)obj); } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx")); workbook.write(out); out.close(); System.out.println("howtodoinjava_demo.xlsx written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } }


Reading an excel file

Reading an excel file is also very simple if we divide this in steps.

  1. Create workbook instance from excel sheet
  2. Get to the desired sheet
  3. Increment row number
  4. iterate over all cells in a row
  5. repeat step 3 and 4 until all data is read

Lets see all above steps in code. I am writing the code to read the excel file created in above example.

package com.howtodoinjava.demo.poi; //import statements public class ReadExcelDemo { public static void main(String[] args) { try { FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "t"); break; } } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } } } Output: ID NAME LASTNAME 1.0 Amit Shukla 2.0 Lokesh Gupta 3.0 John Adwards 4.0 Brian Schultz

Using formulas in excel sheet

When working on complex excel sheets, we encounter many cells which have formula to calculate their values. These are formula cells. Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.

Les see one example of how to set formula cells in excel?

In this code, there are four cells in a row and fourth one in multiplication of all previous 3 rows. So the formula will be : A2*B2*C2 (in second row)

public static void main(String[] args) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest"); Row header = sheet.createRow(0); header.createCell(0).setCellValue("Pricipal"); header.createCell(1).setCellValue("RoI"); header.createCell(2).setCellValue("T"); header.createCell(3).setCellValue("Interest (P r t)"); Row dataRow = sheet.createRow(1); dataRow.createCell(0).setCellValue(14500d); dataRow.createCell(1).setCellValue(9.25); dataRow.createCell(2).setCellValue(3d); dataRow.createCell(3).setCellFormula("A2*B2*C2"); try { FileOutputStream out = new FileOutputStream(new File("formulaDemo.xlsx")); workbook.write(out); out.close(); System.out.println("Excel with foumula cells written successfully"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }

Similarly, I you want to read a file which have formula cells in it, use following logic to evaluate the formula cells.

public static void readSheetWithFormula() { try { FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type after eveluating formulae //If it is formula cell, it will be evaluated otherwise no change will happen switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "tt"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "tt"); break; case Cell.CELL_TYPE_FORMULA: //Not again break; } } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } } Output: Pricipal RoI T Interest (P r t) 14500.0 9.25 3.0 402375.0

Formatting the cells

So for we have seen the examples of reading/ writing and excel file using apache POI. But, when we are creating a report in excel file and it becomes utmost important to add formatting on cells which fit into any per-determined criteria. This formatting can be a different coloring based on certain value range, based on expiry date limit etc.

In below examples, I am taking couple of such formatting examples for various purposes.

1) Cell value is in between a certain range

This piece of code will color any cell in range whose value is between a configured range. [e.g. between 50 and 70]

static void basedOnValue(Sheet sheet) { //Creating some random values sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(74); sheet.createRow(2).createCell(0).setCellValue(50); sheet.createRow(3).createCell(0).setCellValue(51); sheet.createRow(4).createCell(0).setCellValue(49); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); //Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); //Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.GREEN.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); }

2) Highlight duplicate values

Highlight all cells which have duplicate values in observed cells

static void formatDuplicates(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Code"); sheet.createRow(1).createCell(0).setCellValue(4); sheet.createRow(2).createCell(0).setCellValue(3); sheet.createRow(3).createCell(0).setCellValue(6); sheet.createRow(4).createCell(0).setCellValue(3); sheet.createRow(5).createCell(0).setCellValue(5); sheet.createRow(6).createCell(0).setCellValue(8); sheet.createRow(7).createCell(0).setCellValue(0); sheet.createRow(8).createCell(0).setCellValue(2); sheet.createRow(9).createCell(0).setCellValue(8); sheet.createRow(10).createCell(0).setCellValue(6); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"); }

3) Color alternate rows in different colors

A simple code to color each alternate row in a different color

static void shadeAlt(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }

4) Color amounts which are going to expire in next 30 days

A very useful code for financial projects which keep track of dead lines.

static void expiryInNext30Days(Sheet sheet) { CellStyle style = sheet.getWorkbook().createCellStyle(); style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm")); sheet.createRow(0).createCell(0).setCellValue("Date"); sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29"); sheet.createRow(2).createCell(0).setCellFormula("A2+1"); sheet.createRow(3).createCell(0).setCellFormula("A3+1"); for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted"); }

I am ending this post here for keeping the post in limit. I will post some useful code samples in coming posts.

Sourcecode download

Click on below given link to download the source code of above examples.

Happy Learning !!

References

http://poi.apache.org/spreadsheet/quick-guide.html

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

The class that represent the style for a cell is . 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:

HSSFWorkbook workbook =new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle();

HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle();

Once style is created, we can call its methods in order to set the style properties. The class has a serie of constants that will help us to configurate the style. Similarly, the class that allow us to manage colors is :

// Sets the cell background style.setFillForegroundColor(HSSFColor.BLUE_GREY.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

// Sets the cell background style.setFillForegroundColor(HSSFColor.BLUE_GREY.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

Probably, we also want to modify the properites of the text that cell contains. To do so, we must create a font style (below).

How to create a font style

Font styles are represented by class . 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:

HSSFFont font = workbook.createFont();

HSSFFont font = workbook.createFont();

Once we have the font, we need to assign it to a cell style:

style.setFont(font);

style.setFont(font);

Complete example

As example, we are going to generate an excel file like this:

We use an auxiliary class, called , that simple give us some test data to fill our excel table:

11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 publicfinalclass FakeDataProvider {   /** Return the columns name for the table */publicstatic List<String> getTableHeaders(){ List<String> tableHeader =new ArrayList<String>(); tableHeader.add("Name"); tableHeader.add("Address"); tableHeader.add("Phone");   return tableHeader;}     /** * Return values for the table * * @param numberOfRows Number of rows we want to receive * * @return Values */publicstatic List<List<String>> getTableContent(int numberOfRows){if(numberOfRows <=0){thrownewIllegalArgumentException("The number of rows must be a positive integer");}   List<List<String>> tableContent =new ArrayList<List<String>>();   List<String> row =null;for(int i =0; i < numberOfRows; i++){ tableContent.add(row =new ArrayList<String>()); row.add("my name is "+ i); row.add("my address is "+ i); row.add("my phone is "+ i);}   return tableContent;}   }

public final class FakeDataProvider { /** Return the columns name for the table */ public static List<String> getTableHeaders() { List<String> tableHeader = new ArrayList<String>(); tableHeader.add("Name"); tableHeader.add("Address"); tableHeader.add("Phone"); return tableHeader; } /** * Return values for the table * * @param numberOfRows Number of rows we want to receive * * @return Values */ public static List<List<String>> getTableContent(int numberOfRows) { if (numberOfRows <= 0) { throw new IllegalArgumentException("The number of rows must be a positive integer"); } List<List<String>> tableContent = new ArrayList<List<String>>(); List<String> row = null; for (int i = 0; i < numberOfRows; i++) { tableContent.add(row = new ArrayList<String>()); row.add("my name is " + i); row.add("my address is " + i); row.add("my phone is " + i); } return tableContent; } }

The class responsible of the excel generation is . It has an only one public method, , that returns the generated workbook:

15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 publicclass ExcelGenerator {   // Excel work bookprivate HSSFWorkbook workbook;   // Fontsprivate HSSFFont headerFont;private HSSFFont contentFont;   // Stylesprivate HSSFCellStyle headerStyle;private HSSFCellStyle oddRowStyle;private HSSFCellStyle evenRowStyle;   // Integer to store the index of the next rowprivateint rowIndex;     /** * Make a new excel workbook with sheet that contains a stylized table * * @return */public HSSFWorkbook generateExcel(){   // Initialize rowIndex rowIndex =0;   // New Workbook workbook =new HSSFWorkbook();   // Generate fonts headerFont = createFont(HSSFColor.WHITE.index, (short)12, true); contentFont = createFont(HSSFColor.BLACK.index, (short)10, false);   // Generate styles headerStyle = createStyle(headerFont, HSSFCellStyle.ALIGN_CENTER, HSSFColor.BLUE_GREY.index, true, HSSFColor.WHITE.index); oddRowStyle = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT, HSSFColor.GREY_25_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index); evenRowStyle = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT, HSSFColor.GREY_40_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index);   // New sheet HSSFSheet sheet = workbook.createSheet("Very Cool Sheet");   // Table header HSSFRow headerRow = sheet.createRow( rowIndex++); List<String> headerValues = FakeDataProvider.getTableHeaders();   HSSFCell headerCell =null;for(int i =0; i < headerValues.size(); i++){ headerCell = headerRow.createCell(i); headerCell.setCellStyle(headerStyle); headerCell.setCellValue( headerValues.get(i));}     // Table content HSSFRow contentRow =null; HSSFCell contentCell =null;   // Obtain table content values List<List<String>> contentRowValues = FakeDataProvider.getTableContent(20);for(List<String> rowValues : contentRowValues){   // At each row creation, rowIndex must grow one unit contentRow = sheet.createRow( rowIndex++);for(int i =0; i < rowValues.size(); i++){ contentCell = contentRow.createCell(i); contentCell.setCellValue( rowValues.get(i));   // Style depends on if row is odd or even contentCell.setCellStyle( rowIndex %2==0? oddRowStyle : evenRowStyle );}}     // Autosize columnsfor(int i =0; i < headerValues.size(); sheet.autoSizeColumn(i++));   return workbook;}     /** * Create a new font on base workbook * * @param fontColor Font color (see {@link HSSFColor}) * @param fontHeight Font height in points * @param fontBold Font is boldweight (<code>true</code>) or not (<code>false</code>) * * @return New cell style */private HSSFFont createFont(short fontColor, short fontHeight, boolean fontBold){   HSSFFont font = workbook.createFont(); font.setBold(fontBold); font.setColor(fontColor); font.setFontName("Arial"); font.setFontHeightInPoints(fontHeight);   return font;}     /** * Create a style on base workbook * * @param font Font used by the style * @param cellAlign Cell alignment for contained text (see {@link HSSFCellStyle}) * @param cellColor Cell background color (see {@link HSSFColor}) * @param cellBorder Cell has border (<code>true</code>) or not (<code>false</code>) * @param cellBorderColor Cell border color (see {@link HSSFColor}) * * @return New cell style */private HSSFCellStyle createStyle(HSSFFont font, short cellAlign, short cellColor, boolean cellBorder, short cellBorderColor){   HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(cellAlign); style.setFillForegroundColor(cellColor); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   if(cellBorder){ style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   style.setTopBorderColor(cellBorderColor); style.setLeftBorderColor(cellBorderColor); style.setRightBorderColor(cellBorderColor); style.setBottomBorderColor(cellBorderColor);}   return style;}}

public class ExcelGenerator { // Excel work book private HSSFWorkbook workbook; // Fonts private HSSFFont headerFont; private HSSFFont contentFont; // Styles private HSSFCellStyle headerStyle; private HSSFCellStyle oddRowStyle; private HSSFCellStyle evenRowStyle; // Integer to store the index of the next row private int rowIndex; /** * Make a new excel workbook with sheet that contains a stylized table * * @return */ public HSSFWorkbook generateExcel() { // Initialize rowIndex rowIndex = 0; // New Workbook workbook = new HSSFWorkbook(); // Generate fonts headerFont = createFont(HSSFColor.WHITE.index, (short)12, true); contentFont = createFont(HSSFColor.BLACK.index, (short)10, false); // Generate styles headerStyle = createStyle(headerFont, HSSFCellStyle.ALIGN_CENTER, HSSFColor.BLUE_GREY.index, true, HSSFColor.WHITE.index); oddRowStyle = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT, HSSFColor.GREY_25_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index); evenRowStyle = createStyle(contentFont, HSSFCellStyle.ALIGN_LEFT, HSSFColor.GREY_40_PERCENT.index, true, HSSFColor.GREY_80_PERCENT.index); // New sheet HSSFSheet sheet = workbook.createSheet("Very Cool Sheet"); // Table header HSSFRow headerRow = sheet.createRow( rowIndex++ ); List<String> headerValues = FakeDataProvider.getTableHeaders(); HSSFCell headerCell = null; for (int i = 0; i < headerValues.size(); i++) { headerCell = headerRow.createCell(i); headerCell.setCellStyle(headerStyle); headerCell.setCellValue( headerValues.get(i) ); } // Table content HSSFRow contentRow = null; HSSFCell contentCell = null; // Obtain table content values List<List<String>> contentRowValues = FakeDataProvider.getTableContent(20); for (List<String> rowValues : contentRowValues) { // At each row creation, rowIndex must grow one unit contentRow = sheet.createRow( rowIndex++ ); for (int i = 0; i < rowValues.size(); i++) { contentCell = contentRow.createCell(i); contentCell.setCellValue( rowValues.get(i) ); // Style depends on if row is odd or even contentCell.setCellStyle( rowIndex % 2 == 0 ? oddRowStyle : evenRowStyle ); } } // Autosize columns for (int i = 0; i < headerValues.size(); sheet.autoSizeColumn(i++)); return workbook; } /** * Create a new font on base workbook * * @param fontColor Font color (see {@link HSSFColor}) * @param fontHeight Font height in points * @param fontBold Font is boldweight (<code>true</code>) or not (<code>false</code>) * * @return New cell style */ private HSSFFont createFont(short fontColor, short fontHeight, boolean fontBold) { HSSFFont font = workbook.createFont(); font.setBold(fontBold); font.setColor(fontColor); font.setFontName("Arial"); font.setFontHeightInPoints(fontHeight); return font; } /** * Create a style on base workbook * * @param font Font used by the style * @param cellAlign Cell alignment for contained text (see {@link HSSFCellStyle}) * @param cellColor Cell background color (see {@link HSSFColor}) * @param cellBorder Cell has border (<code>true</code>) or not (<code>false</code>) * @param cellBorderColor Cell border color (see {@link HSSFColor}) * * @return New cell style */ private HSSFCellStyle createStyle(HSSFFont font, short cellAlign, short cellColor, boolean cellBorder, short cellBorderColor) { HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(cellAlign); style.setFillForegroundColor(cellColor); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); if (cellBorder) { style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(cellBorderColor); style.setLeftBorderColor(cellBorderColor); style.setRightBorderColor(cellBorderColor); style.setBottomBorderColor(cellBorderColor); } return style; } }

From our main class, we only need to use the method to obtain the workbook, and then we write it to disk:

11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 publicclass App {   publicstaticvoid main(String[] args){   HSSFWorkbook workbook =new ExcelGenerator().generateExcel();   // Writing the excel to output filetry{OutputStream out =newFileOutputStream("src/main/resources/ExcelWithStyles.xls"); workbook.write(out); workbook.close(); out.flush(); out.close();}catch(IOException e){System.err.println("Error at file writing"); e.printStackTrace();}}}

public class App { public static void main(String[] args) { HSSFWorkbook workbook = new ExcelGenerator().generateExcel(); // Writing the excel to output file try { OutputStream out = new FileOutputStream("src/main/resources/ExcelWithStyles.xls"); workbook.write(out); workbook.close(); out.flush(); out.close(); } catch (IOException e) { System.err.println("Error at file writing"); e.printStackTrace(); } } }

Style with user name

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:

headerStyle.setUserStyleName("Header");

headerStyle.setUserStyleName("Header");

The style had been shown like this:

Row styles

Same to cells, rows () have a method . 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 (), this cell will not use the row style.

Optimization

Cell styles () and fonts () are both created on the workbook.

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.

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.

Source code

You can get the source code of the example from my GitHub repository. The shown example is Poi-ExcelWithStyles.

Saludos,

This entry was posted in Apache POI, Excel and tagged apache poi, excel, java by Ángel. Bookmark the permalink.

0 thoughts on “Hssfworkbook Header Example For Essay

Leave a Reply

Your email address will not be published. Required fields are marked *