- 中文翻译
- 信息点
- 分 类
- 有一级类和二级类
- 中文名
- POI信息点
- POI全称
- Point of Information
-
POI
2021-01-17 17:10:20Poi(适合小数据量) Apache POI 官网:https://poi.apache.org/ POI是Apache软件基金会的,POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。 所以POI的主要功能是可以用Java操作...Poi(适合小数据量)
Apache POI 官网:https://poi.apache.org/
POI是Apache软件基金会的,POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件,这里我们主要讲Excel小数据写
1 .导入依赖
<dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.1</version> </dependency> </dependencies>
2 .开启读写操作,代码走起
无非就是对api的充分认识,接下来我们先去了解他的api
Workbook wordkbook =new HSSFWorkbook();//创建一个Workbook对象 wordkbook.createSheet();//创建表名,如果不写参数,会有默认值 Row row1=sheet.createRow(0);//根据里面的数字拿到对应的行,0默认为第一行 Cell cell = row1.createCell(0);//根据行对象创建单元格,这里0为第一个 cell.setCellValue("");//可以给单元格赋值
写入一个Excel
package com.kuang; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.joda.time.DateTime; import org.junit.Test; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; public class ExcelWriteTest { //先要有个路劲 static String path="D:\JAVA---EasyExcel\TEST"; @Test public void testWrite03(String[] args) throws IOException { //1,创建一个工作薄 Workbook wordkbook =new HSSFWorkbook(); //表名 Sheet sheet=wordkbook.createSheet("灰灰统计表"); //创建行 Row row1=sheet.createRow(0); //4.创建一个单元格 Cell cell = row1.createCell(0); cell.setCellValue("今日新增观众"); Cell cell2 = row1.createCell(1); cell2.setCellValue("卢本伟"); //创建行 Row row2=sheet.createRow(1); //4.创建一个单元格 Cell cell3 = row2.createCell(0); cell3.setCellValue("统计时间"); Cell cell24= row2.createCell(1); String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell24.setCellValue(time); //生成一张表 03是xls 07是xlsx FileOutputStream fileOutputStream = new FileOutputStream(path + "灰灰统计表03.xls"); wordkbook.write(fileOutputStream); fileOutputStream.close(); System.out.println("灰灰统计表03已生成"); } @Test public void testWrite07() throws IOException { //1,创建一个工作薄 Workbook wordkbook =new XSSFWorkbook(); //表名 Sheet sheet=wordkbook.createSheet("灰灰统计表"); //创建行 Row row1=sheet.createRow(0); //4.创建一个单元格 Cell cell = row1.createCell(0); cell.setCellValue("今日新增观众"); Cell cell2 = row1.createCell(1); cell2.setCellValue("卢本伟"); //创建行 Row row2=sheet.createRow(1); //4.创建一个单元格 Cell cell3 = row2.createCell(0); cell3.setCellValue("统计时间"); Cell cell24= row2.createCell(1); String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell24.setCellValue(time); //生成一张表 03是xls 07是xlsx FileOutputStream fileOutputStream = new FileOutputStream(path + "\灰灰统计表07.xlsx"); wordkbook.write(fileOutputStream); fileOutputStream.close(); System.out.println("灰灰统计表07已生成"); } }
上面写完后会在项目目录下生成一个表格
03 | 07 版本的写,就是对象不同,方法一样的!
大数据写
HSSF
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
@Test public void testWrite03BigData() throws IOException { long begin = System.currentTimeMillis(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 65535; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(path + "//testWrite03BigData"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end-begin)/1000); }
XSSF
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点:可以写较大的数据量,如20万条
@Test public void testWrite07BigData() throws IOException { long begin = System.currentTimeMillis(); Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 655350; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(path + "//testWrite03BigData.xlsx"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end-begin)/1000); }
SXSSF
优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
注意:
过程中会产生临时文件,需要清理临时文件
默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件 如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook ( 数量 )
@Test public void testWrite07BigDataS() throws IOException { long begin = System.currentTimeMillis(); Workbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(path + "//testWrite07BigDataS.xlsx"); workbook.write(outputStream); outputStream.close(); ((SXSSFWorkbook)workbook).dispose();//关闭临时文件 long end = System.currentTimeMillis(); System.out.println((double) (end-begin)/1000); }
SXSSFWorkbook-来至官方的解释:实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入 非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注释…仍然只存 储在内存中,因此如果广泛使用,可能需要大量内存。
读取单一类型的数据
这个操作跟上述的写并没有什么不同,不同就是方法是get而不是set
static String path="F:\\demo\\javapoi\\demopoi"; @Test public void testRead03() throws IOException { //Sheet sheet=workbook.createSheet("统计表"); //sheet操作表中元素 FileInputStream fileInputStream = new FileInputStream(path + "\灰灰统计表03.xls"); Workbook workbook=new HSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); // Sheet sheet2 = workbook.getSheet("灰灰统计表"); Row row = sheet.getRow(1); Cell cell = row.getCell(0); Cell cell2 = row.getCell(1); System.out.println(cell.getStringCellValue()); System.out.println(cell2.getStringCellValue()); fileInputStream.close(); }
这里值得注意的是,使用表格对象要注意三种创建方式
- POI-HSSF
- POI-XSSF
- SXSSF
**HSSF:*Excel97-2003版本,扩展名为.xls。一个sheet最大行数*65536,最大列数256。
**XSSF:*Excel2007版本开始,扩展名为.xlsx。一个sheet最大行数*1048576,最大列数16384。
SXSSF:**是在XSSF基础上,POI3.8版本开始提供的**支持低内存占用的操作方式,扩展名为.xlsx。
Excel版本兼容性是向下兼容。
读取不同类型的数据
在读取数据的时候我们需要先判断值类型,才能用对应API
下面这个是先拿到表头那一行,相当于数据库的字段
FileInputStream fileInputStream = new FileInputStream(path + "数据表07.xlsx"); Workbook workbook=new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row rowTitle = sheet.getRow(0); if(rowTitle!=null){ int cellCount=rowTitle.getPhysicalNumberOfCells(); //拿到第row行的那一行的总个数 for (int i = 0; i <cellCount ; i++) { //循环个数取出 Cell cell = rowTitle.getCell(i); if(cell!=null){ //如果不等于空取出值 int cellType = cell.getCellType(); //这里是知道我们标题是String,考虑不确定的时候怎么取 String cellValue = cell.getStringCellValue(); System.out.print(cellValue+"|"); } } System.out.println(); }
下面接着读取对应的数据,这里就需要我们刚刚讲的类型判断
int cellType=cell.getCellType();利用这个,然后判断它的XSSFCell类型再具体输出
//获取表中内容 int rowCount=sheet.getPhysicalNumberOfRows(); for(int rowNum=1;rowNum<rowCount;rowNum++){ Row rowData=sheet.getRow(rowNum); //取出对应的行 if(rowData!=null){ int cellCount=rowTitle.getPhysicalNumberOfCells(); for(int cellNum=0;cellNum<cellCount;cellNum++){ System.out.print("["+(rowNum+1+"-"+(cellNum+1)+"]")); Cell cell = rowData.getCell(cellNum); //匹配数据类型 if(cell!=null){ int cellType=cell.getCellType(); switch (cellType){ case XSSFCell.CELL_TYPE_STRING: System.out.print("字符串:"+cell.getStringCellValue());break; case XSSFCell.CELL_TYPE_BOOLEAN: System.out.print("布尔:"+cell.getBooleanCellValue());break; case XSSFCell.CELL_TYPE_NUMERIC: if(HSSFDateUtil.isCellDateFormatted(cell)){ System.out.println("日期格式:"+new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss"));break; }else cell.setCellType(XSSFCell.CELL_TYPE_STRING); System.out.print("整形:"+cell.toString());break; case XSSFCell.CELL_TYPE_BLANK: System.out.print("空");break; case XSSFCell.CELL_TYPE_ERROR: System.out.print("数据类型错误");break; case Cell.CELL_TYPE_FORMULA: //拿到计算公式 XSSFFormulaEvaluator FormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); String formula=cell.getCellFormula(); System.out.println("公式:"+formula); // CellValue evaluate = FormulaEvaluator.evaluate(cell); String cellValue=evaluate.formatAsString(); System.out.println(cellValue); break; default:break; } } } } } fileInputStream.close();
-
java实现在线预览--poi实现word、excel、ppt转html
2019-07-31 18:39:43java实现在线预览- -之poi实现word、excel、ppt转htmljava实现在线预览
- -之poi实现word、excel、ppt转html
###简介
java实现在线预览功能是一个大家在工作中也许会遇到的需求,如果公司有钱,直接使用付费的第三方软件或者云在线预览服务就可以了,例如永中office、office web 365(http://www.officeweb365.com/)他们都有云在线预览服务,就是要钱0.0
如果想要免费的,可以用openoffice,还需要借助其他的工具(例如swfTools、FlexPaper等)才行,可参考这篇文章http://blog.csdn.net/z69183787/article/details/17468039,写的挺细的,实现原理就是:
1.通过第三方工具openoffice,将word、excel、ppt、txt等文件转换为pdf文件;
2.通过swfTools将pdf文件转换成swf格式的文件;
3.通过FlexPaper文档组件在页面上进行展示。
当然如果装了Adobe Reader XI,那把pdf直接拖到浏览器页面就可以直接打开预览,这样就不需要步骤2、3了,前提就是客户装了Adobe Reader XI这个pdf阅读器。
我这里介绍通过poi实现word、excel、ppt转html,这样就可以放在页面上了。###word转html
package wordToHtml; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.InputStream; import java.util.List; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.transform.OutputKeys; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.stream.StreamResult; import org.apache.commons.io.FileUtils; import org.apache.poi.hwpf.HWPFDocument; import org.apache.poi.hwpf.converter.PicturesManager; import org.apache.poi.hwpf.converter.WordToHtmlConverter; import org.apache.poi.hwpf.usermodel.Picture; import org.apache.poi.hwpf.usermodel.PictureType; import org.w3c.dom.Document; public class PoiWordToHtml { public static void main(String[] args) throws Throwable { final String path = "D:\\poi-test\\wordToHtml\\"; final String file = "人员选择系分.doc"; InputStream input = new FileInputStream(path + file); HWPFDocument wordDocument = new HWPFDocument(input); WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter( DocumentBuilderFactory.newInstance().newDocumentBuilder() .newDocument()); wordToHtmlConverter.setPicturesManager(new PicturesManager() { public String savePicture(byte[] content, PictureType pictureType, String suggestedName, float widthInches, float heightInches) { return suggestedName; } }); wordToHtmlConverter.processDocument(wordDocument); List pics = wordDocument.getPicturesTable().getAllPictures(); if (pics != null) { for (int i = 0; i < pics.size(); i++) { Picture pic = (Picture) pics.get(i); try { pic.writeImageContent(new FileOutputStream(path + pic.suggestFullFileName())); } catch (FileNotFoundException e) { e.printStackTrace(); } } } Document htmlDocument = wordToHtmlConverter.getDocument(); ByteArrayOutputStream outStream = new ByteArrayOutputStream(); DOMSource domSource = new DOMSource(htmlDocument); StreamResult streamResult = new StreamResult(outStream); TransformerFactory tf = TransformerFactory.newInstance(); Transformer serializer = tf.newTransformer(); serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8"); serializer.setOutputProperty(OutputKeys.INDENT, "yes"); serializer.setOutputProperty(OutputKeys.METHOD, "html"); serializer.transform(domSource, streamResult); outStream.close(); String content = new String(outStream.toByteArray()); FileUtils.writeStringToFile(new File(path, "人员选择系分.html"), content, "utf-8"); } }
###excel转html
package excelToHtml; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.List; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.transform.OutputKeys; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.stream.StreamResult; import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.converter.ExcelToHtmlConverter; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hwpf.converter.PicturesManager; import org.apache.poi.hwpf.converter.WordToHtmlConverter; import org.apache.poi.hwpf.usermodel.Picture; import org.apache.poi.hwpf.usermodel.PictureType; import org.w3c.dom.Document; public class PoiExcelToHtml { final static String path = "D:\\poi-test\\excelToHtml\\"; final static String file = "exportExcel.xls"; public static void main(String args[]) throws Exception { InputStream input=new FileInputStream(path+file); HSSFWorkbook excelBook=new HSSFWorkbook(input); ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter (DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument() ); excelToHtmlConverter.processWorkbook(excelBook); List pics = excelBook.getAllPictures(); if (pics != null) { for (int i = 0; i < pics.size(); i++) { Picture pic = (Picture) pics.get (i); try { pic.writeImageContent (new FileOutputStream (path + pic.suggestFullFileName() ) ); } catch (FileNotFoundException e) { e.printStackTrace(); } } } Document htmlDocument =excelToHtmlConverter.getDocument(); ByteArrayOutputStream outStream = new ByteArrayOutputStream(); DOMSource domSource = new DOMSource (htmlDocument); StreamResult streamResult = new StreamResult (outStream); TransformerFactory tf = TransformerFactory.newInstance(); Transformer serializer = tf.newTransformer(); serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8"); serializer.setOutputProperty (OutputKeys.INDENT, "yes"); serializer.setOutputProperty (OutputKeys.METHOD, "html"); serializer.transform (domSource, streamResult); outStream.close(); String content = new String (outStream.toByteArray() ); FileUtils.writeStringToFile(new File (path, "exportExcel.html"), content, "utf-8"); } }
###ppt转html
其实只是ppt转图片,有了图片后放到页面上去,点击下一页就一张张显示就可以了。这里只介绍ppt转图片的过程。package pptToImg; import java.awt.Dimension; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.awt.Color; import java.awt.Dimension; import java.awt.Graphics2D; import java.awt.geom.Rectangle2D; import java.awt.image.BufferedImage; import org.apache.poi.hslf.model.TextRun; import org.apache.poi.hslf.record.Slide; import org.apache.poi.hslf.usermodel.RichTextRun; import org.apache.poi.hslf.usermodel.SlideShow; public class PPTtoImage { public static void main(String[] args) { // 读入PPT文件 File file = new File("D:/poi-test/pptToImg/test.ppt"); doPPTtoImage(file); } public static boolean doPPTtoImage(File file) { boolean isppt = checkFile(file); if (!isppt) { System.out.println("The image you specify don't exit!"); return false; } try { FileInputStream is = new FileInputStream(file); SlideShow ppt = new SlideShow(is); is.close(); Dimension pgsize = ppt.getPageSize(); org.apache.poi.hslf.model.Slide[] slide = ppt.getSlides(); for (int i = 0; i < slide.length; i++) { System.out.print("第" + i + "页。"); TextRun[] truns = slide[i].getTextRuns(); for ( int k=0;k<truns.length;k++){ RichTextRun[] rtruns = truns[k].getRichTextRuns(); for(int l=0;l<rtruns.length;l++){ int index = rtruns[l].getFontIndex(); String name = rtruns[l].getFontName(); rtruns[l].setFontIndex(1); rtruns[l].setFontName("宋体"); // System.out.println(rtruns[l].getText()); } } BufferedImage img = new BufferedImage(pgsize.width,pgsize.height, BufferedImage.TYPE_INT_RGB); Graphics2D graphics = img.createGraphics(); graphics.setPaint(Color.BLUE); graphics.fill(new Rectangle2D.Float(0, 0, pgsize.width, pgsize.height)); slide[i].draw(graphics); // 这里设置图片的存放路径和图片的格式(jpeg,png,bmp等等),注意生成文件路径 FileOutputStream out = new FileOutputStream("D:/poi-test/pptToImg/pict_"+ (i + 1) + ".jpeg"); javax.imageio.ImageIO.write(img, "jpeg", out); out.close(); } System.out.println("success!!"); return true; } catch (FileNotFoundException e) { System.out.println(e); // System.out.println("Can't find the image!"); } catch (IOException e) { } return false; } // function 检查文件是否为PPT public static boolean checkFile(File file) { boolean isppt = false; String filename = file.getName(); String suffixname = null; if (filename != null && filename.indexOf(".") != -1) { suffixname = filename.substring(filename.indexOf(".")); if (suffixname.equals(".ppt")) { isppt = true; } return isppt; } else { return isppt; } } }
ppt转图片有个缺陷,就是ppt里不是宋体的字有些可能会变成框框。
以上都需要引入poi的jar包。
要实现在线预览,只需把转换得到的html在新标签页打开或者镶嵌到某块区域就可以展现了。 -
poi
2020-08-27 17:14:37poi 两种模式 1.用户模式 一次性加载excel(xml文档以一棵DOM树的形式存放在内存中) 数据量大会OOM 2.SAX模式(XSSF and SAX(event API)) 逐行读取xml解析(excel有03和07两种版本,两个版本数据存储方式截然不同,...poi 两种模式
1.用户模式
一次性加载excel(xml文档以一棵DOM树的形式存放在内存中) 数据量大会OOM
2.SAX模式(XSSF and SAX(event API))
逐行读取xml解析(excel有03和07两种版本,两个版本数据存储方式截然不同,sax解析方式也各不一样 api复杂不利于实现)
EasyExcel
官网
https://www.yuque.com/easyexcel/doc/easyexcel
原理
1.对poi的封装和改版
2.文件解压文件读取通过文件形式(通过磁盘可以减少内存的使用)
3.采用sax模式一行一行解析,并将一行的解析结果以观察者的模式通知处理
4.抛弃不重要的数据(样式,字体,宽度) -
Apache POI与Alibaba EasyExcel 的使用
2020-09-30 14:04:20Apache POI 与 Alibaba EasyExcel Apache POI <dependencies> <!-- xls(03) --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</...Apache POI 与 Alibaba EasyExcel 的使用
- Apache POI
<dependencies> <!-- xls(03) --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!-- xls(07) --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <!-- 日期格式化工具 --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
- 测试写(03版)
String Path = "D:\\data\\"; @Test public void testWrite03() throws Exception { // 创建工作簿对象 03 Workbook workbook = new HSSFWorkbook(); // 创建一个工作表 Sheet sheet0 = workbook.createSheet("BLU测试表"); // 创建一行 Row row0 = sheet0.createRow(0); // 创建一个单元格 Cell cell00 = row0.createCell(0); cell00.setCellValue("编号"); Cell cell01 = row0.createCell(1); cell01.setCellValue("姓名"); Row row1 = sheet0.createRow(1); Cell cell10 = row1.createCell(0); cell10.setCellValue(1); Cell cell11 = row1.createCell(1); cell11.setCellValue("BLU"); Cell cell12 = row1.createCell(2); String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell12.setCellValue(time); FileOutputStream fos = new FileOutputStream(Path + "test03.xls"); workbook.write(fos); fos.close(); System.out.println("文件生成完毕"); }
- 测试写(07版)
String Path = "D:\\data\\"; @Test public void testWrite07() throws Exception { Workbook workbook = new XSSFWorkbook(); Sheet sheet0 = workbook.createSheet("BLU测试表"); Row row0 = sheet0.createRow(0); Cell cell00 = row0.createCell(0); cell00.setCellValue("编号"); Cell cell01 = row0.createCell(1); cell01.setCellValue("姓名"); Row row1 = sheet0.createRow(1); Cell cell10 = row1.createCell(0); cell10.setCellValue(1); Cell cell11 = row1.createCell(1); cell11.setCellValue("BLU"); Cell cell12 = row1.createCell(2); String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell12.setCellValue(time); FileOutputStream fos = new FileOutputStream(Path + "test07.xlsx"); workbook.write(fos); fos.close(); System.out.println("文件生成完毕"); }
- 测试大数据写入(03版)
String Path = "D:\\data\\"; /** * 65536行数据耗时:2.815s * 最多只能创建65536行 */ @Test public void testWrite03BigData() throws Exception { long begin = System.currentTimeMillis(); HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 65536; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream fos = new FileOutputStream(Path + "testWrite03BigData.xls"); workbook.write(fos); fos.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); }
- 测试大数据写入(07版)
String Path = "D:\\data\\"; /** * 65536行数据耗时6.843s * 10万行数据耗时13.028s * 内存占用大 */ @Test public void testWrite07BigData() throws Exception { long begin = System.currentTimeMillis(); XSSFWorkbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream fos = new FileOutputStream(Path + "testWrite07BigData.xlsx"); workbook.write(fos); fos.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); }
- 使用 SXSSFWorkbook 测试大数据写入
String Path = "D:\\data\\"; /** * 10万行数据耗时1.916s */ @Test public void testWrite07BigDataS() throws Exception { long begin = System.currentTimeMillis(); SXSSFWorkbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream fos = new FileOutputStream(Path + "testWrite07BigDataS.xlsx"); workbook.write(fos); fos.close(); // 清除临时文件 workbook.dispose(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); }
- 测试读(03版)
String Path = "D:\\data\\"; @Test public void testRead03() throws Exception { FileInputStream fis = new FileInputStream(Path + "test03.xls"); Workbook workbook = new HSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(1); Cell cell0 = row.getCell(0); double value0 = cell0.getNumericCellValue(); System.out.println(value0); Cell cell1 = row.getCell(1); String value1 = cell1.getStringCellValue(); System.out.println(value1); fis.close(); }
1.0 BLU
- 测试读(07版)
String Path = "D:\\data\\"; @Test public void testRead07() throws Exception { FileInputStream fis = new FileInputStream(Path + "test07.xlsx"); Workbook workbook = new XSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(1); Cell cell0 = row.getCell(0); double value0 = cell0.getNumericCellValue(); System.out.println(value0); Cell cell1 = row.getCell(1); String value1 = cell1.getStringCellValue(); System.out.println(value1); fis.close(); }
1.0 BLU
- 根据数据类型读取的示例:
BLU.xls文件:
String Path = "D:\\data\\"; @Test public void testCellType() throws Exception { FileInputStream fis = new FileInputStream(Path + "BLU.xls"); Workbook workbook = new HSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row rowTitle = sheet.getRow(0); if (rowTitle != null) { int cellCount = rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowTitle.getCell(cellNum); if (cell != null) { String cellValue = cell.getStringCellValue(); System.out.print(cellValue + " | "); } } } System.out.println(); int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row rowData = sheet.getRow(rowNum); if (rowData != null) { int cellCount = rowData.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowData.getCell(cellNum); if (cell != null) { int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: System.out.print(String.valueOf(cell.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_NUMERIC: if(HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); String s = new DateTime(date).toString("yyyy-MM-dd"); System.out.print(s); }else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); System.out.print(cell.toString()); } break; case HSSFCell.CELL_TYPE_ERROR: break; } System.out.print(" | "); } } System.out.println(); } } fis.close(); }
手机号 | 日期 | 订单号 | 商品编号 | 商品名称 | 价格 | 销售数量 | 销售金额 | 已发货 | 15651776666 | 2020-09-30 | 000001 | 1 | 蒙牛 | 65.5 | 1 | 65.5 | true | 15651776666 | 2020-10-01 | 000002 | 2 | 脑白金 | 100 | 10 | 1000 | false |
- 读取计算函数的示例:
func.xls文件:
String Path = "D:\\data\\"; @Test public void testFormula() throws Exception { FileInputStream fis = new FileInputStream(Path + "func.xls"); Workbook workbook = new HSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(4); Cell cell = row.getCell(0); FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); int cellType = cell.getCellType(); switch(cellType) { case Cell.CELL_TYPE_FORMULA: String formula = cell.getCellFormula(); System.out.println(formula); CellValue evaluate = formulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } }
SUM(A2:A4) 600.0
- Alibaba EasyExcel
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.0-beta2</version> </dependency> <!-- 日期格式化工具 --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.62</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
- 实体类:
package com.blu.easy; import java.util.Date; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; @Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; /** * 忽略这个字段 */ @ExcelIgnore private String ignore; }
- 监听器:
package com.blu.easy; import java.util.ArrayList; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; public class DemoDataListener extends AnalysisEventListener<DemoData> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); private DemoDAO demoDAO; public DemoDataListener() { demoDAO = new DemoDAO(); } public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } @Override public void invoke(DemoData data, AnalysisContext context) { System.out.println(JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成!"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据,开始存储数据库!", list.size()); demoDAO.save(list); LOGGER.info("存储数据库成功!"); } }
- DAO:
package com.blu.easy; import java.util.List; /** * 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。 **/ public class DemoDAO { public void save(List<DemoData> list) { // 持久化操作 // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 } }
- 测试:
package com.blu.easy; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.junit.Test; import com.alibaba.excel.EasyExcel; public class EasyTest { String Path = "D:\\data\\"; private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } /** * 最简单的写 */ @Test public void simpleWrite() { String fileName = Path + "easyTest.xlsx"; EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } /** * 最简单的读 */ @Test public void simpleRead() { // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 String fileName = Path + "easyTest.xlsx"; // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); } }
写入结果:
读取结果:{"date":1601434567000,"doubleData":0.56,"string":"字符串0"} {"date":1601434567000,"doubleData":0.56,"string":"字符串1"} {"date":1601434567000,"doubleData":0.56,"string":"字符串2"} {"date":1601434567000,"doubleData":0.56,"string":"字符串3"} {"date":1601434567000,"doubleData":0.56,"string":"字符串4"} {"date":1601434567000,"doubleData":0.56,"string":"字符串5"} {"date":1601434567000,"doubleData":0.56,"string":"字符串6"} {"date":1601434567000,"doubleData":0.56,"string":"字符串7"} {"date":1601434567000,"doubleData":0.56,"string":"字符串8"} {"date":1601434567000,"doubleData":0.56,"string":"字符串9"}
-
Apache POI使用详解
2017-06-05 20:11:35一 :简介开发中经常会设计到excel的处理,如导出Excel,导入Excel到数据库中,操作Excel目前有两个框架,一个是apache 的poi, 另一个是 Java Excel Apache POI 简介是用Java编写的免费开源的跨平台的 Java API,... -
poi3.9
2013-01-27 22:36:06poi3.9 -
poi的3.17版本问题
2020-04-01 12:01:32网上很多版本对于poi中样式中样式设置都是poi之前的操作,在poi3.17版本中并不能直接使用 (1)单元格样式 在poi导出设置单元格样式的居中问题时候,网上大部分的操作代码如下: XSSFCellStyle cellStyle = wb.... -
如何下载全国的POI数据,如何获取全国的POI数据,poi数据搜索,高德poi获取,poi数据分析,poi免费数据,城市规划...
2020-03-01 22:55:41最近朋友写论文,让我帮他弄poi数据,偶然间发现一个小程序,进而获取了全国的poi数据 教程如下: Step1 打开微信,扫码使用小程序 注意: 1,它会要求填写手机号和邮箱 ps:这里最重要的是邮箱,会收到一封搜索poi数据结果的... -
提高POI 读写效率
2018-02-01 11:14:58POI -
关于poi的NUMERIC读取excel单元格数值类型数字的问题
2020-12-12 11:09:48使用poi读取excel文件单元格的数据,若是单元格数据是数值格式,poi获取单元格的数值值: row.getCell(j).getNumericCellValue() 返回的的结果是double类型 若是单元格是int类型,那获取出来的数值也还是... -
poi.jar 全部jar包程序文件
2012-08-24 15:46:35poi读取excel数据所需要的jar包 -
poi简介与简单使用
2019-03-09 17:22:30POI是Apache软件基金会用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的... -
Java POI 导入导出Excel简单实例源代码
2014-08-28 16:25:39Java POI 导入导出Excel简单实例源代码 该源代码的jar包,参见以下博文附录截图 Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 http://blog.csdn.net/evangel_z/article/details/7332535 web页面导出Excel... -
java使用poi读取doc和docx文件
2016-07-22 10:53:02这几天在学习java io流的东西,有一个网友... 于是在网上搜寻了一阵之后才发现原来doc文档和excel一样不能用普通的io流的方法来读取,而是也需要用poi,于是进行了一番尝试后,终于以正确的编码格式读取了这个doc文件。 -
POI3.17与POI旧版本对比,方便POI升级jar包修改
2020-04-30 16:15:42POI3.17与POI旧版本对比,方便POI升级jar包修改颜色定义变化获取单元格格式设置单元格格式设置单元格垂直居中样式设置边框合并单元格设置字体加粗 最近使用EasyExcel写导入导出Excel,需要POI版本是3.17,之前是3.9... -
JAVA使用POI读取EXCEL文件的简单model
2017-05-23 14:42:03[java] view plain copy print?packagepoi;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.util.Iterator;importorg.apache.poi.hssf.usermodel.HS -
POI教程
2019-01-15 11:08:19POI报表 --用POI与Excel交互 AURISOFT 第一章 POI简介 --Jakata Poi HSSF:纯java的Excel解决方案 在我们实际的开发中,表现层的解决方案虽然有多样,但是IE浏览器已成为最多人使用的浏览器,因为大家都用... -
java poi导出Excel表格超大数据量解决方案
2016-07-02 10:59:43Java实现导出excel表格功能,大部分都会使用apache poi, apache poi API 地址 -
POI 题解
2016-09-01 15:59:48从2015年开始往前一点一点做的POI题解汇总(全中文!!!): POI2015 POI2014 POI2013 POI2012 POI2011 -
Java 操作Excel poi读写excel
2018-10-21 10:45:46Java 操作Excel poi读写excel,excel导入导出(精简版) 1.所需jar包 下载地址:https://download.csdn.net/download/u014646662/10734734 2.源码 写操作(excel导出) package cn.com.tengen.excel; import... -
poi内存溢出解决办法
2018-01-03 11:41:55poi -
POI 技术实战
2018-08-14 00:43:43POI 技术如何实现对 Word 和 Excel 的读写操作?POI 技术相对其他同类型技术的优劣势又是哪些?怎样实现复杂的 Excel 读写操作?POI 对于 Word 和 Excel 有足够友好吗?这个 Chat,带领大家使用免费却实用的 POI ... -
使用Java代码(POI)读写 Excel
2020-01-07 19:49:59使用Java代码(POI)读写 Excel,这篇博客给出了实现逻辑并有完整的代码实现。 -
POI复制行,POI复制列
2018-06-07 16:56:18package ...import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi... -
easy poi 与poi 冲突解决方案
2019-12-25 11:45:23在1个项目中使用easy poi 是不错的选择,不过会与项目中的poi冲突,这就需要解决jar包冲突的问题,在网上找了很多解决方案吗,但是并没有找到。方便日后查阅。希望帮助大家。 <!-- Excel 导出 --> <... -
【POI框架实战】——POI导出Excel时设置单元格类型为数值类型
2015-11-17 19:04:12最近做的一个ITFIN的项目中,导出的数据中有文本格式...然后往单元格中存放数据的时候要设置数据的格式为double类型,如果查看poi的源码HSSFCell.java会发现设置数据的方法如下,所以用setCellValue(double)方法即可。 -
Java POI Excel读取
2017-12-12 11:54:01POI版本3.15 pom文件 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <... -
Poi-tl
2020-01-09 16:05:18Poi-tl导出word模板 poi-ti是什么 poi-tl是一个Word 模板引擎,基于Apache poi,目标是在文档的任何地方做任何事情。具体详情可以查看官网信息。poi-tl 项目开发中使用 为什么要使用poi-tl 原因很简单就是简单方便...