【Apache POI】基础处理方法总结
来源：互联网 发布：腾讯管家win10优化 编辑：程序博客网 时间：2024/04/29 20:27
有关Apache POI处理文档的基础方法总结如下：
Java读取Excel文件和Java写入Excel文件参看博主的另外两篇文章。
/** * 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 ); } 0 0
- 【Apache POI】基础处理方法总结
- Apache POI 总结
- Apache POI 总结
- Apache poi常用方法
- Apache POI 处理 Microsoft Office文档
- 使用Apache POI处理EXCEL数据
- Apache POI HSSF读写Excel总结
- Apache POI HSSF读写Excel总结
- Apache POI HSSF读写Excel总结
- Apache POI HSSF读写Excel总结
- Apache POI HSSF读写Excel总结
- POI操作Excel方法总结
- apache POI 导出excel相关方法
- Apache POI的API的核心方法
- java处理excel（java使用Apache POI处理Excel）
- POI （Apache POI）
- 【POI】Apache POI
- Apache POI
- 九度OJ 1512 用两个栈实现队列
- Linux下的解压命令详情
- 短语元素
- java语言基础(21)——面向对象（成员变量和局部变量的区别）
- java重this的用法
- 【Apache POI】基础处理方法总结
- 第一篇
- 基础练习 2n皇后问题
- jsp
- 快速判素数——Miller Rabin
- Linux下用gedit编写文本
- Asus Xtion Pro Live标定
- Apache Maven 在Windows下安装和配置
- 一步一步
