export导出数据

第一步在SRC目录下建立，内容就是你要映射的字段
terminalExport.config

第二步：
建立你要导出的MODE BEEN

package com.ccs.terminal.model;
/*---------------------------------------------------------------------------------------------------------------------------
 * create by zyh on 2016-05-31 version V2016_1.0 for 通话记录导出实体BEEN                     -------
 * ------------------------------------------------------------------------------------------------------------------------*/
public class ActionListExport {
    private String terminalKey;//设备号
    private String phoneNumber;//电话号码
    private String actionType;//动作
    private String beginDate;//通话开始时间
    private String endDate;//通话结束时间
    public String getTerminalKey() {
        return terminalKey;
    }
    public void setTerminalKey(String terminalKey) {
        this.terminalKey = terminalKey;
    }
    public String getPhoneNumber() {
        return phoneNumber;
    }
    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }
    public String getActionType() {
        return actionType;
    }
    public void setActionType(String actionType) {
        this.actionType = actionType;
    }
    public String getBeginDate() {
        return beginDate;
    }
    public void setBeginDate(String beginDate) {
        this.beginDate = beginDate;
    }
    public String getEndDate() {
        return endDate;
    }
    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }
}

第三步 在mybatis.xml文件中写好返回的字段MAP

    <!-- 终端信息映射 -->
  <resultMap id="ExportMap" type="com.ccs.terminal.model.ActionListExport" >
      <result column="phone_number" property="phoneNumber" jdbcType="VARCHAR" />
      <result column="action_type" property="actionType" jdbcType="VARCHAR" />
      <result column="begin_Date" property="beginDate" jdbcType="TIMESTAMP" />
      <result column="end_date" property="endDate" jdbcType="TIMESTAMP" />
       <result column="terminal_key" property="terminalKey" jdbcType="VARCHAR" />
  </resultMap>

    <!-- 查询所有终端数据 -->
  <select id="exportCallRecord"  resultMap="ExportMap"  parameterType="java.util.Map" >
       select   <include refid="Base_Column_List_export" /> from shrg_action_list_t
    </select>

springmvc control

        /**
         * 导出来通话记录
         * @version V2016_1.0
         * @author zyh
         * @Date 2016年5月31日
         * @param request  void
         * @param response void
         *
         */
         @RequestMapping(value="/getTerminalExport")    
         public  void  getTerminalExport(HttpServletResponse response,HttpServletRequest  request){
             Long userId = null;
             Map<String,Object> map = new HashMap<String,Object>();
                try {
                        userId = StringUtil.getUserId(request);
                        String downloadPath = StringUtil.downloadPath(userId,request);
                        String downloadFileName=StringUtil.getDateTimeFileName(Constant.TERMINAL_CALL_RECORD_EXPORT);
                        map.put(Constant.USER_ID, userId);
                        List<ActionListExport> list = actionListService.exportCallRecord(map);
                        ExcelMain.exportData(list, StringUtil.getProperties(Constant.TERMINAL_CALL_RECORD_EXPORT_CONFIG),downloadPath , downloadFileName, ActionListExport.class);
                        StringUtil.downloadLocalFile(userId, downloadFileName, Constant.XLS_EXCEL, request, response);
                }catch(Exception e){
                        int resultCode = sysLogService.writeLog(SysLogUtil.exceptionLogMap(this, Constant.ERROR_LEVEL, Constant.TERMINAL_MANAGER, Constant.EXPORT_TERMINAL, e,userId));
                        StringUtil.exceptionHandle(response,resultCode);
                }
         }

上面方法中的几个方法

    /**
     *  下载文件的文件夹路径
     * @version V2016_1.0
     * @author caoxiaoxiong
     * @Date 2016年5月31日
     * @param userId 用户id
     * @param fileName 文件名
     * @param fileType 文件类型
     * @param request
     * @return String
     *
     */
    public static String downloadPath(Long userId,HttpServletRequest request){
        StringBuffer path = new StringBuffer();
        path.append(request.getSession().getServletContext().getRealPath("")).append(Constant.FILE_DOWNLOAD_PATH)
        .append(Constant.PATH_SEPARATOR_SLASH).append(userId).append(Constant.PATH_SEPARATOR_SLASH);

        return path.toString();
    }

         /**
          * 
          * @version V2016_1.0
          * @author caoxiaoxiong
     * @Date 2016年5月31日
          * @param simpleFileName 简单文件名
          * @return String
          *
          */
         public static String getDateTimeFileName(String simpleFileName){
             return new StringBuffer(simpleFileName).append(DateTimeUtil.getFileDateTimeStr()).toString();
         }

    /**
     * 导出excel xls数据
     * @version V2016_1.0
     * @author caoxiaoxiong
     * @Date 2016年5月31日
     * @param list
     * @param properties
     * @param path
     * @param fileName
     * @param clazz void
     *
     */
    public static   <T> void exportData(List<T> list,Properties properties,String path,String fileName,Class<T> clazz){
            //存储为数组类型
            String data[][] = ExcelImportExport.parseLand(list, clazz, properties);
            //文件不存在就创建
            if(!new File(path).exists()){
                new File(path).mkdirs();
            }
            ExcelImportExport.writeFile(path, fileName,new StringBuffer(Constant.SPOT).append(Constant.XLS_EXCEL).toString(),data);
    }
}

    /**
     * 下载本地文件，支持各种文件类型
     * @version V2016_1.0
     * @author caoxiaoxiong
     * @Date 2016年5月31日
     * @param userId 用户id
     * @param fileName 文件名
     * @param fileType 文件类型
     * @param request   
     * @param response void
     *
     */
    public  static void downloadLocalFile(Long userId,String fileName,String fileType,HttpServletRequest request,HttpServletResponse response) throws Exception{
                BufferedInputStream bufferedInputStream = null;//缓冲输入流
                BufferedOutputStream bufferedOutputStream = null;//缓冲输出流
                //导出文件名
                File file = new File(fileDownloadPath(userId, fileName, fileType, request));
                bufferedInputStream = new BufferedInputStream(new FileInputStream(file));
                response.reset();
                response.setContentType(Constant.CONTENT_TYPE);
                bufferedOutputStream = new BufferedOutputStream(response.getOutputStream());
                int bytesRead = Constant.ZERO;//字节
                //这个地方的同上传的一样。我就不多说了，都是用输入流进行先读，然后用输出流去写，唯一不同的是我用的是缓冲输入输出流
                byte[] buffer = new byte[Constant.BUFFER_SIZE];
                //这个就就是弹出下载对话框的关键代码
                response.setHeader(Constant.CONTENT_DISPOSITION ,new StringBuffer(Constant.ATTACHMENT ).append(new String(file.getName().getBytes(Constant.GBK), Constant.ISO_8859_1)).toString());
                response.addHeader(Constant.CONTENT_LENGTH, "" + file.length());
                //读取要导出的注册码文件
                while ((bytesRead = bufferedInputStream.read(buffer, Constant.ZERO, Constant.BUFFER_SIZE)) != Constant.MINUS_ONE) {
                    bufferedOutputStream.write(buffer, Constant.ZERO, bytesRead);
                }
                bufferedOutputStream.flush();
                bufferedInputStream.close();
                bufferedOutputStream.close();

    }

package com.ccs.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;



/*------------------------------------------------------------------
 * create by caoxiaoxiong on 2016-05-31 version V2016_1.0 for Excel导入导出类
 * -----------------------------------------------------------------*/
public class ExcelImportExport {

    /**
     * 根据对象取出List里的值，并赋值给二维数组
     * 二维数组的第一行存储属性名，从第二行开始存储值
     * 
     * @param objects
     * @param clazz
     * @return
     */
    public  static   <T> String[][] parseLand(List<T> objects,Class<T> clazz,Properties properties) {
        int size = objects.size();//记录所有的数据
        Field[] fields = clazz.getDeclaredFields();//获取Class类的所有字段
        String[][] content = new String[size + 1][fields.length];//定义存储数据的数组
        /**
         * 把属性名称存储到二维数组content的第一行
         */
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];    //字段
            //content[0][i] = f.getName(); caoxiaoxiong 动态配置导出数据头
            if((properties != null) && (properties.getProperty(f.getName()) != null)){
                content[0][i] = properties.getProperty(f.getName());
            }

        }
        /**
         * 把List里的值存储到二维数组，从二维数组第二行开始
         */
        for (int i = 0 ; i < size; i++) {
            T classT = objects.get(i);//获取数据
            for (int j = 0; j < fields.length; j++) {
                Field f = fields[j];//字段
                f.setAccessible(true);//值为 true 则指示反射的对象在使用时应该取消 Java 语言访问检查
                try {
                    String value = "";//用于存储值的变量
                    //获取对象school对应的Field值
                    if (f.get(classT) != null) {
                        value = f.get(classT).toString();
                    }
                    content[i+1][j] = value;
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return content;
    }

    /**
     *  写入excel文件
     *  @updateBy A1
     *  @updateDate 2015年12月30日
     *  @param fileName
     *  @param content
     *  @return String
     *
     */
    public static String writeFile(String path,String fileName,String fileType, String[][] content) {
        WritableWorkbook wwb = null;
        String filePath = path + fileName +fileType;//文件路径
        try {
            //创建一个可读写的工作簿
            wwb = Workbook.createWorkbook(new File(filePath));
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (wwb != null) {
            //取得我们要操作的sheet，并对其进行相应的操作，如改名、合并单元格、设置列宽、行高等
            WritableSheet ws = wwb.createSheet(fileName, 1);
            /**
             * 把数据全部存储到WritableSheet里
             */
            for (int row = 0; row < content.length; row++) {
                for (int j = 0; j < content[row].length; j++) {
                    Label labelC = new Label(j, row, content[row][j]);
                    try {
                        ws.addCell(labelC);
                    } catch (RowsExceededException e) {
                        e.printStackTrace();
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            try {
                wwb.write();//写到文件里
                wwb.close();
                return filePath;
            } catch (IOException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    /**
     * 把excel里的值存储到List里
     * @param file
     * @param clazz
     * @return
     */
    public static <T> List<T> xlsxExcelToClass(File file,Class<T> clazz,Properties properties){
        List<T> lands = new ArrayList<T>();
        try {
            FileInputStream fileInputStream;
            fileInputStream = new FileInputStream(file);

            // 构造 XSSFWorkbook 对象
            XSSFWorkbook xwb = null;
            try {
                xwb = new XSSFWorkbook(fileInputStream);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(0);
            // 定义 row、cell
            XSSFRow row;//行
            XSSFCell cell;//列
            List<String> keys = new ArrayList<String>();
            // 循环输出表格中的内容
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                //取首行数据
                if(i == 0){
                    row = sheet.getRow(i);
                    for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                        // 通过 row.getCell(j).toString() 获取单元格内容，
                        cell = row.getCell(j);

                        if(cell != null){//不为空转换微字符串存储
                            if((properties != null) && (properties.getProperty(cell.toString()) != null)){
                                keys.add(properties.getProperty(cell.toString()));
                            }
                        }
                    }
                }

                if(i>=1){ //决定从哪一行开始提取,这里从第一行
                    Map<String,String> map = new HashMap<String, String>();
                    row = sheet.getRow(i);
                    for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                        // 通过 row.getCell(j).toString() 获取单元格内容，
                        cell = row.getCell(j);
                        if(cell!=null){
                            map.put(keys.get(j), cell.toString());
                        }
                        //keys.add(cell);
                    }
                    try {
                        T land = newTclass(clazz);
                        ObjectReflectUtil.setFieldValue(land, map);
                        lands.add(land);
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }                   
                }
            }
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }
        return lands;
    }


    /**
     * 把xls excel里的值存储到List里
     * @param file
     * @param clazz
     * @return
     */
    public static <T> List<T> xlsExcelToClass(File file,Class<T> clazz,Properties properties){
        List<T> lands = new ArrayList<T>();
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            POIFSFileSystem poifs = new POIFSFileSystem(fileInputStream);//设置要读取的文件路径
            HSSFWorkbook workbook = new HSSFWorkbook(poifs);//得到文档对象
            HSSFSheet sheet = workbook.getSheetAt(0);//得到第一个表单 
            Iterator<Row> rows = sheet.rowIterator();//迭代行
            int index = 0;
            List<String> keys = new ArrayList<String>();
            while (rows.hasNext()) {//如果n行有数据，就进行取数
                HSSFRow row = (HSSFRow) rows.next();
                if(index == 0){
                    得到 n行的总列数  
                    int num = row.getLastCellNum();
                    for(int i = 0 ; i < num;i++ ){
                        HSSFCell cell = row.getCell(i);
                        if(cell!=null){
                            String value = getStringCellValue(cell);
                            if((properties != null) && (properties.getProperty(value) != null)){
                                keys.add(properties.getProperty(value));
                            }
                        }
                    }
                }
                if(index>=1){ //决定从哪一行开始提取,这里从第一行
                    Map<String,String> map = new HashMap<String, String>();
                    int num = row.getLastCellNum();
                    for(int i = 0 ; i < num;i++ ){
                        HSSFCell cell = row.getCell(i);
                        if(cell!=null){
                            map.put(keys.get(i), getStringCellValue(cell));
                        }
                    }               
                    try {
                        T land = newTclass(clazz);
                        ObjectReflectUtil.setFieldValue(land, map);
                        lands.add(land);
                    } catch (InstantiationException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }                   
                }
                index++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return lands;
    }

    /**
     * 创建对象
     *  @updateBy A1
     *  @updateDate 2015年12月30日
     *  @param clazz
     *  @return
     *  @throws InstantiationException
     *  @throws IllegalAccessException T
     *
     */
    private static <T> T newTclass(Class<T> clazz) throws InstantiationException, IllegalAccessException{
        T a=clazz.newInstance();
        return a;

    }
    // 获取单元格数据内容为字符串类型的数据
    private static String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }

    /*
    private static String getUUID() {
        UUID uuid = UUID.randomUUID();
        String str = uuid.toString();
        return str.substring(0, 8);
    }

    public static void main(String[] args) {
        //写入测试
        List<User> users=new ArrayList<User>();
       User u=new User();
       u.setName("joe");
       u.setPassword("123");
       users.add(u);
       User s=new User();
       s.setName("sophia");
       s.setPassword("123456");
       users.add(s);       
        String[][] content = parseLand(users,User.class);
        String uuid = getUUID();
         writeFile("d:\\",uuid, ".xls",content);       
         System.out.println("写入完成");
//       //读取测试
         User temp=new User();
         File file = new File("d:\\" + uuid +".xls");
         List<User> us=excelToClass(file,User.class);
         for(User i:us)
         {
             System.out.println(i.getName());
             System.out.println(i.getPassword());
         }
         System.out.println("读取完成");
    }*/
}