将数据库中的数据按要求导出到excel表中

java开发 专栏收录该内容
49 篇文章 1 订阅

这次来总结一下导出的将数据库中的数据导出到excel表中的功能。其实和从excel表中把数据导入到数据库一样,关键在于jar包,代码都没有什么难度,先来看一下效果图:
这里写图片描述
点击页面中的导出按钮,就会下载一个excel文件,文件中保存了数据库中的数据如图:
这里写图片描述

好的整体的效果大概就是这样子,现在来看看具体代码是怎么实现的呢。首先看jsp页面。
其实jsp页面没有什么特殊的,就是点击导出按钮触发一个action事件,然后进入action层。

<html>
<head>
<script type="text/javascript">
/**
 * 导出满足条件的报障单
 */
function exportRepair(){
    var status=$('#status').val();
    var rid=$('#rid').val();
    var startTime=$('#startTime').val();
    var endTime=$('#endTime').val();
    var name=$('#name').val();
    var ename=$('#ename').val();
    var cyid = $("#cyid").val();
    //alert("参数:"+status+rid+startTime+endTime+name+ename+"结束");
    var url = "../PilotMS/repair_exportRepair?1=1";
    url += "&keywords=" + 0;
    url += "&status=" + status;
    url += "&rid=" + rid;
    url += "&startTime=" + startTime;
    url += "&endTime=" + endTime;
    url += "&name=" + name;
    url += "&ename=" + ename;   
    document.location.href = url;
/*  $.post(
            '../PilotMS/repair_exportRepair',
            {keywords:0,status:status, rid:rid, startTime:startTime, endTime:endTime, name:name, ename:ename},
            function(data, status){
                if (data){
                    repair.findAll(0, rid, startTime, endTime, name, cyid, status, ename, "1");
                }
            }
        );*/

}
</script>
</head>
<body>
    <table height="100%" style="margin-top: -3px;">
        <tr>
            <td valign="middle" align="right" colspan="5"><button
                    class="save-btn" onclick="exportRepair()">导出</button></td>
        </tr>
    </table>
    </div>
</body>
</html>

当然我这是基于struts2框架的,struts.xml还要进行配置,使得jsp页面的中的路径能够找到指定的action。我这里就不写了,相信大家伙能够使用自己的方法view层找到对应的controller层吧。
然后在action中代码如下:


    /**
     * 导出报障单
     */
    public void exportRepair(){
        try {   
            pageNo = "1";
            repair = new Repair();      
            if(keywords.equals("0")){
                String serialNum=null;
                Company company = (Company) getSession().getAttribute(COMPANY);
                Customer customer = (Customer) getSession().getAttribute(CUSTOMER);//***
                if(company != null){
                //  cyid = company.getCyid();
                    serialNum=company.getSerialNum();
                    repair.setType(COMPANY);
                }
                //****
                if(customer != null){
                    //cid = customer.getCid();
                    serialNum=customer.getSerialNum();
                    repair.setType(CUSTOMER);
                }
                repair.setSerialNum(serialNum);
                repair.setCyid(cyid);
                repair.setName(name);
                repair.setRid(rid);
                repair.setStatus(status);
                repair.setLongitude(startTime);
                repair.setLatitude(endTime);
                repair.setEname(ename);
                repair.setOrderBy("time");// 按报修时间排序
                list = repairBiz.findAll(repair, null);
                if(list!=null&&list.size()>0){
                    page = new Page(Integer.parseInt(pageNo), list.size(), list.size());                    
                    list = repairBiz.findAll(repair, page);
                }           
            }else if(keywords.equals("1")){
                repair.setCyid(cyid);
                repair.setRid(rid);
                setStatus(4);
                repair.setStatus(4);
                repair.setOvertime("overtime");
                list = repairBiz.findAll(repair, null);
                if(list!=null&&list.size()>0){
                    page = new Page(Integer.parseInt(pageNo), list.size(), list.size());
                    list = repairBiz.findAll(repair, page);
                }   
            }else if(keywords.equals("2")){
                repair.setCyid(cyid);
                repair.setRid(rid);
                setStatus(5);
                repair.setStatus(5);
                repair.setGrade(1);
                list = repairBiz.findAll(repair, null);
                if(list!=null&&list.size()>0){
                    page = new Page(Integer.parseInt(pageNo), list.size(), list.size());
                    list = repairBiz.findAll(repair, page);
                }   
            }                                       
            List<List<Object>> list1 = repairBiz.getExportRepairDataList(list);
            String[] headers={"序号","报障单号","报障日期","处理时间", "网点名称", "报障人","联系电话", "设备类型", "故障情况",
                    "处理方法", "结果", "工程师", "抽查回访结果","备注"};
            String fileName = "报障单";
            ExportExcel.excelOut(headers, list1, fileName, request, response);          
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }   

思路就是现在数据库中查询出先要导出的数据list,上面代码红的list就是从数据库中获得的数据,然后把数据进行处理成要导入到excel表中形式。那个就是上面代码的List


public List<List<Object>> getExportRepairDataList(List<Repair> repairList) {
        // TODO Auto-generated method stub
        List<List<Object>> list = new ArrayList<List<Object>>();
        if(repairList != null && repairList.size() > 0){
            int count=0;
            for (Repair repair : repairList) {
                List<Object> rowDataList = new ArrayList<Object>();

                //序号
                count++;
                rowDataList.add(count);
                //报障单号
                if(repair.getRid()==null){
                    rowDataList.add("");
                }else {             
                    rowDataList.add(""+repair.getRid());                    
                }               
                // 报障日期
                if(repair.getCreateDate()==null){
                    rowDataList.add("");
                }else {
                    String createTime=repair.getCreateDate().toString();
                    //System.out.println(createTime.substring(0, 11));
                    rowDataList.add(""+createTime.substring(0, 11));

                }

                // 处理时间
                if(repair.getFinishDate()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getFinishDate().toString().substring(0, 11));
                }   

                // 网点名称
                if(repair.getCompany()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getCompany());
                }   

                // 报障人
                if(repair.getName()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getName());
                }   

                //联系电话
                if(repair.getPhone()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getPhone());
                }   

                // 设备类型
                if(repair.getMname()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getMname());
                }   

                //故障情况
                if(repair.getContent()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getContent());
                }   

                //处理方法
                if(repair.getSuggest()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getSuggest());
                }   

                //结果                
                if(repair.getStatus()==0){
                    rowDataList.add("待响应");
                }else if(repair.getStatus()==1){
                    rowDataList.add("转接中");
                }else if(repair.getStatus()==2){
                    rowDataList.add("处理中");
                }else if(repair.getStatus()==3){
                    rowDataList.add("已预约");
                }else if(repair.getStatus()==4){
                    rowDataList.add("待评价");
                }else if(repair.getStatus()==5){
                    rowDataList.add("已评价");
                }else if(repair.getStatus()==7){
                    rowDataList.add("关单");
                }else {
                    rowDataList.add("");
                }

                //工程师
                if(repair.getEname()==null){
                    rowDataList.add("");
                }else {
                    rowDataList.add(""+repair.getEname());
                }   

                //抽查回访结果
                if(repair.getAvgScore()==null){
                    rowDataList.add("");
                }else {
                    double  avgScore=Double.parseDouble(repair.getAvgScore());
                    if(avgScore==5){
                        rowDataList.add("非常满意");
                    }else if(avgScore==4){
                        rowDataList.add("满意");
                    }else if(avgScore==3){
                        rowDataList.add("一般般");
                    }else if(avgScore==2){
                        rowDataList.add("不满意");
                    }else if(avgScore==1){
                        rowDataList.add("非常不满意");
                    }else {
                        rowDataList.add("");
                    }               
                }   

                //备注                
                rowDataList.add("");

                list.add(rowDataList);
            }
        }
        return list;
    }

这样一个基本的导出功能就已经实现了。附上ExportExcel.java文件,设置表格的样式。

package com.pilot.util;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

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.HSSFColor;

public class ExportExcel {
    public static void excelOut(String[] cloumName, List<List<Object>> list,String fileName,
        HttpServletRequest request,HttpServletResponse response){  
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(fileName);
        HSSFRow oneRow=sheet.createRow(0);
        oneRow.setHeight((short) 500);
        HSSFCell cell = null;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        style.setFont(font);
        style.setWrapText(true);

        HSSFCellStyle style1 = workbook.createCellStyle();
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style1.setFillForegroundColor(HSSFColor.AQUA.index);
        style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style1.setFont(font); 

        HSSFRow row = sheet.createRow(0);    
        for(int i = 0; i < cloumName.length; i++){    
            //单元格  
            cell = row.createCell(i);    
            cell.setCellStyle(style1);    
            cell.setCellValue(cloumName[i]);   
        }    

        for (int i = 0; i < list.size(); i++){    
            row = sheet.createRow(i + 1);    
            List<Object> dataList = list.get(i);    
            for (int j = 0; j < dataList.size(); j++) {  
                // 表格内容样式设置  
                cell = row.createCell(j);
                cell.setCellStyle(style);    
                cell.setCellValue(String.valueOf(dataList.get(j)));   
                sheet.autoSizeColumn(( short ) j);
            }  
         }  

        try{   
            response.reset();
            OutputStream os = response.getOutputStream();
            response.setHeader("Content-disposition","attachment; filename="+URLEncoder.encode(fileName, "utf-8")+".xls");//设定输出文件头
            response.setContentType("application/msexcel");//定义输出类型
            workbook.write(os);  
            os.close();
        } catch (Exception e){  
             e.printStackTrace();    
        }
    }

    /**
     * 自定义表头的报表导出
     * @param interf 自定义表头接口实现
     * @param list 数据内容list
     * @param fileName 文件名称
     * @param headerRows 表头占据的行数
     * @param request HttpServletRequest
     * @param response HttpServletResponse
     */
    public static void excelOutWithCustomHeader(CustomHeaderInterf interf, List<List<Object>> list,String fileName, int headerRows, 
            HttpServletRequest request,HttpServletResponse response){  
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(fileName);
        HSSFRow oneRow=sheet.createRow(0);
        oneRow.setHeight((short) 500);
        HSSFCell cell = null;

        // 内容单元格的样式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        style.setFont(font);
        style.setWrapText(true);

        // 表头单元格的样式
        HSSFCellStyle style1 = workbook.createCellStyle();
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style1.setFillForegroundColor(HSSFColor.AQUA.index);
        style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style1.setFont(font); 

        HSSFRow row = sheet.createRow(0);    
        if(interf != null){
            // 调用自定义表头的实现方法
            interf.dealCustomHeader(sheet, style1);
        }

        for (int i = 0; i < list.size(); i++){    
            row = sheet.createRow(i + headerRows);    
            List<Object> dataList = list.get(i);    
            for (int j = 0; j < dataList.size(); j++) {  
                // 表格内容样式设置  
                cell = row.createCell(j);
                cell.setCellStyle(style);    
                cell.setCellValue(String.valueOf(dataList.get(j)));   
                sheet.autoSizeColumn(( short ) j);
            }  
         }  

        try{ 
            response.reset();
            OutputStream os = response.getOutputStream();
            response.setHeader("Content-disposition","attachment; filename="+URLEncoder.encode(fileName, "utf-8")+".xls");//设定输出文件头
            response.setContentType("application/msexcel");//定义输出类型
            workbook.write(os);
            os.close();
        } catch (Exception e){  
             e.printStackTrace();    
        }
    }

    /**
     * 自定义表头处理接口定义
     * @author 朱伟权
     */
    public interface CustomHeaderInterf{

        /**
         * 自定义表头处理方法
         * @param sheet excel的sheet对象
         * @param style 表头单元格样式
         */
        void dealCustomHeader(HSSFSheet sheet, HSSFCellStyle style);
    }
}

附上需要的jar包:
http://download.csdn.net/download/qq_27790011/10149812

其实还很简单的,不要把问题想的开复杂,动手试一试会发现原来你也可以!

  • 0
    点赞
  • 2
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值