Java之Excel操作 Apache POI
转自：http://blog.csdn.net/vacblog/article/details/8237674下载地址：http://poi.apache.org/
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
[*]HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
[*]XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
[*]HWPF － 提供读写Microsoft Word DOC格式档案的功能。
[*]HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
[*]HDGF － 提供读Microsoft Visio格式档案的功能。
[*]HPBF － 提供读Microsoft Publisher格式档案的功能。
[*]HSMF － 提供读Microsoft Outlook格式档案的功能。
操作：
生成Workbook ： HSSFWorkbook wb = new HSSFWorkbook();
添加Worksheet（不添加sheet时生成的xls文件打开时会错）：Sheet sheet3 = wb.createSheet("new sheet");
保存为Excel文件： out = new FileOutputStream("c:\\text.xls"); wb.write(out);
打开Workbook：
方法一：
in = new FileInputStream(TEST_WORKBOOK_NAME);
Workbook wb = WorkbookFactory.create(in);
方法二：
[*]in = new FileInputStream(TEST_WORKBOOK_NAME);
[*] POIFSFileSystem fs = new POIFSFileSystem(in);
[*] wb = new HSSFWorkbook(fs);
打开加密的Workbook(读加密)
[*]FileInputStream input = new FileInputStream(TEST_WORKBOOK_NAME_ENCRYPTED);
[*]BufferedInputStream binput = new BufferedInputStream(input);
[*]POIFSFileSystem poifs = new POIFSFileSystem(binput);
[*]
[*]Biff8EncryptionKey.setCurrentUserPassword(TEST_WORKBOOK_PASSWORD);
[*]
[*]HSSFWorkbook wb = new HSSFWorkbook(poifs);
[*]
[*]System.out.println("====================EncryptedWorkbook====================");
[*]System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
[*]System.out.println("Sheet0's name:" + wb.getSheetName(0));
[*]System.out.println();
复制Sheet ：wb.cloneSheet(1);
修改Sheet名称：wb.setSheetName(i, "SheetName new");
删除Sheet： wb.removeSheetAt(1);
调整Sheet顺序 ：
Java代码
[*]wb.setSheetOrder("SheetName3", 1);
[*]wb.setSheetOrder(wb.getSheetName(4), 0);
设置当前Sheet： t.setActiveSheet();
或: wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);
固定窗口 :wb.getSheet("SheetName4").createFreezePane(2, 2);
分割窗口
wb.getSheet("SheetName5").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);
Sheet缩放 :
[*]//扩大(200%)
[*]wb.getSheet("sheetname1").setZoom(2, 1);
[*]//缩小(50%)
[*]wb.getSheet("sheetname2").setZoom(1, 2);
不显示网格线
Java代码
[*]//不显示网格线
[*]wb.getSheet("sheetname8").setDisplayGridlines(false);
23、设置分页
Java代码
[*]//设置第一页：3行2列 （可以多次设置）
[*]wb.getSheet("sheetname9").setRowBreak(2);
[*]wb.getSheet("sheetname9").setColumnBreak(1);
24、添加，删除，合并单元格
Java代码
[*]//追加行
[*]for (int i = 0; i < 10; i++) {
[*] Row row = wb.getSheet("sheetname10").createRow(i);
[*] for (int j = 0; j < 10; j++) {
[*] //添加单元格
[*] Cell cell = row.createCell(j);
[*] cell.setCellValue(i + 1);
[*] }
[*]
[*] //删除单元格
[*] row.removeCell(row.getCell(5));
[*]}
[*]
[*]//合并单元格
[*]//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
[*]wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));
25、设置Header，Footer
Java代码
[*]//Header
[*]Header header = wb.getSheet("sheetname11").getHeader();
[*]header.setLeft(HSSFHeader.startUnderline() +
[*] HSSFHeader.font("宋体", "Italic") +
[*] "文字文字" +
[*] HSSFHeader.endUnderline());
[*]header.setCenter(HSSFHeader.fontSize((short)16) +
[*] HSSFHeader.startDoubleUnderline() +
[*] HSSFHeader.startBold() +
[*] "汉字汉字" +
[*] HSSFHeader.endBold() +
[*] HSSFHeader.endDoubleUnderline());
[*]header.setRight("打印时间:" + HSSFHeader.date() + " " + HSSFHeader.time());
[*]
[*]//Footer
[*]Footer footer = wb.getSheet("sheetname11").getFooter();
[*]footer.setLeft("Copyright @ rensanning");
[*]footer.setCenter("Page:" + HSSFFooter.page() + " / " + HSSFFooter.numPages());
[*]footer.setRight("File:" + HSSFFooter.file());
26、设置单元格值
Java代码
[*]//boolean
[*]Cell cell00 = rows[0].createCell(0);
[*]boolean val00 = true;
[*]cell00.setCellValue(val00);
[*]
[*]//Calendar 格式化
[*]CellStyle styleCalendar = wb.createCellStyle();
[*]DataFormat formatCalendar = wb.createDataFormat();
[*]styleCalendar.setDataFormat(formatCalendar.getFormat("yyyy/mm/dd"));
[*]Cell cell11 = rows[1].createCell(0);
[*]Calendar val11 = Calendar.getInstance();
[*]cell11.setCellStyle(styleCalendar);
[*]cell11.setCellValue(val11);
[*]
[*]//Date 格式化
[*]CellStyle styleDate = wb.createCellStyle();
[*]DataFormat formatDate = wb.createDataFormat();
[*]styleDate.setDataFormat(formatDate.getFormat("yyyy/mm/dd hh:mm"));
[*]Cell cell21 = rows[2].createCell(0);
[*]Date val21 = new Date();
[*]cell21.setCellStyle(styleDate);
[*]cell21.setCellValue(val21);
[*]
[*]//double
[*]Cell cell30 = rows[3].createCell(0);
[*]double val30 = 1234.56;
[*]cell30.setCellValue(val30);
[*]
[*]//double 格式化
[*]CellStyle styleDouble = wb.createCellStyle();
[*]DataFormat formatDouble = wb.createDataFormat();
[*]styleDouble.setDataFormat(formatDouble.getFormat("#,##0.00"));
[*]Cell cell31 = rows[3].createCell(1);
[*]double val31 = 1234.56;
[*]cell31.setCellStyle(styleDouble);
[*]cell31.setCellValue(val31);
[*]
[*]//String
[*]Cell cell40 = rows[4].createCell(0);
[*]HSSFRichTextString val40 = new HSSFRichTextString("Test汉字");
[*]cell40.setCellValue(val40);
27、设置单元格边线
Java代码
[*]wb.getSheet("sheetname2").setColumnWidth(1, 4096);
[*]
[*]Row row1 = wb.getSheet("sheetname2").createRow(1);
[*]row1.setHeightInPoints(70);
[*]
[*]Cell cell1_1 = row1.createCell(1);
[*]cell1_1.setCellValue("Sample");
[*]
[*]CellStyle style = wb.createCellStyle();
[*]
[*]style.setBorderTop(CellStyle.BORDER_DASHED);
[*]style.setBorderBottom(CellStyle.BORDER_DOUBLE);
[*]style.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT);
[*]style.setBorderRight(CellStyle.BORDER_MEDIUM);
[*]
[*]style.setTopBorderColor(IndexedColors.MAROON.getIndex());
[*]style.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());
[*]style.setLeftBorderColor(IndexedColors.ORANGE.getIndex());
[*]style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());
[*]
[*]cell1_1.setCellStyle(style);
28、设置单元格背景填充
Java代码
[*]wb.getSheet("sheetname3").setColumnWidth(0, 4096);
[*]wb.getSheet("sheetname3").setColumnWidth(1, 4096);
[*]wb.getSheet("sheetname3").setColumnWidth(2, 4096);
[*]
[*]Row row1 = wb.getSheet("sheetname3").createRow(1);
[*]row1.setHeightInPoints(70);
[*]
[*]Cell cell1_0 = row1.createCell(0);
[*]Cell cell1_1 = row1.createCell(1);
[*]Cell cell1_2 = row1.createCell(2);
[*]
[*]cell1_0.setCellValue("THIN_VERT_BANDS");
[*]cell1_1.setCellValue("BIG_SPOTS");
[*]cell1_2.setCellValue("THICK_HORZ_BANDS");
[*]
[*]CellStyle style1 = wb.createCellStyle();
[*]style1.setFillPattern(CellStyle.THIN_VERT_BANDS);
[*]style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());
[*]style1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
[*]
[*]CellStyle style2 = wb.createCellStyle();
[*]style2.setFillPattern(CellStyle.BIG_SPOTS);
[*]style2.setFillForegroundColor(IndexedColors.RED.getIndex());
[*]style2.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
[*]
[*]CellStyle style3 = wb.createCellStyle();
[*]style3.setFillPattern(CellStyle.THICK_HORZ_BANDS);
[*]style3.setFillForegroundColor(IndexedColors.PINK.getIndex());
[*]style3.setFillBackgroundColor(IndexedColors.BROWN.getIndex());
[*]
[*]cell1_0.setCellStyle(style1);
[*]cell1_1.setCellStyle(style2);
[*]cell1_2.setCellStyle(style3);
29、设置单元格注释
Java代码
[*]HSSFCreationHelper createHelper =
[*] (HSSFCreationHelper)wb.getCreationHelper();
[*]Drawing patriarch = wb.getSheet("sheetname4").createDrawingPatriarch();
[*]
[*]//注释
[*]Row row = wb.getSheet("sheetname4").createRow(1);
[*]Cell cell = row.createCell(1);
[*]
[*]HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0,
[*] (short) 4, 2, (short) 6, 5);
[*]
[*]Comment comment = patriarch.createCellComment(clientAnchor);
[*]comment.setString(createHelper.createRichTextString("注释注释111"));
[*]comment.setAuthor("rensanning");
[*]
[*]cell.setCellComment(comment);
[*]
[*]//带字体的注释
[*]Row row2 = wb.getSheet("sheetname4").createRow(2);
[*]Cell cell2 = row2.createCell(1);
[*]
[*]Font font = wb.createFont();
[*]font.setFontName("宋体");
[*]font.setFontHeightInPoints((short)10);
[*]font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
[*]font.setColor(HSSFColor.RED.index);
[*]
[*]Comment comment2 = patriarch.createCellComment(clientAnchor);
[*]HSSFRichTextString text = new HSSFRichTextString("注释注释222");
[*]text.applyFont(font);
[*]comment2.setString(text);
[*]comment2.setAuthor("rensanning");
[*]
[*]cell2.setCellComment(comment2);
30、设置单元格字体（斜体，粗体，下线，取消线，字体，大小，背景色）
Java代码
[*]Font font = null;
[*]CellStyle style = null;
[*]
[*]//斜体
[*]font = wb.createFont();
[*]font.setItalic(true);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(1).getCell(1).setCellStyle(style);
[*]
[*]//粗体
[*]font = wb.createFont();
[*]font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(2).getCell(1).setCellStyle(style);
[*]
[*]//字体名
[*]font = wb.createFont();
[*]font.setFontName("Courier New");
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(3).getCell(1).setCellStyle(style);
[*]
[*]//字体大小
[*]font = wb.createFont();
[*]font.setFontHeightInPoints((short)20);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(4).getCell(1).setCellStyle(style);
[*]
[*]//文字颜色
[*]font = wb.createFont();
[*]font.setColor(HSSFColor.YELLOW.index);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(5).getCell(1).setCellStyle(style);
[*]
[*]//上标
[*]font = wb.createFont();
[*]font.setTypeOffset(HSSFFont.SS_SUPER);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(6).getCell(1).setCellStyle(style);
[*]
[*]//下标
[*]font = wb.createFont();
[*]font.setTypeOffset(HSSFFont.SS_SUB);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(7).getCell(1).setCellStyle(style);
[*]
[*]//删除线
[*]font = wb.createFont();
[*]font.setStrikeout(true);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(8).getCell(1).setCellStyle(style);
[*]
[*]//下划线
[*]font = wb.createFont();
[*]font.setUnderline(HSSFFont.U_SINGLE);
[*]style = wb.createCellStyle();
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(9).getCell(1).setCellStyle(style);
[*]
[*]//背景色
[*]style = wb.createCellStyle();
[*]style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
[*]style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
[*]
[*]style.setFont(font);
[*]
[*]wb.getSheet("sheetname5").getRow(10).getCell(1).setCellStyle(style);
31、设置超链接
Java代码
[*]HSSFCreationHelper createHelper =
[*] (HSSFCreationHelper)wb.getCreationHelper();
[*]
[*]CellStyle style = wb.createCellStyle();
[*]Font font = wb.createFont();
[*]font.setUnderline(HSSFFont.U_SINGLE);
[*]font.setColor(HSSFColor.BLUE.index);
[*]style.setFont(font);
[*]
[*]//追加行
[*]Row[] rows = new Row[10];
[*]for (int i = 0; i < 10; i++) {
[*] rows = wb.getSheet("sheetname6").createRow(i);
[*]}
[*]
[*]//URL
[*]rows[0].createCell(0).setCellValue("URL Link");
[*]
[*]HSSFHyperlink link1 = createHelper.createHyperlink(HSSFHyperlink.LINK_URL);
[*]link1.setAddress("http://poi.apache.org/");
[*]rows[0].getCell(0).setHyperlink(link1);
[*]rows[0].getCell(0).setCellStyle(style);
[*]
[*]rows[1].createCell(0).setCellValue("Email Link");
[*]
[*]HSSFHyperlink link2 = createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);
[*]link2.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
[*]rows[1].getCell(0).setHyperlink(link2);
[*]rows[1].getCell(0).setCellStyle(style);
[*]
[*]//File
[*]rows[2].createCell(0).setCellValue("File Link");
[*]
[*]HSSFHyperlink link3 = createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);
[*]link3.setAddress("link.xls");
[*]rows[2].getCell(0).setHyperlink(link3);
[*]rows[2].getCell(0).setCellStyle(style);
[*]
[*]//Workbook内
[*]rows[3].createCell(0).setCellValue("Worksheet Link");
[*]
[*]HSSFHyperlink link4 = createHelper.createHyperlink(HSSFHyperlink.LINK_DOCUMENT);
[*]link4.setAddress("sheetname1!A1");
[*]rows[3].getCell(0).setHyperlink(link4);
[*]rows[3].getCell(0).setCellStyle(style);
32、设置单元格横向对齐，纵向对齐
Java代码
[*]//横向对齐
[*]wb.getSheet("sheetname7").setColumnWidth(2, 3072);
[*]
[*]Row[] row = new Row[7];
[*]Cell[] cell = new Cell[7];
[*]
[*]for (int i = 0 ; i < 7 ; i++){
[*] row = wb.getSheet("sheetname7").createRow(i + 1);
[*] cell = row.createCell(2);
[*] cell.setCellValue("Please give me a receipt");
[*]}
[*]
[*]CellStyle style0 = wb.createCellStyle();
[*]style0.setAlignment(CellStyle.ALIGN_GENERAL);
[*]cell[0].setCellStyle(style0);
[*]
[*]CellStyle style1 = wb.createCellStyle();
[*]style1.setAlignment(CellStyle.ALIGN_LEFT);
[*]cell[1].setCellStyle(style1);
[*]
[*]CellStyle style2 = wb.createCellStyle();
[*]style2.setAlignment(CellStyle.ALIGN_CENTER);
[*]cell[2].setCellStyle(style2);
[*]
[*]CellStyle style3 = wb.createCellStyle();
[*]style3.setAlignment(CellStyle.ALIGN_RIGHT);
[*]cell[3].setCellStyle(style3);
[*]
[*]CellStyle style4 = wb.createCellStyle();
[*]style4.setAlignment(CellStyle.ALIGN_FILL);
[*]cell[4].setCellStyle(style4);
[*]
[*]CellStyle style5 = wb.createCellStyle();
[*]style5.setAlignment(CellStyle.ALIGN_JUSTIFY);
[*]cell[5].setCellStyle(style5);
[*]
[*]CellStyle style6 = wb.createCellStyle();
[*]style6.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
[*]cell[6].setCellStyle(style6);
[*]
[*]//纵向对齐
[*]Row row2 = wb.getSheet("sheetname8").createRow(1);
[*]row2.setHeightInPoints(70);
[*]Cell[] cell2 = new Cell[4];
[*]
[*]for (int i = 0 ; i < 4 ; i++){
[*] cell2 = row2.createCell(i + 1);
[*] cell2.setCellValue("Please give me a receipt");
[*]}
[*]
[*]CellStyle style02 = wb.createCellStyle();
[*]style02.setVerticalAlignment(CellStyle.VERTICAL_TOP);
[*]cell2[0].setCellStyle(style02);
[*]
[*]CellStyle style12 = wb.createCellStyle();
[*]style12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
[*]cell2[1].setCellStyle(style12);
[*]
[*]CellStyle style22 = wb.createCellStyle();
[*]style22.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
[*]cell2[2].setCellStyle(style22);
[*]
[*]CellStyle style32 = wb.createCellStyle();
[*]style32.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);
[*]cell2[3].setCellStyle(style32);
33、设置单元格旋转角度
Java代码
[*]Row[] row = new Row[4];
[*]Cell[] cell = new Cell[4];
[*]
[*]for (int i = 0 ; i < 4 ; i++){
[*] row = wb.getSheet("sheetname9").createRow(i + 1);
[*] cell = row.createCell(2);
[*] cell.setCellValue("Coffee");
[*]}
[*]
[*]CellStyle style0 = wb.createCellStyle();
[*]style0.setRotation((short)45);
[*]cell[0].setCellStyle(style0);
[*]
[*]CellStyle style1 = wb.createCellStyle();
[*]style1.setRotation((short)0);
[*]cell[1].setCellStyle(style1);
[*]
[*]CellStyle style2 = wb.createCellStyle();
[*]style2.setRotation((short)-45);
[*]cell[2].setCellStyle(style2);
[*]
[*]CellStyle style3 = wb.createCellStyle();
[*]style3.setRotation((short)-90);
[*]cell[3].setCellStyle(style3);
34、设置单元格自动折行
Java代码
[*]Row[] row = new Row[2];
[*]Cell[] cell = new Cell[2];
[*]
[*]for (int i = 0 ; i < 2 ; i++){
[*] row = wb.getSheet("sheetname10").createRow(i + 1);
[*] cell = row.createCell(2);
[*] cell.setCellValue("Thank you very much.");
[*]}
[*]
[*]CellStyle style0 = wb.createCellStyle();
[*]style0.setWrapText(true);
[*]cell[0].setCellStyle(style0);
[*]
[*]CellStyle style1 = wb.createCellStyle();
[*]style1.setWrapText(false);
[*]cell[1].setCellStyle(style1);
35、设置单元格文字缩进
Java代码
[*]Row[] row = new Row[4];
[*]Cell[] cell = new Cell[4];
[*]
[*]for (int i = 0 ; i < 4 ; i++){
[*] row = wb.getSheet("sheetname11").createRow(i + 1);
[*] cell = row.createCell(2);
[*] cell.setCellValue("Coffee");
[*]}
[*]
[*]CellStyle style1 = wb.createCellStyle();
[*]style1.setIndention((short)1);
[*]style1.setAlignment(CellStyle.ALIGN_LEFT);
[*]cell[1].setCellStyle(style1);
[*]
[*]CellStyle style2 = wb.createCellStyle();
[*]style2.setIndention((short)2);
[*]style2.setAlignment(CellStyle.ALIGN_LEFT);
[*]cell[2].setCellStyle(style2);
[*]
[*]CellStyle style3 = wb.createCellStyle();
[*]style3.setIndention((short)3);
[*]style3.setAlignment(CellStyle.ALIGN_LEFT);
[*]cell[3].setCellStyle(style3);
36、自定义格式
Java代码
[*]Row[] rows = new Row[2];
[*]for (int i = 0; i < rows.length; i++) {
[*] rows = wb.getSheet("sheetname12").createRow(i + 1);
[*]}
[*]DataFormat format = wb.createDataFormat();
[*]
[*]CellStyle[] styles = new CellStyle[2];
[*]for (int i = 0; i < styles.length; i++) {
[*] styles = wb.createCellStyle();
[*]}
[*]styles[0].setDataFormat(format.getFormat("0.0"));
[*]styles[1].setDataFormat(format.getFormat("#,##0.000"));
[*]
[*]Cell[] cells = new Cell[2];
[*]for (int i = 0; i < cells.length; i++) {
[*] cells = rows.createCell(1);
[*] cells.setCellValue(1111.25);
[*]
[*] cells.setCellStyle(styles);
[*]}
37、设置公式
Java代码
[*]Row row1 = wb.getSheet("sheetname13").createRow(1);
[*]Row row2 = wb.getSheet("sheetname13").createRow(2);
[*]
[*]Cell cell1_1 = row1.createCell(1);
[*]Cell cell1_2 = row1.createCell(2);
[*]Cell cell1_3 = row1.createCell(3);
[*]Cell cell2_3 = row2.createCell(3);
[*]
[*]cell1_1.setCellValue(30);
[*]cell1_2.setCellValue(25);
[*]cell1_3.setCellFormula("B2+C2");
[*]cell2_3.setCellFormula("MOD(B2,C2)");
38、画直线，圆圈（椭圆），正方形（长方形）,Textbox
Java代码
[*]HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname14")).createDrawingPatriarch();
[*]
[*]//直线
[*]HSSFClientAnchor clientAnchor1 = new HSSFClientAnchor(0, 0, 0, 0,
[*] (short) 4, 2, (short) 6, 5);
[*]HSSFSimpleShape shape1 = patriarch.createSimpleShape(clientAnchor1);
[*]shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
[*]
[*]//圆圈（椭圆）
[*]HSSFClientAnchor clientAnchor2 = new HSSFClientAnchor(0, 0, 0, 0,
[*] (short) 8, 4, (short) 6, 5);
[*]HSSFSimpleShape shape2 = patriarch.createSimpleShape(clientAnchor2);
[*]shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
[*]
[*]//正方形（长方形）
[*]HSSFClientAnchor clientAnchor3 = new HSSFClientAnchor(0, 0, 0, 0,
[*] (short) 12, 6, (short) 6, 5);
[*]HSSFSimpleShape shape3 = patriarch.createSimpleShape(clientAnchor3);
[*]shape3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
[*]
[*]//Textbox
[*]HSSFClientAnchor clientAnchor4 = new HSSFClientAnchor(0, 0, 0, 0,
[*] (short) 14, 8, (short) 6, 5);
[*]HSSFTextbox textbox = patriarch.createTextbox(clientAnchor4);
[*]textbox.setString(new HSSFRichTextString("This is a test"));
39、插入图片
Java代码
[*]//需要commons-codec-1.6.jar
[*]FileInputStream jpeg = new FileInputStream("resource/test.jpg");
[*]byte[] bytes = IOUtils.toByteArray(jpeg);
[*]int pictureIndex = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
[*]jpeg.close();
[*]
[*]HSSFCreationHelper helper = (HSSFCreationHelper) wb.getCreationHelper();
[*]
[*]HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname15")).createDrawingPatriarch();
[*]
[*]HSSFClientAnchor clientAnchor = helper.createClientAnchor();
[*]
[*]clientAnchor.setCol1(3);
[*]clientAnchor.setRow1(2);
[*]
[*]HSSFPicture picture = patriarch.createPicture(clientAnchor, pictureIndex);
[*]picture.resize();
40、设置可输入List
Java代码
[*]CellRangeAddressList addressList = new CellRangeAddressList(
[*] 0,
[*] 0,
[*] 0,
[*] 0);
[*]
[*]final String[] DATA_LIST = new String[] {
[*] "10",
[*] "20",
[*] "30",
[*]};
[*]DVConstraint dvConstraint =
[*] DVConstraint.createExplicitListConstraint(DATA_LIST);
[*]
[*]HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
[*]dataValidation.setSuppressDropDownArrow(false);
[*]
[*]wb.getSheet("sheetname16").addValidationData(dataValidation);
41、设置输入提示信息
Java代码
[*]CellRangeAddressList addressList = new CellRangeAddressList(
[*] 0,
[*] 0,
[*] 0,
[*] 0);
[*]
[*]final String[] DATA_LIST = new String[] {
[*] "10",
[*] "20",
[*] "30",
[*]};
[*]DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);
[*]
[*]HSSFDataValidation dataValidation =
[*] new HSSFDataValidation(addressList, dvConstraint);
[*]dataValidation.setSuppressDropDownArrow(false);
[*]dataValidation.createPromptBox("输入提示", "请从下拉列表中选择！");
[*]dataValidation.setShowPromptBox(true);
[*]
[*]wb.getSheet("sheetname17").addValidationData(dataValidation);
页:
[1]