【Apache POI】基础处理方法总结

ゝ一纸荒年。 2022-09-30 11:50 17阅读 0赞

**有关Apache POI处理文档的基础方法总结如下：**

[Java读取Excel文件][Java_Excel]和[Java写入Excel文件][Java_Excel 1]参看博主的另外两篇文章。

/**
     * POI处理基础方法总结
     * @author lmb
     *
     */
    public class POIUtil {
    
    	
        //1.创建工作簿 (WORKBOOK)
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
        //2.创建工作表（SHEET）
        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();
        //3.创建单元格(CELL)
        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();
        //4.创建指定单元格式的单元格
        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();
        //5. 单元格的不同格式
        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();
        //6.单元格的不通对齐方式
        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);
        }
        //7.单元格的边框设置Working with borders
        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();
        //8.填充和颜色设置
        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();
        //9.合并单元格操作
        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();
        //10.字体设置
        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();
        //11.自定义颜色
        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();
        //12.读和重写EXCEL文件
        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();
        //13.在EXCEL单元格中使用自动换行
        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();
        //14.数字格式自定义
        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();
        //15.调整工作单位置
        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();
        //16.设置打印区域
        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();
        //17.标注脚注
        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();
        //18.使用方便的内部提供的函数
        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();
        //19.在工作单中移动行，调整行的上下位置
        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();
        //20.选种指定的工作单
        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();
        //21.工作单的放大缩小
        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();
        //22.头注和脚注
        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();
        //以上实例代码均来自官方网站
        //POI中使用的颜色是用颜色索引来实现，如下：
       /*
        * 颜色对照表 数字代表颜色索引
            8： BLACK
            60： BROWN
            59： OLIVE_GREEN
            58： DARK_GREEN
            56： DARK_TEAL
            18： DARK_BLUE
            32： DARK_BLUE
            62： INDIGO
            63： GREY_80_PERCENT
            53： ORANGE
            19： DARK_YELLOW
            17： GREEN
            21： TEAL
            38： TEAL
            12： BLUE
            39： BLUE
            54： BLUE_GREY
            23： GREY_50_PERCENT
            10： RED
            52： LIGHT_ORANGE
            50： LIME
            57： SEA_GREEN
            49： AQUA
            48： LIGHT_BLUE
            20： VIOLET
            36： VIOLET
            55： GREY_40_PERCENT
            14： PINK
            33： PINK
            51： GOLD
            13： YELLOW
            34： YELLOW
            11： BRIGHT_GREEN
            35： BRIGHT_GREEN
            15： TURQUOISE
            35： TURQUOISE
            16： DARK_RED
            37： DARK_RED
            40： SKY_BLUE
            61： PLUM
            25： PLUM
            22： GREY_25_PERCENT
            45： ROSE
            43： LIGHT_YELLOW
            42： LIGHT_GREEN
            41： LIGHT_TURQUOISE
            27：LIGHT_TURQUOISE
            44： PALE_BLUE
            46： LAVENDER
            9： WHITE
            24： CORNFLOWER_BLUE
            26： LEMON_CHIFFON
            25： MAROON
            28： ORCHID
            29： CORAL
            30： ROYAL_BLUE
            31： LIGHT_CORNFLOWER_BLUE
        */
       //23、自定义颜色，去掉注释，贴加，其他则查看颜色对照表
       HSSFPalette palette = this.getCustomPalette();
       palette.setColorAtIndex(idx,
                i, //RGB red (0-255)
                j,    //RGB green
                k     //RGB blue
             );         
    
    }

[Java_Excel]: http://blog.csdn.net/lmb55/article/details/64537293
[Java_Excel 1]: http://blog.csdn.net/lmb55/article/details/64542690