阿里开源的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;
}
});
}
评论区