报表技术之Excel格式报表生成（POI）

最新推荐文章于 2021-12-16 09:32:04 发布

chiyiji9177

最新推荐文章于 2021-12-16 09:32:04 发布

阅读量307

点赞数

文章标签：测试 java web.xml

原文链接：https://my.oschina.net/wuaiting/blog/3060790

版权

报表技术之Excel格式报表生成（POI）

知识点简介

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。下面我们来看一下Apache POI 中提供的几大部分的作用：

HSSF －提供读写Microsoft Excel XLS格式档案的功能。　　

XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。　　

HWPF －提供读写Microsoft Word DOC格式档案的功能。　　

HSLF －提供读写Microsoft PowerPoint格式档案的功能。　　

HDGF －提供读Microsoft Visio格式档案的功能。　　

HPBF －提供读Microsoft Publisher格式档案的功能。　　

HSMF －提供读Microsoft Outlook格式档案的功能。

相关图解-Excel

入门demo

1：创建web项目

2：添加依赖 pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.itheima.demo</groupId>
  <artifactId>poi-demo</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <properties>
		<spring.version>4.2.4.RELEASE</spring.version>
	</properties>
	
	<dependencies>
		<!-- Spring -->
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-context</artifactId>
				<version>${spring.version}</version>
			</dependency>
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-beans</artifactId>
				<version>${spring.version}</version>
			</dependency>
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-webmvc</artifactId>
				<version>${spring.version}</version>
			</dependency>
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-aspects</artifactId>
				<version>${spring.version}</version>
			</dependency>
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-context-support</artifactId>
				<version>${spring.version}</version>
			</dependency>
			<dependency>
				<groupId>org.springframework</groupId>
				<artifactId>spring-test</artifactId>
				<version>${spring.version}</version>
			</dependency>
				<dependency>
				<groupId>junit</groupId>
				<artifactId>junit</artifactId>
				<version>4.9</version>
			</dependency>
			<dependency>
				<groupId>com.alibaba</groupId>
				<artifactId>fastjson</artifactId>
				<version>1.2.28</version>
			</dependency>
			<dependency>
				<groupId>javassist</groupId>
				<artifactId>javassist</artifactId>
				<version>3.11.0.GA</version>
			</dependency>
			<dependency>
				<groupId>javax.servlet</groupId>
				<artifactId>servlet-api</artifactId>
				<version>2.5</version>
				<scope>provided</scope>
			</dependency>
			
			<!-- poi依赖  -->
			    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>

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

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

    <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
    <dependency>
        <groupId>dom4j</groupId>
        <artifactId>dom4j</artifactId>
        <version>1.6.1</version>
    </dependency>
			
	</dependencies>
	
	
	
	<build>
		
		<plugins>			
			<!-- java编译插件 -->
			<plugin>
			  <groupId>org.apache.maven.plugins</groupId>
			  <artifactId>maven-compiler-plugin</artifactId>
			  <configuration>
			    <source>1.8</source>
			    <target>1.8</target>
			    <encoding>UTF-8</encoding>
			  </configuration>
			</plugin>

			
			<plugin>
				<groupId>org.apache.tomcat.maven</groupId>
				<artifactId>tomcat7-maven-plugin</artifactId>
				<version>2.2</version>
				<configuration>
					<!-- 指定端口 -->
					<port>8080</port>
					<!-- 请求路径 -->
					<path>/</path>
				</configuration>
	  	  </plugin>

		</plugins>
	
	</build>
</project>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	version="2.5">
   <!-- 解决post乱码 -->
	<filter>
		<filter-name>CharacterEncodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>utf-8</param-value>
		</init-param>
		<init-param>  
            <param-name>forceEncoding</param-name>  
            <param-value>true</param-value>  
        </init-param>  
	</filter>
	<filter-mapping>
		<filter-name>CharacterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>	
	
  <servlet>
  	<servlet-name>springmvc</servlet-name>
  	<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  	<!-- 指定加载的配置文件 ，通过参数contextConfigLocation加载-->
  	<init-param>
  		<param-name>contextConfigLocation</param-name>
  		<param-value>classpath:spring/springmvc.xml</param-value>
  	</init-param>
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>springmvc</servlet-name>
  	<url-pattern>*.do</url-pattern>
  </servlet-mapping>
  
</web-app>

springmvc.xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:dubbo="http://code.alibabatech.com/schema/dubbo"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://code.alibabatech.com/schema/dubbo http://code.alibabatech.com/schema/dubbo/dubbo.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">


	<mvc:annotation-driven>
	  <mvc:message-converters register-defaults="true">
	    <bean class="com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter">
	      <property name="supportedMediaTypes" value="application/json"/>
	      <property name="features">
	        <array>
	          <value>WriteMapNullValue</value>
	          <value>WriteDateUseDateFormat</value>
	        </array>
	      </property>
	    </bean>
	  </mvc:message-converters>
	</mvc:annotation-driven>

	<context:component-scan base-package="com.cdq.poi"></context:component-scan>
</beans>

这里创建一个User实体类作为数据

public class User {
	
	private Integer id;
	private String username;
	private String nickname;
	private String address;
	private String sex;
	private String phone;
	
	public User() {
		super();
	}
	
	public User(Integer id, String username, String nickname, String address, String sex, String phone) {
		super();
		this.id = id;
		this.username = username;
		this.nickname = nickname;
		this.address = address;
		this.sex = sex;
		this.phone = phone;
	}

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getNickname() {
		return nickname;
	}
	public void setNickname(String nickname) {
		this.nickname = nickname;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", nickname=" + nickname + ", address=" + address
				+ ", sex=" + sex + ", phone=" + phone + "]";
	}
}

这里将数据获取也写在了controller中

package com.cdq.poi;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

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;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/export")
public class ExportController {
	/**
	 * Excel 2007版本前

		Excel 的工作簿对应POI的HSSFWorkbook对象；
		Excel 的工作表对应POI的HSSFSheet对象；
		Excel 的行对应POI的HSSFRow对象；
		Excel 的单元格对应POI的HSSFCell对象。
		
		
		
		Excel 2007版本及07以后版本
		
		Excel 的工作簿对应POI的XSSFWorkbook对象；
		Excel 的工作表对应POI的XSSFSheet对象；
		Excel 的行对应POI的XSSFRow对象；
		Excel 的单元格对应POI的XSSFCell对象。
	 * @param response
	 * @throws IOException 
	 */
	@RequestMapping("/order")
	public void exportOrderExcel(HttpServletResponse response) throws IOException{
		
		//1:创建一个工作簿(即excel文档)
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		//2:创建工作表 sheel页 
		XSSFSheet xssfSheet = xssfWorkbook.createSheet("订单报表"); //指定sheet页名称为:订单报表
		
		// 准备表头数据
		String [] header = {"用户ID","用户姓名","用户昵称","地址","性别","电话"};
		// 准备表体数据
		List<User> bodyList = createBodyData();
		
		//3:创建表头
		//3.1创建表头行
		XSSFRow headerRow = xssfSheet.createRow(0);// 创建第一行
		//3.2创建表头所有列
		for(int i = 0;i<header.length;i++){
			//创建每一列
			XSSFCell xssfCell = headerRow.createCell(i);
			//为每个单元格填充值
			xssfCell.setCellValue(header[i]);
		}
		
		//4:创建数据体行 创建没行对应的列 并为每列赋值
		for(int i=0;i<bodyList.size();i++){
			User user = bodyList.get(i);
			XSSFRow row = xssfSheet.createRow(i+1); //创建行-----注意这里要从第二行开始创建 因为刚开始表头已经占了了一行
			
			XSSFCell idCell = row.createCell(0); //创建当前行第一列的单元格
			idCell.setCellValue(user.getId());
			
			XSSFCell userNameCell = row.createCell(1);//创建当前行第二列的单元格
			userNameCell.setCellValue(user.getUsername());
			
			XSSFCell nickNameCell = row.createCell(2);//创建当前行第三列的单元格
			nickNameCell.setCellValue(user.getNickname());
			
			XSSFCell addressCell = row.createCell(3); //创建当前行第四列的单元格
			addressCell.setCellValue(user.getAddress());
			
			XSSFCell sexCell = row.createCell(4);//创建当前行第五列的单元格
			sexCell.setCellValue(user.getSex());
			
			XSSFCell phoneCell = row.createCell(5);//创建当前行第六列的单元格
			phoneCell.setCellValue(user.getPhone());
			
		}
		
		
		//使用字节流输出
		//定义文件名
		String fileName = "2019-03-01至2019-04-01已发货订单报表.xlsx";
		//设置文件下载的响应头
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename="+new String((fileName).getBytes(), "iso-8859-1"));
		//使用工作簿对象进行输出到字节流  这里的字节流是response对象提供
		xssfWorkbook.write(response.getOutputStream());
		//关闭
		xssfWorkbook.close();
		
	}

	private List<User> createBodyData() {
		List<User> userList = new ArrayList<>();
		for(int i=0;i<20;i++){
			userList.add(new User(i+1, "李四"+i, "小李子", "西永微电园", "男", "123456"));
		}
		return userList;
	}

}

这里建了一个index.html点击导出表格，也可以直接请求http://localhost:8080/export/order.do 下载表格

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<a href="/export/order.do">导出</a>
</body>
</html>

导出表格如图

转载于:https://my.oschina.net/wuaiting/blog/3060790

chiyiji9177

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
报表技术之Excel格式报表生成（POI）

报表技术之Excel格式报表生成（POI）知识点简介Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。Apache POI 是创建和维护操作各种符合Office ...
复制链接

扫一扫