取代 POI

最新推荐文章于 2022-07-22 10:10:33 发布

shengyao15

最新推荐文章于 2022-07-22 10:10:33 发布

阅读量428

点赞数

分类专栏：开源工具文章标签： Excel Bean HP Java Struts

本文链接：https://blog.csdn.net/shengyao15/article/details/83921755

版权

开源工具专栏收录该内容

4 篇文章 0 订阅

订阅专栏

项目中用POI来生成excel导出查询的结果，会有如下问题。
1，内存溢出经过测试，如果tomcat采用默认的JVM大小64M，当数据大于4w条就会OOM，
2，当数据为1W条，连续点击，会出现 socket write error。以下是HP的主要代码
3，【主要原因】 Excel样式会依赖与POI的API，不方便修改。
如果是用jsp来生成的话，样式可以有html来控制，而html和xls之间是可以通过EXCEL互相转化的。

Action


fileName = response.encodeURL(new String(ExportPRAction.SUMMARY_XLS.getBytes(), "UTF-8"));

			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ fileName + "\"");

			ServletOutputStream out = response.getOutputStream();
			export.exportFileBeansToExcel(bean.getFileBeans(), out);
			response.setStatus(HttpServletResponse.SC_OK);
			response.flushBuffer();
			out.close();

ExcelExporter


public void exportFileBeansToExcel(List<FileBean> beans, OutputStream out)
			throws Exception {

		if ((null == beans) || beans.isEmpty() || (null == out)) {
			logger.info(BEAN_OR_OUT_IS_NULL);
			throw new InvalidParameterException(BEAN_OR_OUT_IS_NULL);
		}

		Workbook workBook = new HSSFWorkbook();
		Sheet sheet = workBook.createSheet(SHEET_NAME);

		createHeader(workBook, sheet);

		createContents(workBook, sheet, beans);

		setSizeAutoFit(sheet);

		workBook.write(out);
	}

JSP生成Excel的方式


<%@ page contentType="application/vnd.ms-excel; charset=UTF-8"%>
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page language="java"
	import="com.hp.ucmdb.adapter.bean.SummaryPRBean"%>
<%@ page language="java" import="com.hp.ucmdb.adapter.bean.FileBean"%>
<%@ page language="java" import="java.util.ArrayList"%>
<%@ taglib prefix="logic" uri="http://struts.apache.org/tags-logic"%>
<%@ taglib prefix="bean" uri="http://struts.apache.org/tags-bean"%>
<%
	String filename = new String("Table");
	response.addHeader("Content-Disposition", "filename=" + filename
			+ ".xls");
%>

<html xmlns:v="urn:schemas-microsoft-com:vml"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 12">
</head>

<body link=blue vlink=purple>
<table border=0 cellpadding=0 cellspacing=0 width=1321
	style='border-collapse: collapse; table-layout: fixed; width: 991pt'>
	<col width=33
		style='mso-width-source: userset; mso-width-alt: 1206; width: 25pt'>
	<col width=307
		style='mso-width-source: userset; mso-width-alt: 11227; width: 230pt'>
	<col width=92
		style='mso-width-source: userset; mso-width-alt: 3364; width: 69pt'>
	<col width=131
		style='mso-width-source: userset; mso-width-alt: 4790; width: 98pt'>
	<col width=137 span=2
		style='mso-width-source: userset; mso-width-alt: 5010; width: 103pt'>
	<col width=103
		style='mso-width-source: userset; mso-width-alt: 3766; width: 77pt'>
	<col width=76
		style='mso-width-source: userset; mso-width-alt: 2779; width: 57pt'>
	<col width=143
		style='mso-width-source: userset; mso-width-alt: 5229; width: 107pt'>
	<col width=110
		style='mso-width-source: userset; mso-width-alt: 4022; width: 83pt'>
	<col width=52
		style='mso-width-source: userset; mso-width-alt: 1901; width: 39pt'>
	<tr bgcolor="blue" height=19 style='height: 14.25pt'>
		<td align="center" height=19 class=xl65 width=33
			style='height: 14.25pt; width: 25pt'>#</td>
		<td align="center" class=xl65 width=307 style='width: 230pt'>File
		Name</td>
		<td align="center" class=xl65 width=92 style='width: 69pt'>Data
		Source</td>
		<td align="center" class=xl65 width=131 style='width: 98pt'>File
		Modified Date</td>
		<td align="center" class=xl65 width=137 style='width: 103pt'>Process
		Start Date</td>
		<td align="center" class=xl65 width=137 style='width: 103pt'>Process
		End Date</td>
		<td align="center" class=xl65 width=103 style='width: 77pt'>Total
		Records</td>
		<td align="center" class=xl65 width=76 style='width: 57pt'>Status</td>
		<td align="center" class=xl65 width=143 style='width: 107pt'>Successful
		Records</td>
		<td align="center" class=xl65 width=110 style='width: 83pt'>Failed
		Records</td>
		<td align="center" class=xl65 width=52 style='width: 39pt'>CI/min</td>
	</tr>

	<logic:iterate id="li" name="fileBeans" indexId="index">
		<tr height=17 style='height: 12.75pt'>
			<td align="center"><%=index.intValue() + 1%></td>
			<td align="center"><bean:write name="li" property="fileName" /></td>
			<td align="center"><logic:empty
				name="<bean:write name='li' property='fileDataSource'/>">N/A</logic:empty>
			<logic:notEmpty
				name="<bean:write name='li' property='fileDataSource'/>">
				<bean:write name='li' property='fileDataSource' />
			</logic:notEmpty></td>
			<td align="center"><bean:write name="li" property="submitDate" /></td>
			<td align="center"><bean:write name="li"
				property="processStartDate" /></td>
			<td align="center"><bean:write name="li"
				property="processEndDate" /></td>
			<td align="center"><bean:write name="li" property="recordCount" /></td>
			<td align="center"><bean:write name="li" property="fileStatus" /></td>
			<td align="center"><logic:empty
				name="<bean:write name='li' property='recordsProcessed'/>">N/A</logic:empty>
			<logic:notEmpty
				name="<bean:write name='li' property='recordsProcessed'/>">
				<bean:write name='li' property='recordsProcessed' />
			</logic:notEmpty></td>
			<td align="center"><logic:empty
				name="<bean:write name='li' property='recordsInError'/>">N/A</logic:empty>
			<logic:notEmpty
				name="<bean:write name='li' property='recordsInError'/>">
				<bean:write name='li' property='recordsInError' />
			</logic:notEmpty></td>
			<td align="center"><bean:write name="li"
				property="ciNumbersPerMinute" /></td>

		</tr>
	</logic:iterate>

</table>

</body>

</html>

shengyao15

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
取代 POI

项目中用POI来生成excel导出查询的结果，会有如下问题。1，内存溢出经过测试，如果tomcat采用默认的JVM大小64M，当数据大于4w条就会OOM， 2，当数据为1W条，连续点击，会出现 socket write error。以下是HP的主要代码3，【主要原因】 Excel样式会依赖与POI的API，不方便修改。如果是用jsp来生成的话，样式可以有html...
复制链接

扫一扫

专栏目录