export导出数据

来源：互联网发布：js中的map 编辑：程序博客网时间：2024/05/22 03:44

第一步在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("读取完成");    }*/}

0 0