因需要实现页面上导出Excel时能够隐藏某些Excel列，（jasperreports6.3.1）尝试修改Jasperreports的jrxml模板文件，比如在jrxml元素table或者文本元素的reportElement标签里添加属性net.sf.jasperreports.export.xls.cell.hidden，结论是无论是xls格式还是xlsx格式都不奏效。
(但是，net.sf.jasperreports.export.xls.freeze.column.edge这个属性实现冻结行列却能生效，不知为何)
于是借助第三方API：JXL、POI，在Excel文件生成后解除这些API修改生成的Excel以实现Web页面导出时就隐藏某些列。
项目基于struts2框架，基于编译好的jasper模板使用struts2-jasperreports-plugin填充数据在web上实现导出Excel文件，
基本流程：
一，使用iReport(5.6)设计基础模板；
过程略；
二，重写/扩展struts2的类似struts2-jasperreports-plugin的自定义result处理过程；
1，获取jasper模板(location)、Struts2的dataSource、format、值栈数据、值栈参数
2，设定HttpServletResponse的response类型(本质上就是对HttpServletResponse对象设置以响应HttpServletRequest)
3，填充数据生成JasperPrint
4，JRXlsxExporter/JRXlsExporter导出Excel文件
5，使用POI/Jxl修改Excel工作簿行列隐藏属性(使用struts值栈传递参数)，写入字节流
自定义Result代码：
public class JasperReportsResult extends StrutsResultSupport implements JasperReportConstants {
private static final long serialVersionUID = -2523174799621182907L;
private final static Logger LOG = LoggerFactory.getLogger(JasperReportsResult.class);
protected String dataSource;
protected String format;
protected String documentName;
protected String contentDisposition;
protected String delimiter;
protected String imageServletUrl = "/images/";
protected String timeZone;
/**
* Connection which can be passed to the report
* instead od dataSource.
*/
protected String connection;
/**
* Names a report parameters map stack value, allowing
* additional report parameters from the action.
*/
protected String reportParameters;
/**
* Names an exporter parameters map stack value,
* allowing the use of custom export parameters.
*/
protected String exportParameters;
/**
* Default ctor.
*/
public JasperReportsResult() {
super();
}
/**
* Default ctor with location.
*
* @param location Result location.
*/
public JasperReportsResult(String location) {
super(location);
}
public String getImageServletUrl() {
return imageServletUrl;
}
public void setImageServletUrl(final String imageServletUrl) {
this.imageServletUrl = imageServletUrl;
}
public void setDataSource(String dataSource) {
this.dataSource = dataSource;
}
public void setFormat(String format) {
this.format = format;
}
public void setDocumentName(String documentName) {
this.documentName = documentName;
}
public void setContentDisposition(String contentDisposition) {
this.contentDisposition = contentDisposition;
}
public void setDelimiter(String delimiter) {
this.delimiter = delimiter;
}
/**
* set time zone id
*
* @param timeZone
*/
public void setTimeZone(final String timeZone) {
this.timeZone = timeZone;
}
public String getReportParameters() {
return reportParameters;
}
public void setReportParameters(String reportParameters) {
this.reportParameters = reportParameters;
}
public String getExportParameters() {
return exportParameters;
}
public void setExportParameters(String exportParameters) {
this.exportParameters = exportParameters;
}
public String getConnection() {
return connection;
}
public void setConnection(String connection) {
this.connection = connection;
}
private String tmppath;
private String hiddencs;
protected void doExecute(String finalLocation, ActionInvocation invocation) throws Exception {
// Will throw a runtime exception if no "datasource" property. TODO Best place for that is...?
initializeProperties(invocation);
if (LOG.isDebugEnabled()) {
LOG.debug("Creating JasperReport for dataSource = " + dataSource + ", format = " + format);
}
HttpServletRequest request = (HttpServletRequest) invocation.getInvocationContext().get(ServletActionContext.HTTP_REQUEST);
HttpServletResponse response = (HttpServletResponse) invocation.getInvocationContext().get(ServletActionContext.HTTP_RESPONSE);
// Handle IE special case: it sends a "contype" request first.
// TODO Set content type to config settings?
if ("contype".equals(request.getHeader("User-Agent"))) {
try {
// response.setContentType("application/pdf");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//xlsx
// response.setContentType("application/vnd.ms-excel");//xls
response.setContentLength(0);
ServletOutputStream outputStream = response.getOutputStream();
outputStream.close();
} catch (IOException e) {
LOG.error("Error writing report output", e);
throw new ServletException(e.getMessage(), e);
}
return;
}
// Construct the data source for the report.
ValueStack stack = invocation.getStack();
ValueStackDataSource stackDataSource = null;
/**
* 获取值栈中的值
*
* ****/
this.hiddencs = (String) stack.findString("xlsxhidecolums");
LOG.info("value stack, xlsxhidecolums: "+ hiddencs);
Connection conn = (Connection) stack.findValue(connection);
if (conn == null)
stackDataSource = new ValueStackDataSource(stack, dataSource);
// Determine the directory that the report file is in and set the reportDirectory parameter
// For WW 2.1.7:
// ServletContext servletContext = ((ServletConfig) invocation.getInvocationContext().get(ServletActionContext.SERVLET_CONFIG)).getServletContext();
ServletContext servletContext = (ServletContext) invocation.getInvocationContext().get(ServletActionContext.SERVLET_CONTEXT);
String systemId = servletContext.getRealPath(finalLocation);
tmppath = servletContext.getRealPath("/") + "/" + UUID.randomUUID()+String.valueOf(System.currentTimeMillis()) + ".xlsx";/////
Map parameters = new ValueStackShadowMap(stack);
File directory = new File(systemId.substring(0, systemId.lastIndexOf(File.separator)));
parameters.put("reportDirectory", directory);
parameters.put(JRParameter.REPORT_LOCALE, invocation.getInvocationContext().getLocale());
// put timezone in jasper report parameter
if (timeZone != null) {
timeZone = conditionalParse(timeZone, invocation);
final TimeZone tz = TimeZone.getTimeZone(timeZone);
if (tz != null) {
// put the report time zone
parameters.put(JRParameter.REPORT_TIME_ZONE, tz);
}
}
// Add any report parameters from action to param map.
Map reportParams = (Map) stack.findValue(reportParameters);
if (reportParams != null) {
if (LOG.isDebugEnabled()) {
LOG.debug("Found report parameters; adding to parameters...");
}
parameters.putAll(reportParams);
}
// String hide = (String) stack.findValue(hidecolumns);
// LOG.info("hide str: "+hide);
byte[] output;
JasperPrint jasperPrint;
// Fill the report and produce a print object
try {
JasperReport jasperReport = (JasperReport) JRLoader.loadObjectFromFile(systemId);
if (conn == null)
jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, stackDataSource);
else
jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, conn);
} catch (JRException e) {
LOG.error("Error building report for uri " + systemId, e);
throw new ServletException(e.getMessage(), e);
}
// Export the print object to the desired output format
try {
if (contentDisposition != null || documentName != null) {
final StringBuffer tmp = new StringBuffer();
tmp.append((contentDisposition == null) ? "inline" : contentDisposition);
if (documentName != null) {
tmp.append("; filename=");
tmp.append(documentName);
tmp.append(".");
tmp.append(format.toLowerCase());
}
response.setHeader("Content-disposition", tmp.toString());
}
JRExporter exporter;
if ( format.equals(FORMAT_XLSX)){
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//xlsx
// response.setContentType("application/vnd.ms-excel");//xls
exporter = new JRXlsxExporter(); //xlsx
// exporter = new JRXlsExporter();//xls
}
else {
throw new ServletException("Unknown report format: " + format);
}
Map exportParams = (Map) stack.findValue(exportParameters);
if (exportParams != null) {
if (LOG.isDebugEnabled()) {
LOG.debug("Found export parameters; adding to exporter parameters...");
}
exporter.getParameters().putAll(exportParams);
}
output = exportReportToBytes(jasperPrint, exporter);
} catch (JRException e) {
String message = "Error producing " + format + " report for uri " + systemId;
LOG.error(message, e);
throw new ServletException(e.getMessage(), e);
}
response.setContentLength(output.length);
writeReport(response, output);
}
/**
* Writes report bytes to response output stream.
*
* @param response Current response.
* @param output Report bytes to write.
* @throws ServletException on stream IOException.
*/
private void writeReport(HttpServletResponse response, byte[] output) throws ServletException {
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
outputStream.write(output);
outputStream.flush();
} catch (IOException e) {
LOG.error("Error writing report output", e);
throw new ServletException(e.getMessage(), e);
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
LOG.error("Error closing report output stream", e);
throw new ServletException(e.getMessage(), e);
}
}
}
/**
* Sets up result properties, parsing etc.
*
* @param invocation Current invocation.
* @throws Exception on initialization error.
*/
private void initializeProperties(ActionInvocation invocation) throws Exception {
if (dataSource == null && connection == null) {
String message = "No dataSource specified...";
LOG.error(message);
throw new RuntimeException(message);
}
if (dataSource != null)
dataSource = conditionalParse(dataSource, invocation);
format = conditionalParse(format, invocation);
if (StringUtils.isEmpty(format)) {
format = FORMAT_XLSX; //XLSX
}
if (contentDisposition != null) {
contentDisposition = conditionalParse(contentDisposition, invocation);
}
if (documentName != null) {
documentName = conditionalParse(documentName, invocation);
}
reportParameters = conditionalParse(reportParameters, invocation);
exportParameters = conditionalParse(exportParameters, invocation);
}
/**
* Run a Jasper report to CSV format and put the results in a byte array
*
* @param jasperPrint The Print object to render as CSV
* @param exporter The exporter to use to export the report
* @return A CSV formatted report
* @throws net.sf.jasperreports.engine.JRException
* If there is a problem running the report
* @throws IOException
* @throws BiffException
* @throws WriteException
*/
private byte[] exportReportToBytes(JasperPrint jasperPrint, JRExporter exporter) throws JRException, IOException, BiffException, WriteException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
FileOutputStream tmpos = new FileOutputStream(this.tmppath);//
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, tmpos);
exporter.exportReport();
boolean ifhide = false;
org.apache.poi.ss.usermodel.Workbook wb = //new HSSFWorkbook(new FileInputStream(this.tmppath));//xls
new XSSFWorkbook(new FileInputStream(this.tmppath));//XLSX ;
String hidden[] = this.hiddencs.split("\\,", -1);
for (int i = 0; i < hidden.length; i++) {
wb.getSheetAt(0).setColumnHidden(Integer.valueOf(hidden[i]), true);//参数获取
}
wb.write(baos);
tmpos.close();
wb.close();
byte[] output;
File f = new File(this.tmppath);
f.delete();
if (delimiter != null) {
exporter.setParameter(JRCsvExporterParameter.FIELD_DELIMITER, delimiter);
}
output = baos.toByteArray();
return output;
}
}
三，struts2项目中配置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>struts2plugintest1</display-name>
<filter>
<filter-name>struts2</filter-name>
<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>struts2</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
四，struts2项目配置struts.xml
1，struts2会按顺序加载struts-default.xml/struts-plugin.xml/struts.xml的配置项，struts.xml中配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
"http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
<constant name="struts.i18n.encoding" value="UTF-8" />
<constant name="struts.devMode" value="true" />
<constant name="struts.configuration.xml.reload" value="true" />
<package name="default" namespace="/" extends="jasperxlsxhide-default">
<action name="xlsxhide" class="merrick.action.TesthideAction" >
<result type="jasperxlsxhide" name="getxlsx">
<param name="dataSource">dlst</param>
<param name="location">/reports/report3.jasper</param>
<param name="reportParameters">pmap</param>
<param name="format">XLSX</param>
</result>
</action>
</package>
</struts>
2，自定义(扩展/重写)的Reslut类型(jasperxlsxhide)，需要配置在插件jar包中的struts-plugin.xml文件中：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
"http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
<package name="jasperxlsxhide-default" extends="struts-default">
<result-types>
<result-type name="jasperxlsxhide" class="struts2plugin.merrick.xlsxhide.japser.JasperReportsResult"/>
</result-types>
</package>
</struts>
五，struts2的Action调用部分
public String execute() throws Exception {// TEST SUCCESSFUL
Map param = new HashMap<String , Object>();
param.put("parameterA", "11");
param.put("paramtitle", "HELLO");//
param.put("displayCol4", true);//work
this.setPmap(param);
ArrayList<DataBean> dataList = new ArrayList<DataBean>();
setListdata(dataList);
this.setDlst(dataList);
//http://localhost:8080/struts2plugintest1/xlsxhide.action
ctx.getActionInvocation().getStack().set("xlsxhidecolums", "3,4");//
return "getxlsx";
}
763
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
