我的Java开发生涯记（15）——关于导入导出Excel

最新推荐文章于 2023-11-13 10:37:55 发布

原创最新推荐文章于 2023-11-13 10:37:55 发布 · 259 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#自我成长

自习专栏收录该内容

40 篇文章

订阅专栏

本文详细介绍了在前端开发中实现数据导出为Excel文件及从Excel导入数据的方法。涵盖了页面元素设计、导出功能的前后端交互、导入功能的文件解析与数据处理流程。适合对前端数据操作感兴趣的开发者阅读。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019.7.30著

多愁善感的我，想事情贼多，有时候会因此而烦恼。还是应该找点事情分散一下注意力的。

哪吒听说最近很火，我决定了，这周六日去电影院看看。

今天做的导出功能，所以先把导出功能说一下。

先是页面

<div class="searchbox-op">
	<button class="btn btn-primary exportAll  fr" title="导出第三方对账单" rel="tooltip">导出第三方对账单</button>
	<a class="btn btn-primary fr" href="javascript:toInitStock()" title="第三方订单对账导入" rel="tooltip">第三方订单对账导入</a>
</div>

<script>
//导出功能
$(".exportAll").click(function () {
    bootbox.confirm("您确定导出所有的筛选数据吗?", function (r) {
        var channelOrderId = $.trim($("#channelOrderId").val());
        var orderId = $.trim($("#orderId").val());
        var channelName = $.trim($("#channelName").val());
        if (r) {
            window.location.href = "${pageContext.servletContext.contextPath}/storeback/thirdpartyorderbill/exportAll?channelOrderId=" + channelOrderId + "&orderId=" + orderId +"&channelName=" + channelName;
        }
    });
})
</script>

再就是后台代码

@RequestMapping(value = { "/exportAll" }, method = { RequestMethod.GET })
public void exportAll(HttpServletResponse response, HttpServletRequest request) {

    String channelOrderId = request.getParameter("channelOrderId");
    String orderId = request.getParameter("orderId");
    String channelName = request.getParameter("channelName");
    ThirdpartyOrderBillQueryDTO billQueryDTO = new ThirdpartyOrderBillQueryDTO();
    if (StringUtils.isNotEmpty(channelOrderId)) {
    billQueryDTO.setChannelOrderId(channelOrderId);
   billQueryDTO.getMapCondition().put("channelOrderId",ConditionOpTypeEnum.EQ.getCode());
    }
    if (StringUtils.isNotEmpty(orderId)) {
	billQueryDTO.setOrderId(orderId);
        billQueryDTO.getMapCondition().put("orderId",ConditionOpTypeEnum.EQ.getCode());
    }
    if(StringUtils.isNotEmpty(channelName)){
	    billQueryDTO.setChannelName(channelName);
    }
    //拼接查询条件
    this.getQM(billQueryDTO);
    RetDTO ret = myService.getByConditionNoPage(billQueryDTO);
    List<ThirdpartyOrderBillDTO> list = JSON.parseArray(ret.getRetData(), ThirdpartyOrderBillDTO.class);

    List dataset = null;
    if (list != null && list.size() > 0) {
	    RetDTO retDTO = orderChannel4otherApi.getAll();
	    if (retDTO != null && RetDTO.SUCCESS.equals(retDTO.getRetStatus()) && !StringUtils.isEmpty(retDTO.getRetData())) {
		    //组装显示数据
		    dataset = this.getList(retDTO, list);
	    }
    }


    //在这里dataset是里需要导出数据的集合，其余的参数都有
    try {
	if (list != null) {
	String getexportName = "导出第三方对账单";
	//1.获取EXCEL表头
	String[] header = {"渠道订单号", "订单编号", "渠道名称", "清洗项目", "数量", "原订单价格", "导入订单价格", "佣金比率"};
	//2.对应的字段
	Object[] columns = {"channelOrderId", "orderId", "channelName", "cleaningProject", "amount", "originalMoney", "importMoney", "brokerage"};
	ExcelUtil.writeExcelByObject(dataset, response, getexportName, header, columns);
	}
    } catch (Exception e) {
    }
}

其实在ExcelUtil里面还有很多的导出方法，只是传递的参数可能会不一样，大家可以自行探索

接下来是导入功能

导入功能相对来说会简单些

页面

<script>
//导入订单
function toInitStock(){
	location.href="${pageContext.servletContext.contextPath}/storeback/thirdpartyorderbill/importThirdpartyOrderBill";
}
</script>

后台

//list就是导入表格的数据
@ResponseBody
@RequestMapping(value = { "/import" }, method = { RequestMethod.POST })
public String importStock(@RequestParam(value = "myFiles", required = false) MultipartFile[] files,HttpServletRequest request, HttpServletResponse response, Model model) throws Exception {
    File[] files1 = UploadFileUtils.fileTransform(mfiles);
    if (files1 != null && files1.length > 0) {
        String fileName = file.getName();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        List<ImportOrderDTO> importOrderList = new ArrayList<>();
        if ("xls".equalsIgnoreCase(suffix) || "xlsx".equalsIgnoreCase(suffix)) {
            //实体类
            ImportThirdpartyOrderBillDTO model = new ImportThirdpartyOrderBillDTO();
            //表格纵向代表的字段
            Object[] columns = { "channelName","channelOrderId","cleaningProject","amountStr"};
            List<ImportThirdpartyOrderBillDTO> list = null;
            try {
                list = UploadFileUtils.importExcel(file, model, columns);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}