java中导出数据 springmvc框架根据中文名称导出到excel中
//控制类
package excel;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.CollectionUtils;
import bean.PartnerBusinessOrderResponse;
@Controller
@RequestMapping("/excel")
public class ExcelTest {
@RequestMapping(value = "/export")
public void exportExcel(HttpServletRequest httpServletRequest, HttpServletResponse response) {
System.out.println("进入导出》》》》》》》》》》》》》》》》》》》");
try {
//获取数据
List<PartnerBusinessOrderResponse> list = new ArrayList<PartnerBusinessOrderResponse>();
PartnerBusinessOrderResponse bean=new PartnerBusinessOrderResponse();
bean.setBusinessName("自己");
bean.setBusinessCode("中国");
list.add(bean);
if (CollectionUtils.isEmpty(list)) {
list.add(new PartnerBusinessOrderResponse());
}
//attachment指定独立文件下载 不指定则回浏览器中直接打开
String fileName = "业务订单导出" + new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSS").format(new Date()) + ".xlsx";
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
//导出excel
System.out.println("list==========="+list);
EasyExcel.write(response.getOutputStream(), PartnerBusinessOrderResponse.class).sheet("合伙人业务订单").doWrite(list);
System.out.println("业务订单导出end");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
response.flushBuffer();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
//实体类类
package bean;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
public class PartnerBusinessOrderResponse {
/**
* 主键id
*/
@ColumnWidth(15)
@ExcelProperty(value = "订单号",index=0)
private Long id;
/**
* 订单号
*/
@ColumnWidth(15)
@ExcelProperty(value = "订单号",index=1)
private String orderNo;
/**
* 业务类型名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "业务类型",index=2)
private String businessTypeName;
/**
* 业务编码
*/
@ColumnWidth(15)
@ExcelProperty(value = "业务编码",index=3)
private String businessCode;
/**
* 业务名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "业务名称",index=4)
private String businessName;
/**
* 办理号码
*/
@ColumnWidth(15)
@ExcelProperty(value = "办理号码",index=5)
private String handleMobile;
/**
* 办理时间
*/
@ColumnWidth(15)
@ExcelIgnore
private Long handleTime;
/**
*办理时间---格式化
* @return
*/
@ColumnWidth(15)
@ExcelProperty(value = "办理时间",index=6)
private String handleTimeString;
/**
* 办理渠道：1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他
*/
@ColumnWidth(15)
@ExcelIgnore
private String handleChannel;
/**
* 办理渠道：1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他
*/
@ColumnWidth(15)
@ExcelProperty(value = "办理渠道",index=7)
private String handleChannelString;
/**
* 平台名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "所属平台",index=8)
private String platformName;
/**
* 归属工号
*/
@ColumnWidth(15)
@ExcelProperty(value = "归属工号",index=9)
private String belongEmployeeNum;
/**
* 四级机构名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "地市",index=10)
private String fourOrgName;
/**
* 五级机构名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "区县",index=11)
private String fiveOrgName;
/**
*直属高级合伙人姓名
*/
@ColumnWidth(15)
@ExcelProperty(value = "归属合伙人姓名",index=12)
private String partnerLeaderName;
/**
*直属高级合伙人手机号
*/
@ColumnWidth(15)
@ExcelProperty(value = "归属合伙人手机号",index=13)
private String partnerLeaderMobile;
/**
* 合伙人等级名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "合伙人等级",index=14)
private String partnerLevelName;
/**
*经办人
*/
@ColumnWidth(15)
@ExcelProperty(value = "经办人姓名",index=15)
private String partnerName;
/**
*经办人手机号
*/
@ColumnWidth(15)
@ExcelProperty(value = "经办人手机号",index=16)
private String partnerMobile;
/**
* 办理状态（1：成功；2：失败）
*/
@ColumnWidth(15)
@ExcelIgnore
private Integer handleStatus;
/**
* 办理状态（1：成功；2：失败）---中文
*/
@ColumnWidth(15)
@ExcelProperty(value = "是否办理成功",index=17)
private String handleStatusString;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getBusinessTypeName() {
return businessTypeName;
}
public void setBusinessTypeName(String businessTypeName) {
this.businessTypeName = businessTypeName;
}
public String getBusinessCode() {
return businessCode;
}
public void setBusinessCode(String businessCode) {
this.businessCode = businessCode;
}
public String getBusinessName() {
return businessName;
}
public void setBusinessName(String businessName) {
this.businessName = businessName;
}
public String getHandleMobile() {
return handleMobile;
}
public void setHandleMobile(String handleMobile) {
this.handleMobile = handleMobile;
}
public Long getHandleTime() {
return handleTime;
}
public void setHandleTime(Long handleTime) {
this.handleTime = handleTime;
}
public String getHandleTimeString() {
return handleTimeString;
}
public void setHandleTimeString(String handleTimeString) {
this.handleTimeString = handleTimeString;
}
public String getHandleChannel() {
return handleChannel;
}
public void setHandleChannel(String handleChannel) {
this.handleChannel = handleChannel;
}
public String getHandleChannelString() {
return handleChannelString;
}
public void setHandleChannelString(String handleChannelString) {
this.handleChannelString = handleChannelString;
}
public String getPlatformName() {
return platformName;
}
public void setPlatformName(String platformName) {
this.platformName = platformName;
}
public String getBelongEmployeeNum() {
return belongEmployeeNum;
}
public void setBelongEmployeeNum(String belongEmployeeNum) {
this.belongEmployeeNum = belongEmployeeNum;
}
public String getFourOrgName() {
return fourOrgName;
}
public void setFourOrgName(String fourOrgName) {
this.fourOrgName = fourOrgName;
}
public String getFiveOrgName() {
return fiveOrgName;
}
public void setFiveOrgName(String fiveOrgName) {
this.fiveOrgName = fiveOrgName;
}
public String getPartnerLeaderName() {
return partnerLeaderName;
}
public void setPartnerLeaderName(String partnerLeaderName) {
this.partnerLeaderName = partnerLeaderName;
}
public String getPartnerLeaderMobile() {
return partnerLeaderMobile;
}
public void setPartnerLeaderMobile(String partnerLeaderMobile) {
this.partnerLeaderMobile = partnerLeaderMobile;
}
public String getPartnerLevelName() {
return partnerLevelName;
}
public void setPartnerLevelName(String partnerLevelName) {
this.partnerLevelName = partnerLevelName;
}
public String getPartnerName() {
return partnerName;
}
public void setPartnerName(String partnerName) {
this.partnerName = partnerName;
}
public String getPartnerMobile() {
return partnerMobile;
}
public void setPartnerMobile(String partnerMobile) {
this.partnerMobile = partnerMobile;
}
public Integer getHandleStatus() {
return handleStatus;
}
public void setHandleStatus(Integer handleStatus) {
this.handleStatus = handleStatus;
}
public String getHandleStatusString() {
return handleStatusString;
}
public void setHandleStatusString(String handleStatusString) {
this.handleStatusString = handleStatusString;
}
@Override
public String toString() {
return "PartnerBusinessOrderResponse [id=" + id + ", orderNo=" + orderNo
+ ", businessTypeName=" + businessTypeName + ", businessCode=" + businessCode + ", businessName="
+ businessName + ", handleMobile=" + handleMobile + ", handleTime=" + handleTime + ", handleTimeString="
+ handleTimeString + ", handleChannel=" + handleChannel + ", handleChannelString=" + handleChannelString
+ ", platformName=" + platformName + ", belongEmployeeNum=" + belongEmployeeNum + ", fourOrgName="
+ fourOrgName + ", fiveOrgName=" + fiveOrgName + ", partnerLeaderName=" + partnerLeaderName
+ ", partnerLeaderMobile=" + partnerLeaderMobile + ", partnerLevelName=" + partnerLevelName
+ ", partnerName=" + partnerName + ", partnerMobile=" + partnerMobile + ", handleStatus=" + handleStatus
+ ", handleStatusString=" + handleStatusString + "]";
}
}
//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">
<display-name>writeToExcel</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>SpringMVC</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springMVC.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>SpringMVC</servlet-name>
<url-pattern>*.do</url-pattern>
<url-pattern>/</url-pattern>
</servlet-mapping>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:mybatis-confg.xml</param-value>
</context-param>
</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:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task-3.2.xsd"
xmlns:task="http://www.springframework.org/schema/task">
<!--1、开启注解模式-->
<mvc:annotation-driven/>
<!--4、扫描所有的控制器类-->
<context:component-scan base-package="excel"/>
<context:component-scan base-package="bean"/>
<!--配置WEB-INF下面的资源可以访问-->
<mvc:resources mapping="/css/**" location="/easyui/css/"/>
<mvc:resources mapping="/js/**" location="/easyui/js/"/>
<mvc:resources mapping="/qrcode/**" location="/WEB-INF/classes/file/qrcode/" />
<!--3、视图解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/> -->
<property name="prefix" value="/WEB-INF/me"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
//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</groupId>
<artifactId>writeToExcel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>org.apache.directory.studio</groupId>
<artifactId>org.apache.commons.codec</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.54</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<!-- web -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.0.6.RELEASE</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
</dependencies>
</project>
//请求路径 localhost:8080/writeToExcel/excel/export.do
