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

柔光的暖阳◎ 2022-05-18 06:45 196阅读 0赞

# 前言 #

--------------------

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

# 演示 #

--------------------

![这里写图片描述][20180731122713385]  
![这里写图片描述][2018073112064719]

# 结语 #

--------------------

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

[Java_poi_Excel]: https://blog.csdn.net/ththcc/article/details/78175377
[20180731122713385]: /images/20220518/cf70dfbf8d884e498db19787ec1b9a01.png
[2018073112064719]: /images/20220518/59306678454b4f8faf265fb7b8e00814.png