Java读写Excel之POI超入门(转)
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-2008)。(原文:http://rensanning.iteye.com/blog/1538591)
先导入以下包,版本可以自行选择。
dom4j-1.7-20060614.jar
如果处理.xlsx、docx、pptx的话可以试试Docx4j 。
Docx4j is a Java library for creating and manipulating Microsoft Open XML (Word docx, Powerpoint pptx, and Excel xlsx) files.
- 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格式档案的功能。
http://poi.apache.org/
Busy Developers' Guide to HSSF and XSSF Features
版本:poi-3.8-20120326.jar
1、生成Workbook
//生成Workbook HSSFWorkbook wb = new HSSFWorkbook(); //添加Worksheet(不添加sheet时生成的xls文件打开时会报错) @SuppressWarnings("unused") Sheet sheet1 = wb.createSheet(); @SuppressWarnings("unused") Sheet sheet2 = wb.createSheet(); @SuppressWarnings("unused") Sheet sheet3 = wb.createSheet("new sheet"); @SuppressWarnings("unused") Sheet sheet4 = wb.createSheet("rensanning"); //保存为Excel文件 FileOutputStream out = null; try { out = new FileOutputStream("c:\\text.xls"); wb.write(out); } catch (IOException e) { System.out.println(e.toString()); } finally { try { out.close(); } catch (IOException e) { System.out.println(e.toString()); } }
2、生成Workbook OOXML形式(.xlsx)
//生成Workbook XSSFWorkbook wb = new XSSFWorkbook(); //......
3、打开Workbook
//方法一:使用WorkbookFactory FileInputStream in = null; Workbook wb = null; try { in = new FileInputStream(TEST_WORKBOOK_NAME); wb = WorkbookFactory.create(in); } catch (IOException e) { System.out.println(e.toString()); } catch (InvalidFormatException e) { System.out.println(e.toString()); } finally { try { in.close(); } catch (IOException e) { System.out.println(e.toString()); } } System.out.println("====================Workbook===================="); System.out.println("Number of Sheets:" + wb.getNumberOfSheets()); System.out.println("Sheet3's name:" + wb.getSheetName(3)); System.out.println(); //方法二:使用POIFSFileSystem try { in = new FileInputStream(TEST_WORKBOOK_NAME); POIFSFileSystem fs = new POIFSFileSystem(in); wb = new HSSFWorkbook(fs); } catch (IOException e) { System.out.println(e.toString()); } finally { try { in.close(); } catch (IOException e) { System.out.println(e.toString()); } } System.out.println("====================Workbook===================="); System.out.println("Number of Sheets:" + wb.getNumberOfSheets()); System.out.println("Sheet3's name:" + wb.getSheetName(3)); System.out.println();
4、打开加密的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();
5、追加Sheet
Sheet sheet = wb.createSheet("append sheet");
6、复制Sheet
wb.cloneSheet(1);
7、修改Sheet名称
wb.setSheetName(i, "SheetName new");
8、删除Sheet
wb.removeSheetAt(1);
9、设置下部Sheet名的Tab的第一个可见Tab
//设置下部Sheet名的Tab的第一个可见Tab(以左的Sheet看不见) wb.setFirstVisibleTab(2);
10、调整Sheet顺序
wb.setSheetOrder("SheetName3", 1); wb.setSheetOrder(wb.getSheetName(4), 0);
11、设置当前Sheet
t.setActiveSheet();
//设置当前Sheet wb.setActiveSheet(wb.getNumberOfSheets() - 1); //(Excel的当前Sheet被设置,需要结合setSelected使用,不然下部Sheet名的Tab还是默认为第一个) //(需要选择多个Sheet的话,每个Sheet调用setSelected(true)即可) wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);
12、固定窗口
wb.getSheet("SheetName4").createFreezePane(2, 2);
13、分割窗口
wb.getSheet("SheetName5").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);
14、Sheet缩放
//setZoom(int numerator, int denominator) //"numerator"÷"denominator" 例如: 3÷1=3 那就是设置为300% //扩大(200%) wb.getSheet("sheetname1").setZoom(2, 1); //缩小(50%) wb.getSheet("sheetname2").setZoom(1, 2);
15、行列分组
wb.getSheet("sheetname3").groupColumn(4, 7); wb.getSheet("sheetname3").groupColumn(9, 12); wb.getSheet("sheetname3").groupColumn(10, 11); wb.getSheet("sheetname3").groupRow(5, 14); wb.getSheet("sheetname3").groupRow(7, 13); wb.getSheet("sheetname3").groupRow(16, 19);
16、关闭分组
wb.getSheet("sheetname3").setColumnGroupCollapsed(10, true); wb.getSheet("sheetname3").setRowGroupCollapsed(7, true);
17、插入行
Row row1 = wb.getSheet("sheetname4").createRow(1); Cell cell1_1 = row1.createCell(1); cell1_1.setCellValue(123); Row row4 = wb.getSheet("sheetname4").createRow(4); Cell cell4_3 = row4.createCell(3); cell4_3.setCellValue("中国");
18、删除行
Row row = wb.getSheet("sheetname4").getRow(1); wb.getSheet("sheetname4").removeRow(row);
19、移动行
//******移动行只移动内容,不牵扯行的删除和插入 //移动行(把第1行和第2行移到第5行之后) wb.getSheet("sheetname5").shiftRows(0, 1, 5); //移动行(把第3行和第4行往上移动1行) wb.getSheet("sheetname5").shiftRows(2, 3, -1);
20、修改行高
//设置默认行高 wb.getSheet("sheetname6").setDefaultRowHeight((short)100); //设置行高 wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));
21、修改列宽
//设置默认列宽 wb.getSheet("sheetname7").setDefaultColumnWidth(12); //设置列宽 wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);
22、不显示网格线
//不显示网格线 wb.getSheet("sheetname8").setDisplayGridlines(false);
23、设置分页
//设置第一页:3行2列 (可以多次设置) wb.getSheet("sheetname9").setRowBreak(2); wb.getSheet("sheetname9").setColumnBreak(1);
24、添加,删除,合并单元格
//追加行 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
//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、设置单元格值
//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、设置单元格边线
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、设置单元格背景填充
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、设置单元格注释
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、设置单元格字体(斜体,粗体,下线,取消线,字体,大小,背景色)
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、设置超链接
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[i] = 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); //Mail 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、设置单元格横向对齐,纵向对齐
//横向对齐 wb.getSheet("sheetname7").setColumnWidth(2, 3072); Row[] row = new Row[7]; Cell[] cell = new Cell[7]; for (int i = 0 ; i < 7 ; i++){ row[i] = wb.getSheet("sheetname7").createRow(i + 1); cell[i] = row[i].createCell(2); cell[i].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[i] = row2.createCell(i + 1); cell2[i].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、设置单元格旋转角度
Row[] row = new Row[4]; Cell[] cell = new Cell[4]; for (int i = 0 ; i < 4 ; i++){ row[i] = wb.getSheet("sheetname9").createRow(i + 1); cell[i] = row[i].createCell(2); cell[i].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、设置单元格自动换行
Row[] row = new Row[2]; Cell[] cell = new Cell[2]; for (int i = 0 ; i < 2 ; i++){ row[i] = wb.getSheet("sheetname10").createRow(i + 1); cell[i] = row[i].createCell(2); cell[i].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、设置单元格文字缩进
Row[] row = new Row[4]; Cell[] cell = new Cell[4]; for (int i = 0 ; i < 4 ; i++){ row[i] = wb.getSheet("sheetname11").createRow(i + 1); cell[i] = row[i].createCell(2); cell[i].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、自定义格式
Row[] rows = new Row[2]; for (int i = 0; i < rows.length; i++) { rows[i] = wb.getSheet("sheetname12").createRow(i + 1); } DataFormat format = wb.createDataFormat(); CellStyle[] styles = new CellStyle[2]; for (int i = 0; i < styles.length; i++) { styles[i] = 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[i] = rows[i].createCell(1); cells[i].setCellValue(1111.25); cells[i].setCellStyle(styles[i]); }
37、设置公式
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
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、插入图片
//需要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
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、设置输入提示信息
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);
文章转载请标明出处,如果,您认为阅读这篇博客让您有些收获,不妨点击一下推荐按钮,据说喜欢分享的,后来都成了大神
我国每年都有2000万人得胃炎,胃癌,很大一部分原因是没有及时吃早餐。
支付宝早餐计划,每年发放20亿早餐补贴来鼓励您及时吃早餐。打开支付宝首页搜:510050164,或 点击领取支付宝红包 领取补贴,用于早餐消费,答应我,好好照顾自己。
复制【Y:/OUvlEf4647u 或复制此消息打开支付宝,建议使用支付宝收💰码。 $140 X@h.GOBM W:/D】打开支付宝,申请支付宝收钱码,更快更便捷,或 点击图片,使用支付宝扫码,领取支付宝收钱码
欢迎扫码关注微信公众号 | 欢迎扫码加入QQ交流群 | 欢迎扫码加入微信交流群 |
![]() |
![]() |
![]() |