侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

导出excel

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 85 阅读 / 13,097 字
温馨提示:
本文最后更新于 2021-04-10,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
阿里开源的EasyExcel 快速、简单避免OOM的java处理Excel工具,内存溢出
在后台查数据 生成Excel文件然后返回到浏览器.
<dependency>
	<groupId>net.sourceforge.jexcelapi</groupId>
	<artifactId>jxl</artifactId>
	<version>2.6.12</version>
</dependency>
@GetMapping(value = "/exportExcel")
public void exportExcel(@RequestParam("currentPage") Integer currentPage,
                        @RequestParam("pageSize") Integer pageSize,
                        @RequestParam("depotId") Long depotId,
                        @RequestParam("monthTime") String monthTime,
                        @RequestParam("headIds") String headIds,
                        @RequestParam("materialIds") String materialIds,
                        HttpServletRequest request, HttpServletResponse response) {
    Long tenantId = Long.parseLong(request.getSession().getAttribute("tenantId").toString());
    try {
        List<DepotItemVo4WithInfoEx> dataList = depotItemService.findByAll(headIds, materialIds, (currentPage-1)*pageSize, pageSize);
        //存放数据json数组
        String[] names = {"名称", "型号", "单位", "单价", "上月结存数量", "入库数量", "出库数量", "本月结存数量", "结存金额"};
        String title = "库存报表";
        List<String[]> objects = new ArrayList<String[]>();
        if (null != dataList) {
            for (DepotItemVo4WithInfoEx diEx : dataList) {
                Long mId = diEx.getMId();
                String timeA = monthTime+"-01 00:00:00";
                String timeB = monthTime+"-31 23:59:59";
                String[] objs = new String[9];
                objs[0] = diEx.getMName().toString();
                objs[1] = diEx.getMModel().toString();
                objs[2] = diEx.getMaterialUnit().toString();
                objs[3] = getUnitPrice(diEx.getPresetPriceOne(), diEx.getPriceStrategy()).toString();
                objs[4] = depotItemService.getStockByParam(depotId,mId,null,timeA,tenantId).toString();
                objs[5] = depotItemService.getInNumByParam(depotId,mId,timeA,timeB,tenantId).toString();
                objs[6] = depotItemService.getOutNumByParam(depotId,mId,timeA,timeB,tenantId).toString();
                BigDecimal thisSum = depotItemService.getStockByParam(depotId,mId,null,null,tenantId);
                objs[7] = thisSum.toString();
                objs[8] = thisSum.multiply(getUnitPrice(diEx.getPresetPriceOne(), diEx.getPriceStrategy())).toString();
                objects.add(objs);
            }
        }
				//这是先生成Excel文件.
        File file = ExcelUtils.exportObjectsWithoutTitle(title, names, title, objects);
				//这是把Excel文件用response返回.
        ExportExecUtil.showExec(file, file.getName() + "-" + monthTime, response);
    } catch (Exception e) {
        e.printStackTrace();
    }
}
/**
 * java后台生成excel
   不需要第一行的title
 *
 * @param fileName
 * @param names
 * @param title
 * @param objects
 * @return
 * @throws Exception
 */
public static File exportObjectsWithoutTitle(String fileName,
		String[] names, String title, List<String[]> objects)
		throws Exception {
	File excelFile = new File(fileName);
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);

	// 第一行的格式
	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 15,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);

	// 设置字体以及单元格格式
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
	WritableCellFormat format = new WritableCellFormat(wfont);
	format.setAlignment(Alignment.LEFT);
	format.setVerticalAlignment(VerticalAlignment.TOP);

	// 第一行写入标题
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 0, names[i], wcfFC));
	}

	// 其余行依次写入数据
	int rowNum = 1;
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;
	}
	wtwb.write();
	wtwb.close();
	return excelFile;
}

public static void showExec(File excelFile,String fileName,HttpServletResponse response) throws Exception{
	   response.setContentType("application/octet-stream");
       fileName = new String(fileName.getBytes("gbk"),"ISO8859_1");
	   response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
	   FileInputStream fis = new FileInputStream(excelFile);
	   OutputStream out = response.getOutputStream();

	   int SIZE = 1024 * 1024;
	   byte[] bytes = new byte[SIZE];
	   int LENGTH = -1;
	   while((LENGTH = fis.read(bytes)) != -1){
		   out.write(bytes,0,LENGTH);
	   }

	   out.flush();
	   fis.close();
}
js
//核心 就是一个get请求,服务器返回Excel文件.
window.location.href = "/depotItem/exportExcel?browserType=" + getOs() 
      + "¤tPage=" + pageNo + "&pageSize=" + pageSize + "&depotId=" + $.trim($("#searchProjectId").val()) 
      + "&monthTime=" + $("#searchMonth").val() + "&headIds=" + HeadIds + "&materialIds=" + mIds;

function showEachDetails(pageNo, pageSize) {
        $.ajax({
            type: "get",
            url: "/depotHead/findByMonth",
            dataType: "json",
            data: ({
                monthTime: $("#searchMonth").val()
            }),
            success: function (res) {
                if(res && res.code === 200 && res.data) {
                    var HeadIds = res.data.HeadIds;
                    if (HeadIds) {
                        //获取排序后的产品ID
                        $.ajax({
                            type: "get",
                            url: "/material/findByOrder",
                            dataType: "json",
                            success: function (res) {
                                if(res && res.code === 200 && res.data) {
                                    var mIds = res.data.mIds;
                                    if (mIds) {
                                        if (pageSize === 3000) {
                                            window.location.href = "/depotItem/exportExcel?browserType=" + getOs() + "¤tPage=" + pageNo + "&pageSize=" + pageSize + "&depotId=" + $.trim($("#searchProjectId").val()) + "&monthTime=" + $("#searchMonth").val() + "&headIds=" + HeadIds + "&materialIds=" + mIds;
                                        }
                                        else {
                                            $.ajax({
                                                type: "post",
                                                url: "/depotItem/findByAll",
                                                dataType: "json",
                                                data: ({
                                                    currentPage: pageNo,
                                                    pageSize: pageSize,
                                                    depotId: $.trim($("#searchProjectId").val()),
                                                    monthTime: $("#searchMonth").val(),
                                                    headIds: HeadIds,
                                                    materialIds: mIds,
                                                    mpList: mPropertyList
                                                }),
                                                success: function (res) {
                                                    if(res && res.code === 200) {
                                                        $("#tableData").datagrid('loadData', res.data);
                                                    }
                                                },
                                                //此处添加错误处理
                                                error: function () {
                                                    $.messager.alert('查询提示', '查询数据后台异常,请稍后再试!', 'error');
                                                    return;
                                                }
                                            });

                                            //总金额
                                            $.ajax({
                                                type: "post",
                                                url: "/depotItem/totalCountMoney",
                                                dataType: "json",
                                                data: ({
                                                    depotId: $.trim($("#searchProjectId").val()),
                                                    monthTime: $("#searchMonth").val(),
                                                    headIds: HeadIds,
                                                    materialIds: mIds
                                                }),
                                                success: function (res) {
                                                    if(res && res.code === 200) {
                                                        if(res.data && res.data.totalCount) {
                                                            var count = res.data.totalCount.toString();
                                                            if (count.lastIndexOf('.') > -1) {
                                                                count = count.substring(0, count.lastIndexOf('.') + 3);
                                                            }
                                                            $("#searchTable .total-count").text("本月合计金额:" + count + "元");//本月合计金额
                                                        }
                                                    }
                                                },
                                                //此处添加错误处理
                                                error: function () {
                                                    $.messager.alert('查询提示', '查询数据后台异常,请稍后再试!', 'error');
                                                    return;
                                                }
                                            });
                                        }
                                    }
                                    else {
                                        $.messager.alert('查询提示', '本月无数据!', 'error');
                                    }
                                }
                            },
                            //此处添加错误处理
                            error: function () {
                                $.messager.alert('查询提示', '查询数据后台异常,请稍后再试!', 'error');
                                return;
                            }
                        });
                    }
                    else {
                        $.messager.alert('查询提示', '本月无数据!', 'error');
                    }
                }
            },
            //此处添加错误处理
            error: function () {
                $.messager.alert('查询提示', '查询数据后台异常,请稍后再试!', 'error');
                return;
            }
        });
    }
0

评论区