Java中使用POI导出Excel 之项目实战详细教程

最新推荐文章于 2024-03-04 13:58:31 发布

Hello_World_QWP

最新推荐文章于 2024-03-04 13:58:31 发布

阅读量2.7k

点赞数 1

分类专栏： Apache_POI 文章标签： excel poi java Spring Hibernate

本文链接：https://blog.csdn.net/Hello_World_QWP/article/details/78404571

版权

Apache_POI 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

《 Java中使用POI导出Excel 》

@本教程的所有内容版权归本人所有，与企业/公司无关

前言：

在项目开发的过程中，因为需要将系统中的业务数据进行导出，根据开发经验，我个人觉得在目前看来，Apache 下的 POI 还是比较流行的，所以就选定了 POI 来实现这个导出功能，在导出功能编写之初，因为对 POI 的 API 不是很熟，花了大把时间来进行查阅相关的 API；所以，在本教程中，你会看到 POI 导出工具类中会有很多的注释，这样你就可以自己更具注释改造样式咯。

本教程中涉及到的技术：

Java + POI + Spring + SpringMVC + Servlet + Hibernate + Jsp + JavaScript + JQuery LigerUI；

遵循 MVC 设计模式；

Jar 包：

以下为 JavaWeb 项目中使用本实例所涉及的 Jar 包，下载时可能需要点积分，如果没有积分可以给留言，我发给你：

Servlet ：

http://download.csdn.net/download/hello_world_qwp/10037520

POI ：

http://download.csdn.net/download/hello_world_qwp/10037550

如果你只需要查看 POI 导出工具类，可以根据下图直接进入：

快捷进入 POI 实现导出工具类

JavaBean ：

package com.etc.third.tlq.bean;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.hibernate.annotations.GenericGenerator;

import com.etccity.core.crud.bean.BaseEntity;

/**
 * 业务 Bean
 * 
 * 
 * @ClassName : ExportExcelUtil
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年11月1日 上午10:29:06
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 */
@SuppressWarnings("serial")
@Entity
@Table(name = "tlq_car_out") 
public class TlqCarOut implements BaseEntity {

	private Integer seqNo;

	private Integer region;

	private String carOwner;

	private String carGroup;

	private Integer carTypeNo;

	private String carType;

	private String inCarNo;

	private String inDeviceName;

	private Integer inFlag;

	private Date inTime;

	private String inPic;

	private String inOperatorName;

	private Date inOperationTime;

	private String inOperationTypeName;

	private String outCarNo;

	private String outDeviceName;

	private Integer outFlag;

	private Date outTime;

	private String outPic;

	private String outOperatorName;

	private Date outOperationTime;

	private String outOperationTypeName;

	private Double payMoney;

	private Double agioMoney;

	private Double chargeMoney;

	private String inMemo;

	private String outMemo;

	private Integer abort;

	private String inPicMin;

	private String outPicMin;

	private String outCarNocolor;

	private String inCarNocolor;

	private String id;

	private String status;

	private String parkName;

	private Double epMoney;

	private String inPicId;

	private String inPicMinId;

	private String outPicId;

	private String outPicMinId;

	public void setSeqNo(Integer value) {
		this.seqNo = value;
	}

	public void setRegion(Integer value) {
		this.region = value;
	}

	public void setCarOwner(String value) {
		this.carOwner = value;
	}

	public void setCarGroup(String value) {
		this.carGroup = value;
	}

	public void setCarTypeNo(Integer value) {
		this.carTypeNo = value;
	}

	public void setCarType(String value) {
		this.carType = value;
	}

	public void setInCarNo(String value) {
		this.inCarNo = value;
	}

	public void setInDeviceName(String value) {
		this.inDeviceName = value;
	}

	public void setInFlag(Integer value) {
		this.inFlag = value;
	}

	public void setInTime(Date value) {
		this.inTime = value;
	}

	public void setInPic(String value) {
		this.inPic = value;
	}

	public void setInOperatorName(String value) {
		this.inOperatorName = value;
	}

	public void setInOperationTime(Date value) {
		this.inOperationTime = value;
	}

	public void setInOperationTypeName(String value) {
		this.inOperationTypeName = value;
	}

	public void setOutCarNo(String value) {
		this.outCarNo = value;
	}

	public void setOutDeviceName(String value) {
		this.outDeviceName = value;
	}

	public void setOutFlag(Integer value) {
		this.outFlag = value;
	}

	public void setOutTime(Date value) {
		this.outTime = value;
	}

	public void setOutPic(String value) {
		this.outPic = value;
	}

	public void setOutOperatorName(String value) {
		this.outOperatorName = value;
	}

	public void setOutOperationTime(Date value) {
		this.outOperationTime = value;
	}

	public void setOutOperationTypeName(String value) {
		this.outOperationTypeName = value;
	}

	public void setPayMoney(Double value) {
		this.payMoney = value;
	}

	public void setAgioMoney(Double value) {
		this.agioMoney = value;
	}

	public void setChargeMoney(Double value) {
		this.chargeMoney = value;
	}

	public void setInMemo(String value) {
		this.inMemo = value;
	}

	public void setOutMemo(String value) {
		this.outMemo = value;
	}

	public void setAbort(Integer value) {
		this.abort = value;
	}

	public void setInPicMin(String value) {
		this.inPicMin = value;
	}

	public void setOutPicMin(String value) {
		this.outPicMin = value;
	}

	public void setOutCarNocolor(String value) {
		this.outCarNocolor = value;
	}

	public void setInCarNocolor(String value) {
		this.inCarNocolor = value;
	}

	public void setId(String value) {
		this.id = value;
	}

	public void setStatus(String value) {
		this.status = value;
	}

	@Column(name = "SeqNO", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getSeqNo() {
		return this.seqNo;
	}

	@Column(name = "Region", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getRegion() {
		return this.region;
	}

	@Column(name = "CarOwner", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getCarOwner() {
		return this.carOwner;
	}

	@Column(name = "CarGroup", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getCarGroup() {
		return this.carGroup;
	}

	@Column(name = "CarTypeNO", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getCarTypeNo() {
		return this.carTypeNo;
	}

	@Column(name = "CarType", unique = false, nullable = true, insertable = true, updatable = true, length = 20)
	public String getCarType() {
		return this.carType;
	}

	@Column(name = "InCarNO", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getInCarNo() {
		return this.inCarNo;
	}

	@Column(name = "InDeviceName", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getInDeviceName() {
		return this.inDeviceName;
	}

	@Column(name = "InFlag", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getInFlag() {
		return this.inFlag;
	}

	@Column(name = "InTime", unique = false, nullable = true, insertable = true, updatable = true, length = 0)
	public Date getInTime() {
		return this.inTime;
	}

	@Column(name = "InPic", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getInPic() {
		return this.inPic;
	}

	@Column(name = "InOperatorName", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getInOperatorName() {
		return this.inOperatorName;
	}

	@Column(name = "InOperationTime", unique = false, nullable = true, insertable = true, updatable = true, length = 0)
	public Date getInOperationTime() {
		return this.inOperationTime;
	}

	@Column(name = "InOperationTypeName", unique = false, nullable = true, insertable = true, updatable = true, length = 200)
	public String getInOperationTypeName() {
		return this.inOperationTypeName;
	}

	@Column(name = "OutCarNO", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getOutCarNo() {
		return this.outCarNo;
	}

	@Column(name = "OutDeviceName", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getOutDeviceName() {
		return this.outDeviceName;
	}

	@Column(name = "OutFlag", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getOutFlag() {
		return this.outFlag;
	}

	@Column(name = "OutTime", unique = false, nullable = true, insertable = true, updatable = true, length = 0)
	public Date getOutTime() {
		return this.outTime;
	}

	@Column(name = "OutPic", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getOutPic() {
		return this.outPic;
	}

	@Column(name = "OutOperatorName", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getOutOperatorName() {
		return this.outOperatorName;
	}

	@Column(name = "OutOperationTime", unique = false, nullable = true, insertable = true, updatable = true, length = 0)
	public Date getOutOperationTime() {
		return this.outOperationTime;
	}

	@Column(name = "OutOperationTypeName", unique = false, nullable = true, insertable = true, updatable = true, length = 200)
	public String getOutOperationTypeName() {
		return this.outOperationTypeName;
	}

	@Column(name = "PayMoney", unique = false, nullable = true, insertable = true, updatable = true, length = 20)
	public Double getPayMoney() {
		return this.payMoney;
	}

	@Column(name = "AgioMoney", unique = false, nullable = true, insertable = true, updatable = true, length = 20)
	public Double getAgioMoney() {
		return this.agioMoney;
	}

	@Column(name = "ChargeMoney", unique = false, nullable = true, insertable = true, updatable = true, length = 20)
	public Double getChargeMoney() {
		return this.chargeMoney;
	}

	@Column(name = "InMemo", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getInMemo() {
		return this.inMemo;
	}

	@Column(name = "OutMemo", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getOutMemo() {
		return this.outMemo;
	}

	@Column(name = "Abort", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public Integer getAbort() {
		return this.abort;
	}

	@Column(name = "InPicMin", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getInPicMin() {
		return this.inPicMin;
	}

	@Column(name = "OutPicMin", unique = false, nullable = true, insertable = true, updatable = true, length = 65535)
	public String getOutPicMin() {
		return this.outPicMin;
	}

	@Column(name = "OutCarNOColor", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getOutCarNocolor() {
		return this.outCarNocolor;
	}

	@Column(name = "InCarNOColor", unique = false, nullable = true, insertable = true, updatable = true, length = 50)
	public String getInCarNocolor() {
		return this.inCarNocolor;
	}

	@Id
	@GeneratedValue(generator = "system-uuid")
	@GenericGenerator(name = "system-uuid", strategy = "uuid")
	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = true, length = 50)
	public String getId() {
		return this.id;
	}

	@Column(name = "status", unique = false, nullable = true, insertable = true, updatable = true, length = 10)
	public String getStatus() {
		return this.status;
	}

	@Transient
	public String getParkName() {
		return parkName;
	}

	public void setParkName(String parkName) {
		this.parkName = parkName;
	}

	@Column(name = "EP_MONEY")
	public Double getEpMoney() {
		return epMoney;
	}

	public void setEpMoney(Double epMoney) {
		this.epMoney = epMoney;
	}

	@Column(name = "INPIC_ID")
	public String getInPicId() {
		return inPicId;
	}

	public void setInPicId(String inPicId) {
		this.inPicId = inPicId;
	}

	@Column(name = "INPICMIN_ID")
	public String getInPicMinId() {
		return inPicMinId;
	}

	public void setInPicMinId(String inPicMinId) {
		this.inPicMinId = inPicMinId;
	}

	@Column(name = "OUTPIC_ID")
	public String getOutPicId() {
		return outPicId;
	}

	public void setOutPicId(String outPicId) {
		this.outPicId = outPicId;
	}

	@Column(name = "OUTPICMIN_ID")
	public String getOutPicMinId() {
		return outPicMinId;
	}

	public void setOutPicMinId(String outPicMinId) {
		this.outPicMinId = outPicMinId;
	}

	public ExportExcelUtil(Integer seqNo, Integer region, String carOwner, String carGroup, Integer carTypeNo,
			String carType, String inCarNo, String inDeviceName, Integer inFlag, Date inTime, String inPic,
			String inOperatorName, Date inOperationTime, String inOperationTypeName, String outCarNo,
			String outDeviceName, Integer outFlag, Date outTime, String outPic, String outOperatorName,
			Date outOperationTime, String outOperationTypeName, Double payMoney, Double agioMoney, Double chargeMoney,
			String inMemo, String outMemo, Integer abort, String inPicMin, String outPicMin, String outCarNocolor,
			String inCarNocolor, String id, String status, String parkName, Double epMoney, String inPicId,
			String inPicMinId, String outPicId, String outPicMinId) {
		super();
		this.seqNo = seqNo;
		this.region = region;
		this.carOwner = carOwner;
		this.carGroup = carGroup;
		this.carTypeNo = carTypeNo;
		this.carType = carType;
		this.inCarNo = inCarNo;
		this.inDeviceName = inDeviceName;
		this.inFlag = inFlag;
		this.inTime = inTime;
		this.inPic = inPic;
		this.inOperatorName = inOperatorName;
		this.inOperationTime = inOperationTime;
		this.inOperationTypeName = inOperationTypeName;
		this.outCarNo = outCarNo;
		this.outDeviceName = outDeviceName;
		this.outFlag = outFlag;
		this.outTime = outTime;
		this.outPic = outPic;
		this.outOperatorName = outOperatorName;
		this.outOperationTime = outOperationTime;
		this.outOperationTypeName = outOperationTypeName;
		this.payMoney = payMoney;
		this.agioMoney = agioMoney;
		this.chargeMoney = chargeMoney;
		this.inMemo = inMemo;
		this.outMemo = outMemo;
		this.abort = abort;
		this.inPicMin = inPicMin;
		this.outPicMin = outPicMin;
		this.outCarNocolor = outCarNocolor;
		this.inCarNocolor = inCarNocolor;
		this.id = id;
		this.status = status;
		this.parkName = parkName;
		this.epMoney = epMoney;
		this.inPicId = inPicId;
		this.inPicMinId = inPicMinId;
		this.outPicId = outPicId;
		this.outPicMinId = outPicMinId;
	}

	public ExportExcelUtil() {
		super();
	}

	@Override
	public String toString() {
		return "ExportExcelUtil [seqNo=" + seqNo + ", region=" + region + ", carOwner=" + carOwner + ", carGroup="
				+ carGroup + ", carTypeNo=" + carTypeNo + ", carType=" + carType + ", inCarNo=" + inCarNo
				+ ", inDeviceName=" + inDeviceName + ", inFlag=" + inFlag + ", inTime=" + inTime + ", inPic=" + inPic
				+ ", inOperatorName=" + inOperatorName + ", inOperationTime=" + inOperationTime
				+ ", inOperationTypeName=" + inOperationTypeName + ", outCarNo=" + outCarNo + ", outDeviceName="
				+ outDeviceName + ", outFlag=" + outFlag + ", outTime=" + outTime + ", outPic=" + outPic
				+ ", outOperatorName=" + outOperatorName + ", outOperationTime=" + outOperationTime
				+ ", outOperationTypeName=" + outOperationTypeName + ", payMoney=" + payMoney + ", agioMoney="
				+ agioMoney + ", chargeMoney=" + chargeMoney + ", inMemo=" + inMemo + ", outMemo=" + outMemo
				+ ", abort=" + abort + ", inPicMin=" + inPicMin + ", outPicMin=" + outPicMin + ", outCarNocolor="
				+ outCarNocolor + ", inCarNocolor=" + inCarNocolor + ", id=" + id + ", status=" + status + ", parkName="
				+ parkName + ", epMoney=" + epMoney + ", inPicId=" + inPicId + ", inPicMinId=" + inPicMinId
				+ ", outPicId=" + outPicId + ", outPicMinId=" + outPicMinId + "]";
	}

}

业务持久层：

package com.etc.third.tlq.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;

import com.etc.bus.carpark.car.dao.DataBaseSqlFile;
import com.etc.third.tlq.bean.StopInThePayment;
import com.etc.third.tlq.bean.TlqCarOut;
import com.etccity.core.crud.dao.impl.EntityDaoImpl;
import com.etccity.security.CurrentSessionUser;

/**
 *  业务持久层
 * 
 * 
 * @ClassName : TlqCarOutDao
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:52
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 */
@Repository("tlqCarOutDao")
public class TlqCarOutDao extends EntityDaoImpl
     
     
      
       {
	
	@Autowired
	private DataBaseSqlFile dataBaseSqlFile;

	/**
	 * 获取用户信息
	 * 
      
      
	 * 
	 * @Title : getSessionUser
	 *        
	 *        
      
      
	 * @Description : TODO
	 *              
	 *              
      
      
	 * @Author : HuaZai
	 *         
	 *         
      
      
	 * @Date : 2017年10月23日 上午11:01:38
	 *       
	 */
	public String getSessionUser() {
		CurrentSessionUser user = (CurrentSessionUser) SecurityContextHolder.getContext().getAuthentication()
				.getPrincipal();
		String userCode = user.getDepartment().getLevelCode();
		return userCode;
	}

	/**
	 *  数据集合
	 * 
      
      
	 * 
	 * @Title : getExportStopInThePaymentList
	 *        
	 *        
      
      
	 * @Description : TODO
	 *              
	 *              
      
      
	 * @Author : HuaZai
	 *         
	 *         
      
      
	 * @Date : 2017年10月23日 上午9:44:47
	 *       
	 */
	@SuppressWarnings({ "static-access", "rawtypes" })
	public List
      
      
       
        getExportStopInThePaymentList() {
		SQLQuery query = super.getSession().getSessionFactory().getCurrentSession()
				.createSQLQuery(dataBaseSqlFile.STOP_IN_THE_PAYMENT);
		query.setString(0, getSessionUser());
		List list = query.list();
		List
       
       
        
         stopInThePayments = new ArrayList<>();
		for (int i = 0; i < list.size(); i++) {
			StopInThePayment stopInThePayment = new StopInThePayment();
			Object[] object = (Object[]) list.get(i);
			if (object[0] != null) {
				stopInThePayment.setName(object[0].toString());
			} else {
				stopInThePayment.setName("-----");
			}
			if (object[1] != null) {
				stopInThePayment.setOutcarno(object[1].toString());
			} else {
				stopInThePayment.setOutcarno("-----");
			}
			if (object[2] != null) {
				stopInThePayment.setTime(object[2].toString());
			} else {
				stopInThePayment.setTime("-----");
			}
			if (object[3] != null) {
				stopInThePayment.setPaymoney(object[3].toString());
			} else {
				stopInThePayment.setPaymoney("-----");
			}
			if (object[4] != null) {
				stopInThePayment.setAgiomoney(object[4].toString());
			} else {
				stopInThePayment.setAgiomoney("-----");
			}
			if (object[5] != null) {
				stopInThePayment.setCharmoney(object[5].toString());
			} else {
				stopInThePayment.setCharmoney("-----");
			}
			if (object[6] != null) {
				stopInThePayment.setType(object[6].toString());
			} else {
				stopInThePayment.setType("-----");
			}
			if (object[7] != null) {
				stopInThePayment.setFreestatus(object[7].toString());
			} else {
				stopInThePayment.setFreestatus("-----");
			}
			if (object[8] != null) {
				stopInThePayment.setOutoperationtime(object[8].toString());
			} else {
				stopInThePayment.setOutoperationtime("-----");
			}
			if (object[9] != null) {
				stopInThePayment.setOutmemo(object[9].toString());
			} else {
				stopInThePayment.setOutmemo("-----");
			}
			stopInThePayments.add(stopInThePayment);
		}
		return stopInThePayments;
	}
}

业务服务层：

package com.etc.third.tlq.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.etc.third.tlq.bean.StopInThePayment;
import com.etc.third.tlq.bean.TlqCarOut;
import com.etc.third.tlq.dao.TlqCarOutDao;
import com.etccity.core.crud.manager.impl.EntityManageImpl;

/**
 * 业务服务层
 * 
 * 
 * @ClassName : TlqCarOutManager
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:42
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 */
@Service("tlqCarOutManager")
@Transactional
public class TlqCarOutManager extends EntityManageImpl
    
    
     
      {
    
	@Autowired
	TlqCarOutDao tlqCarOutDao;

	public List
     
     
      
       getExportStopInThePaymentList() {
		return tlqCarOutDao.getExportStopInThePaymentList();
	}

}

业务控制层：

package com.etc.third.tlq.web;

import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.etc.bus.carpark.utils.ExportExcelUtil;
import com.etc.third.tlq.bean.StopInTheDaily;
import com.etc.third.tlq.bean.StopInThePayment;
import com.etc.third.tlq.bean.TlqCarOut;
import com.etc.third.tlq.dao.TlqCarInDao;
import com.etc.third.tlq.service.TlqCarOutManager;
import com.etccity.core.crud.web.SpringSupportAction;

/**
 * 业务控制层
 * 
 *  
 * @ClassName : TlqCarOutAction
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:32
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 */
@Controller
@RequestMapping("/tlq/car/out/") 
public class TlqCarOutAction extends SpringSupportAction
    
    
     
      {

	@Autowired
	private TlqCarOutManager tlqCarOutManager;
	@SuppressWarnings("unused")
	@Autowired
	private TlqCarInDao tlqCarInDao;
	@Autowired
	private ExportExcelUtil
     
     
      
       export;

	/**
	 * 导出测试数据
	 * 
      
      
	 * 
	 * @Title : exportPayment
	 *        
	 *        
      
      
	 * @Description : TODO
	 *              
	 *              
      
      
	 * @Author : HuaZai
	 *         
	 *         
      
      
	 * @Date : 2017年10月23日 上午10:32:04
	 *       
	 */
	@RequestMapping(value = "export/payment")
	public void exportPayment(HttpServletRequest request, HttpServletResponse response) {
		List
      
      
       
        dataSet = tlqCarOutManager.getExportStopInThePaymentList();
		String[] headers = { "编号", "停车场名称", "车牌号", "停车时长", "应收金额", "优惠金额", "实收金额", "缴费方式", "缴费状态", "缴费时间", "备注" };
		try {
			export.exportExcel(request, response, dataSet, headers);
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

业务 SQL 集合：

package com.etc.bus.carpark.car.dao;

import org.springframework.stereotype.Component;

/**
 * 业务SQL 集合
 * 
 * 
 * @ClassName : DataBaseSqlFile
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年10月23日 上午9:53:11
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 */
@Component 
public class DataBaseSqlFile {

	/**
	 * 业务 SQL
	 */
	public final static String STOP_IN_THE_PAYMENT = "SELECT N.NAME, N.OUTCARNO, DECODE(TO_CHAR(TRUNC((N.OUTTIME - N.INTIME) * 24)), 0, NULL, TO_CHAR(TRUNC((N.OUTTIME - N.INTIME) * 24)) || '小时') || TO_CHAR(TRUNC(MOD((N.OUTTIME - N.INTIME) * 24 * 60, 60))) || '分' || TO_CHAR(TRUNC(MOD((N.OUTTIME - N.INTIME) * 24 * 60 * 60, 60))) || '秒' TIME, N.PAYMONEY, N.AGIOMONEY, DECODE(R.STATUS, 99, R.MONEY, N.CHARGEMONEY) AS CHARMONEY, NVL(LTRIM(R.TYPE), '现金') AS TYPE, TO_CHAR(NVL(R.STATUS, TO_CHAR(DECODE(N.CHARGEMONEY, (N.PAYMONEY - N.AGIOMONEY), 99, 11)))) AS FREE_STATUS, N.OUTOPERATIONTIME, N.OUTMEMO, N.GCODE FROM (SELECT O.*, G.NAME, G.ID PARK_ID, G.ORG_CODE GCODE FROM TLQ_CAR_OUT O, PARK_INFO G WHERE G.CODE = TO_CHAR(O.REGION) AND (O.PAYMONEY <> 0 OR O.CHARGEMONEY <> 0) AND G.ORG_CODE LIKE ? || '%' ORDER BY O.OUTTIME DESC) N LEFT JOIN MONEY_CAR_RECORD R ON N.INTIME = R.IN_TIME AND N.INCARNO = R.CAR_NO AND N.PARK_ID = R.PARK_ID AND R.STATUS = '99'";

}

POI 导出 Excel 工具类：

package com.etc.bus.carpark.util;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.springframework.stereotype.Component;

import com.etc.third.tlq.bean.StopInThePayment;

/**
 * Java 使用 POI + Servlet + Spring 实现 业务数据导出为 Excel 文件
 * 
 * 
 * @ClassName : ExportExcelUtil
 *            
 *            
 * @Description : TODO
 *              
 *              
 * @Author : HuaZai
 *         
 *         
 * @ContactInformation : 1461522031@qq.com/huazai6789@aliyun.com
 *                     
 *                     
 * @Date : 2017年10月31日 下午4:17:48
 *       
 * 
 *       
 * @Version : V1.0.0
 *          
 *
 * @param 
    
    
     
     
 */
@SuppressWarnings({ "serial", "deprecation" })
@Component
public class ExportExcelUtil
     
     
      
       extends HttpServlet {

	/**
	 * 导出业务数据工具类
	 * 
      
      
	 * 
	 * @Title : exportExcel
	 *        
	 *        
      
      
	 * @Description : TODO
	 *              
	 *              
      
      
	 * @Author : HuaZai
	 *         
	 *         
      
      
	 * @Date : 2017年10月31日 下午4:18:07
	 *       
	 */
	public void exportExcel(HttpServletRequest request, HttpServletResponse response, List
      
      
       
        dataSet,
			String[] headers) throws UnsupportedEncodingException {
		// 声明一个工作簿
		HSSFWorkbook wb = new HSSFWorkbook();
		// 设置下载时弹出框
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		String fileName = "临停缴费";
		response.addHeader("Content-Disposition",
				"attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
		// 在 Excel 中声明一个 Sheet
		HSSFSheet sheet = wb.createSheet();
		HSSFRow row1 = sheet.createRow(0);
		row1.setHeight((short) 800);
		// 创建表头单元格
		HSSFCell cell1 = row1.createCell(0);
		cell1.setCellValue("临停缴费记录报表");
		// 设置字体
		HSSFFont font = wb.createFont();
		// 字体高度
		font.setFontHeightInPoints((short) 20);
		// 字体颜色
		font.setColor(HSSFFont.COLOR_NORMAL);
		// 字体类型
		font.setFontName("仿宋");
		// 字体宽度
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		// 设置单元格类型
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		// 水平居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 垂直居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setWrapText(true);
		// 设置单元格样式
		cell1.setCellStyle(cellStyle);

		// 创建内容单元格
		HSSFRow row = sheet.createRow(1);
		// 设置行高
		row.setHeight((short) 400);
		// 设置没列的宽度
		sheet.setColumnWidth(0, 3000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 4000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 6000);
		sheet.setColumnWidth(5, 6000);
		sheet.setColumnWidth(6, 6000);
		sheet.setColumnWidth(7, 6000);
		sheet.setColumnWidth(8, 6000);
		sheet.setColumnWidth(9, 6000);
		sheet.setColumnWidth(10, 6000);
		sheet.setColumnWidth(11, 6000);

		// 设置字体样式
		HSSFFont fontlast = wb.createFont();
		// 字体高度
		fontlast.setFontHeightInPoints((short) 12);
		// 字体颜色
		fontlast.setColor(HSSFFont.COLOR_NORMAL);
		// 字体类型
		fontlast.setFontName("宋体");
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(fontlast);
		// 水平居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 垂直居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setWrapText(true);

		// 设值表头值
		for (int k = 0; k < headers.length; k++) {
			HSSFCell cell = row.createCell(k);
			cell.setCellValue(headers[k]);
			cell.setCellStyle(cellStyle);
		}

		List
       
       
        
         stopInThePayments = dataSet;
		// 遍历数据集合
		for (int i = 0; i < dataSet.size(); i++) {
			row = sheet.createRow(i + 2);
			StopInThePayment stopInThePayment = stopInThePayments.get(i);
			HSSFCell cell = row.createCell(0);
			cell.setCellValue(i + 1);// 编号
			cell.setCellStyle(style);
			cell = row.createCell(1);
			cell.setCellValue(stopInThePayment.getName());// 停车场名称
			cell.setCellStyle(style);
			cell = row.createCell(2);
			cell.setCellValue(stopInThePayment.getOutcarno());// 车牌号
			cell.setCellStyle(style);
			cell = row.createCell(3);
			cell.setCellValue(stopInThePayment.getTime());// 停车时长
			cell.setCellStyle(style);
			cell = row.createCell(4);
			cell.setCellValue(stopInThePayment.getPaymoney());// 应收金额
			cell.setCellStyle(style);
			cell = row.createCell(5);
			cell.setCellValue(stopInThePayment.getAgiomoney());// 优惠金额
			cell.setCellStyle(style);
			cell = row.createCell(6);
			cell.setCellValue(stopInThePayment.getCharmoney());// 实收金额
			cell.setCellStyle(style);
			cell = row.createCell(7);
			cell.setCellValue(stopInThePayment.getType());// 缴费方式
			cell.setCellStyle(style);
			cell = row.createCell(8);
			cell.setCellValue(stopInThePayment.getFreestatus());// 缴费状态
			cell.setCellStyle(style);
			cell = row.createCell(9);
			cell.setCellValue(stopInThePayment.getOutoperationtime());// 缴费时间
			cell.setCellStyle(style);
			cell = row.createCell(10);
			cell.setCellValue(stopInThePayment.getOutmemo());// 备注
			cell.setCellStyle(style);
		}
		Region region = new Region(0, (short) 0, 0, (short) (10));
		sheet.addMergedRegion(region);
		try {
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

JSP 实例代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>




      
      
Java中使用POI导出Excel
<%@include file="base-list.jsp"%>
<script type="text/javascript" language="">
	var Config = {
		tbar : [ {
			text : '详情',
			id : 'detail',
			icon : 'detail',
			click : detail
		}, "-", {
			text : '导出',
			id : 'export',
			icon : 'export',
			click : export_money_car_record
		} ],
		listeners : {

		}
	};

	// 本例主要为导出操作 ，所以重点在这儿，其它代码切掉了；
	//注意下面这段代码，解决浏览器各个版本的解析都不同，所以必须加上这个，还可以解决掉浏览器的兼容性的问题；
	var base = "${pageContext.request.scheme}://${pageContext.request.serverName}:${pageContext.request.serverPort}${pageContext.request.contextPath}/";
	function export_money_car_record() {
		window.location.href = base + "tlq/car/out/export/payment.do";
	}

	/* 
	除了上面的跳转外，还有下面一种：
	  
	
	 */
</script>

总结：

在使用的过程中你可能遇到的问题：

【项目实战】 Apache POI 导出 Excel 常见的23问题

Invalid row number (65536) outside allowable range (0..65535)

好了，关于 “ Java中使用POI导出Excel ” 就编写到这儿，本人在利用工作和下班的空余时间，编写并整理出了此详细的教程，两个目的：1、帮助自己节省开发时间，提高开发效率；2、帮助别人少走弯路；

如果在开发的过程中，遇到了什么问题，也可以给我留言，或者发邮箱，一起探讨！

同时也希望大家多多关注CSND的IT社区。

Hello_World_QWP

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
4
评论
Java中使用POI导出Excel 之项目实战详细教程

《 Java中使用POI导出Excel 》@本教程的所有内容版权归本人所有，与企业/公司无关前言：在项目开发的过程中，因为需要将系统中的业务数据进行导出，根据开发经验，我个人觉得在目前看来，Apache 下的 POI 还是比较流行的，所以就选定了 POI 来实现这个导出功能，在导出功能编写之初，因为对 POI 的 API 不是很熟，花了大把时间来进行查阅相关的
复制链接

扫一扫