POI POI HSSF 기능 가이드 -- 퀵·가이드 (한글)

Overview
일반적인 오해는 POI가 엑셀파일을 쓴다는 부분입니다. POI는 프로젝트의 이름입니다. POI는 엑셀파일을 쓰는 HSSF를 포함한, 여러종류의 컴포넌트를 포함하고 있습니다. 이하의 컴포넌트가 POI의 전체 프로젝트이며 각각의 사용목적을 간단히 기술하겠습니다.

POIFS (POI Filesystem)
POIFS는 프로젝트의 가장 오래되고 안정된 컴포넌트입니다. 이것은 OLE 2 복합도큐먼트형식을 순수 자바로 포팅한 것입니다. 이 컴포넌트는 읽고 쓰는 기능을 제공합니다. 프로젝트의 모든 컴포넌트들이 궁극적으로 이에 의존하고 있습니다.the POIFS project page를 보시면 더 많은 정보를 얻으실수 있습니다.



POI HSSF 기능 가이드 -- 퀵·가이드
엑 셀 시트 읽기/기입을 위해서(때문에)HSSF를 서둘러 사용하고 싶습니까? 이 가이드는 , 그런 당신을 위해서(때문에) 있습니다. IHSSF user-API (이)가 취급할 수 있는 범위를 좀 더 깊게 알고 싶어지면 ,HOWTO가이드를 조사해 봐 주세요. 이용법의 상세가 포함되어 있으므로.
기능 일람
신규 워크북 작성
신규 시트 작성
셀 작성
일자 형식 셀 작성
다른 종류의 셀의 대처법
셀내의 위치 지정
괘선의 대처법
필/색
셀의 결합
폰트의 취급
커스텀·칼라
워크북의 읽고 쓰기
셀내의 개행
유저 정의 데이터 형식의 작성
인쇄 범위를 1페이지에
인쇄 범위의 지정
footer에의 페이지 번호의 삽입
열의 상하 이동
시트를 선택 상태에
시트의 확대율 지정
윈도우범위의 분할과 고정
행과 열의 반복
헤더·footer

기능
신규 워크북 작성
    HSSFWorkbook wb = new HSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
신규 시트 작성
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
셀 작성
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short)0);
    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short)0);
    cell.setCellValue(1);
 
    // Or do it on one line.
    row.createCell((short)1).setCellValue(1.2);
    row.createCell((short)2).setCellValue("This is a string");
    row.createCell((short)3).setCellValue(true);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
일자 형식 셀 작성
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short)0);
 
    // Create a cell and put a date value in it.  The first cell is not styled
    // as a date.
    HSSFCell cell = row.createCell((short)0);
    cell.setCellValue(new Date());
 
    // we style the second cell as a date (and time).  It is important to
    // create a new cell style from the workbook otherwise you can end up
    // modifying the built in style and effecting not only this cell but other cells.
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    cell = row.createCell((short)1);
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
다른 종류의 셀의 대처법
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow((short)2);
    row.createCell((short) 0).setCellValue(1.1);
    row.createCell((short) 1).setCellValue(new Date());
    row.createCell((short) 2).setCellValue("a string");
    row.createCell((short) 3).setCellValue(true);
    row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
셀내의 위치 지정
    public static void main(String[] args)
            throws IOException
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row = sheet.createRow((short) 2);
        createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
        createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
        createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
        createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
        createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
        createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
        createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);
 
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
 
    }
 
    /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb        the workbook
     * @param row       the row to create the cell in
     * @param column    the column number to create the cell in
     * @param align     the alignment for the cell.
     */
    private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
    {
        HSSFCell cell = row.createCell(column);
        cell.setCellValue("Align It");
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(align);
        cell.setCellStyle(cellStyle);
    }
                   
괘선의 대처법
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);
 
    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue(4);
 
    // Style the cell with borders all around.
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBottomBorderColor(HSSFColor.BLACK.index);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setLeftBorderColor(HSSFColor.GREEN.index);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.BLUE.index);
    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(HSSFColor.BLACK.index);
    cell.setCellStyle(style);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
필/색
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);
 
    // Aqua background
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(HSSFColor.AQUA.index);
    style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("X");
    cell.setCellStyle(style);
 
    // Orange "foreground", foreground being the fill foreground not the font color.
    style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.ORANGE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cell = row.createCell((short) 2);
    cell.setCellValue("X");
    cell.setCellStyle(style);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
셀의 결합
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    HSSFRow row = sheet.createRow((short) 1);
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of merging");
 
    sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
폰트의 취급
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);
 
    // Create a new font and alter it.
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short)24);
    font.setFontName("Courier New");
    font.setItalic(true);
    font.setStrikeout(true);
 
    // Fonts are set into a style so create a new one to use.
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);
 
    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of fonts");
    cell.setCellStyle(style);
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
커스텀·칼라
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow((short) 0);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("Default Palette");
 
    //apply some colors from the standard palette,
    // as in the previous examples.
    //we'll use red text on a lime background
 
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.LIME.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
 
    cell.setCellStyle(style);
 
    //save with the default palette
    FileOutputStream out = new FileOutputStream("default_palette.xls");
    wb.write(out);
    out.close();
 
    //now, let's replace RED and LIME in the palette
    // with a more attractive combination
    // (lovingly borrowed from freebsd.org)
 
    cell.setCellValue("Modified Palette");
 
    //creating a custom palette for the workbook
    HSSFPalette palette = wb.getCustomPalette();
 
    //replacing the standard red with freebsd.org red
    palette.setColorAtIndex(HSSFColor.RED.index,
            (byte) 153,  //RGB red (0-255)
            (byte) 0,    //RGB green
            (byte) 0     //RGB blue
    );
    //replacing lime with freebsd.org gold
    palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);
 
    //save with the modified palette
    // note that wherever we have previously used RED or LIME, the
    // new colors magically appear
    out = new FileOutputStream("modified_palette.xls");
    wb.write(out);
    out.close();
                   
워크북의 읽고 쓰기
    POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream("workbook.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell((short)3);
    if (cell == null)
        cell = row.createCell((short)3);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue("a test");
 
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
셀내의 개행
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();
 
    cs = wb.createCellStyle();
 
    cs.setFont( f2 );
    //Word Wrap MUST be turned on
    cs.setWrapText( true );
 
    r = s.createRow( (short) 2 );
    r.setHeight( (short) 0x349 );
    c = r.createCell( (short) 2 );
    c.setCellType( HSSFCell.CELL_TYPE_STRING );
    c.setCellValue( "Use n with word wrap on to create a new line" );
    c.setCellStyle( cs );
    s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
 
    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
    wb.write( fileOut );
    fileOut.close();

유저 정의 데이터 형식의 작성
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFCellStyle style;
    HSSFDataFormat format = wb.createDataFormat();
    HSSFRow row;
    HSSFCell cell;
    short rowNum = 0;
    short colNum = 0;
 
    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0"));
    cell.setCellStyle(style);
 
    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.0000"));
    cell.setCellStyle(style);
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
인쇄 범위를 1페이지에
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFPrintSetup ps = sheet.getPrintSetup()
   
    sheet.setAutobreaks(true)
   
    ps.setFitHeight((short)1);
    ps.setFitWidth((short)1);
 
 
    // Create various cells and rows for spreadsheet.
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
인쇄 범위의 지정
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    wb.setPrintArea(0, "$A$1:$C$2");
    //sets the print area for the first sheet
    //Alternatively:
    //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details) 
 
    // Create various cells and rows for spreadsheet.
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                       
footer에의 페이지 번호의 삽입
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFFooter footer = sheet.getFooter()
   
    footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
   
 
 
    // Create various cells and rows for spreadsheet.
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
편리한 함수의 사용
편리한 함수는 ,contrib에 놓여져 있어 , 결합 범위의 주위에 괘선을 긋거나 스타일 속성을 명시적인 스타일의 신규 작성 없이 가거나 하는 , 등이라고 하는 기능이 있습니다.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet( "new sheet" );
 
    // Create a merged region
    HSSFRow row = sheet1.createRow( (short) 1 );
    HSSFRow row2 = sheet1.createRow( (short) 2 );
    HSSFCell cell = row.createCell( (short) 1 );
    cell.setCellValue( "This is a test of merging" );
    Region region = new Region( 1, (short) 1, 4, (short) 4 );
    sheet1.addMergedRegion( region );
 
    // Set the border and border colors.
    final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;
    HSSFRegionUtil.setBorderBottom( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderTop( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderLeft( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderRight( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
 
    // Shows some usages of HSSFCellUtil
    HSSFCellStyle style = wb.createCellStyle();
    style.setIndention((short)4);
    HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);
    HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");
    HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);
 
    // Write out the workbook
    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
    wb.write( fileOut );
    fileOut.close();
                   
열의 상하 이동
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("row sheet");
 
    // Create various cells and rows for spreadsheet.
 
    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
    sheet.shiftRows(5, 10, -5);
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
시트를 선택 상태에
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("row sheet");
    sheet.setSelected(true);
 
    // Create various cells and rows for spreadsheet.
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
시트의 확대율 지정
줌은 , 분수로 표현됩니다. 예를 들면 ,75%을 표현하려면 , 분모에 4, 분자에 3을 사용한다 , 라고 하는 상태입니다.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    sheet1.setZoom(3,4);   // 75 percent magnification
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
윈도우범위의 분할과 고정
There are two types of panes you can create; freeze panes and split panes.
A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism:
sheet1.createFreezePane( 3, 2, 3, 2 );
The first two parameters are the columns and rows you wish to split by. The second two parameters indicate the cells that are visible in the bottom right quadrant.
Split pains appear differently. The split area is divided into four separate work area's. The split occurs at the pixel level and the user is able to adjust the split by dragging it to a new position.
Split panes are created with the following call:
sheet2.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );
The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel. The second parameter is the y position of the split. Again in 1/20th of a point.
The last parameter indicates which pane currently has the focus. This will be one of HSSFSheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
    HSSFSheet sheet3 = wb.createSheet("third sheet");
    HSSFSheet sheet4 = wb.createSheet("fourth sheet");
 
    // Freeze just one row
    sheet1.createFreezePane( 0, 1, 0, 1 );
    // Freeze just one column
    sheet2.createFreezePane( 1, 0, 1, 0 );
    // Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
    sheet3.createFreezePane( 2, 2 );
    // Create a split with the lower left side being the active quadrant
    sheet4.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
행과 열의 반복
It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class.
This function Contains 5 parameters. The first parameter is the index to the sheet (0 = first sheet). The second and third parameters specify the range for the columns to repreat. To stop the columns from repeating pass in -1 as the start and end column. The fourth and fifth parameters specify the range for the rows to repeat. To stop the columns from repeating pass in -1 as the start and end rows.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
 
    // Set the columns to repeat from column 0 to 2 on the first sheet
    wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);
    // Set the the repeating rows and columns on the second sheet.
    wb.setRepeatingRowsAndColumns(1,4,5,1,2);
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
헤더·footer
샘플은 , 헤더에 대하는 것입니다만 , footer에도 직접 들어맞읍니다.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
 
    HSSFHeader header = sheet.getHeader();
    header.setCenter("Center Header");
    header.setLeft("Left Header");
    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
by Glen Stampoultzis