精华内容
下载资源
问答
  • 本篇文章主要介绍了利用Java Apache POI 生成Word文档示例代码,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • apache poi jar包

    2018-12-01 14:13:27
    读取excel表格内容,2007版本之后的xlsx格式需要的apache jar包。
  • apache POI 读取 Excel

    2019-03-01 16:44:13
    apache poi 读取 Excel 的 jar 包 博文链接:https://wxinpeng.iteye.com/blog/231895
  • apache poi 导出excel、word,替换word内容,导出word-文本、图片、表格
  • POI中中关于word的各种操作都在这里,将数据导入方便转换!
  • Apache POI 文件 入门 步骤1:下载 git clone https://github.com/T5750/poi.git cd poi 步骤2:启动伺服器 mvn clean spring-boot:run 测试 TestReadExcel , TestReadExcelDemo TestExportExcel , ...
  • apache poi各jar包及简介

    2017-08-25 13:47:27
    Excel文件上传下载必备包 : poi-3.14-20160307.jar,poi-excelant-3.14-20160307.jar,poi-ooxml-3.14-20160307.jar,poi-ooxml-schemas-3.14-20160307.jar,poi-scratchpad-3.14-20160307.jar,共5个
  • 使用java poi动态编辑生成word文件,生成报表信息。支持插入循环list数据和单个数据得工具类,和测试模板,直接导入就能使用
  • 使用java Apache poi 根据word模板生成word报表 仅支持docx格式的word文件,大概是word2010及以后版本,doc格式不支持。 使用说明:https://blog.csdn.net/u012775558/article/details/79678701
  • Apache poi 根据word模板生成word报表 替换 循环列表 图片,代码调试过了,修改相应的图片位置,word模板路径即可运行
  • Aspose for Apache POI是一个提供比较源代码示例的项目,该示例使用Aspose for Java API和Apache POI来执行相同的文件处理任务。 此外,该项目提供了Aspose提供的高级功能的源代码示例,而Apache POI中缺少这些功能...
  • 使用POI读写Word doc文件 Apache poi的hwpf模块是专门用来对word doc文件进行读写操作的。在hwpf里面我们使用HWPFDocument来表示一个word doc文档
  • Apache POI导入和导出Excel文件
  • apache poi教程_Apache POI教程

    千次阅读 2020-07-06 23:54:52
    apache poi教程Welcome to Apache POI Tutorial. Sometimes we need to read data from Microsoft Excel Files or we need to generate reports in Excel format, mostly for Business or Finance purposes. Java ...

    apache poi教程

    Welcome to Apache POI Tutorial. Sometimes we need to read data from Microsoft Excel Files or we need to generate reports in Excel format, mostly for Business or Finance purposes. Java doesn’t provide built-in support for working with excel files, so we need to look for open source APIs for the job. When I started the hunt for Java APIs for excel, most of the people recommended JExcel or Apache POI.

    欢迎使用Apache POI教程。 有时我们需要从Microsoft Excel文件中读取数据,或者我们需要以Excel格式生成报告,主要用于商业或财务目的。 Java不提供使用excel文件的内置支持,因此我们需要为此工作寻找开源API。 当我开始搜寻excel的Java API时,大多数人都推荐JExcel或Apache POI。

    After further research, I found that Apache POI is the way to go for following main reasons. There are some other reasons related to advanced features but let’s not go into that much detail.

    经过进一步的研究,我发现Apache POI是行之有效的,其主要原因如下。 还有其他一些与高级功能有关的原因,但让我们不再赘述。

    • Backing of Apache foundation.

      Apache基础的后盾。
    • JExcel doesn’t support xlsx format whereas POI supports both xls and xlsx formats.

      JExcel不支持xlsx格式,而POI支持xls和xlsx格式。
    • Apache POI provides stream-based processing, that is suitable for large files and requires less memory.

      Apache POI提供基于流的处理,该处理适用于大文件且需要较少的内存。

    Apache POI (Apache POI)

    Apache POI provides excellent support for working with Microsoft Excel documents. Apache POI is able to handle both XLS and XLSX formats of spreadsheets.

    Apache POI为使用Microsoft Excel文档提供了出色的支持。 Apache POI能够处理电子表格的XLS和XLSX格式。

    Some important points about Apache POI API are:

    关于Apache POI API的一些重要点是:

    1. Apache POI contains HSSF implementation for Excel ’97(-2007) file format i.e XLS.

      Apache POI包含用于Excel '97(-2007)文件格式(即XLS)的HSSF实现。
    2. Apache POI XSSF implementation should be used for Excel 2007 OOXML (.xlsx) file format.

      Apache POI XSSF实现应用于Excel 2007 OOXML(.xlsx)文件格式。
    3. Apache POI HSSF and XSSF API provides mechanisms to read, write or modify excel spreadsheets.

      Apache POI HSSF和XSSF API提供了读取,写入或修改excel电子表格的机制。
    4. Apache POI also provides SXSSF API that is an extension of XSSF to work with very large excel sheets. SXSSF API requires less memory and is suitable when working with very large spreadsheets and heap memory is limited.

      Apache POI还提供了SXSSF API,它是XSSF的扩展,可以与非常大的ex​​cel工作表一起使用。 SXSSF API需要较少的内存,适用于处理非常大的电子表格且堆内存受限制的情况。
    5. There are two models to choose from – event model and user model. Event model requires less memory because the excel file is read in tokens and requires processing them. User model is more object oriented and easy to use and we will use this in our examples.

      有两种模型可供选择-事件模型和用户模型。 事件模型需要较少的内存,因为excel文件是在令牌中读取并需要对其进行处理的。 用户模型更加面向对象并且易于使用,我们将在示例中使用它。
    6. Apache POI provides excellent support for additional excel features such as working with Formulas, creating cell styles by filling colors and borders, fonts, headers and footers, data validations, images, hyperlinks etc.

      Apache POI为其他excel功能提供了出色的支持,例如使用公式,通过填充颜色和边框,字体,页眉和页脚,数据验证,图像,超链接等来创建单元格样式。

    Apache POI Maven依赖关系 (Apache POI Maven Dependencies)

    If you are using maven, add below Apache POI dependencies.

    如果使用的是maven,请在Apache POI依赖项下面添加。

    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>3.10-FINAL</version>
    </dependency>
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.10-FINAL</version>
    </dependency>

    Current version of Apache POI is 3.10-FINAL. If you are having standalone java application, include jars from below image.

    Apache POI的当前版本是3.10-FINAL。 如果您有独立的Java应用程序,请在图像下方添加jar。

    Apache POI示例–读取Excel文件 (Apache POI Example – Read Excel File)

    Let’s say we have an excel file “Sample.xlsx” with two sheets and having data like below image. We want to read the excel file and create the list of Countries. Sheet1 has some additional data, that we will ignore while parsing it.

    假设我们有一个Excel文件“ Sample.xlsx”,其中包含两张纸,数据如下图所示。 我们想读取excel文件并创建国家列表。 Sheet1还有一些其他数据,我们在解析时会忽略它们。

    Our Country java bean code is:

    我们国家的Java bean代码是:

    Country.java

    Country.java

    package com.journaldev.excel.read;
    
    public class Country {
    
    	private String name;
    	private String shortCode;
    	
    	public Country(String n, String c){
    		this.name=n;
    		this.shortCode=c;
    	}
    	
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getShortCode() {
    		return shortCode;
    	}
    	public void setShortCode(String shortCode) {
    		this.shortCode = shortCode;
    	}
    	
    	@Override
    	public String toString(){
    		return name + "::" + shortCode;
    	}
    	
    }

    Apache POI example program to read excel file to the list of countries looks like below.

    用于将excel文件读取到国家/地区列表的Apache POI示例程序如下所示。

    ReadExcelFileToList.java

    ReadExcelFileToList.java

    package com.journaldev.excel.read;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    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.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ReadExcelFileToList {
    
    	public static List<Country> readExcelData(String fileName) {
    		List<Country> countriesList = new ArrayList<Country>();
    		
    		try {
    			//Create the input stream from the xlsx/xls file
    			FileInputStream fis = new FileInputStream(fileName);
    			
    			//Create Workbook instance for xlsx/xls file input stream
    			Workbook workbook = null;
    			if(fileName.toLowerCase().endsWith("xlsx")){
    				workbook = new XSSFWorkbook(fis);
    			}else if(fileName.toLowerCase().endsWith("xls")){
    				workbook = new HSSFWorkbook(fis);
    			}
    			
    			//Get the number of sheets in the xlsx file
    			int numberOfSheets = workbook.getNumberOfSheets();
    			
    			//loop through each of the sheets
    			for(int i=0; i < numberOfSheets; i++){
    				
    				//Get the nth sheet from the workbook
    				Sheet sheet = workbook.getSheetAt(i);
    				
    				//every sheet has rows, iterate over them
    				Iterator<Row> rowIterator = sheet.iterator();
    				while (rowIterator.hasNext()) 
    		        {
    					String name = "";
    					String shortCode = "";
    					
    					//Get the row object
    					Row row = rowIterator.next();
    					
    					//Every row has columns, get the column iterator and iterate over them
    					Iterator<Cell> cellIterator = row.cellIterator();
    		             
    		            while (cellIterator.hasNext()) 
    		            {
    		            	//Get the Cell object
    		            	Cell cell = cellIterator.next();
    		            	
    		            	//check the cell type and process accordingly
    		            	switch(cell.getCellType()){
    		            	case Cell.CELL_TYPE_STRING:
    		            		if(shortCode.equalsIgnoreCase("")){
    		            			shortCode = cell.getStringCellValue().trim();
    		            		}else if(name.equalsIgnoreCase("")){
    		            			//2nd column
    		            			name = cell.getStringCellValue().trim();
    		            		}else{
    		            			//random data, leave it
    		            			System.out.println("Random data::"+cell.getStringCellValue());
    		            		}
    		            		break;
    		            	case Cell.CELL_TYPE_NUMERIC:
    		            		System.out.println("Random data::"+cell.getNumericCellValue());
    		            	}
    		            } //end of cell iterator
    		            Country c = new Country(name, shortCode);
    		            countriesList.add(c);
    		        } //end of rows iterator
    				
    				
    			} //end of sheets for loop
    			
    			//close file input stream
    			fis.close();
    			
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		
    		return countriesList;
    	}
    
    	public static void main(String args[]){
    		List<Country> list = readExcelData("Sample.xlsx");
    		System.out.println("Country List\n"+list);
    	}
    
    }

    The program is very easy to understand and contains following steps:

    该程序非常易于理解,包含以下步骤:

    1. Create Workbook instance based on the file type. XSSFWorkbook for xlsx format and HSSFWorkbook for xls format. Notice that we could have created a wrapper class with factory pattern to get the workbook instance based on the file name.

      根据文件类型创建Workbook实例。 XSSFWorkbook格式的HSSFWorkbook和xls格式的HSSFWorkbook 。 请注意,我们可以使用工厂模式创建包装类,以基于文件名获取工作簿实例。
    2. Use workbook getNumberOfSheets() to get the number of sheets and then use for loop to parse each of the sheets. Get the Sheet instance using getSheetAt(int i) method.

      使用工作簿getNumberOfSheets()获取工作表的数量,然后使用for循环来解析每个工作表。 使用getSheetAt(int i)方法获取Sheet实例。
    3. Get Row iterator and then Cell iterator to get the Cell object. Apache POI is using iterator pattern here.

      获取Row迭代器,然后获取Cell迭代器以获取单元对象。 Apache POI在这里使用迭代器模式
    4. Use switch-case to read the type of Cell and the process it accordingly.

      使用开关盒来读取Cell的类型并对其进行相应处理。

    Now when we run above Apache POI example program, it produces following output on console.

    现在,当我们在Apache POI示例程序之上运行时,它将在控制台上产生以下输出。

    Random data::1.0
    Random data::2.0
    Random data::3.0
    Random data::4.0
    Country List
    [India::IND, Afghanistan::AFG, United States of America::USA, Anguilla::AIA, 
    Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]

    Apache POI示例–编写Excel文件 (Apache POI Example – Write Excel File)

    Writing excel file in apache POI is similar to reading, except that here we first create the workbook. Then set sheets, rows and cells values and use FileOutputStream to write it to file. Let’s write a simple apache POI example where we will use list of countries from the above method to save into another file in a single sheet.

    用apache POI编写excel文件与阅读类似,不同之处在于,我们首先创建工作簿。 然后设置工作表,行和单元格的值,并使用FileOutputStream将其写入文件。 让我们写一个简单的Apache POI示例,在该示例中,我们将使用上述方法中的国家/地区列表将其保存到一张纸中的另一个文件中。

    WriteListToExcelFile.java

    WriteListToExcelFile.java

    package com.journaldev.excel.read;
    
    import java.io.FileOutputStream;
    import java.util.Iterator;
    import java.util.List;
    
    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.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class WriteListToExcelFile {
    
    	public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{
    		Workbook workbook = null;
    		
    		if(fileName.endsWith("xlsx")){
    			workbook = new XSSFWorkbook();
    		}else if(fileName.endsWith("xls")){
    			workbook = new HSSFWorkbook();
    		}else{
    			throw new Exception("invalid file name, should be xls or xlsx");
    		}
    		
    		Sheet sheet = workbook.createSheet("Countries");
    		
    		Iterator<Country> iterator = countryList.iterator();
    		
    		int rowIndex = 0;
    		while(iterator.hasNext()){
    			Country country = iterator.next();
    			Row row = sheet.createRow(rowIndex++);
    			Cell cell0 = row.createCell(0);
    			cell0.setCellValue(country.getName());
    			Cell cell1 = row.createCell(1);
    			cell1.setCellValue(country.getShortCode());
    		}
    		
    		//lets write the excel data to file now
    		FileOutputStream fos = new FileOutputStream(fileName);
    		workbook.write(fos);
    		fos.close();
    		System.out.println(fileName + " written successfully");
    	}
    	
    	public static void main(String args[]) throws Exception{
    		List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");
    		WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
    	}
    }

    When I execute above apache POI example program, the excel file generated looks like below image.

    当我执行以上apache POI示例程序时,生成的excel文件如下图所示。

    Apache POI示例–阅读Excel公式 (Apache POI Example – Read Excel Formula)

    Sometimes we need to handle complex excel files with formulas, let’s see a simple apache POI example to read the formula of a cell with it’s value.

    有时我们需要使用公式处理复杂的excel文件,让我们看一个简单的apache POI示例,以读取具有其值的单元格的公式。

    ReadExcelFormula.java

    ReadExcelFormula.java

    package com.journaldev.excel.read;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.Iterator;
    
    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.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ReadExcelFormula {
    
    	public static void readExcelFormula(String fileName) throws IOException{
    		
    		FileInputStream fis = new FileInputStream(fileName);
    		
    		//assuming xlsx file
    		Workbook workbook = new XSSFWorkbook(fis);
    		Sheet sheet = workbook.getSheetAt(0);
    		Iterator<Row> rowIterator = sheet.iterator();
    		while (rowIterator.hasNext()) 
            {
    			Row row = rowIterator.next();
    			Iterator<Cell> cellIterator = row.cellIterator();
                
                while (cellIterator.hasNext()) 
                {
                	Cell cell = cellIterator.next();
                	switch(cell.getCellType()){
                	case Cell.CELL_TYPE_NUMERIC:
                		System.out.println(cell.getNumericCellValue());
                		break;
                	case Cell.CELL_TYPE_FORMULA:
                		System.out.println("Cell Formula="+cell.getCellFormula());
                		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
                		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
                			System.out.println("Formula Value="+cell.getNumericCellValue());
                		}
                	}
                }
            }
    	}
    	
    	public static void main(String args[]) throws IOException {
    		readExcelFormula("FormulaMultiply.xlsx");
    	}
    }

    When we execute above apache poi example program, we get following output.

    当我们执行上面的apache poi示例程序时,我们得到以下输出。

    1.0
    2.0
    3.0
    4.0
    Cell Formula=A1*A2*A3*A4
    Cell Formula Result Type=0
    Formula Value=24.0

    Apache POI示例– Excel编写公式 (Apache POI Example – Excel Write Formula)

    Sometimes, we need to do some calculations and then write the cell values. We can use the excel formulas to do this calculation and that will make it more accurate because values will change if the cell values used in calculations are changed.

    有时,我们需要进行一些计算,然后编写单元格值。 我们可以使用excel公式进行此计算,这将使其更加准确,因为如果更改了计算中使用的单元格值,则值将更改。

    Let’s see a simple example to write excel file with formulas using apache poi api.

    让我们看一个简单的示例,使用apache poi api用公式编写excel文件。

    WriteExcelWithFormula.java

    WriteExcelWithFormula.java

    package com.journaldev.excel.read;
    
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class WriteExcelWithFormula {
    
    	public static void writeExcelWithFormula(String fileName) throws IOException{
    		Workbook workbook = new XSSFWorkbook();
    		Sheet sheet = workbook.createSheet("Numbers");
    		Row row = sheet.createRow(0);
    		row.createCell(0).setCellValue(10);
    		row.createCell(1).setCellValue(20);
    		row.createCell(2).setCellValue(30);
    		//set formula cell
    		row.createCell(3).setCellFormula("A1*B1*C1");
    		
    		//lets write to file
    		FileOutputStream fos = new FileOutputStream(fileName);
    		workbook.write(fos);
    		fos.close();
    		System.out.println(fileName + " written successfully");
    	}
    	
    	public static void main(String[] args) throws IOException {
    		writeExcelWithFormula("Formulas.xlsx");
    	}
    }

    The excel file produced with above Apache POI API example program looks like below image.

    上面的Apache POI API示例程序产生的excel文件如下图所示。

    That’s all on Apache POI tutorial for working with excel files, look into Apache POI classes methods to learn more features of it.

    Apache POI教程包含用于处理Excel文件的全部内容,请查看Apache POI类方法以了解其更多功能。

    References: Apache POI Developers Guide

    参考Apache POI开发人员指南

    翻译自: https://www.journaldev.com/2562/apache-poi-tutorial

    apache poi教程

    展开全文
  • apache poi根据模板导出excel的模版
  • 本篇文章主要介绍了java后台利用Apache poi 生成excel文档提供前台下载示例,非常具有实用价值,需要的朋友可以参考下
  • Apache POI资源包

    2019-04-06 11:45:39
    apache开发的用于java跨平台读取各类Microsoft文档的资源包jar合集。包括说明文档
  • NULL 博文链接:https://mianhuaman.iteye.com/blog/1277240
  • apache poi

    2020-09-22 15:11:59
    POI项目:Java使用POI读取和写入Excel <groupId>org.apache.poi <artifactId>poi <groupId>org.apache.poi <artifactId>poi-ooxml </dependency>
  • 一个使用Apache POI简化从数据库到Excel文件的导出的库 由于大量出口的性能需求,MemPOI不适合与ORM一起使用。 简短 支持 Apache POI 4.0.0以上 Java 8+ 进口 有Gradle implementation group : ' it.firegloves ' ...
  • 主要介绍了Java中使用Apache POI读取word文件简单示例,本文着重介绍了一些必要条件,然后给出一个简单读取示例,需要的朋友可以参考下
  • Apache POI 所有 jar 下载

    2018-04-12 21:57:20
    Apache POIApache软件基金会提供的100%开源库。大多数中小规模的应用程序开发主要依赖于Apache POI(HSSF+ XSSF)。它支持Excel 库的所有基本功能; 然而,呈现和文本提取是它的主要特点。
  • poiApache POI的镜像
  • NULL 博文链接:https://keep-going.iteye.com/blog/1907664
  • POI工程结构 HSSF提供读写Excel XLS格式档案的功能 XSSF提供读写Excel OOXML XLSX格式档案的功能
  • Apache POI 中文使用指南

    千次阅读 2021-02-19 10:51:05
    Apache POI 中文使用手册

    1. Apache POI 中文使用手册

    1.1 Apache POI 项目介绍

    • Apache POI项目的任务是创建和维护Java API,以基于Office Open XML标准(OOXML)和Microsoft的OLE 2复合文档格式(OLE2)处理各种文件格式。
    • 简而言之,您可以使用Java读写MS Excel文件。
    • 此外,您可以使用Java读写MS Word和MS PowerPoint文件。
    • Apache POI是您的Java Excel解决方案(适用于Excel 97-2008)
    • Apache POI项目是用于基于Microsoft的OLE 2复合文档格式开发文件格式的纯Java端口的主项目
    • Apache POI还是用于基于Office Open XML(ooxml)开发文件格式的纯Java端口的主项目

    简言之,Apache POI 是Java 领域中可以操作World,Excel,PPT文件的类库,可以用于生成报表,数据处理等.

    值得注意的是,Apache POI 从4.0.1版本开始,需要JDK 8 或更高版本支持。

    1.2 处理组件

    1.2.1 Excel 文件处理组件

    POIFSHSSFXSSFSXSSF
    OIFS是POI中最古老,最稳定的部分。OLE 2复合文档格式到纯Java的移植。读写*.xls文件读写*.xlsx 文件读写*.xlsx 大文件

    1.2.2 Word 文件处理组件

    HWPFXWPF
    处理*.doc文件处理*.docx 文件

    1.2.3 PPT 文件处理组件

    HSLFXSLF
    处理*.ppt文件处理*.pptx 文件

    1.2.4 文档属性组件

    • HPSF是OLE 2属性集格式到纯Java的移植。
    • 属性集通常用于存储文档的属性(标题,作者,最后修改日期等),但是它们也可以用于特定于应用程序的目的。
    • HPSF支持读取和写入属性。

    1.2.5 Visio 文件处理组件

    HDGFXDGF
    处理*.vsd 文件处理*.vsdx文件

    1.2.6 Microsoft Publisher 98(-2007)文件处理组件

    • HPBF是我们将Microsoft Publisher 98(-2007)文件格式移植到纯Java的端口。
    • 目前,它仅支持低水平读取大约一半的文件部分,并支持简单的文本提取

    1.2.7 OutLook 文件处理组件

    • SMF是Microsoft Outlook消息文件格式到纯Java的移植。目前,它仅包含MSG文件的某些文本内容以及一些附件。进一步的支持和文档进展缓慢
    • Microsoft最近在其OSP中添加了Outlook文件格式

    1.2.8 OutLook 附件文件处理组件

    • HMEF是Microsoft TNEF(传输中性编码格式)文件格式到纯Java的移植。Outlook有时会使用TNEF对消息进行编码,通常会以winmail.dat的形式出现。HMEF当前仅支持较低级别的阅读,但我们希望添加文本和附件提取。
    • 处理winmail.dat 格式文件

    1.3 Apache POI 下载

    依赖引用关系图如下所示:
    在这里插入图片描述

    1.3.1 直接下载

    点击下载Apache POI
    在这里插入图片描述

    1.3.2 Maven依赖下载

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>5.0.0</version>
    </dependency>
    

    点击查看最新版

    1.4 使用Apache POI

    Apache POI 虽然支持很多种文档格式的读写,但是我们最常用的还是对Excel 文件的读写。

    1.4.1 使用Apache POI 读写Excel 文件

    正如前面所说,Apache POI 针对读写Excel 文件提供了三个组件:

    HSSFXSSFSXSSF
    HSSF是POI项目对Excel '97(-2007)文件格式的纯Java实现XSSF是POI项目对Excel 2007 OOXML(.xlsx)文件格式的纯Java实现。SXSSF是XSSF的API兼容流扩展,可用于必须生成非常大的电子表格且堆空间有限的情况
    处理*.xls 文件处理*.xlsx 文件处理超大的*xlsx 文件
    • 生成电子表格的另一种方法是通过Cocoon序列化器(但是您仍将间接使用HSSF)。使用Cocoon,您可以通过简单地应用样式表并指定序列化程序来序列化任何XML数据源(例如,可能是在SQL中输出的ESQL页面)。
    • 3.8-beta3开始,POI提供了基于XSSF的低内存占用的SXSSF API。
    • SXSSF是XSSF的API兼容流扩展,可用于必须生成非常大的电子表格且堆空间有限的情况。SXSSF通过限制对滑动窗口内的行的访问来实现其低内存占用,而XSSF允许对文档中的所有行进行访问。不再存在于窗口中的较旧的行由于被写入磁盘而变得不可访问.
    • 在自动刷新模式下,可以指定访问窗口的大小,以在内存中保留一定数量的行。当达到该值时,创建额外的一行会导致索引最低的行从访问窗口中删除并写入磁盘。或者,可以将窗口大小设置为动态增长。可以根据需要通过显式调用flushRows(int keepRows)定期对其进行修剪。
    • 由于实现的流性质,与XSSF相比存在以下限制:
      • 在某个时间点只能访问有限数量的行。
      • 不支持Sheet.clone()
      • 不支持公式评估
    • 电子表格API功能摘要
      在这里插入图片描述

    1.4.1.1 如何创建一个WorkBook?

    创建一个*.xls 文件

    Workbook wb = new HSSFWorkbook();
    try  (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    

    创建一个*.xlsx 文件

    Workbook wb = new XSSFWorkbook();
    try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
        wb.write(fileOut);
    }
    

    1.4.1.2 如何创建一个Sheet?

    Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    Sheet sheet2 = wb.createSheet("second sheet");
    // Note that sheet name is Excel must not exceed 31 characters
    // and must not contain any of the any of the following characters:
    // 0x0000
    // 0x0003
    // colon (:)
    // backslash (\)
    // asterisk (*)
    // question mark (?)
    // forward slash (/)
    // opening square bracket ([)
    // closing square bracket (])
    // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
    // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    

    1.4.1.3 如何创建一个单元格?

    Workbook wb = new HSSFWorkbook();
    //Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue(1);
    // Or do it on one line.
    row.createCell(1).setCellValue(1.2);
    row.createCell(2).setCellValue(
         createHelper.createRichTextString("This is a string"));
    row.createCell(3).setCellValue(true);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    

    1.4.1.4 如何创建一个日期类型的单元格?

    Workbook wb = new HSSFWorkbook();
    //Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(0);
    // Create a cell and put a date value in it.  The first cell is not styled
    // as a date.
    Cell cell = row.createCell(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.
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(
        createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(1);
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);
    //you can also set date as java.util.Calendar
    cell = row.createCell(2);
    cell.setCellValue(Calendar.getInstance());
    cell.setCellStyle(cellStyle);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    

    1.4.1.5 创建多种格式的单元格

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    Row row = sheet.createRow(2);
    row.createCell(0).setCellValue(1.1);
    row.createCell(1).setCellValue(new Date());
    row.createCell(2).setCellValue(Calendar.getInstance());
    row.createCell(3).setCellValue("a string");
    row.createCell(4).setCellValue(true);
    row.createCell(5).setCellType(CellType.ERROR);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    

    1.4.1.6 Files 和InputStream

    当打开 WorkBook(.xls HSSFWorkbook或.xlsx XSSFWorkbook)时,可以从File 或InputStream加载工作簿。

    使用File对象可以减少内存消耗,而InputStream需要更多内存,因为它必须缓冲整个文件。

    如果使用WorkbookFactory,则使用其中一个非常容易:

    // Use a file
    Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
    // Use an InputStream, needs more memory
    Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));
    

    如果直接使用HSSFWorkbook或XSSFWorkbook,通常应遍历POIFSFileSystem或 OPCPackage,以完全控制生命周期(包括完成后关闭文件):

    // HSSFWorkbook, File
    POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
    ....
    fs.close();
    // HSSFWorkbook, InputStream, needs more memory
    POIFSFileSystem fs = new POIFSFileSystem(myInputStream);
    HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
    // XSSFWorkbook, File
    OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
    XSSFWorkbook wb = new XSSFWorkbook(pkg);
    ....
    pkg.close();
    // XSSFWorkbook, InputStream, needs more memory
    OPCPackage pkg = OPCPackage.open(myInputStream);
    XSSFWorkbook wb = new XSSFWorkbook(pkg);
    ....
    pkg.close();
                    
    

    1.4.1.7 展示各种对齐方式

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(2);
        row.setHeightInPoints(30);
        createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
        createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
        createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
        createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
        createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
        createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
        createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {
            wb.write(fileOut);
        }
        wb.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 halign the horizontal alignment for the cell.
     * @param valign the vertical alignment for the cell.
     */
    private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
        Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }
    

    1.4.1.8 设置边框

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(1);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(1);
    cell.setCellValue(4);
    // Style the cell with borders all around.
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(BorderStyle.MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close()
    

    1.4.1.9 遍历行和单元格

    有时,可能只想遍历工作簿中的所有工作表中的所有行或行中的所有单元格。这可以通过简单的for循环来实现。

    通过调用workbook.sheetIterator(), sheet.rowIterator()和row.cellIterator() 或隐式使用for-each循环,可以使用这些迭代器。请注意,rowIterator和cellIterator遍历已创建的行或单元格,跳过空的行和单元格。

    for (Sheet sheet : wb ) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                // Do something here
            }
        }
    }
    

    1.4.1.10 遍历单元格,控制丢失/空白的单元格

    在某些情况下,进行迭代时,您需要完全控制如何处理丢失或空白的行和单元格,并且需要确保访问每个单元格,而不仅仅是访问文件中定义的那些单元格。(CellIterator将仅返回文件中定义的单元格,这在很大程度上是具有值或样式的单元格,但取决于Excel)。

    在这种情况下,应获取一行的第一列和最后一列信息,然后调用getCell(int,MissingCellPolicy) 来获取单元格。使用 MissingCellPolicy 控制空白或空单元格的处理方式。

    // Decide which rows to process
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());
    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
       Row r = sheet.getRow(rowNum);
       if (r == null) {
          // This whole row is empty
          // Handle it as needed
          continue;
       }
       int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
       for (int cn = 0; cn < lastColumn; cn++) {
          Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
          if (c == null) {
             // The spreadsheet is empty in this cell
          } else {
             // Do something useful with the cell's contents
          }
       }
    }
    

    1.4.1.11 获取单元格内容

    要获取单元格的内容,您首先需要知道它是哪种单元格(例如,将字符串单元格作为其数字内容将获得NumberFormatException)。因此,您将需要打开单元格的类型,然后为该单元格调用适当的getter。

    在下面的代码中,我们遍历一张纸中的每个单元格,打印出该单元格的引用(例如A3),然后打印出该单元格的内容。

    // import org.apache.poi.ss.usermodel.*;
    DataFormatter formatter = new DataFormatter();
    Sheet sheet1 = wb.getSheetAt(0);
    for (Row row : sheet1) {
        for (Cell cell : row) {
            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            System.out.print(cellRef.formatAsString());
            System.out.print(" - ");
            // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
            String text = formatter.formatCellValue(cell);
            System.out.println(text);
            // Alternatively, get the value and format it yourself
            switch (cell.getCellType()) {
                case CellType.STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case CellType.NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case CellType.BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case CellType.FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                case CellType.BLANK:
                    System.out.println();
                    break;
                default:
                    System.out.println();
            }
        }
    }
    

    1.4.1.11 文字提取

    对于大多数文本提取要求,标准ExcelExtractor类应提供您所需要的全部。

    try (InputStream inp = new FileInputStream("workbook.xls")) {
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
        ExcelExtractor extractor = new ExcelExtractor(wb);
        extractor.setFormulasNotResults(true);
        extractor.setIncludeSheetNames(false);
        String text = extractor.getText();
        wb.close();
    }
    

    对于非常精美的文本提取,XLS到CSV等,请查看 /src/examples/src/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java

    1.4.1.12 填充和颜色

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(1);
    // Aqua background
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(FillPatternType.BIG_SPOTS);
    Cell cell = row.createCell(1);
    cell.setCellValue("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(FillPatternType.SOLID_FOREGROUND);
    cell = row.createCell(2);
    cell.setCellValue("X");
    cell.setCellStyle(style);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    
    

    1.4.1.13 合并单元格

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    Row row = sheet.createRow(1);
    Cell cell = row.createCell(1);
    cell.setCellValue("This is a test of merging");
    sheet.addMergedRegion(new CellRangeAddress(
            1, //first row (0-based)
            1, //last row  (0-based)
            1, //first column (0-based)
            2  //last column  (0-based)
    ));
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.1.14 设置字体

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(1);
    // Create a new font and alter it.
    Font 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.
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(1);
    cell.setCellValue("This is a test of fonts");
    cell.setCellStyle(style);
    // Write the output to a file
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    
    

    请注意,工作簿中唯一字体的最大数量限制为32767。您应该在应用程序中重新使用字体,而不是为每个单元格创建字体。

    例子:

    错误的写法

    for (int i = 0; i < 10000; i++) {
        Row row = sheet.createRow(i);
        Cell cell = row.createCell(0);
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        cell.setCellStyle(style);
    }
    

    正确的写法

    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);
    for (int i = 0; i < 10000; i++) {
        Row row = sheet.createRow(i);
        Cell cell = row.createCell(0);
        cell.setCellStyle(style);
    }
    

    1.4.1.15 自定义颜色

    HSSF(*.xls文件)

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(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(FillPatternType.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
    cell.setCellStyle(style);
    //save with the default palette
    try (OutputStream out = new FileOutputStream("default_palette.xls")) {
        wb.write(out);
    }
    //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
    try (out = new FileOutputStream("modified_palette.xls")) {
        wb.write(out);
    }
    

    XSSF(*.xlsx 文件)

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell( 0);
    cell.setCellValue("custom XSSF colors");
    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));
    style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    

    1.4.1.16 读和写WorksBooks

    try (InputStream inp = new FileInputStream("workbook.xls")) {
    //InputStream inp = new FileInputStream("workbook.xlsx");
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);
        if (cell == null)
            cell = row.createCell(3);
        cell.setCellType(CellType.STRING);
        cell.setCellValue("a test");
        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    }
    

    1.4.1.17 在单元格中使用换行符

    Workbook wb = new XSSFWorkbook();   //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(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);
    cell.setCellStyle(cs);
    //increase row height to accommodate two lines of text
    row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
    //adjust column width to fit the content
    sheet.autoSizeColumn(2);
    try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.1.18 数据格式化

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    CellStyle style;
    DataFormat format = wb.createDataFormat();
    Row row;
    Cell cell;
    int rowNum = 0;
    int 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);
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.1.19 将工作表调整为一页

    Workbook wb = 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.
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.1.20 设置打印区域

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    //sets the print area for the first sheet
    wb.setPrintArea(0, "$A$1:$C$2");
    //Alternatively:
    wb.setPrintArea(
            0, //sheet index
            0, //start column
            1, //end column
            0, //start row
            0  //end row
    );
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.1.21 设置页脚页数

    Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    Footer footer = sheet.getFooter();
    footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );
    // Create various cells and rows for spreadsheet.
    try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
        wb.write(fileOut);
    }
    wb.close();
    

    1.4.2 Apache POI 提取Word中的文件

    Apache POI 针对Word 的处理有两套API,分别如下:

    HWPFXWPF
    处理*.doc文件处理*.docx 文件

    如果是Maven项目添加如下依赖:

            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.2</version>
            </dependency>
    
            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>
            
            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.1.2</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>4.1.2</version>
            </dependency>
    

    如果是Gradle 项目,添加如下依赖:

        // Apache POI
        // https://mvnrepository.com/artifact/org.apache.poi/poi
        implementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'
        // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
        implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
        // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas
        implementation group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'
        // doc 需要用
        implementation group: 'org.apache.poi', name: 'poi-scratchpad', version: '4.1.2'
    

    1.4.2.1 读取Excel 数据并写入到Word示例

    读取Excel 数据并写入到Word示例

    1.4.2.2 读取Excel 数据并从Word中提取图片示例

    接下来我们来演示如何从word中提取图片。

    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hwpf.HWPFDocument;
    import org.apache.poi.hwpf.model.PicturesTable;
    import org.apache.poi.hwpf.usermodel.Picture;
    import org.apache.poi.xwpf.usermodel.XWPFDocument;
    import org.apache.poi.xwpf.usermodel.XWPFParagraph;
    import org.apache.poi.xwpf.usermodel.XWPFPictureData;
    import org.apache.poi.xwpf.usermodel.XWPFRun;
    
    import java.io.*;
    import java.util.List;
    
    /**
     * @author qing-feng.zhao
     */
    @Slf4j
    public class SmartPoiWordUtils {
        /**
         * 静态工具类应该禁用构造方法
         */
        private SmartPoiWordUtils(){}
        /**
         * 写入数据到word
         * @param fileName
         * @param dataListArg
         * @throws IOException
         */
        public static void writeDataToWord(String fileName, List<String> dataListArg) throws IOException {
            try(FileOutputStream out = new FileOutputStream(fileName);){
                //创建一个文档
                XWPFDocument xwpfDocument=new XWPFDocument();
                //创建一个段落
                XWPFParagraph xwpfParagraph;
                //创建一片区域
                XWPFRun run;
                for (String lineData:dataListArg
                ) {
                    xwpfParagraph= xwpfDocument.createParagraph();
                    run=xwpfParagraph.createRun();
                    run.setText(lineData);
                }
                xwpfDocument.write(out);
                xwpfDocument.close();
            }
        }
    
        public synchronized static void fetchPictureFromWord(File worldFilePath,String targetPictureFolder,String pictureFileName) throws IOException {
                String fileType=SmartFileUtils.getNormalFileType(worldFilePath.getName());
                Integer numberId=1;
                switch (fileType.toLowerCase()){
                    case "doc":
                        try(HWPFDocument hwpfDocument=new HWPFDocument(new FileInputStream(worldFilePath.getAbsolutePath()))
                        ){
                            PicturesTable picturesTable=hwpfDocument.getPicturesTable();
                            List<Picture> pictures = picturesTable.getAllPictures();
                            for (Picture picture : pictures) {
                                // 输出图片到磁盘
                                String pictureFiePath=targetPictureFolder + File.separator + pictureFileName+numberId+"."+picture.suggestFileExtension();
                                try(OutputStream out = new FileOutputStream(pictureFiePath)){
                                    picture.writeImageContent(out);
                                }
                                numberId++;
                            }
                        }
                        break;
                    case "docx":
                        try(XWPFDocument xwpfDocument=new XWPFDocument(new FileInputStream(worldFilePath.getAbsolutePath()))){
                            //得到word的数据流
                            List<XWPFPictureData> xwpfPictureDataList=xwpfDocument.getAllPictures();
                            if(null!=xwpfPictureDataList&&xwpfPictureDataList.size()>0){
                                for (XWPFPictureData item:xwpfPictureDataList
                                ) {
                                    byte[] bytes = item.getData();
                                    String pictureFiePath=targetPictureFolder + File.separator + pictureFileName+numberId+"."+item.suggestFileExtension();
                                    File file=new File(pictureFiePath);
                                    if(file.exists()){
                                        file.delete();
                                    }
                                    try(FileOutputStream fos = new FileOutputStream(pictureFiePath)){
                                        fos.write(bytes);
                                    }
                                    numberId++;
                                }
                            }
                        } catch (IOException e) {
                            log.error("错误的文件:{}",e);
                            log.error("出错文件路径",worldFilePath.getAbsolutePath());
                        }
                        break;
                    default:
                        log.error("{}无效的文件格式:{}",worldFilePath,fileType);
                        break;
                }
            }
    }
    

    测试调用类如下:

         public static void main(String[] args) {
            File currentWordFile=new File("/Users/zhaoqingfeng/desktop/test.docx");
            String targetPicturePath=currentWordFile.getParentFile().getPath();
            String wordFileNameWithOutFileType=SmartFileUtils.getNormalFileNameWithOutFileType(currentWordFile.getName());
            try {
                SmartPoiWordUtils.fetchPictureFromWord(currentWordFile,targetPicturePath,wordFileNameWithOutFileType);
            } catch (IOException e) {
                log.error("文件I/O异常",e);
                log.error("处理文件名称:{}",currentWordFile.getName());
                log.error("处理文件路径:{}",currentWordFile.getAbsolutePath());
            }
        }
    

    如果word中插入一张图片,上面代码执行完成后,在/Users/zhaoqingfeng/Desktop/ 路径下就会生成一张图片。

    本篇完~

    展开全文
  • 下面小编就为大家带来一篇基于apache poi根据模板导出excel的实现方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • apachepoi-源码

    2021-03-16 17:38:18
    apachepoi
  • Apache poi 操作 excel 文件压缩包,亲测可用

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 48,636
精华内容 19,454
关键字:

apachepoi

友情链接: look.rar