- 浏览: 237013 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
jiangmin0021:
看不到 图片啊 亲 可以把文档发我邮箱吗?谢谢你了 4 ...
GT-P7300刷机打电话 -
justboy1987:
看不到图片 发我下邮箱吧 175636422@qq.com ...
GT-P7300刷机打电话 -
wo107504944:
真的可以吗
GT-P7300刷机打电话 -
hoversong:
solr-*.*.*.war放到tomcat的webapps下 ...
Can't find resource 'solrconfig.xml' in classpath or 'solr\.\conf/' -
comsci:
最有可能大规模应用的神经网络技术应该是这样的,我猜想的 ...
自然语言处理--从规则到统计
1.创建一个工作薄
//创建一个工作薄 XSSFWorkbook wb = new XSSFWorkbook(); //创建一个电子表格createSheet XSSFSheet sheet = wb.createSheet("创建一个带名字的电子表格"); //XSSFSheet sheet = wb.createSheet();//调用默认构造创建电子表格 //创建第三行,行和列都是从0开始计算的 XSSFRow row = sheet.createRow((short) 2); row.setHeightInPoints(30);//设置行高30 //1-8行的列宽为256像素 15在这里表示一个像素 for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); }
2.输出到文件或者流
FileOutputStream fileOut = new FileOutputStream("E:/temp/study/xssf-align.xlsx"); wb.write(fileOut); fileOut.close();
3.对单元格进行对齐居中设置
createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); private static void createCell(XSSFWorkbook wb, XSSFRow row, short column, short halign, short valign) { XSSFCell cell = row.createCell(column); cell.setCellValue(new XSSFRichTextString("Align It")); //=======创建单元格样式:开始=========== CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); //=======创建单元格样式:结束=========== cell.setCellStyle(cellStyle); }
4.最大化的使用Workbook
(1) 使用模板,例如 单元格样式和数字格式化等重复使用的最后使用全局对象
/** * 创建样式库. */ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("date", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("header", style5); return styles; }
使用样式库
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception { Random rnd = new Random(); Calendar calendar = Calendar.getInstance(); SpreadsheetWriter sw = new SpreadsheetWriter(out); sw.beginSheet(); //创建头部行 sw.insertRow(0); int styleIndex = styles.get("header").getIndex(); sw.createCell(0, "Title", styleIndex); sw.createCell(1, "% Change", styleIndex); sw.createCell(2, "Ratio", styleIndex); sw.createCell(3, "Expenses", styleIndex); sw.createCell(4, "Date", styleIndex); sw.endRow(); //插入数据行 for (int rownum = 1; rownum < 100000; rownum++) { sw.insertRow(rownum); sw.createCell(0, "Hello, " + rownum + "!"); sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex()); sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex()); sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex()); sw.createCell(4, calendar, styles.get("date").getIndex()); sw.endRow(); calendar.roll(Calendar.DAY_OF_YEAR, 1); } sw.endSheet(); }
5 创建各种类型的单元格(数字类型,富文本类型,日期类型,公式,超级连接)
// 创建数字类型的单元格 Cell cell = row.createCell((short)0); cell.setCellValue(1); row.createCell(1).setCellValue(1.2); //创建单元格并接设置值为简单字符串 row.createCell(2).setCellValue("This is a string cell"); //创建单元格并接设置值为富文本 RichTextString str = creationHelper.createRichTextString("Apache"); Font font = wb.createFont(); font.setItalic(true); font.setUnderline(Font.U_SINGLE); str.applyFont(font); row.createCell(3).setCellValue(str); //创建boolean类型的单元格 row.createCell(4).setCellValue(true); //创建单元格,当前单元的值是通过公式得到的 formula row.createCell(5).setCellFormula("SUM(A1:B1)"); //创建日期类型的单元格并接进行格式化 CellStyle style = wb.createCellStyle(); style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(6); cell.setCellValue(new Date()); cell.setCellStyle(style); //创建超级类型的单元格 row.createCell(7).setCellFormula("SUM(A1:B1)"); cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");
6.自定义单元格的数据格式
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); DataFormat format = wb.createDataFormat(); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(11111.25); //创建单元格样式 CellStyle style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style);
7.在单元格上添加注释
CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet(); //创建单元格1 Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); //单元格1添加注释 Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); //创建单元格2 Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); //给单元格2添加注释 Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //设置注释字体的样式 Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short)14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setColumn(2); comment2.setRow(2);
8.我们知道excel中可以内嵌各种数据,下面演示如何提取各种类型的数据
public static void main(String[] args) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(args[0]); for (PackagePart pPart : workbook.getAllEmbedds()) { String contentType = pPart.getContentType(); // Excel Workbook - either binary or OpenXML if (contentType.equals("application/vnd.ms-excel")) {//offic 2003 excel HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream()); } // Excel Workbook - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {//offic 2007 excel XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream()); } // Word Document - binary (OLE2CDF) file format else if (contentType.equals("application/msword")) {//offic 2003 word HWPFDocument document = new HWPFDocument(pPart.getInputStream()); } // Word Document - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) { XWPFDocument document = new XWPFDocument(pPart.getInputStream()); } // PowerPoint Document - binary file format else if (contentType.equals("application/vnd.ms-powerpoint")) { HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream()); } // PowerPoint Document - OpenXML file format else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) { OPCPackage docPackage = OPCPackage.open(pPart.getInputStream()); XSLFSlideShow slideShow = new XSLFSlideShow(docPackage); } // Any other type of embedded object. else { System.out.println("Unknown Embedded Document: " + contentType); InputStream inputStream = pPart.getInputStream(); } } }
9.单元格背景色的设值
// Aqua background //创建样式 CellStyle style = wb.createCellStyle(); //设值添加背景色 style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.BIG_SPOTS); Cell cell = row.createCell((short) 1); cell.setCellValue(new XSSFRichTextString("X")); cell.setCellStyle(style); // Orange "foreground", foreground being the fill foreground not the font color. //创建样式 style = wb.createCellStyle(); //设值添加背景色 style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row.createCell((short) 2); cell.setCellValue(new XSSFRichTextString("X")); cell.setCellStyle(style);
10.设置打印时内容为一页演示,设置页眉页脚
//设置打印格式化为一页显示 public static void main(String[]args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup 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("E:/temp/study/fitSheetToOnePage.xlsx"); wb.write(fileOut); fileOut.close(); }
//在页脚设置当前页编号
Footer footer = sheet.getFooter(); //&P == current page number //&N == page numbers footer.setRight("Page &P of &N"); //设置页头 Header firstHeader=((XSSFSheet)sheet).getFirstHeader(); //&F == workbook file name firstHeader.setLeft("&F ......... first header");
11.在单元格中创建各种类型的超级链接、合并单元格操作、在单元格中实现在动换行。
CreationHelper createHelper = wb.getCreationHelper(); //超级链接的样式,蓝色并接默认有下划线 CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); //创建链接到http://poi.apache.org/的超级链接 Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short)0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //链接指定目录的文件 cell = sheet.createRow(1).createCell((short)0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("c://link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //调用本地客户端发送邮件 cell = sheet.createRow(2).createCell((short)0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //创建一个定位到Target Sheet'!A1的链接 Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short)0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1");//连接到Target Sheet a1单元格 cell.setHyperlink(link2); cell.setCellStyle(hlink_style);
Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue(new XSSFRichTextString("This is a test of merging")); //(行,行,列,列) 合并单元格 sheet.addMergedRegion(new CellRangeAddress(5, 7, 1, 2));//第二行 二到三列
//单元格中实现自动换行 Cell cell = row.createCell(2); cell.setCellValue("Use \n with word wrap on to create a new line"); //to enable newlines you need set a cell styles with wrap=true CellStyle cs = wb.createCellStyle(); cs.setWrapText(true);//开启自动换行需要setWrapText(true) cell.setCellStyle(cs);
12 待续
评论
4 楼
lyndan
2013-01-07
public static void main(String args[]){ System.out.println("做个小测试"); }
3 楼
lijunwyf41
2012-07-23
XSSFWorkbook 这个类在哪个包里面?能不能把你所引用的包也上传?我下载最新的,没有XSSFWorkbook 这个类,
2 楼
haldis
2012-07-12
有完整的例子吗?谢谢
1 楼
huwei117
2011-08-12
学习了,谢谢!
相关推荐
Apache POI HSSF和XSSF读写EXCEL总结
poi excel poi excel poi excel poi excel poi excel poi excel poi excel poi excel
poi操作Excel 包含HSSF,XSSF两种方式的导入导出 支持97-2003版本的Excel与2007等高版本的Excel 下载之后eclipse导入替换jdk直接运行即可
想尽快的使用HSSF和XSSF对电子表格进行操作吗?这个指南正是您所需要的。现在稳定的POI的版本为3.6。但最近在查阅POI的资料时发现,虽然资料很多,但是大都局限于2.x的版本,3.x中文资料比较少,查阅apache网站的...
POI-HSSF和POI-XSSF和SXSSF(自POI 3.8 beta3起)-用于访问Microsoft Excel格式文件
POI学习笔记第二版更详细的POI学习笔记第二版更详细的
Apache POI HSSF and XSSF 快速指南帮助文档 API poi-3.15
POI生成Excel POI读取Excel java操作Excel Servlet生成Excel web项目,包含含读取Excel与生成Excel方法
import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
poi log4j excel poi log4j excel poi log4j excel poi log4j excel poi log4j excel poi log4j excel poi log4j excel
excelpoi学习代码
JAVA操作excel poi poi.jarJAVA操作excel poi poi.jarJAVA操作excel poi poi.jarJAVA操作excel poi poi.jar
关于java EXCEL poi 合并后无法显示或无法设置边框问题 【本人亲测】 excel poi 新手! 坑了1.5天!
java导出excel POI jar包 java代码地址http://blog.csdn.net/awenluck/article/details/51488537
POI3.5_HSSF_和XSSF_Excel操作快速入门手册.pdf
java 控制Excel poi 3.1 java 控制Excel poi 3.1 java 控制Excel poi 3.1 java 控制Excel poi 3.1 java 控制Excel poi 3.1
poi基于模板导出excel,poi基于模板导出excelpoi,基于模板导出excel
excel格式输出是Java变成中数据输出的一种主要格式,本文中对poi接口中hssf、xssf的几个实际有效的操作方法给出具体书写格式,希望能够对你有所提示和帮助。
poi读写excel,poi总结; poi读写excel,poi总结 poi读写excel,poi总结
excelPOI 代码 学习用