Apache POI API

CODE

UPDATE

// Add New Row
 public static void main(String[] args) {
 		// GET
        String excelFilePath = "JavaBooks.xls";
         
        try {
            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
 			
            Sheet sheet = workbook.getSheetAt(0);
 
            Object[][] bookData = {
                    {"The Passionate Programmer", "Chad Fowler", 16},
                    {"Software Craftmanship", "Pete McBreen", 26},
                    {"The Art of Agile Development", "James Shore", 32},
                    {"Continuous Delivery", "Jez Humble", 41},
            };
 
            int rowCount = sheet.getLastRowNum();
 			
            // APPEND
            for (Object[] aBook : bookData) {
                Row row = sheet.createRow(++rowCount);
 
                int columnCount = 0;
                 
                Cell cell = row.createCell(columnCount);
                cell.setCellValue(rowCount);
                 
                for (Object field : aBook) {
                    cell = row.createCell(++columnCount);
                    if (field instanceof String) {
                        cell.setCellValue((String) field);
                    } else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    }
                }
 
            }
 
            inputStream.close();
 
            FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
             
        } catch (IOException | EncryptedDocumentException
                | InvalidFormatException ex) {
            ex.printStackTrace();
        }
    }


// Update Existing
// Retrieve the row and check for null
HSSFRow sheetrow = sheet.getRow(row);
if(sheetrow == null){
    sheetrow = sheet.createRow(row);
}
//Update the value of cell
cell = sheetrow.getCell(col);
if(cell == null){
    cell = sheetrow.createCell(col);
}
cell.setCellValue("Pass");

INIT

// WORKBOOK
Workbook workbook = new SXSSFWorkbook(); 		
// SHEET
Sheet sheet = workbook.createSheet("이력서");
// ROW
Row headRow = sheet.createRow(0); // 0 = 0th index RowNum
// IndexCnt
int rowIndex = 0;
int cellIndex = 0;

CELL STYLE

// Create Style for Workbook
CellStyle style1 = workbook.createCellStyle();
style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style1.setAlignment(HorizontalAlignment.CENTER);
style1.setVerticalAlignment(VerticalAlignment.CENTER);
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderBottom(BorderStyle.THIN);
style1.setBottomBorderColor(IndexedColors.BLUE.getIndex());

FONT STYLE

Font font = ((SXSSFWorkbook) workbook).createFont();
font.setFontName("맑은 고딕");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
style1.setFont(font);

APPLY STYLE TO ROW

Row headRow = sheet.createRow(0);
headRow.setRowStyle(style1);

CREATE CELL in Row

// Create cell in Row
Cell headRowCell= headRow.createCell(0);

MERGE CELLS AND ADD STYLE

// PARAM
// startRow, endRow, startColumn, endColumn

// Merges the cells
CellRangeAddress cellRangeAddress = new CellRangeAddress(row1.getRowNum(), row1.getRowNum() + 1, 0, 1 );
sheet1.addMergedRegion(cellRangeAddress);

// Creates the cell
Cell cell1 = row1.createCell(0);
cell1.setCellValue("성  명");
cell1.setCellStyle(style1);

// Sets the borders to the merged cell
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet1);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet1);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet1);
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet1);

STYLE

BORDERSTYLE

/**
     * Thin border
     */
    THIN(0x1),

    /**
     * Medium border
     */
    MEDIUM(0x2),

    /**
     * dash border
     */
    DASHED(0x3),

    /**
     * dot border
     */
    DOTTED(0x4),

    /**
     * Thick border
     */
    THICK(0x5),

    /**
     * double-line border
     */
    DOUBLE(0x6),

    /**
     * hair-line border
     */
    HAIR(0x7),

    /**
     * Medium dashed border
     */
    MEDIUM_DASHED(0x8),

    /**
     * dash-dot border
     */
    DASH_DOT(0x9),

    /**
     * medium dash-dot border
     */
    MEDIUM_DASH_DOT(0xA),

    /**
     * dash-dot-dot border
     */
    DASH_DOT_DOT(0xB),

    /**
     * medium dash-dot-dot border
     */
    MEDIUM_DASH_DOT_DOT(0xC),

    /**
     * slanted dash-dot border
     */
    SLANTED_DASH_DOT(0xD);

INDEXEDCOLOR

    BLACK1(0),
    WHITE1(1),
    RED1(2),
    BRIGHT_GREEN1(3),
    BLUE1(4),
    YELLOW1(5),
    PINK1(6),
    TURQUOISE1(7),
    BLACK(8),
    WHITE(9),
    RED(10),
    BRIGHT_GREEN(11),
    BLUE(12),
    YELLOW(13),
    PINK(14),
    TURQUOISE(15),
    DARK_RED(16),
    GREEN(17),
    DARK_BLUE(18),
    DARK_YELLOW(19),
    VIOLET(20),
    TEAL(21),
    GREY_25_PERCENT(22),
    GREY_50_PERCENT(23),
    CORNFLOWER_BLUE(24),
    MAROON(25),
    LEMON_CHIFFON(26),
    LIGHT_TURQUOISE1(27),
    ORCHID(28),
    CORAL(29),
    ROYAL_BLUE(30),
    LIGHT_CORNFLOWER_BLUE(31),
    SKY_BLUE(40),
    LIGHT_TURQUOISE(41),
    LIGHT_GREEN(42),
    LIGHT_YELLOW(43),
    PALE_BLUE(44),
    ROSE(45),
    LAVENDER(46),
    TAN(47),
    LIGHT_BLUE(48),
    AQUA(49),
    LIME(50),
    GOLD(51),
    LIGHT_ORANGE(52),
    ORANGE(53),
    BLUE_GREY(54),
    GREY_40_PERCENT(55),
    DARK_TEAL(56),
    SEA_GREEN(57),
    DARK_GREEN(58),
    OLIVE_GREEN(59),
    BROWN(60),
    PLUM(61),
    INDIGO(62),
    GREY_80_PERCENT(63),
    AUTOMATIC(64);

FILLPATTERNTYPE

   /**  No background */
     NO_FILL(0),

    /**  Solidly filled */
     SOLID_FOREGROUND(1),

    /**  Small fine dots */
     FINE_DOTS(2),

    /**  Wide dots */
     ALT_BARS(3),

    /**  Sparse dots */
     SPARSE_DOTS(4),

    /**  Thick horizontal bands */
     THICK_HORZ_BANDS(5),

    /**  Thick vertical bands */
     THICK_VERT_BANDS(6),

    /**  Thick backward facing diagonals */
     THICK_BACKWARD_DIAG(7),

    /**  Thick forward facing diagonals */
     THICK_FORWARD_DIAG(8),

    /**  Large spots */
     BIG_SPOTS(9),

    /**  Brick-like layout */
     BRICKS(10),

    /**  Thin horizontal bands */
     THIN_HORZ_BANDS(11),

    /**  Thin vertical bands */
     THIN_VERT_BANDS(12),

    /**  Thin backward diagonal */
     THIN_BACKWARD_DIAG(13),

    /**  Thin forward diagonal */
     THIN_FORWARD_DIAG(14),

    /**  Squares */
     SQUARES(15),

    /**  Diamonds */
     DIAMONDS(16),

    /**  Less Dots */
     LESS_DOTS(17),

    /**  Least Dots */
     LEAST_DOTS(18);

    /**
     * The horizontal alignment is general-aligned. Text data is left-aligned.
     * Numbers, dates, and times are rightaligned. Boolean types are centered.
     * Changing the alignment does not change the type of data.
     */
    GENERAL,

    /**
     * The horizontal alignment is left-aligned, even in Rightto-Left mode.
     * Aligns contents at the left edge of the cell. If an indent amount is specified, the contents of
     * the cell is indented from the left by the specified number of character spaces. The character spaces are
     * based on the default font and font size for the workbook.
     */
    LEFT,

    /**
     * The horizontal alignment is centered, meaning the text is centered across the cell.
     */
    CENTER,

    /**
     * The horizontal alignment is right-aligned, meaning that cell contents are aligned at the right edge of the cell,
     * even in Right-to-Left mode.
     */
    RIGHT,

    /**
     * Indicates that the value of the cell should be filled
     * across the entire width of the cell. If blank cells to the right also have the fill alignment,
     * they are also filled with the value, using a convention similar to centerContinuous.
     * <p>
     * Additional rules:
     * <ol>
     * <li>Only whole values can be appended, not partial values.</li>
     * <li>The column will not be widened to 'best fit' the filled value</li>
     * <li>If appending an additional occurrence of the value exceeds the boundary of the cell
     * left/right edge, don't append the additional occurrence of the value.</li>
     * <li>The display value of the cell is filled, not the underlying raw number.</li>
     * </ol>
     */
    FILL,

    /**
     * The horizontal alignment is justified (flush left and right).
     * For each line of text, aligns each line of the wrapped text in a cell to the right and left
     * (except the last line). If no single line of text wraps in the cell, then the text is not justified.
     */
    JUSTIFY,

    /**
     * The horizontal alignment is centered across multiple cells.
     * The information about how many cells to span is expressed in the Sheet Part,
     * in the row of the cell in question. For each cell that is spanned in the alignment,
     * a cell element needs to be written out, with the same style Id which references the centerContinuous alignment.
     */
    CENTER_SELECTION,

    /**
     * Indicates that each 'word' in each line of text inside the cell is evenly distributed
     * across the width of the cell, with flush right and left margins.
     * <p>
     * When there is also an indent value to apply, both the left and right side of the cell
     * are padded by the indent value.
     * </p>
     * <p> A 'word' is a set of characters with no space character in them. </p>
     * <p> Two lines inside a cell are separated by a carriage return. </p>
     */
    DISTRIBUTED;

VERTICALALIGN

   /**
     * The vertical alignment is aligned-to-top.
     */
    TOP,

    /**
     * The vertical alignment is centered across the height of the cell.
     */
    CENTER,

    /**
     * The vertical alignment is aligned-to-bottom. (typically the default value)
     */
    BOTTOM,

    /**
     * <p>
     * When text direction is horizontal: the vertical alignment of lines of text is distributed vertically,
     * where each line of text inside the cell is evenly distributed across the height of the cell,
     * with flush top and bottom margins.
     * </p>
     * <p>
     * When text direction is vertical: similar behavior as horizontal justification.
     * The alignment is justified (flush top and bottom in this case). For each line of text, each
     * line of the wrapped text in a cell is aligned to the top and bottom (except the last line).
     * If no single line of text wraps in the cell, then the text is not justified.
     *  </p>
     */
    JUSTIFY,

    /**
     * <p>
     * When text direction is horizontal: the vertical alignment of lines of text is distributed vertically,
     * where each line of text inside the cell is evenly distributed across the height of the cell,
     * with flush top
     * </p>
     * <p>
     * When text direction is vertical: behaves exactly as distributed horizontal alignment.
     * The first words in a line of text (appearing at the top of the cell) are flush
     * with the top edge of the cell, and the last words of a line of text are flush with the bottom edge of the cell,
     * and the line of text is distributed evenly from top to bottom.
     * </p>
     */
    DISTRIBUTED;