java不同方式方式导入导出数据

方法一： 根据  easypoi  实现导入导出

导入：

前端部分：

<div style="vertical-align:top;padding-left:5px; position: relative; display:inline">
                <input id="excle" type="file" class="btn"/>
                <input  type="file" class="btn" style="position: absolute; width: 30px; height: 30px; top:0;opacity: 0">
            </div>

js:

$("#excle").change(function(){
        var file= document.getElementById("excle").files[0];
        var formData=new FormData();
        formData.append("file",file);
        top.jzts();
        $.ajax({
            //bbk2300/lecturerExcelImport.do
            url : "<%=basePath%>oce6001/Map6001ExcelImport.do",
            type : "POST",
            dataType:"JSON",
            data : formData,
            // 告诉jQuery不要去处理发送的数据
            processData : false,
            // 告诉jQuery不要去设置Content-Type请求头
            contentType : false,
            success:function (data) {
                top.hangge();
                tosearch(1);
                alert(data.meta.msg);
            },
            error: function(XMLHttpRequest, textStatus, errorThrown){
                top.hangge();
                tosearch(1);
                alert(JSON.parse(XMLHttpRequest.responseText).msg);
            }
        });

    })

后台部分：

Controller 

import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;


/**
	 * 20180908
	 * 效果图产品库导入
	 *
	 * @param request 讲师Excel基本信息
	 * @return 返回导入是否成功的
	 * @throws IOException 抛出异常
	 */
	@RequestMapping(value = "/Map6001ExcelImport", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	@ResponseBody
	public Response Map6001ExcelImport(HttpServletRequest request) throws IOException {
		Response res = null;
		MultipartHttpServletRequest mhs = (MultipartHttpServletRequest) request;
		/**excel作品模板上传的名称*/
		MultipartFile file;
		file = mhs.getFile("file");
		ImportParams importParams = new ImportParams();
		/**是否启用验证功能，默认是false不做验证检测,这里的验证在实体对象中使用annotation标签验证*/
		importParams.setNeedVerfiy(true);
		try {
			/**把上传的Excel文件转换为EasyPoi的一个导入结果对象*/
			ExcelImportResult<Map6001ImportExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), Map6001ImportExcel.class,
					importParams);
			int count = map6001Service.inserListMap6001(result);
			if(count>0){
				return new Response().success("导入成功"+count+"条");
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

server

/**
     * 导入评论信息
     * @param list
     * @return
     * @throws Exception
     */
    @Override
    public int  inserListMap6001(ExcelImportResult<Map6001ImportExcel> list)throws Exception{
        /**所有导入的结果集*/
        List<Map6001ImportExcel> allList = list.getFailList();
        List<Map6001DO> map6001DOList = new ArrayList<>();
        for(Map6001ImportExcel importExcel:allList){
            Map6001DO map6001DO=new Map6001DO();
            map6001DO.setDisId(importExcel.getDisId());
            map6001DO.setOpId(importExcel.getOpId());
            map6001DO.setDisOpId(importExcel.getDisOpId());
            map6001DO.setArtId(importExcel.getArtId());
            map6001DO.setParentId(importExcel.getParentId());
            map6001DO.setDisContent(importExcel.getDisContent());
            map6001DO.setDisUser(importExcel.getDisUser());
            //格式转换
            Date date =  CommonWan4.parseUsDateTime02(importExcel.getDisDate());
            map6001DO.setDisDate(date);
            map6001DO.setDisStatus(importExcel.getDisStatus());
            Map6001DisExpandOne map6001DisExpandOne=new Map6001DisExpandOne();
            map6001DisExpandOne.setSource(importExcel.getDisExpandOne());
            map6001DO.setDisExpandOne(map6001DisExpandOne);
            map6001DOList.add(map6001DO);
        }
        if(map6001DOList.size()>0){
            //批量写入数据库
            return daoMap6001.saveListDb("Map6001DOMapper.insertMap6001List",map6001DOList);
        }
            return 0;
        };
    

 

bean:

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.hibernate.validator.constraints.NotBlank;

import java.io.Serializable;

/**
 * Excel导入实体类
 *
 * @author yedong
 * @version 1.0
 * @date 2018.908
 */
@Getter
@Setter
@ToString
public class Map6001ImportExcel implements Serializable {
    /**
     * 
     */
    @Excel(name = "评论唯一ID")
    @NotBlank(message = "评论唯一ID")
    private String disId;
    /**
     * 被评论会员ID
     */
    @Excel(name = "评论会员ID")
    private String opId;
    /**
     * 被评论会员ID
     */
    @Excel(name = "被评论会员ID")
    private String disOpId;
    /**
     * 关联产品ID
     */
    @Excel(name = "关联产品ID")
    private String artId;
    /**
     * 上级评论ID
     */
    @Excel(name = "上级评论ID")
    private String parentId;
    /**
     * 评论内容
     */
    @Excel(name = "评论内容")
    private String disContent;
    /**
     * 评论人
     */
    @Excel(name = "评论人")
    private String disUser;
    /**
     * 评论时间
     */
    @Excel(name = "评论时间")
    private String disDate;
    /**
     * 评论状态(1可用2不可用)
     */
    @Excel(name = "评论状态")
    private String disStatus;
    /**
     * 评论类型（1帖子2产品3效果图）
     */
    @Excel(name = "评论类型")
    private String disExpandOne;
}

公共方法：

/**
     * 导入导出功能
     * @param timeStr
     * @return
     */
    public static Date parseUsDateTime02(String timeStr) {
        Preconditions.checkArgument(StringUtils.isNotBlank(timeStr), "时间参数不能为空");
        String pattern = "EEE MMM dd HH:mm:ss zzz yyyy";
        try {
            Date date = new SimpleDateFormat(pattern, Locale.US).parse(timeStr);
            return date;
        } catch (ParseException e) {
            System.out.println("时间解析错误");
        }
        return null;
    }

 导出：

前段部分： 

<a class="btn btn-info" title="批量导出" href="javascript:void(0)" onclick="onExi()">批量导出</a>

function onExi() {
        //获取ID串
        var SelectValue="";
       $("input[name='ids']:checked").each(function(j) {
            if (j >= 0) {
                SelectValue += $(this).val() + ","
            }
        });
        if(SelectValue==undefined || SelectValue==''){
            alert("导出失败,请选择导出的数据");
            return ;
        }
        window.location.href="excelController/exportYuYue.do?selectValue="+SelectValue
    }

 后台部分：

/**
     * 导出预约数据
     *
     * @param response 出参对象
     * @return 返回数据
     * @throws Exception 抛出异常
     */
    @RequestMapping(value = "/exportYuYue")
    public void exportYuYue(HttpServletResponse response, String selectValue) throws Exception {
        /**重置响应对象*/
        response.reset();
        /**当前日期，用于导出文件名称*/
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        /**设置excel的文件名称*/
        String dateStr = "[效果图信息-" + sdf.format(new Date()) + "]";
        /**指定下载的文件名--设置响应头*/
        response.setCharacterEncoding("UTF-8");
        /**转换一下编码，防止乱码*/
        response.setHeader("Content-Disposition", "attachment;filename=" +
                new String(dateStr.getBytes("UTF-8"), "ISO-8859-1") + ".xls");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        //获取导出参数
        //请求中台获取数据预约列表数据
        //请求中台获取所有预约信息
        Map map = new HashMap();
        List<Long> listLong = new ArrayList<>();
        if (selectValue != null && !selectValue.equals("")) {
            String tr[] = selectValue.split(",");
            for (int i = 0; i < tr.length - 1; i++) {
                Long lo = Long.valueOf(tr[i]);
                listLong.add(lo);
            }
        } else {
            return;
        }
        map.put("ids", listLong);
        String url = ConstOce.ZTVIP_USER + "/sign/getAllAboutListEx";
        String map1 = JSON.toJSONString(map);
        String json = HttpUtil.httpsRequest(url, "POST", map1);
        JSONObject jsonObject1 = JSON.parseObject(json);
        if (jsonObject1 == null || !jsonObject1.getString("code").equals("100")) {
            new Response().failure("获取失败");
            return;
        }
        if (jsonObject1.get("list") != null && !jsonObject1.get("list").equals("")) {
            JSONArray jsonArray = JSON.parseArray(jsonObject1.get("list") + "");
            List<Oce4000Expor> list = new ArrayList();
            for (int j = 0; j < jsonArray.size(); j++) {
                JSONObject jsonObject = jsonArray.getJSONObject(j);
                Oce4000Expor oce4000Expor = new Oce4000Expor();
                if (jsonObject.getString("provinceCode") != null && !jsonObject.getString("provinceCode").equals("")) {
                    Oce1006DO oce1006DO = oce1006Service.findByKeyOrName(jsonObject.getString("provinceCode"));
                    if (oce1006DO != null) {
                        oce4000Expor.setProvinceCode(oce1006DO.getProvinceName());
                    }
                }
                if (jsonObject.getString("cityCode") != null && !jsonObject.getString("cityCode").equals("")) {
                    Oce1007DO oce1007DO = oce1007Service.findByKeyOrName(jsonObject.getString("cityCode"));
                    if (oce1007DO != null) {
                        oce4000Expor.setCityCode(oce1007DO.getCityName());
                    }
                }
                if (jsonObject.getString("districtCode") != null && !jsonObject.getString("districtCode").equals("")) {
                    Oce1008DO oce1008DO = oce1008Service.findByKeyOrName(jsonObject.getString("districtCode"));
                    if (oce1008DO != null) {
                        oce4000Expor.setDistrictCode(oce1008DO.getAreaName());
                    }
                }
                //补充其他数据
                oce4000Expor.setAboutId(jsonObject.getString("id"));
                oce4000Expor.setAboutName(jsonObject.getString("customerName"));
                oce4000Expor.setAddress(jsonObject.getString("address"));
                oce4000Expor.setCreationDateStr(jsonObject.getString("creationDateStr"));
                oce4000Expor.setAboutPhone(jsonObject.getString("mobile"));
                list.add(oce4000Expor);
            }
            /**在这里把查询到的数据插入Excel模板*/
            ExportParams params = new ExportParams("预约列表信息", "预约列表信息");
            Workbook workbook = ExcelExportUtil.exportExcel(params, Oce4000Expor.class, list);
            workbook.write(response.getOutputStream());
        }
    }
    

方法二： （暂时没时间写）