Excel的导入导出 -- java

要用到的框架：spring

ResManager:

public static void printExcelHeader(HttpServletResponse response,String title,String[] colTitles,String filename) throws Exception{
        if("".equals(filename)||filename==null) filename = "exportData";
        /*filename = URLEncoder.encode(filename, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename="+filename+".xls");
        response.setContentType("application/ms-excel;charset=UTF-8");*/
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"),"ISO-8859-1") + ".xls");
        response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");

        printerWriter.set(response.getWriter());

        if(!"".equals(title)&&title!=null){
            printerWriter.get().println("<table border='1'><tr><td colspan='2' bgcolor='#eee'>" + title + "</td></tr></table>");
        }

        printerWriter.get().println("<table border='1'>");
        printerWriter.get().println("<tr>");
        for(int i=0;i<colTitles.length;i++) printerWriter.get().println("<th>" + colTitles[i] + "</th>");
        printerWriter.get().println("</tr>");
    }
    public static void printExcelRows(HttpServletResponse response,List dataList,String[] colNames) throws Exception{
        printerWriter.set(response.getWriter());
        for(Object obj:dataList) {
            printerWriter.get().println("<tr>");
            if(obj instanceof Map){
                for(String name:colNames){
                    printerWriter.get().println("<td style=\"mso-number-format:'\\@'\">" + getStr(((Map) obj).get(name)) + "</td>");
                }
            }else{
                for(String name:colNames){
                    printerWriter.get().println("<td style=\"mso-number-format:'\\@'\">" + getStr(BeanUtils.getProperty(obj, name)) + "</td>");
                }
            }
            printerWriter.get().println("</tr>");
        }
    }
    public static void printExcelEnd(HttpServletResponse response) throws Exception{
        printerWriter.set(response.getWriter());
        printerWriter.get().println("</table>");
    }
    public static void printExcel(HttpServletResponse response,List dataList,String title,String[] colTitles,String[] colNames,String filename) throws Exception{
        printerWriter.set(response.getWriter());
        printExcelHeader(response,title,colTitles,filename);
        printExcelRows(response,dataList,colNames);
        printExcelEnd(response);
    }

导出：ExportExcel

@RequestMapping("ExportExcel.do")
    public void customerInfoExport(HttpServletResponse response,int page,int rows
    ) throws Exception {

        PageRequest pageRequest = new PageRequest();
        pageRequest.setPageNo(page);
        pageRequest.setPageSize(rows);
        Page<OaAccount> pageMode = oaAccountService.findPage(pageRequest);//findPage查询出一个List出来

        List<OaAccount> accountList = pageMode.getResult();

        //字段名
        String[] fields= new String[]
                {"statement","code","accountGroup","name","txt50","lockedStatus","deleteStatus","createDate","createUser","updateDate","updateUser"};//假如是一个对象里还有一个对象的话可以用对象.对象的方式去获取
        //标题头
        String[] heads=
                {"账表","编码","科目组","科目名称","锁定状态","删除状态","创建时间","创建用户","更新时间","更新用户"};


        //导出excel
        ResManager.printExcel(response, accountList, "数据", heads, fields, "数据");

    }

导入：importExcle

public class OaAccountDto {
    /**
     * 科目编码
     */
    private String code;

    /**
     * 长文本
     */
    private String name;
    /**
     * 账表
     */
    private String statement;

    /**
     * 科目组
     */
    private String accountGroup;

    @ExcelResources(title = "code",order = 1)
    public String getCode() {
        return code;
    }


    public void setCode(String code) {
        this.code = code;
    }

    @ExcelResources(title = "name",order = 2)
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @ExcelResources(title = "statement",order = 3)
    public String getStatement() {
        return statement;
    }

    public void setStatement(String statement) {
        this.statement = statement;
    }

    @ExcelResources(title = "accountGroup",order = 4)
    public String getAccountGroup() {
        return accountGroup;
    }

    public void setAccountGroup(String accountGroup) {
        this.accountGroup = accountGroup;
    }

    public OaAccount getAccount(){
        OaAccount account = new OaAccount();
        account.setCode(this.code);
        account.setName(this.name);
        account.setAccountGroup(this.accountGroup);
        account.setStatement(this.statement);
        return account;
    }

    public static List<OaAccount> getAccounts(List<Object> itemDatas) {
        List<OaAccount> ss = new ArrayList<OaAccount>();
        for (Object obj : itemDatas) {
            OaAccountDto med = (OaAccountDto) obj;
            OaAccount m = med.getAccount();
            ss.add(m);
        }
        return ss;
    }
}

@RequestMapping(value = "/import.do",method = RequestMethod.POST)
    public void importExcel(HttpServletRequest req, HttpServletResponse response,HttpSession session) throws Exception {
        //@RequestParam(required = false, value = "file")  MultipartFile itemDataeList
        MultipartHttpServletRequest mul = (MultipartHttpServletRequest) req;
        List<MultipartFile> files= mul.getFiles("file");
        MultipartFile itemDataeList=files.get(0);

        int i = 0;
        int j = 0;
        JSONObject json = new JSONObject();
        //给json默认值
        Map data =new HashMap();
        data.put("addTotal", 0);
        data.put("updateTotal", 0);
        String name = itemDataeList.getOriginalFilename();
        try {
            String path = req.getSession().getServletContext().getRealPath("/xls/" + name);
            FileUtils.copyInputStreamToFile(itemDataeList.getInputStream(), new File(path));
            List<Object> meds = ExcelUtil.getInstance().readExcel2ObjsByPath(path, OaAccountDto.class, 1, 3, 0); //导入时标题的各个名称需与定义的注解标题一致
            //通过Dto获取实体类的List
            List<OaAccount> materials = OaAccountDto.getAccounts(meds);
            for (OaAccount itemData : materials) {

                //如果删除标识为X 则设置DeleteStatus 状态为已删除
//                if("X".equals(itemData.getXloev().toUpperCase())){
//                    itemData.setDeleteStatus(1);
//                }


                //设置2个条件，如果2个条件与数据库里的一样，就update，不一样就save

                if (itemData.getCode() != null && !"".equals(itemData.getCode().trim())) {
                    itemData.setCode(itemData.getCode().trim());
                    OaAccount account = new OaAccount();
                    account.setCode(itemData.getCode());
                    List<OaAccount> itemDataeList1 = this.oaAccountService.getByValues(account);
                    if (itemDataeList1.isEmpty()) {
                        itemData.setCreateDate(Utils.getLongDateStr(new Date()));
                        //itemData.setCreateUser(loginName);
                        itemData.setDeleteStatus(0);
                        itemData.setLockedStatus(0);
                        this.oaAccountService.save(itemData);
                        i++;
                        data.put("addTotal", i);
                    } else {
                        itemData.setId(itemDataeList1.get(0).getId());
                        if(Utils.isNotEmpty(itemDataeList1.get(0).getCode())){
                            itemData.setCode(itemDataeList1.get(0).getCode());
                        }
                        if(Utils.isNotEmpty(itemDataeList1.get(0).getName())){
                            itemData.setName(itemDataeList1.get(0).getName());
                        }
                        if(Utils.isNotEmpty(itemDataeList1.get(0).getStatement())){
                            itemData.setStatement(itemDataeList1.get(0).getStatement());
                        }
                        if(Utils.isNotEmpty(itemDataeList1.get(0).getAccountGroup())){
                            itemData.setAccountGroup(itemDataeList1.get(0).getAccountGroup());
                        }
                        //itemData.setUpdateUser(loginName);
                        itemData.setUpdateDate(Utils.getLongDateStr(new Date()));
                        itemData.setDeleteStatus(0);
                        itemData.setLockedStatus(0);
                        this.oaAccountService.updateById(itemData);
                        j++;
                        data.put("updateTotal", j);
                    }
                }else {
                    data.put("message", "请把信息填写完整！！");
                }
            }
            data.put("success",true);
            json.put("data",data);
            json.put("success",true);
            ResManager.getTextOut(response).print(json);
            ResManager.close();
        } catch (IOException e) {
            json.put("success",false);
            json.put("error", e.getMessage());
        } finally {
            ResManager.getTextOut(response).print(json);
            ResManager.close();
        }
    }

下载导入模板

@RequestMapping(value = "downloadTemplate.do")
    public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String title = "基本信息excel模板";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        POIFSFileSystem fs = new POIFSFileSystem(getClass().getClassLoader().getResourceAsStream("xls/account.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        wb.write(response.getOutputStream());
    }

模板示例已上传！ 点击查看下载