# Apache POI API

csk917work·2021년 11월 26일
0

## CODE

### UPDATE


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

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

// Sets the borders to the merged cell
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);

HORZALIGN

    /**
* 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>
* <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;
기술블로거입니다