前言
近日,发现本博客阅读数最高的一篇博客是Java中使用poi导入、导出Excel。这篇博文是早期转载于一位博主,文章讲解的是POI的具体实现。如果想成功运行例子,还需要修改部分代码。另外,没有Servlet的基础,可能会有些吃亏。因此,思索考虑,决定写下这篇博文,供新手学习且想实现Excel导出功能的人。
前期准备
jar:
poi-3.9.jar 实现Excel导出
log4j.jar 日志记录
ojdbc7.jar oracle数据库连接
javax.servlet.jar javaee里面的规范
java:
ExportSearchResultServlet.java 导出Excel类
DeliveryDao.java 数据库查询类
DeliverySearchResult.java 数据库查询结果封装类
ConnectionFactory.java 连接池工厂类
DateUtil.java 日期格式化类
Constant.java 封装常量的类
InitProperties.java 加载配置文件类
jsp:
export.jsp 导出Excel页面
table:
ecl_redemption_delivery_master
environment:
tomcat8.0、oracle、jdk1.8
Tips:考虑到公司部分代码的版权问题,这里一些代码不一一贴出来,还望各位见谅。
代码实现
ExportSearchResultServlet.java
package ecl.delivery.servelt;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
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.HSSFColor;
import com.hthk.ffpc.sql.ConnectionFactory;
import com.hthk.ffpc.sql.DataException;
import ecl.delivery.dao.DeliveryDao;
import ecl.delivery.model.DeliverySearchCriteria;
import ecl.delivery.model.DeliverySearchResult;
import ecl.util.Constant;
import ecl.util.DateUtil;
import ecl.util.InitProperties;
public class ExportDeliverySearchResultServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
final private static Logger logger = Logger.getLogger(ExportDeliverySearchResultServlet.class);
SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
private String[] header = { "Account No",
"Contract No",
"Circuit No",
};
public void init() throws ServletException {
}
final public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// doPost(request, response);
}
final public void doPost(HttpServletRequest req, HttpServletResponse resp) {
logger.debug("ExportDeliverySearchResultServlet - Start");
InitProperties ip = new InitProperties();
//String filePath = ip.getProperties(Constant.STORAGE_PATH);
String filePath="D://"
File serverFile = null;
ServletOutputStream servletOutputStream = null;
ConnectionFactory connectionFactory = null;
Connection con = null;
try {
connectionFactory = new ConnectionFactory("atoms");
con = connectionFactory.getConnection();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Result");
int rowNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
int colNumInHeader = 0;
HSSFCell cell = row.createCell(colNumInHeader);
final HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFFont boldFont = wb.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle.setFont(boldFont);
boldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
final HSSFCellStyle redStyle = wb.createCellStyle();
{
HSSFFont font = wb.createFont();
font.setColor(new HSSFColor.RED().getIndex());
redStyle.setFont(font);
}
final HSSFCellStyle dimStyle = wb.createCellStyle();
{
HSSFFont dimFont = wb.createFont();
dimFont.setColor(new HSSFColor.GREY_40_PERCENT().getIndex());
dimStyle.setFont(dimFont);
}
// Add Header - START
for (int i = 0; i < header.length; i++) {
String field = header[i];
cell = row.createCell(i);
cell.setCellValue((String) field);
cell.setCellStyle(boldStyle);
}
// Add Header - END
DeliveryDao dao = new DeliveryDao();
List<DeliverySearchResult> result = dao.searchResult(con);
for (int i = 0; i < result.size(); i++) {
DeliverySearchResult r = (DeliverySearchResult) result.get(i);
row = sheet.createRow(rowNum++);
int colNum = 0;
cell = row.createCell(colNum++);
cell.setCellValue(r.getAcctNo());
cell = row.createCell(colNum++);
cell.setCellValue(r.getContNo());
cell = row.createCell(colNum++);
cell.setCellValue(r.getCircNo());
}
String today = DateUtil.dateToStr(DateUtil.getToday(), DateUtil.SDF_DMYHMS_RPT_FMT);
String downloadFileName = "deliveryResult_" + today + ".xls";
logger.debug("ExportDeliverySearchResultServlet.doPost - downloadFileName=" + downloadFileName);
servletOutputStream = this.getServletOutputStream(resp, downloadFileName);
serverFile = new File(filePath + File.separator + downloadFileName);
this.exportToServer(wb, serverFile);
this.readFileForDownload(serverFile, servletOutputStream);
} catch (IOException ex) {
logger.error("ExportDeliverySearchResultServlet.doPost - IOException:" + ex.toString());
ex.printStackTrace();
} catch (Exception ex) {
logger.error("ExportDeliverySearchResultServlet.doPost - Exception:" + ex.toString());
ex.printStackTrace();
} finally {
if (con != null) {
try {
connectionFactory.finalize();
} catch (DataException e) {
logger.error("ExportDeliverySearchResultServlet.doPost - finally DataException:" + e.toString());
}
}
try {
if (servletOutputStream != null) {
servletOutputStream.flush();
servletOutputStream.close();
}
} catch (IOException ex) {
logger.error("ExportDeliverySearchResultServlet.doPost - finally IOException:" + ex.toString());
}
}
logger.debug("ExportDeliverySearchResultServlet - END");
}
private ServletOutputStream getServletOutputStream(HttpServletResponse response, String outputFileName)
throws IOException {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + outputFileName);
ServletOutputStream sos;
sos = response.getOutputStream();
return sos;
}
private void exportToServer(HSSFWorkbook wb, File serverFile) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(serverFile);
wb.write(fos);
} catch (Exception ex) {
logger.error("exportToServer Exception ", ex);
} finally {
if (fos != null) {
try {
fos.flush();
fos.close();
} catch (IOException ex) {
logger.error("exportToServer IOException: ", ex);
} catch (Exception ex) {
logger.error("exportToServer Exception: ", ex);
}
}
}
}
public void readFileForDownload(File serverFile, OutputStream outputStream)
throws FileNotFoundException, IOException {
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream(serverFile);
bis = new BufferedInputStream(fis);
int intRead = -1;
while ((intRead = bis.read()) != -1) {
outputStream.write(intRead);
}
} finally {
if (bis != null) {
bis.close();
bis = null;
}
if (fis != null) {
fis.close();
fis = null;
}
}
}
}
DeliveryDao.java
package ecl.delivery.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.hthk.ffpc.sql.DataException;
import admin.User;
import ecl.delivery.model.DeliverySearchCriteria;
import ecl.delivery.model.DeliverySearchResult;
import ecl.util.StringUtil;
public class DeliveryDao {
private static final Logger logger = Logger.getLogger(DeliveryDao.class);
public String nullToEmptyStr(String input) {
String result = "";
if (input != null) {
return input;
}
return result;
}
public void close(ResultSet rs, Statement statement) {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public List<DeliverySearchResult> searchResult(Connection con, DeliverySearchCriteria criteria) throws DataException {
String sql = "";
ResultSet rs = null;
PreparedStatement pstm = null;
int i = 1;
List<DeliverySearchResult> result = new ArrayList<DeliverySearchResult>();
sql = " SELECT DELIVERY_ID,"
+ " ACCT_NO,"
+ " CONT_NO,"
+ " CIRC_NO"
+ " FROM ECL_REDEMPTION_DELIVERY_MASTER e";
try {
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while (rs.next()) {
DeliverySearchResult r = new DeliverySearchResult();
r.setDeliveryId(rs.getInt("DELIVERY_ID"));
r.setAcctNo(rs.getString("ACCT_NO"));
r.setContNo(rs.getString("CONT_NO"));
r.setCircNo(rs.getString("CIRC_NO"));
result.add(r);
}
}catch (Exception e) {
logger.error(e.getMessage(),e);
throw new DataException(e);
} finally {
close(rs,pstm);
}
return result;
}
DeliverySearchResult.java
package ecl.delivery.model;
public class DeliverySearchResult {
private Integer deliveryId;
private String acctNo;
private String contNo;
private String circNo;
public DeliverySearchResult() {
this.deliveryId = 0;
this.acctNo = "";
this.contNo = "";
this.circNo = "";
}
public Integer getDeliveryId() {
return deliveryId;
}
public void setDeliveryId(Integer deliveryId) {
this.deliveryId = deliveryId;
}
public String getAcctNo() {
return acctNo;
}
public void setAcctNo(String acctNo) {
this.acctNo = acctNo;
}
public String getContNo() {
return contNo;
}
public void setContNo(String contNo) {
this.contNo = contNo;
}
public String getCircNo() {
return circNo;
}
public void setCircNo(String circNo) {
this.circNo = circNo;
}
}
DateUtil.java
package ecl.util;
import java.text.SimpleDateFormat;
import java.util.*;
import org.apache.log4j.Logger;
public class DateUtil {
private static Logger log = Logger.getLogger(DateUtil.class);
public static final SimpleDateFormat SDF_DMY = new SimpleDateFormat("dd-MM-yyyy");
public static final SimpleDateFormat SDF_YMD = new SimpleDateFormat("yyyy-MM-dd");
public static final SimpleDateFormat SDF_YMDHMS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static final SimpleDateFormat SDF_DMYHMS = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
public static final SimpleDateFormat SDF_DMYHMS_RPT_FMT = new SimpleDateFormat("ddMMyyyyHHmmss");
public static Date getToday(){
Date currDate = new Date();
try {
currDate = Calendar.getInstance().getTime();
} catch (Exception e) {
log.error("DateUtil.getToday error:"+e.getMessage());
}
return currDate;
}
public static String getTodayStr(boolean defaultFormat, String returnFormat){
String result = "";
try {
Date currDate = Calendar.getInstance().getTime();
if (defaultFormat){
result = SDF_DMY.format(currDate);
} else {
SimpleDateFormat sdf;
if (StringUtil.isNull(returnFormat)){
sdf = SDF_DMY;
} else {
sdf = new SimpleDateFormat(returnFormat);
}
result = sdf.format(currDate);
}
} catch (Exception e) {
log.info("DateUtil.getTodayStr defaultFormat:"+defaultFormat);
log.info("DateUtil.getTodayStr returnFormat:"+returnFormat);
log.error("DateUtil.getTodayStr error:"+e.getMessage());
}
return result;
}
public static String dateToStr(Date date, SimpleDateFormat sdf){
String result = "";
try {
if (date != null){
result = sdf.format(date);
}
} catch (Exception e) {
log.info("DateUtil.dateToStr date:"+date);
log.error("DateUtil.dateToStr error:"+e.getMessage());
}
return result;
}
public static String dateToStr(Date date, String format){
String result = "";
try {
if (date != null){
SimpleDateFormat sdf1 = new SimpleDateFormat(format);
result = sdf1.format(date);
}
} catch (Exception e) {
log.info("DateUtil.dateToStr format:"+format);
log.info("DateUtil.dateToStr date:"+date);
log.error("DateUtil.dateToStr error:"+e.getMessage());
}
return result;
}
}
export.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<%
String contextPath = request.getContextPath();
%>
<body>
<form method="post" action="<%=contextPath%>/servlet/ecl.delivery.servelt.ExportDeliverySearchResultServlet">
<input type="submit" value="Export"/>
</form>
</body>
</html>
演示
结语
这部分代码如果想连接数据库成功,是不能实现的,如果想实现对数据库的连接,请再看一看有关连接数据库的教程哦。