用户列表Excel导出实例（POI）

最新推荐文章于 2024-05-17 16:40:45 发布

幸福是什么感觉呢

最新推荐文章于 2024-05-17 16:40:45 发布

阅读量632

点赞数 1

分类专栏：学习轨迹 javaEE java前端 java

本文链接：https://blog.csdn.net/zd305826520/article/details/51003435

版权

学习轨迹同时被 3 个专栏收录

34 篇文章 0 订阅

订阅专栏

java

33 篇文章 0 订阅

订阅专栏

javaEE

32 篇文章 0 订阅

订阅专栏

1.jsp页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="s" uri="/struts-tags"%>
<html>
<head>
    <title>用户管理</title>
    <%@include file="/common/header.jsp" %>
    <script type="text/javascript">
          //全选、全反选
        function doSelectAll(){
            // jquery 1.6 前
            //$("input[name=selectedRow]").attr("checked", $("#selAll").is(":checked"));
            //prop jquery 1.6+建议使用
            $("input[name=selectedRow]").prop("checked", $("#selAll").is(":checked"));        
        }
          //添加
          function doAdd(){
              document.forms[0].action="${basePath}nsfw/user_addUI.action";
              document.forms[0].submit();
          }
          //编辑
          function doEdit(id){
              document.forms[0].action="${basePath}nsfw/user_editUI.action?user.id=" + id;
              document.forms[0].submit();
          }
        //删除
          function doDelete(id){
              document.forms[0].action="${basePath}nsfw/user_delete.action?user.id=" + id;
              document.forms[0].submit();
          }
      //多选删除
          function doDeleteAll(){
              document.forms[0].action="${basePath}nsfw/user_deleteSelected.action";
              document.forms[0].submit();
          }
      //用户列表导出
      function doExportExcel(){
          window.open("${basePath}nsfw/user_exportExcel.action");
      }
    </script>
</head>
<body class="rightBody">
<form name="form1" action="" method="post" enctype="multipart/form-data">
    <div class="p_d_1">
        <div class="p_d_1_1">
            <div class="content_info">
                <div class="c_crumbs"><div><b></b><strong>用户管理</strong></div> </div>
                <div class="search_art">
                    <li>
                        用户名：<s:textfield name="user.name" cssClass="s_text" id="userName"  cssStyle="width:160px;"/>
                    </li>
                    <li><input type="button" class="s_button" value="搜 索" onclick="doSearch()"/></li>
                    <li style="float:right;">
                        <input type="button" value="新增" class="s_button" onclick="doAdd()"/>&nbsp;
                        <input type="button" value="删除" class="s_button" onclick="doDeleteAll()"/>&nbsp;
                        <input type="button" value="导出" class="s_button" onclick="doExportExcel()"/>&nbsp;
                        <input name="userExcel" type="file"/>
                        <input type="button" value="导入" class="s_button" onclick="doImportExcel()"/>&nbsp;

                    </li>
                </div>

                <div class="t_list" style="margin:0px; border:0px none;">
                    <table width="100%" border="0">
                        <tr class="t_tit">
                            <td width="30" align="center"><input type="checkbox" id="selAll" onclick="doSelectAll()" /></td>
                            <td width="140" align="center">用户名</td>
                            <td width="140" align="center">帐号</td>
                            <td width="160" align="center">所属部门</td>
                            <td width="80" align="center">性别</td>
                            <td align="center">电子邮箱</td>
                            <td width="100" align="center">操作</td>
                        </tr>
                        <s:iterator value="userList" status="st">
                            <tr <s:if test="#st.odd">bgcolor="f8f8f8"</s:if> >
                                <td align="center"><input type="checkbox" name="selectedRow" value='<s:property value="id"/>'/></td>
                                <td align="center"><s:property value="name"/></td>
                                <td align="center"><s:property value="account"/></td>
                                <td align="center"><s:property value="dept"/></td>
                                <td align="center"><s:property value="gender?'男':'女'"/></td>
                                <td align="center"><s:property value="email"/></td>
                                <td align="center">
                                    <a href="javascript:doEdit('<s:property value="id"/>')">编辑</a>
                                    <a href="javascript:doDelete('<s:property value="id"/>')">删除</a>
                                </td>
                            </tr>
                        </s:iterator>
                    </table>
                </div>
            </div>
        <div class="c_pate" style="margin-top: 5px;">
        <table width="100%" class="pageDown" border="0" cellspacing="0"
            cellpadding="0">
            <tr>
                <td align="right">
                     总共1条记录，当前第 1 页，共 1 页 &nbsp;&nbsp;
                            <a href="#">上一页</a>&nbsp;&nbsp;<a href="#">下一页</a>
                    到&nbsp;<input type="text" style="width: 30px;" onkeypress="if(event.keyCode == 13){doGoPage(this.value);}" min="1"
                    max="" value="1" /> &nbsp;&nbsp;
                </td>
            </tr>
        </table>    
        </div>
        </div>
    </div>
</form>

</body>
</html>

2.控制器action的核心代码片段

//导出用户列表
    public void exportExcel(){
        try {
            //1.查找用户列表
            userList = userService.findObjects();
            //2.导出
            HttpServletResponse response = ServletActionContext.getResponse();
            response.setContentType("application/x-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(),"ISO-8859-1"));
            ServletOutputStream outputStream = response.getOutputStream();
            userService.exportExcel(userList,outputStream);
            if(outputStream != null){
                outputStream.close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

3.业务测（service）代码

@Override
    public void exportExcel(List<User> userList, ServletOutputStream outputStream) {
        ExcelUtil.exportUserExcel(userList, outputStream);
    }

4.抽取的业务层逻辑代码（抽取成静态的工具类，直接调用）

package cn.buaa.core.util;

import java.util.List;

import javax.servlet.ServletOutputStream;

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

import cn.buaa.nsfw.user.entity.User;

public class ExcelUtil {

    /**
     * 用户列表导出
     * @param userList 用户列表
     * @param outputStream 输出流
     */
    public static void exportUserExcel(List<User> userList, ServletOutputStream outputStream) {

        try {
            //1、创建工作簿
             HSSFWorkbook workbook = new HSSFWorkbook(); 
            //1.1、创建合并单元格对象
             CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);
             //1.2、头标题样式
             HSSFCellStyle style1 = createCellStyle(workbook,(short)16);
            //1.3、列标题样式
             HSSFCellStyle style2 = createCellStyle(workbook,(short)13);

            //2、创建工作表
             HSSFSheet sheet = workbook.createSheet("用户列表");
            //2.1、加载合并单元格对象
             sheet.addMergedRegion(cellRangeAddress);
             //设置默认列宽
             sheet.setDefaultColumnWidth(25);
             
            //3、创建行
            //3.1、创建头标题行；并且设置头标题
             HSSFRow row1 = sheet.createRow(0);
             HSSFCell cell1 = row1.createCell(0);
             //加载单元格样式
             cell1.setCellStyle(style1);
             cell1.setCellValue("用户列表");
            //3.2、创建列标题行；并且设置列标题
             HSSFRow row2 = sheet.createRow(1);
             String[] titles = {"用户名","账号","所属部门","性别","电子邮箱"};
            for(int i = 0;i<titles.length;i++){
                 HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                 cell2.setCellStyle(style2);
                cell2.setCellValue(titles[i]);
            }
            
            //4、操作单元格；将用户列表写入excel
            if(userList != null){
                for(int j = 0;j<userList.size();j++){
                    HSSFRow row = sheet.createRow(j+2);
                    HSSFCell cell11 = row.createCell(0);
                    cell11.setCellValue(userList.get(j).getName());
                    HSSFCell cell12 = row.createCell(1);
                    cell12.setCellValue(userList.get(j).getAccount());
                    HSSFCell cell13 = row.createCell(2);
                    cell13.setCellValue(userList.get(j).getDept());
                    HSSFCell cell14 = row.createCell(3);
                    cell14.setCellValue(userList.get(j).isGender() ? "男" : "女");
                    HSSFCell cell15 = row.createCell(4);
                    cell15.setCellValue(userList.get(j).getEmail());
                }
            }
            
            //5、输出
            workbook.write(outputStream);
            workbook.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 创建单元格样式
     * @param workbook
     * @param fontSize
     * @return
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle style = workbook.createCellStyle();
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  //水平居中
         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  //垂直居中
         //创建字体
         HSSFFont font = workbook.createFont();
         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗字体
         font.setFontHeightInPoints(fontSize);
         //加载字体
         style.setFont(font);
        return style;
    }
}