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

《 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 导出工具类，可以根据下图 直接进入：

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 : [e-mail]/[e-mail] *                     
 *                     
 * @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;}@Transientpublic 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();}@Overridepublic 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 : [e-mail]/[e-mail] *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:52 *       
 *  *       
 * @Version : V1.0.0 *          
 * */@Repository("tlqCarOutDao")public class TlqCarOutDao extends EntityDaoImpl {@Autowiredprivate 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 : [e-mail]/[e-mail] *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:42 *       
 *  *       
 * @Version : V1.0.0 *          
 * */@Service("tlqCarOutManager")@Transactionalpublic class TlqCarOutManager extends EntityManageImpl {    @AutowiredTlqCarOutDao 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 : [e-mail]/[e-mail] *                     
 *                     
 * @Date : 2017年10月20日 下午5:06:32 *       
 *  *       
 * @Version : V1.0.0 *          
 * */@Controller@RequestMapping("/tlq/car/out/") public class TlqCarOutAction extends SpringSupportAction {@Autowiredprivate TlqCarOutManager tlqCarOutManager;@SuppressWarnings("unused")@Autowiredprivate TlqCarInDao tlqCarInDao;@Autowiredprivate 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 blocke.printStackTrace();}}}

业务 SQL 集合 ： 

package com.etc.bus.carpark.car.dao;import org.springframework.stereotype.Component;/** * 业务SQL 集合 * 
 *  * @ClassName : DataBaseSqlFile *            
 *            
 * @Description : TODO *              
 *              
 * @Author : HuaZai *         
 *         
 * @ContactInformation : [e-mail]/[e-mail] *                     
 *                     
 * @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 : [e-mail]/[e-mail] *                     
 *                     
 * @Date : 2017年10月31日 下午4:17:48 *       
 *  *       
 * @Version : V1.0.0 *          
 * * @param  */@SuppressWarnings({ "serial", "deprecation" })@Componentpublic 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 中声明一个 SheetHSSFSheet 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"%><%@include file="base-list.jsp"%>

总结：

      

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

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

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

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

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

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