【基于初学者】SSH+Maven实现Excel导出功能

首先导入相关的配置文件添加到pom.xml里面

 <!-- 导入poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14-beta1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14-beta1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14-beta1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-core</artifactId>
            <version>1.3.2</version>
        </dependency>

接着写实体类

public class Card {

    public Card(String cname, String csex, String cdepartment, String cmobile, String cphone, String cemail,
            String caddress) {
        super();
        this.cname = cname;
        this.csex = csex;
        this.cdepartment = cdepartment;
        this.cmobile = cmobile;
        this.cphone = cphone;
        this.cemail = cemail;
        this.caddress = caddress;
    }

    public Card() {
        super();
        // TODO Auto-generated constructor stub
    }

    private int cid;
    private String cname;
    private String csex;
    private String cdepartment;
    private String cmobile;
    private String cphone;
    private String cemail;
    private String caddress;

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public String getCsex() {
        return csex;
    }

    public void setCsex(String csex) {
        this.csex = csex;
    }

    public String getCdepartment() {
        return cdepartment;
    }

    public void setCdepartment(String cdepartment) {
        this.cdepartment = cdepartment;
    }

    public String getCmobile() {
        return cmobile;
    }

    public void setCmobile(String cmobile) {
        this.cmobile = cmobile;
    }

    public String getCphone() {
        return cphone;
    }

    public void setCphone(String cphone) {
        this.cphone = cphone;
    }

    public String getCemail() {
        return cemail;
    }

    public void setCemail(String cemail) {
        this.cemail = cemail;
    }

    public String getCaddress() {
        return caddress;
    }

    public void setCaddress(String caddress) {
        this.caddress = caddress;
    }

}

然后写Dao接口

public interface ICardDao {
    
    public List<Card> getAlls();
}

BaseDao类

public class BaseDao {
  
    //spring产生bean的时候就有多少个bean 所有用static
    private static SessionFactory sessionFactory;
    

        public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }


    public static void setSessionFactory(SessionFactory sessionFactory) {
        BaseDao.sessionFactory = sessionFactory;
    }

        //c3p0的Session
        public static Session getSession(){
            return sessionFactory.getCurrentSession();
        }
}

然后写DaoImpl类

public class CardDao extends BaseDao implements ICardDao{

    public List<Card> getAlls() {
        String str="select c.cid,c.cname,c.csex from Card c ";
        List<Card> list=getSession().createQuery(str).list();
        return list;
    }
}

接着写实体类的xml文件

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2018-6-11 15:46:00 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
    <class name="com.zking.entity.Card" table="CARD">
        <id name="cid" type="int">
            <column name="CID" />
            <generator class="assigned" />
        </id>
        <property name="cname" type="java.lang.String">
            <column name="CNAME" />
        </property>
        <property name="csex" type="java.lang.String">
            <column name="CSEX" />
        </property>
        <property name="cdepartment" type="java.lang.String">
            <column name="CDEPARTMENT" />
        </property>
        <property name="cmobile" type="java.lang.String">
            <column name="CMOBILE" />
        </property>
        <property name="cphone" type="java.lang.String">
            <column name="CPHONE" />
        </property>
        <property name="cemail" type="java.lang.String">
            <column name="CEMAIL" />
        </property>
        <property name="caddress" type="java.lang.String">
            <column name="CADDRESS" />
        </property>
    </class>
</hibernate-mapping>

写Biz接口

public interface ICardBiz {

    public List<Card> getAlls();
    
}

然后在写BizImpl

public class CardBiz implements ICardBiz{

    private ICardDao iCardDao;
    
    public ICardDao getiCardDao() {
        return iCardDao;
    }


    public void setiCardDao(ICardDao iCardDao) {
        this.iCardDao = iCardDao;
    }


    public List<Card> getAlls() {
        // TODO Auto-generated method stub
        return iCardDao.getAlls();
    }
}

最后写Action类

public class CardAction extends ActionSupport implements ModelDriven<Card> {

    private ICardBiz iCardBiz;
    private Logger log;

    private Card card = new Card();

    public ICardBiz getiCardBiz() {
        return iCardBiz;
    }

    public void setiCardBiz(ICardBiz iCardBiz) {
        this.iCardBiz = iCardBiz;
    }

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }

    public Card getModel() {
        // TODO Auto-generated method stub
        return null;
    }


      @RequestMapping(value = "/exportAll", method = RequestMethod.GET)
    public String exportAll(HttpSession session, HttpServletRequest request,
            HttpServletResponse response) {
        String cname=request.getParameter("cname");
        String csex=request.getParameter("csex");
        int cid=Integer.getInteger(request.getParameter("cid"));
        Card card=new Card();
        card.setCid(cid);
        card.setCname(cname);
        card.setCsex(csex);
        OutputStream out = null;
        String exportFileName = "人员信息";
        try {
            HSSFWorkbook hwb = exportAllHis(cname, csex);
            // response.setContentType("application/x-msdownload");
            response.setContentType("application/vnd.ms-excel");
            // 根据浏览器类型处理文件名称
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            if (agent.indexOf("firefox") > -1) {// 若是火狐
                exportFileName = new String(exportFileName.getBytes("UTF-8"), "ISO8859-1");
            } else {// 其他浏览器
                exportFileName = java.net.URLEncoder.encode(exportFileName, "UTF-8");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName + ".xls");
            out = response.getOutputStream();
            hwb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public HSSFWorkbook exportAllHis(String barCode, String tdh) {
        String[] cellname = { "序号", "id序号", "姓名", "性别" };
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFSheet sheet = hwb.createSheet(new Date().getTime() + "report");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        HSSFCellStyle stycle = hwb.createCellStyle();
        stycle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < cellname.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(cellname[i]);
            cell.setCellStyle(stycle);
            sheet.setColumnWidth((short) i, cellname[i].getBytes().length * 450);
        }
        List<Card> list = iCardBiz.getAlls();
        SimpleDateFormat smp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (list != null) {
            for (int j = 0; j < list.size(); j++) {
                row = sheet.createRow(j + 1);
                row.createCell(0).setCellValue(j + 1 + "");
                Card obj = list.get(j);
                if (obj.getCid() != 0) {
                    row.createCell(1).setCellValue(obj.getCid() + "");
                }
                if (obj.getCname() != null) {
                    row.createCell(2).setCellValue(obj.getCname() + "");
                }
                if (obj.getCsex() != null) {
                    row.createCell(3).setCellValue(obj.getCsex() + "");
                }
            }
        }
        return hwb;
    }

}

然后界面用的是easyui的前端框架

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
            + path + "/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>名片浏览与查询</title>
<jsp:include page="head.jsp"></jsp:include>
<script type="text/javascript">
    var cid = "";
    $(function() {
        $('#tab').datagrid({
            height : 'auto',
            split : true,
            border : true,
            //显示斑马线效果
            striped : true,
            width : 800,
            //分页控件
            pagination : true,
            //行号
            rownumbers : true,
            fitColumns : true,
            //是否单选
            singleSelect : false,
            url : 'cagetAll.action',
            onClickRow : function(index, row) {
                cid = row.cid;
            },
            frozenColumns : [ [ {
                field : 'ck',
                checkbox : true
            } ] ],
            columns : [ [ {
                field : 'cid',
                title : '编号',
                width : 150,
                align : 'center'
            }, {
                field : 'cname',
                title : '姓名',
                width : 150,
                align : 'center'
            }, {
                field : 'csex',
                title : '性别',
                width : 150,
                align : 'center'
            }, {
                field : 'cdepartment',
                title : '单位',
                width : 250,
                align : 'center'
            }, {
                field : 'cmobile',
                title : '手机',
                width : 250,
                align : 'center'
            }, {
                field : 'cphone',
                title : '电话',
                width : 250,
                align : 'center'
            }, {
                field : 'cemail',
                title : 'Email',
                width : 250,
                align : 'center'
            }, {
                field : 'caddress',
                title : '通讯地址',
                width : 280,
                align : 'center'
            }, {
                field : 'gg',
                title : '操作',
                width : 250,
                align : 'center',
                formatter : formatOper
            }, ] ],
            toolbar : [ {
                text : '导出查询结果',
                iconCls : 'icon-print',
                handler : function() {
                    getExcel();
                }
            } ],
            pageList : [ 1, 3, 5, 10 ],
            pageSize : 5,
            loadMSg : '正在加载中.....',

        });

    });

    function formatOper(val, row, index) {
        var gg = "";
        gg += '<img src="easyui/themes/icons/edit_add.png" onclick="adds()"  border="0px"/>';
        gg += '&nbsp;&nbsp;';
        gg += '<img src="easyui/themes/icons/edit_remove.png" onclick="removeit()"  border="0px"/>';
        return gg;
    }

    function whereselect() {
        var mm = "";

        mm += ",";
        mm += document.getElementById("type01").value;
        mm += ".";
        $(function() {
            $('#tab').datagrid({
                url : 'cagetAll.action?mm=' + mm,

            });
        });
    }

    function adds() {
        $('#add').window({
            title : '添加',
            width : 650,
            height : 210,
            modal : true,
        });
    }

    function getExcel() {
      location.href="caexportAll.action"; 
    }
    
    function save() {
        $.ajax({
            url : "caAdd.action",
            type : "post",
            dataType : "text",
            data : {
                cname : $('#cname').val(),
                csex : $('#csex').val(),
                cdepartment : $('#cdepartment').val(),
                cmobile:$('#cmobile').val(),
                cphone:$('#cphone').val(),
                cemail:$('#cemail').val(),
                caddress:$('#caddress').val()
            },success : function(data) {
               if(data){
                   alert("添加成功");
                    window.location.reload();
                    location.href="list.jsp";
               }
            } 
        });
        
    }
    
    //删除
    function removeit() {
        if (cid == 0) {
            alert("请选择一条记录");
            return;
        }
        $.messager.confirm('确认', '您确认想要删除记录吗？', function(r) {
            if (r) {
                $.ajax({
                    url : "cadelete.action",
                    type : "post",
                    datatype : "text",
                    data : {
                        cid : cid,
                    },
                    success : function(data) {
                        if (data == "true") {
                              // 删除成功后刷新页面
                            //window.location.reload();
                            $(function() {
                                $('#tab').datagrid({
                                    data : getData()
                                }).datagrid('clientPaging');
                            });
                            $.messager.alert('确定','删除成功'); 
                        } else {
                             alert("删除失败");
                        }
                    }
                });
            }
        });
    }
</script>
<script type="text/javascript">
    (function($) {
        function pagerFilter(data) {
            if ($.isArray(data)) {
                data = {
                    total : data.length,
                    rows : data
                }
            }
            var target = this;
            var dg = $(target);
            var state = dg.data('datagrid');
            var opts = dg.datagrid('options');
            if (!state.allRows) {
                state.allRows = (data.rows);
            }
            if (!opts.remoteSort && opts.sortName) {
                var names = opts.sortName.split(',');
                var orders = opts.sortOrder.split(',');
                state.allRows.sort(function(r1, r2) {
                    var r = 0;
                    for (var i = 0; i < names.length; i++) {
                        var sn = names[i];
                        var so = orders[i];
                        var col = $(target).datagrid('getColumnOption', sn);
                        var sortFunc = col.sorter || function(a, b) {
                            return a == b ? 0 : (a > b ? 1 : -1);
                        };
                        r = sortFunc(r1[sn], r2[sn]) * (so == 'asc' ? 1 : -1);
                        if (r != 0) {
                            return r;
                        }
                    }
                    return r;
                });
            }
            var start = (opts.pageNumber - 1) * parseInt(opts.pageSize);
            var end = start + parseInt(opts.pageSize);
            data.rows = state.allRows.slice(start, end);
            return data;
        }

        var loadDataMethod = $.fn.datagrid.methods.loadData;
        var deleteRowMethod = $.fn.datagrid.methods.deleteRow;
        $.extend($.fn.datagrid.methods, {
            clientPaging : function(jq) {
                return jq.each(function() {
                    var dg = $(this);
                    var state = dg.data('datagrid');
                    var opts = state.options;
                    opts.loadFilter = pagerFilter;
                    var onBeforeLoad = opts.onBeforeLoad;
                    opts.onBeforeLoad = function(param) {
                        state.allRows = null;
                        return onBeforeLoad.call(this, param);
                    }
                    var pager = dg.datagrid('getPager');
                    pager.pagination({
                        onSelectPage : function(pageNum, pageSize) {
                            opts.pageNumber = pageNum;
                            opts.pageSize = pageSize;
                            pager.pagination('refresh', {
                                pageNumber : pageNum,
                                pageSize : pageSizeze
                            });
                            dg.datagrid('loadData', state.allRows);
                        }
                    });
                    $(this).datagrid('loadData', state.data);
                    if (opts.url) {
                        $(this).datagrid('reload');
                    }
                });
            },
            loadData : function(jq, data) {
                jq.each(function() {
                    $(this).data('datagrid').allRows = null;
                });
                return loadDataMethod.call($.fn.datagrid.methods, jq, data);
            },
            deleteRow : function(jq, index) {
                return jq
                        .each(function() {
                            var row = $(this).datagrid('getRows')[index];
                            deleteRowMethod.call($.fn.datagrid.methods,
                                    $(this), index);
                            var state = $(this).data('datagrid');
                            if (state.options.loadFilter == pagerFilter) {
                                for (var i = 0; i < state.allRows.length; i++) {
                                    if (state.allRows[i] == row) {
                                        state.allRows.splice(i, 1);
                                        break;
                                    }
                                }
                                $(this).datagrid('loadData', state.allRows);
                            }
                        });
            },
            getAllRows : function(jq) {
                return jq.data('datagrid').allRows;
            }
        })
    })(jQuery);

    function getData() {
        $(document).ready(function() {
            $("#tab").datagrid({
                filedcid : 'cid',
                filedcname : 'cname',
                filedcsex : 'csex',
                filedcdepartment : 'cdepartment',
                filedcmobile : 'cmobile',
                filedcphone : 'cphone',
                filedcemail : 'cemail',
                filedcaddress : 'caddress'
            })
        });
    }

    $(function() {
        $('#tab').datagrid({
            data : getData()
        }).datagrid('clientPaging');
    });
</script>
</head>
<body>
    <h3 align="center">名片浏览与查询</h3>
    <div align="center" style="padding-bottom: 50px;">
        名片搜索:<input class="easyui-textbox" id="type01" name="cname">&nbsp;<input
            type="button" onclick="whereselect();" value="查询">

    </div>
    <div align="center">
        <input type="button" value="添加" onclick="adds();">&nbsp;<input
            type="button" value="全选">&nbsp;<input type="button"
            value="将所选择到回收站"> &nbsp;<input type="button" value="导入名片">&nbsp;<input
            type="button" value="导出查询结果" onclick="getExcel();">&nbsp;<input
            type="button" value="进入回收站">
    </div>
    <div align="center" style="padding-top: 10px;">
        <table id="tab">
        </table>
    </div>

    <div id="add" style="display: none;">
        <form method="post" id="ff" style="background-color: #F4F4F4">
            <table align="center" style="padding-top: 20px;">
                <tr>
                    <td>编号 &nbsp;&nbsp;<input class="easyui-textbox" name="cid"
                        id="cid" style=" 150px; height: 25px;" readonly="readonly">
                    </td>
                    <td>&nbsp;姓名 &nbsp;&nbsp;&nbsp;<input class="easyui-textbox"
                        name="cname" id="cname" style=" 150px; height: 25px;">
                    </td>
                </tr>
                <tr>
                    <td>性别 &nbsp;&nbsp;<select id="csex" name="csex" style=" 150px;">
                            <option value="--请选择--" selected="selected">--请选择--</option>
                            <option value="男">男</option>
                            <option value="女">女</option>
                    </select>
                    </td>
                    <td>&nbsp;单位 &nbsp;&nbsp;&nbsp;<input class="easyui-textbox"
                        name="cdepartment" id="cdepartment"
                        style=" 150px; height: 25px;">
                    </td>
                </tr>
                <tr>
                    <td>手机 &nbsp;&nbsp;<input class="easyui-textbox"
                        name="cmobile" id="cmobile" style=" 150px; height: 25px;">
                    </td>
                    <td>&nbsp;电话 &nbsp;&nbsp;&nbsp;<input class="easyui-textbox"
                        name="cphone" id="cphone" style=" 150px; height: 25px;">
                    </td>
                </tr>
                <tr>
                    <td>Email &nbsp;<input class="easyui-textbox" name="cemail"
                        id="cemail" style=" 150px; height: 25px;">
                    </td>
                    <td>通讯地址 <input class="easyui-textbox" name="caddress"
                        id="caddress" style=" 150px; height: 25px;">
                    </td>
                </tr>
            </table>
            <br>
            <div align="center">
                <input type="button" value="确定" onclick="save();">
                &nbsp;&nbsp; <input type="button" onclick="save2()" value="取消">
            </div>
        </form>
    </div>
</body>
</html>

然后数据库这边的话就没有展示了具体表结构可根据实体类来进行设计