新手使用POI结合数据库导出Excel文件

前言

---

  近日，发现本博客阅读数最高的一篇博客是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>

演示

---

结语

---

这部分代码如果想连接数据库成功，是不能实现的，如果想实现对数据库的连接，请再看一看有关连接数据库的教程哦。