Java使用POI导出excel（下）——实例与小技巧

【更新】：thinkgem的导出工具类：

/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导出Excel文件（导出“XLSX”格式，支持大数据量导出   @see org.apache.poi.ss.SpreadsheetVersion）
 * @author ThinkGem
 * @version 2013-04-21
 */
public class ExportExcel {
    
    private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
            
    /**
     * 工作薄对象
     */
    private SXSSFWorkbook wb;
    
    /**
     * 工作表对象
     */
    private Sheet sheet;
    
    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;
    
    /**
     * 当前行号
     */
    private int rownum;
    
    /**
     * 注解列表（Object[]{ ExcelField, Field/Method }）
     */
    List<Object[]> annotationList = Lists.newArrayList();
    
    /**
     * 构造函数
     * @param title 表格标题，传“空值”，表示无标题
     * @param cls 实体对象，通过annotation.ExportField获取标题
     */
    public ExportExcel(String title, Class<?> cls){
        this(title, cls, 1);
    }
    
    /**
     * 构造函数
     * @param title 表格标题，传“空值”，表示无标题
     * @param cls 实体对象，通过annotation.ExportField获取标题
     * @param type 导出类型（1:导出数据；2：导出模板）
     * @param groups 导入分组
     */
    public ExportExcel(String title, Class<?> cls, int type, int... groups){
        // Get annotation field 
        Field[] fs = cls.getDeclaredFields();
        for (Field f : fs){
            ExcelField ef = f.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type()==0 || ef.type()==type)){
                if (groups!=null && groups.length>0){
                    boolean inGroup = false;
                    for (int g : groups){
                        if (inGroup){
                            break;
                        }
                        for (int efg : ef.groups()){
                            if (g == efg){
                                inGroup = true;
                                annotationList.add(new Object[]{ef, f});
                                break;
                            }
                        }
                    }
                }else{
                    annotationList.add(new Object[]{ef, f});
                }
            }
        }
        // Get annotation method
        Method[] ms = cls.getDeclaredMethods();
        for (Method m : ms){
            ExcelField ef = m.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type()==0 || ef.type()==type)){
                if (groups!=null && groups.length>0){
                    boolean inGroup = false;
                    for (int g : groups){
                        if (inGroup){
                            break;
                        }
                        for (int efg : ef.groups()){
                            if (g == efg){
                                inGroup = true;
                                annotationList.add(new Object[]{ef, m});
                                break;
                            }
                        }
                    }
                }else{
                    annotationList.add(new Object[]{ef, m});
                }
            }
        }
        // Field sorting
        Collections.sort(annotationList, new Comparator<Object[]>() {
            public int compare(Object[] o1, Object[] o2) {
                return new Integer(((ExcelField)o1[0]).sort()).compareTo(
                        new Integer(((ExcelField)o2[0]).sort()));
            };
        });
        // Initialize
        List<String> headerList = Lists.newArrayList();
        for (Object[] os : annotationList){
            String t = ((ExcelField)os[0]).title();
            // 如果是导出，则去掉注释
            if (type==1){
                String[] ss = StringUtils.split(t, "**", 2);
                if (ss.length==2){
                    t = ss[0];
                }
            }
            headerList.add(t);
        }
        initialize(title, headerList);
    }
    
    /**
     * 构造函数
     * @param title 表格标题，传“空值”，表示无标题
     * @param headers 表头数组
     */
    public ExportExcel(String title, String[] headers) {
        initialize(title, Lists.newArrayList(headers));
    }
    
    /**
     * 构造函数
     * @param title 表格标题，传“空值”，表示无标题
     * @param headerList 表头列表
     */
    public ExportExcel(String title, List<String> headerList) {
        initialize(title, headerList);
    }
    
    /**
     * 初始化函数
     * @param title 表格标题，传“空值”，表示无标题
     * @param headerList 表头列表
     */
    private void initialize(String title, List<String> headerList) {
        this.wb = new SXSSFWorkbook(500);
        this.sheet = wb.createSheet("Export");
        this.styles = createStyles(wb);
        // Create title
        if (StringUtils.isNotBlank(title)){
            Row titleRow = sheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
        }
        // Create header
        if (headerList == null){
            throw new RuntimeException("headerList not null!");
        }
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints(16);
        for (int i = 0; i < headerList.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(styles.get("header"));
            String[] ss = StringUtils.split(headerList.get(i), "**", 2);
            if (ss.length==2){
                cell.setCellValue(ss[0]);
                Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
                        new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
                comment.setString(new XSSFRichTextString(ss[1]));
                cell.setCellComment(comment);
            }else{
                cell.setCellValue(headerList.get(i));
            }
            sheet.autoSizeColumn(i);
        }
        for (int i = 0; i < headerList.size(); i++) {  
            int colWidth = sheet.getColumnWidth(i)*2;
            sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
        }
        log.debug("Initialize success.");
    }
    
    /**
     * 创建表格样式
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);
        
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        styles.put("data3", style);
        
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
//        style.setWrapText(true);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);
        
        return styles;
    }

    /**
     * 添加一行
     * @return 行对象
     */
    public Row addRow(){
        return sheet.createRow(rownum++);
    }
    

    /**
     * 添加一个单元格
     * @param row 添加的行
     * @param column 添加列号
     * @param val 添加值
     * @return 单元格对象
     */
    public Cell addCell(Row row, int column, Object val){
        return this.addCell(row, column, val, 0, Class.class);
    }
    
    /**
     * 添加一个单元格
     * @param row 添加的行
     * @param column 添加列号
     * @param val 添加值
     * @param align 对齐方式（1：靠左；2：居中；3：靠右）
     * @return 单元格对象
     */
    public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
        Cell cell = row.createCell(column);
        String cellFormatString = "@";
        try {
            if(val == null){
                cell.setCellValue("");
            }else if(fieldType != Class.class){
                cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
            }else{
                if(val instanceof String) {
                    cell.setCellValue((String) val);
                }else if(val instanceof Integer) {
                    cell.setCellValue((Integer) val);
                    cellFormatString = "0";
                }else if(val instanceof Long) {
                    cell.setCellValue((Long) val);
                    cellFormatString = "0";
                }else if(val instanceof Double) {
                    cell.setCellValue((Double) val);
                    cellFormatString = "0.00";
                }else if(val instanceof Float) {
                    cell.setCellValue((Float) val);
                    cellFormatString = "0.00";
                }else if(val instanceof Date) {
                    cell.setCellValue((Date) val);
                    cellFormatString = "yyyy-MM-dd HH:mm";
                }else {
                    cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
                        "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
                }
            }
            if (val != null){
                CellStyle style = styles.get("data_column_"+column);
                if (style == null){
                    style = wb.createCellStyle();
                    style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
                    style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
                    styles.put("data_column_" + column, style);
                }
                cell.setCellStyle(style);
            }
        } catch (Exception ex) {
            log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
            cell.setCellValue(val.toString());
        }
        return cell;
    }

    /**
     * 添加数据（通过annotation.ExportField添加数据）
     * @return list 数据列表
     */
    public <E> ExportExcel setDataList(List<E> list){
        for (E e : list){
            int colunm = 0;
            Row row = this.addRow();
            StringBuilder sb = new StringBuilder();
            for (Object[] os : annotationList){
                ExcelField ef = (ExcelField)os[0];
                Object val = null;
                // Get entity value
                try{
                    if (StringUtils.isNotBlank(ef.value())){
                        val = Reflections.invokeGetter(e, ef.value());
                    }else{
                        if (os[1] instanceof Field){
                            val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
                        }else if (os[1] instanceof Method){
                            val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
                        }
                    }
                    // If is dict, get dict label
                    if (StringUtils.isNotBlank(ef.dictType())){
                        val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
                    }
                }catch(Exception ex) {
                    // Failure to ignore
                    log.info(ex.toString());
                    val = "";
                }
                this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
                sb.append(val + ", ");
            }
            log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
        }
        return this;
    }
    
    /**
     * 输出数据流
     * @param os 输出数据流
     */
    public ExportExcel write(OutputStream os) throws IOException{
        wb.write(os);
        return this;
    }
    
    /**
     * 输出到客户端
     * @param fileName 输出文件名
     */
    public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
        write(response.getOutputStream());
        return this;
    }
    
    /**
     * 输出到文件
     * @param fileName 输出文件名
     */
    public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
        FileOutputStream os = new FileOutputStream(name);
        this.write(os);
        return this;
    }
    
    /**
     * 清理临时文件
     */
    public ExportExcel dispose(){
        wb.dispose();
        return this;
    }
    
//    /**
//     * 导出测试
//     */
//    public static void main(String[] args) throws Throwable {
//        
//        List<String> headerList = Lists.newArrayList();
//        for (int i = 1; i <= 10; i++) {
//            headerList.add("表头"+i);
//        }
//        
//        List<String> dataRowList = Lists.newArrayList();
//        for (int i = 1; i <= headerList.size(); i++) {
//            dataRowList.add("数据"+i);
//        }
//        
//        List<List<String>> dataList = Lists.newArrayList();
//        for (int i = 1; i <=1000000; i++) {
//            dataList.add(dataRowList);
//        }
//
//        ExportExcel ee = new ExportExcel("表格标题", headerList);
//        
//        for (int i = 0; i < dataList.size(); i++) {
//            Row row = ee.addRow();
//            for (int j = 0; j < dataList.get(i).size(); j++) {
//                ee.addCell(row, j, dataList.get(i).get(j));
//            }
//        }
//        
//        ee.writeFile("target/export.xlsx");
//
//        ee.dispose();
//        
//        log.debug("Export success.");
//        
//    }

}

 

一、导出表格样式

　　前台使用easyui（此部分知识待补充）

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Basic DataGrid - jQuery EasyUI Demo</title>
    <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">
    <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">
    <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>
    <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
    <script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>
    <script>
        var url;
        function deleteUser(){
            var row=$('#dg').datagrid('getSelected');
            if(row){
                $.messager.confirm("系统提示","您确定要删除这条记录吗?",function(r){
                    if(r){
                        $.post('user!delete',{delId:row.id},function(result){
                            if(result.success){
                                $.messager.alert("系统提示","已成功删除这条记录!");
                                $("#dg").datagrid("reload");
                            }else{
                                $.messager.alert("系统提示",result.errorMsg);
                            }
                        },'json');
                    }
                });
            }
        }
        
        function newUser(){
            $("#dlg").dialog('open').dialog('setTitle','添加用户');
            $('#fm').form('clear');
            url='user!save';
        }
        
        
        function editUser(){
            var row=$('#dg').datagrid('getSelected');
            if(row){
                $("#dlg").dialog('open').dialog('setTitle','编辑用户');
                $("#name").val(row.name);
                $("#phone").val(row.phone);
                $("#email").val(row.email);
                $("#qq").val(row.qq);
                url='user!save?id='+row.id;
            }
        }
        
        
        function saveUser(){
            $('#fm').form('submit',{
                url:url,
                onSubmit:function(){
                    return $(this).form('validate');
                },
                success:function(result){
                    var result=eval('('+result+')');
                    if(result.errorMsg){
                        $.messager.alert("系统提示",result.errorMsg);
                        return;
                    }else{
                        $.messager.alert("系统提示","保存成功");
                        $('#dlg').dialog('close');
                        $("#dg").datagrid("reload");
                    }
                }
            });
        }
        
        
        function exportUser(){
            window.open('user!export')
        }
    
        
        
    </script>
</head>
<body>
    <table id="dg" title="用户管理" class="easyui-datagrid" style="width:700px;height:365px"
            url="user!list"
            toolbar="#toolbar" pagination="true"
            rownumbers="true" fitColumns="true" singleSelect="true">
        <thead>
            <tr>
                <th field="id" width="50" hidden="true">编号</th>
                <th field="name" width="50">姓名</th>
                <th field="phone" width="50">电话</th>
                <th field="email" width="50">Email</th>
                <th field="qq" width="50">QQ</th>
            </tr>
        </thead>
    </table>
    <div id="toolbar">
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">添加用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">编辑用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="deleteUser()">删除用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>
    </div>
    
    <div id="dlg" class="easyui-dialog" style="width:400px;height:250px;padding:10px 20px"
            closed="true" buttons="#dlg-buttons">
        <form id="fm" method="post">
            <table cellspacing="10px;">
                <tr>
                    <td>姓名：</td>
                    <td><input id="name"  name="user.name" class="easyui-validatebox" required="true" style="width: 200px;"></td>
                </tr>
                <tr>
                    <td>联系电话：</td>
                    <td><input id="phone"  name="user.phone" class="easyui-validatebox" required="true" style="width: 200px;"></td>
                </tr>
                <tr>
                    <td>Email：</td>
                    <td><input id="email"  name="user.email" class="easyui-validatebox" validType="email" required="true" style="width: 200px;"></td>
                </tr>
                <tr>
                    <td>QQ：</td>
                    <td><input id="qq" name="user.qq" class="easyui-validatebox" required="true" style="width: 200px;"></td>
                </tr>
            </table>
        </form>
    </div>
    
    <div id="dlg-buttons">
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="saveUser()">保存</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg').dialog('close')">关闭</a>
    </div>
    
</body>
</html>

　　

 

　　看导出部分：

 <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>

function exportUser(){
            window.open('user!export')
        }

　　后台采用的是struts2（稍旧）

public String export()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            Workbook wb=new HSSFWorkbook();
            String headers[]={"编号","姓名","电话","Email","QQ"};
            ResultSet rs=userDao.userList(con, null);
            ExcelUtil.fillExcelData(rs, wb, headers);
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    

　　这里我们采用值得借鉴的 util 的封装思想，封装两个 util ，编写相关的 静态方法！

　　ExcelUtil：

public class ExcelUtil {

    public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
        int rowIndex=0;
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(rowIndex++);
        for(int i=0;i<headers.length;i++){
            row.createCell(i).setCellValue(headers[i]);
        }
        while(rs.next()){
            row=sheet.createRow(rowIndex++);
            for(int i=0;i<headers.length;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
    }
}

　　ResponseUtil:

public class ResponseUtil {

    public static void write(HttpServletResponse response,Object o)throws Exception{
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.print(o.toString());
        out.flush();
        out.close();
    }
    
    public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

}

 二、使用模板导出数据

　　首先在项目中放入一个模板（提前准备好的模板）

　　模板样式如下：

　　//大概原理就是读取模板，然后往里面塞数据（无需创建工作簿wb）

　　实际代码操作

　　前台页面还是采用上面的easyui添加模板导出按钮：

<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser2()">用模版导出用户</a>

　　

function exportUser2(){
            window.open('user!export2');
        }

　　后台struts2不变

package com.java1234.action;

import java.sql.Connection;
import java.sql.ResultSet;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

import com.java1234.dao.UserDao;
import com.java1234.model.PageBean;
import com.java1234.model.User;
import com.java1234.util.DbUtil;
import com.java1234.util.ExcelUtil;
import com.java1234.util.JsonUtil;
import com.java1234.util.ResponseUtil;
import com.java1234.util.StringUtil;
import com.opensymphony.xwork2.ActionSupport;

public class UserAction extends ActionSupport {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    private String page;
    private String rows;
    private String id;
    private User user;
    private String delId;
    
    
    public String getPage() {
        return page;
    }
    public void setPage(String page) {
        this.page = page;
    }
    public String getRows() {
        return rows;
    }
    public void setRows(String rows) {
        this.rows = rows;
    }
    
    public String getDelId() {
        return delId;
    }
    public void setDelId(String delId) {
        this.delId = delId;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }


    DbUtil dbUtil=new DbUtil();
    UserDao userDao=new UserDao();
    
    public String list()throws Exception{
        Connection con=null;
        PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));
        try{
            con=dbUtil.getCon();
            JSONObject result=new JSONObject();
            JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean));
            int total=userDao.userCount(con);
            result.put("rows", jsonArray);
            result.put("total", total);
            ResponseUtil.write(ServletActionContext.getResponse(),result);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String save()throws Exception{
        if(StringUtil.isNotEmpty(id)){
            user.setId(Integer.parseInt(id));
        }
        Connection con=null;
        try{
            con=dbUtil.getCon();
            int saveNums=0;
            JSONObject result=new JSONObject();
            if(StringUtil.isNotEmpty(id)){
                saveNums=userDao.userModify(con, user);
            }else{
                saveNums=userDao.userAdd(con, user);
            }
            if(saveNums>0){
                result.put("success", "true");
            }else{
                result.put("success", "true");
                result.put("errorMsg", "保存失败");
            }
            ResponseUtil.write(ServletActionContext.getResponse(), result);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String delete()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            JSONObject result=new JSONObject();
            int delNums=userDao.userDelete(con, delId);
            if(delNums==1){
                result.put("success", "true");
            }else{
                result.put("errorMsg", "删除失败");
            }
            ResponseUtil.write(ServletActionContext.getResponse(), result);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    
    public String export()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            Workbook wb=new HSSFWorkbook();
            String headers[]={"编号","姓名","电话","Email","QQ"};
            ResultSet rs=userDao.userList(con, null);
            ExcelUtil.fillExcelData(rs, wb, headers);
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String export2()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            ResultSet rs=userDao.userList(con, null);
            Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    

    
    
    
}

　　核心方法是：

public String export2()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            ResultSet rs=userDao.userList(con, null);
            Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }

　　模板稍有不同：

package com.java1234.util;

import java.io.InputStream;
import java.sql.ResultSet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

    public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
        int rowIndex=0;
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(rowIndex++);
        for(int i=0;i<headers.length;i++){
            row.createCell(i).setCellValue(headers[i]);
        }
        while(rs.next()){
            row=sheet.createRow(rowIndex++);
            for(int i=0;i<headers.length;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
    }
    
    public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
        InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);
        POIFSFileSystem fs=new POIFSFileSystem(inp);
        Workbook wb=new HSSFWorkbook(fs);
        Sheet sheet=wb.getSheetAt(0);
        // 获取列数
        int cellNums=sheet.getRow(0).getLastCellNum();
        int rowIndex=1;
        while(rs.next()){
            Row row=sheet.createRow(rowIndex++);
            for(int i=0;i<cellNums;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
        return wb;
    }
}

 　　ExcelUtils:

    public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
        InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);
        POIFSFileSystem fs=new POIFSFileSystem(inp);
        Workbook wb=new HSSFWorkbook(fs);
        Sheet sheet=wb.getSheetAt(0);
        // 获取列数
        int cellNums=sheet.getRow(0).getLastCellNum();
        int rowIndex=1;
        while(rs.next()){
            Row row=sheet.createRow(rowIndex++);
            for(int i=0;i<cellNums;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
        return wb;
    }

 　　//注意流得读取形式

　　//注意是从第二列开始塞数据（第一列是表头）

　　ResponseUtils不变：

package com.java1234.util;

import java.io.OutputStream;
import java.io.PrintWriter;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;


public class ResponseUtil {

    public static void write(HttpServletResponse response,Object o)throws Exception{
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.print(o.toString());
        out.flush();
        out.close();
    }
    
    public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

}

 三、导入用户

　　前端的对话框的代码：

 

   <form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">
            <table>
                <tr>
                    <td>下载模版：</td>
                    <td><a href="javascript:void(0)" class="easyui-linkbutton"  onclick="downloadTemplate()">导入模版</a></td>
                </tr>
                <tr>
                    <td>上传文件：</td>
                    <td><input type="file" name="userUploadFile"></td>
                </tr>
            </table>
        </form>

 

　　后台：

package com.java1234.action;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

import com.java1234.dao.UserDao;
import com.java1234.model.PageBean;
import com.java1234.model.User;
import com.java1234.util.DbUtil;
import com.java1234.util.ExcelUtil;
import com.java1234.util.JsonUtil;
import com.java1234.util.ResponseUtil;
import com.java1234.util.StringUtil;
import com.opensymphony.xwork2.ActionSupport;

public class UserAction extends ActionSupport {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    private String page;
    private String rows;
    private String id;
    private User user;
    private String delId;
    
    private File userUploadFile;
    
    public String getPage() {
        return page;
    }
    public void setPage(String page) {
        this.page = page;
    }
    public String getRows() {
        return rows;
    }
    public void setRows(String rows) {
        this.rows = rows;
    }
    
    public String getDelId() {
        return delId;
    }
    public void setDelId(String delId) {
        this.delId = delId;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    
    


    public File getUserUploadFile() {
        return userUploadFile;
    }
    public void setUserUploadFile(File userUploadFile) {
        this.userUploadFile = userUploadFile;
    }




    DbUtil dbUtil=new DbUtil();
    UserDao userDao=new UserDao();
    
    public String list()throws Exception{
        Connection con=null;
        PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));
        try{
            con=dbUtil.getCon();
            JSONObject result=new JSONObject();
            JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean));
            int total=userDao.userCount(con);
            result.put("rows", jsonArray);
            result.put("total", total);
            ResponseUtil.write(ServletActionContext.getResponse(),result);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String save()throws Exception{
        if(StringUtil.isNotEmpty(id)){
            user.setId(Integer.parseInt(id));
        }
        Connection con=null;
        try{
            con=dbUtil.getCon();
            int saveNums=0;
            JSONObject result=new JSONObject();
            if(StringUtil.isNotEmpty(id)){
                saveNums=userDao.userModify(con, user);
            }else{
                saveNums=userDao.userAdd(con, user);
            }
            if(saveNums>0){
                result.put("success", "true");
            }else{
                result.put("success", "true");
                result.put("errorMsg", "保存失败");
            }
            ResponseUtil.write(ServletActionContext.getResponse(), result);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String delete()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            JSONObject result=new JSONObject();
            int delNums=userDao.userDelete(con, delId);
            if(delNums==1){
                result.put("success", "true");
            }else{
                result.put("errorMsg", "删除失败");
            }
            ResponseUtil.write(ServletActionContext.getResponse(), result);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    
    public String export()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            Workbook wb=new HSSFWorkbook();
            String headers[]={"编号","姓名","电话","Email","QQ"};
            ResultSet rs=userDao.userList(con, null);
            ExcelUtil.fillExcelData(rs, wb, headers);
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    
    public String export2()throws Exception{
        Connection con=null;
        try {
            con=dbUtil.getCon();
            ResultSet rs=userDao.userList(con, null);
            Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                dbUtil.closeCon(con);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }
    

    public String upload()throws Exception{
        POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
        HSSFWorkbook wb=new HSSFWorkbook(fs);
        HSSFSheet hssfSheet=wb.getSheetAt(0);  // 获取第一个Sheet页
        if(hssfSheet!=null){
            for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
                HSSFRow hssfRow=hssfSheet.getRow(rowNum);
                if(hssfRow==null){
                    continue;
                }
                User user=new User();
                user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
                user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
                user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
                user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
                Connection con=null;
                try{
                    con=dbUtil.getCon();
                    userDao.userAdd(con, user);
                }catch(Exception e){
                    e.printStackTrace();
                }finally{
                    dbUtil.closeCon(con);
                }
            }
        }
        JSONObject result=new JSONObject();
        result.put("success", "true");
        ResponseUtil.write(ServletActionContext.getResponse(), result);
        return null;
    }
    
    
    
}

　　核心方法：

    public String upload()throws Exception{
        POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
        HSSFWorkbook wb=new HSSFWorkbook(fs);
        HSSFSheet hssfSheet=wb.getSheetAt(0);  // 获取第一个Sheet页
        if(hssfSheet!=null){
            for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
                HSSFRow hssfRow=hssfSheet.getRow(rowNum);
                if(hssfRow==null){
                    continue;
                }
                User user=new User();
                user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
                user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
                user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
                user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
                Connection con=null;
                try{
                    con=dbUtil.getCon();
                    userDao.userAdd(con, user);
                }catch(Exception e){
                    e.printStackTrace();
                }finally{
                    dbUtil.closeCon(con);
                }
            }
        }
        JSONObject result=new JSONObject();
        result.put("success", "true");
        ResponseUtil.write(ServletActionContext.getResponse(), result);
        return null;
    }

 　　其实核心呢就是获取到文件（上传的文件，进行遍历，加入数据库，再显示，注意去除表头，从第二行开始）