excel文件导入

流程：
①点击导入按钮，导入到页面上，文件上传到服务器临时文件夹上。
②点击保存按钮时，从服务器临时文件上读取文件，解析文件保存到数据库。
（在数据量大的时候，不适合从前端传输大量的数据到后台，速度比较慢）
java文件

action层

public class InAction extends JsonAction {    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");    // 文件    private File uploadExcel;    // 文件名    private String uploadExcelFileName;    @Autowired    private JXZUserrDao userDao;    @Action(value = "uploadUser")    public String uploadUser() {        String message = new String();        String newUuid = "";        String fileName = "";        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();        JsonResult results = new JsonResult();        try {            FileInputStream input = new FileInputStream(getUploadExcel());            Workbook wb = null;            // 根据文件格式(2003或者2007)来初始化            if (getUploadExcelFileName().endsWith("xlsx")) {                wb = new XSSFWorkbook(input);            } else {                wb = new HSSFWorkbook(input);            }            Sheet sheet = wb.getSheetAt(0);            int totalRow = sheet.getLastRowNum();            Row headRow = sheet.getRow(0);            try {                headRow.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);                headRow.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);            } catch (Exception e1) {                e1.printStackTrace();            }            for (int i = 1; i <= totalRow; i++) {                Row row = sheet.getRow(i);                String username1 = new String();                String password1 = new String();                try {                    row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);                    username1 = row.getCell(0).getStringCellValue();                } catch (Exception e1) {                    e1.printStackTrace();                }                try {                    row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);                    password1 = row.getCell(1).getStringCellValue();                } catch (Exception e1) {                    e1.printStackTrace();                }                Map<String, Object> user = new HashMap<String, Object>();                user.put("username", username1);                user.put("password", password1);                userList.add(user);                JXZUser juser=new JXZUser();                juser.setPassword(password1);                juser.setUsername(username1);                userDao.insert(juser);            }            newUuid = UUID.randomUUID().toString();            Date createTime = new Date();            DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HHmmss");            fileName = newUuid + "_" + sdf.format(createTime) + ".csv";            LinkedHashMap<String, String> head = new LinkedHashMap<String, String>();            head.put("username", "用户名");            head.put("password", "密码");            JXZExportExcelUtil.exportCsv(userList, head, fileName);        } catch (Exception e) {            userList.clear();            e.printStackTrace();        }        results.setMsg(message);        results.setData(userList);        results.setMsgCode(fileName);        setResult(results);        return NONE;    }    public File getUploadExcel() {        return uploadExcel;    }    public void setUploadExcel(File uploadExcel) {        this.uploadExcel = uploadExcel;    }    public String getUploadExcelFileName() {        return uploadExcelFileName;    }    public void setUploadExcelFileName(String uploadExcelFileName) {        this.uploadExcelFileName = uploadExcelFileName;    }}

JXZExportExcelUtil文件（上传文件到服务器）

public class JXZExportExcelUtil {    //上传临时文件路径    private  static String TEMP_FOLDER;    private  final static String path ="importCsv";    static {            Properties prop = new Properties();        InputStream in =ExportExcelUtil.class.getResourceAsStream("/system.properties");//读取配置文件     try {           prop.load(in);           TEMP_FOLDER =prop.getProperty("system.tempFilePath").trim();         } catch (IOException e) {           e.printStackTrace();             }       }    /**     * 导出CVS到文件服务器     *      * @param fHttpServletResponse     *            response     * @param dataList     *            数据     * @param LinkedHashMap     *            <String,String> head     * @return     */    public static boolean exportCsv(List<Map<String, Object>> dataList,            LinkedHashMap<String, String> head, String fileName) {        boolean isSucess = false;        FileOutputStream out = null;// 文件字节输出流（写入到文件）        OutputStreamWriter osw = null;// 将字节流转换为字符流        BufferedWriter bw = null;// 文件缓冲区        try {            File file = new File(TEMP_FOLDER + File.separator + path                    + File.separator + fileName);            File parent = file.getParentFile();            if (parent != null && !parent.exists()) {                parent.mkdirs();            }            out = new FileOutputStream(file);            osw = new OutputStreamWriter(out, "gbk");            bw = new BufferedWriter(osw, 1024);            int headSetSize = 1;            int listSize = 1;            int dataSetSize = 1;            // 循环头信息            for (String fieldName : head.keySet()) {                if (headSetSize == head.keySet().size()) {                    bw.append("\"")                            .append(String.valueOf(head.get(fieldName))                                    .replace("\"", "\"\"")).append("\"")                            .append("\r");                } else {                    bw.append("\"")                            .append(String.valueOf(head.get(fieldName))                                    .replace("\"", "\"\"")).append("\"")                            .append(",");                }                headSetSize++;            }            // 循环数据            if (dataList != null && !dataList.isEmpty()) {                for (Map<String, Object> map : dataList) {                    for (String fieldName : head.keySet()) {                        if (dataSetSize == head.keySet().size()) {                            if (map.get(fieldName) != null) {                                if (map.get(fieldName) instanceof java.sql.Date) {                                    java.sql.Date sqlDate = (java.sql.Date) map                                            .get(fieldName);                                    SimpleDateFormat sdf = new SimpleDateFormat(                                            "yyyy-MM-dd");                                    bw.append("\"").append(sdf.format(sqlDate))                                            .append("\"").append(",");                                } else if (map.get(fieldName) instanceof Date) {                                    Date date = (Date) map.get(fieldName);                                    SimpleDateFormat sdf = new SimpleDateFormat(                                            "yyyy-MM-dd HH:mm:ss");                                    bw.append("\"").append(sdf.format(date))                                            .append("\"");                                } else {                                    bw.append("\"")                                            .append(String.valueOf(                                                    map.get(fieldName))                                                    .replace("\"", "\"\""))                                            .append("\"");                                }                            } else {                                bw.append("   ");                            }                        } else {                            if (map.get(fieldName) != null) {                                if (map.get(fieldName) instanceof java.sql.Date) {                                    java.sql.Date sqlDate = (java.sql.Date) map                                            .get(fieldName);                                    SimpleDateFormat sdf = new SimpleDateFormat(                                            "yyyy-MM-dd");                                    bw.append("\"").append(sdf.format(sqlDate))                                            .append("\"").append(",");                                } else if (map.get(fieldName) instanceof Date) {                                    Date date = (Date) map.get(fieldName);                                    SimpleDateFormat sdf = new SimpleDateFormat(                                            "yyyy-MM-dd HH:mm:ss");                                    bw.append("\"").append(sdf.format(date))                                            .append("\"").append(",");                                } else {                                    bw.append("\"")                                            .append(String.valueOf(                                                    map.get(fieldName))                                                    .replace("\"", "\"\""))                                            .append("\"").append(",");                                }                            } else {                                bw.append("  ").append(",");                            }                        }                        dataSetSize++;                    }                    if (listSize != dataList.size())                        bw.append("\r");                    listSize++;                }            }            isSucess = true;        } catch (Exception e) {            isSucess = false;        } finally {            if (bw != null) {                try {                    bw.close();                    bw = null;                } catch (IOException e) {                    e.printStackTrace();                }            }            if (osw != null) {                try {                    osw.close();                    osw = null;                } catch (IOException e) {                    e.printStackTrace();                }            }            if (out != null) {                try {                    out.close();                    out = null;                } catch (IOException e) {                    e.printStackTrace();                }            }        }        return isSucess;    }}

jsp文件（页面显示，使用了easyui）

<script type="text/javascript">    var userJson;    function batchImport(){        function getData(pageNumber,pageSize){                  var start=(pageNumber-1)*pageSize;                var end=(start+pageSize)<userJson.length?start+pageSize:userJson.length;               var rowsJson=[];               for(start;start< end;start++){                  rowsJson.push(userJson[start])                  }                $('#grid').datagrid('loadData',{total:userJson.length,rows:rowsJson});          }        $("#userIn").form("submit", {            url: '<%=realPath%>/jxzin/uploadUser',            param:"uuid=''",            success: function (data) {                batchInitJXZuser();                var dataJson=eval('(' + data+ ')');                userJson=dataJson.data;                var currPageSize=   $('#grid').datagrid('options').pageSize;                var page=$('#grid').datagrid('getPager');                $(page).pagination({                      onSelectPage: function (pageNumber, pageSize) {                             getData(pageNumber, pageSize);                      }                  });                 getData(1,currPageSize);            },            error: function (data, status, e) { //服务器响应失败处理函数                $.messager.alert('提示', '上传失败', 'info');             }        })    }

var batchInitJXZuser=function(data){        $('#grid').datagrid({         singleSelect:true,//是否单选         fitColumns: false,        loadMsg: "正在努力为您加载数据", //加载数据时向用户展示的语句        pagination:true,//分页控件         rownumbers:true,//行号         pageSize: 10,//每页显示的记录条数，默认为10         pageList: [10,20,50],//可以设置每页记录条数的列表         fit:true,        columns:[[                       {                         field:'username',                         title:'用户名',                     },                       {                         field:'password',                         title:'密码',                     },                ]]           });         }

点击保存按钮
java文件
action层

    /**     * 发放     *      * @return     * @throws Exception     */    @SuppressWarnings("unchecked")    @Action(value = "jxzUserBatchInsert")    public String jxzUserBatchInsert() throws Exception {        String message = new String();        JsonResult results = new JsonResult();        HttpServletRequest request = ServletActionContext.getRequest();        String jsonArr = request.getParameter("jsonArr");        String fileName= request.getParameter("uuid");        if(fileName!=null){            try {                List<String> keyList=new ArrayList<String>();                keyList.add("username");                keyList.add("password");                List<Map<String,Object>>  list=ExportExcelUtil.importCsv(fileName, keyList);                if( list.size()>=1){                    list.remove(0);                }                for (Map<String, Object> map : list) {                    JXZUser juser=new JXZUser();                    juser.setPassword(String.valueOf(map.get("password")));                    juser.setUsername(String.valueOf(map.get("username")));                    userDao.insert(juser);                }                message = "新增成功";                results.setSuccess(true);                ExportExcelUtil.deleteFolder(fileName);            } catch (Exception e) {                message = e.getMessage();                if(StringUtil.isEmpty(message)){                    message = "新增失败";                }                results.setSuccess(false);                results.setMsg(message);                setResult(results);                return NONE;            }            results.setMsg(message);            setResult(results);            return NONE;        }        if(jsonArr!=null){            List<JSONObject> jsonList = (List<JSONObject>) JSONArray                    .fromObject(jsonArr);            try {                for (JSONObject map : jsonList) {                    JXZUser juser=new JXZUser();                    juser.setPassword(String.valueOf(map.get("password")));                    juser.setUsername(String.valueOf(map.get("username")));                    userDao.insert(juser);                }                message = "新增成功";                results.setSuccess(true);            } catch (Exception e) {                message = e.getMessage();                if(StringUtil.isEmpty(message)){                    message = "新增失败";                }                results.setSuccess(false);                results.setMsg(message);                setResult(results);                return NONE;            }            results.setMsg(message);            setResult(results);            return NONE;        }           results.setSuccess(false);        results.setMsg("新增失败");        setResult(results);        return NONE;    }

从服务器上读取文件

 /**     * 导入csv     * @param String fileName     * @param List<String> keyList     * @return  List<Map<String,Object>>     */    public static  List<Map<String,Object>> importCsv(String fileName,List<String> keyList){        List<Map<String,Object>> dataList=new ArrayList<Map<String,Object>>();        BufferedReader br=null;        try {             br = new BufferedReader(new InputStreamReader(new FileInputStream(new File(            TEMP_FOLDER+File.separator+path+File.separator+fileName)), "gbk"));            Pattern pattern = Pattern.compile("(,)?((\"[^\"]*(\"{2})*[^\"]*\")*[^,]*)");              String strLine = null;              while((strLine = br.readLine()) != null) {                  Map<String,Object>  map=new HashMap<String, Object>();                Matcher matcher = pattern.matcher(strLine);                  int cellCount=0;                while(matcher.find()) {                      String cell= matcher.group(2);//group(2) is ((\"[^\"]*(\"{2})*[^\"]*\")*[^,]*)                      Pattern pattern2 = Pattern.compile("\"((.)*)\"");                      Matcher matcher2 = pattern2.matcher(cell);                      if(matcher2.find()) {                          cell = matcher2.group(1);                          map.put(keyList.get(cellCount++), cell);                    }                  }                  dataList.add(map);            }          }catch (Exception e) {            e.printStackTrace();        }finally{            if(br!=null){                try {                    br.close();                    br=null;                } catch (IOException e) {                    e.printStackTrace();                }            }        }        return dataList;    }