使用JasperReport和poi导出Excel

1、前言

在实际开发中，经常会遇到需将员工信息、设备信息导出到Excel。
使用JasperReport和poi导出Excel，如何实现呢
导出页面如下图：

导出的Excel如下图：

2、需要的jar包

JasperReport：
jasperreports-5.6.0.jar
jasperreports-fonts-5.0.0.jar

Poi:
poi-3.12.jar
poi-excelant-3.12.jar
poi-ooxml-3.12.jar
poi-ooxml-schemas-3.12.jar
poi-scratchpad-3.12.jar

3、页面代码：

导出按钮

<td>    <input type="file" name="uploadFile" multiple id="uploadFile" accept=".xls,.xlsx" style="font-size: 10px;font-family: Microsoft YaHei;border-style: inset;border-width: 2px;padding: 1px;">                 </td><td>    <a onclick="exportClick();"        href="javascript:void(0);"        class="easyui-linkbutton ebsButton"        data-options="iconCls:'fa fa-1x fa-fw fa-file-excel-o',plain:true">       导出    </a></td>

exportClick（）函数

//导出设备信息function exportClick(){   //查询要导出设备信息的数量   $.ajax({      type:"POST",      dataType: "json",       cache:false,      url:"/dev/base/dev/getAllDevCount.do",      data: getFormData("searchForm"),      async: false,      success: function(data) {         if(data > 0){            //如果要导出的设备信息的数量大于0，执行数据导出操作            $("#searchForm").attr("action",                 "/dev/base/dev/exportDevs.do").submit();         }else{            layer.msg('未能查询到数据', {icon: 0});         }      },      error: function(data) {         layer.msg('导出失败,查询导出数据数量异常!', {icon: 0});      }  }); }

注：我们执行数据导出操作的代码是：

$("#searchForm").attr("action",                 "/dev/base/dev/exportDevs.do").submit();

为了使用户操作体验更友好，也可以采用在弹出框中显示导出文件。
代码如下图：

var keyWords = $("#details_keyWords").val();var url = "/dev/base/dev/exportDevs.do?keyWords="+keyWords;//弹出窗口的宽度;var iWidth=600; //弹出窗口的高度;var iHeight=200; //获得窗口的垂直位置;var iTop = (window.screen.availHeight-30-iHeight)/2; //获得窗口的水平位置;var iLeft = (window.screen.availWidth-10-iWidth)/2; window.open(url,"","toolbar=no, menubar=no, scrollbars=no, resizable=no, location=no, status=no,height="+iHeight+", width="+iWidth+", top="+iTop+", left="+iLeft); 

如下图：

4、后台代码：
注：查询要导出设备信息的数量的方法getAllDevCount（）就不贴代码了

/** *  * 导出设备信息 * @author张三  * @param response * @param devBean 导出的查询条件 */@RequestMapping("/exportDevs")public void exportDevs(HttpServletResponse response,DeviceBean devBean){   try {      List<DeviceBean> result = deviceService.getAllDevs(bean);     //JasperReport参数封装工具类      JasperReportCriteria criteria = new JasperReportCriteria();     //模板名     criteria.setJrxmlName("Device");     criteria.setResult(result);     //路径     criteria.setJrxmlPath("/report/");     criteria.setAutoBuildJasper(false);     //设置工作薄name     criteria.setSheetName("设备信息");     //设置导出的文件名     criteria.setReportFileName("设备信息"+DateTimeUtils.getNowDateString());//文件名     //文件类型            criteria.setType(JasperReportCriteria.REPORT_TYPE_MSEXCEL);     ReportExporter.exportReport(criteria, response);   } catch (Exception e) {       log.error("导出设备信息异常", e);   }}

JasperReport参数封装工具类JasperReportCriteria.java

@SuppressWarnings("serial")public class JasperReportCriteria implements java.io.Serializable  {    public static final String JRXML_EXT_NAME = ".jrxml";    public static final String JASPER_EXT_NAME = ".jasper";    public static final String REPORT_TYPE_TXT = "text";    public static final String REPORT_TYPE_RTF = "rtf";    public static final String REPORT_TYPE_HTML = "html";    public static final String REPORT_TYPE_XML = "xml";    public static final String REPORT_TYPE_PDF = "pdf";    public static final String REPORT_TYPE_MSWORD = "msword";    public static final String REPORT_TYPE_MSEXCEL = "msexcel";    public static final String REPORT_TYPE_MSPPT = "msppt";    public static final String REPORT_TYPE_CSV = "csv";    public static final String REPORT_EXT_NAME_CSV = ".csv";    public static final String REPORT_EXT_NAME_TXT = ".txt";    public static final String REPORT_EXT_NAME_RTF = ".rtf";    public static final String REPORT_EXT_NAME_HTML = ".html";    public static final String REPORT_EXT_NAME_XML = ".xml";    public static final String REPORT_EXT_NAME_PDF = ".pdf";    public static final String REPORT_EXT_NAME_MSWORD = ".doc";    public static final String REPORT_EXT_NAME_MSEXCEL = ".xls";    public static final String REPORT_EXT_NAME_MSPPT = ".ppt";    public static final String RESPONSE_CONTENT_TYPE_TXT = "text/plain";    public static final String RESPONSE_CONTENT_TYPE_RTF = "application/rtf";    public static final String RESPONSE_CONTENT_TYPE_HTML = "text/html";    public static final String RESPONSE_CONTENT_TYPE_XML = "text/xml";    public static final String RESPONSE_CONTENT_TYPE_PDF = "application/pdf";    public static final String RESPONSE_CONTENT_TYPE_MSWORD = "application/msword";    public static final String RESPONSE_CONTENT_TYPE_MSEXCEL = "application/vnd.ms-excel";    public static final String RESPONSE_CONTENT_TYPE_MSPPT = "application/vnd.ms-powerpoint";    public static final String CHARACTER_ENCODING = "UTF-8";    public static final String DEFAULT_REPORT_FILE_NAME = "noTitle";    /** contentTypeMap */    public static Map<String, String> contentTypeMap;    /** reportExtNameMap */    public static Map<String, String> reportExtNameMap;    static {        contentTypeMap = new HashMap<String, String>();        contentTypeMap.put(REPORT_TYPE_TXT, RESPONSE_CONTENT_TYPE_TXT);        contentTypeMap.put(REPORT_TYPE_RTF, RESPONSE_CONTENT_TYPE_RTF);        contentTypeMap.put(REPORT_TYPE_HTML, RESPONSE_CONTENT_TYPE_HTML);        contentTypeMap.put(REPORT_TYPE_XML, RESPONSE_CONTENT_TYPE_XML);        contentTypeMap.put(REPORT_TYPE_PDF, RESPONSE_CONTENT_TYPE_PDF);        contentTypeMap.put(REPORT_TYPE_MSWORD, RESPONSE_CONTENT_TYPE_MSWORD);        contentTypeMap.put(REPORT_TYPE_MSEXCEL, RESPONSE_CONTENT_TYPE_MSEXCEL);        contentTypeMap.put(REPORT_TYPE_MSPPT, RESPONSE_CONTENT_TYPE_MSPPT);        reportExtNameMap = new HashMap<String, String>();        reportExtNameMap.put(REPORT_TYPE_TXT, REPORT_EXT_NAME_TXT);        reportExtNameMap.put(REPORT_TYPE_RTF, REPORT_EXT_NAME_RTF);        reportExtNameMap.put(REPORT_TYPE_HTML, REPORT_EXT_NAME_HTML);        reportExtNameMap.put(REPORT_TYPE_XML, REPORT_EXT_NAME_XML);        reportExtNameMap.put(REPORT_TYPE_PDF, REPORT_EXT_NAME_PDF);        reportExtNameMap.put(REPORT_TYPE_MSWORD, REPORT_EXT_NAME_MSWORD);        reportExtNameMap.put(REPORT_TYPE_MSEXCEL, REPORT_EXT_NAME_MSEXCEL);        reportExtNameMap.put(REPORT_TYPE_MSPPT, REPORT_EXT_NAME_MSPPT);    }    /** 结果集（根据不同的报表放置于报表对应的类） */    private Collection<?> result;    /** 子报表集合  */    private Map<String,String> subjrXmlNames;    /** sheet名称 for MS-Excel*/    private String sheetName;    /** jrxml文件的名字（不包含包名和后缀名） */    private String jrxmlName;    /** 产生的报表文件名 */    private String reportFileName = JasperReportCriteria.DEFAULT_REPORT_FILE_NAME;    /** 其他显示的属性（ 如列印时间、 条件 等，可以為null） */    private Map<String, Object> parameters;    /** 导出的格式（默认是pdf） */    private String type = JasperReportCriteria.REPORT_TYPE_PDF;    /** 是否使用iReport设计jrxml的方式（true:不使用iReport， false:使用iReport） */    private boolean autoBuildJasper;    /** jrXml路径前缀*/    private String jrxmlPath;    //  get set 方法  省略.....}```> 处理JasperReport工具ReportExporter.java

/* 导出报表 * @param criteria - JasperReport条件设定DTO * @param response - HttpServletResponse * @return 导出是否成功 * @throws Exception */@SuppressWarnings({ "rawtypes", "unchecked" })public static boolean exportReport(JasperReportCriteria criteria, HttpServletResponse response) throws Exception {    log.debug("Enter method ReportExporter.exportReport()");    if (criteria == null) {        return false;    }    ByteArrayOutputStream outputStream = null;    ServletOutputStream oStream = null;    try {        JasperReport jasperReport = null;        // 需要jrXml文档的方式        if (criteria.isAutoBuildJasper() == false) {            String jrXml = criteria.getJrxmlPath() + criteria.getJrxmlName() + JasperReportCriteria.JRXML_EXT_NAME;            InputStream inputStream = ReportExporter.class.getResourceAsStream(jrXml);            JasperDesign design = JRXmlLoader.load(inputStream);            // 编译报表（.jrxml -> .jasper）            jasperReport = JasperCompileManager.compileReport(design);        }        // 填充报表        JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, criteria.getParameters(),                     new JRBeanCollectionDataSource(criteria.getResult()));        outputStream = new ByteArrayOutputStream();        JRAbstractExporter exporter = null;        if (StringUtils.hasText(criteria.getType()) == false) {            exporter = new JRPdfExporter();            SimplePdfExporterConfiguration configuration = new SimplePdfExporterConfiguration();            exporter.setConfiguration(configuration);        } else if (criteria.getType().equals(JasperReportCriteria.REPORT_TYPE_MSWORD)) {            exporter = new JRRtfExporter();            SimpleRtfReportConfiguration configuration = new SimpleRtfReportConfiguration();            exporter.setConfiguration(configuration);        } else if (criteria.getType().equals(JasperReportCriteria.REPORT_TYPE_MSEXCEL)) {            exporter = new JRXlsExporter();            SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();            configuration.setOnePagePerSheet(true);            configuration.setDetectCellType(true);            configuration.setCollapseRowSpan(false);            configuration.setSheetNames(new String[]{criteria.getSheetName()});            exporter.setConfiguration(configuration);        } else {            exporter = new JRPdfExporter();            SimplePdfExporterConfiguration configuration = new SimplePdfExporterConfiguration();            exporter.setConfiguration(configuration);        }        exporter.setExporterInput(new SimpleExporterInput(jasperPrint));        exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputStream));        exporter.exportReport();        byte[] bytes = outputStream.toByteArray();        response.setContentType(JasperReportCriteria.getContentTypeMap().get(criteria.getType()));        response.setCharacterEncoding("iso8859-1");        String fileName = URLEncoder.encode(criteria.getReportFileName(), JasperReportCriteria.CHARACTER_ENCODING) + JasperReportCriteria.getReportExtNameMap().get(criteria.getType());        fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");        response.setHeader("Content-disposition", "attachment;filename="                + fileName);        response.setContentLength(bytes.length);        outputStream.close();        oStream = response.getOutputStream();        oStream.write(bytes, 0, bytes.length);        oStream.flush();        oStream.close();        log.debug("Exit method ReportExporter.exportReport()");        return true;    } catch (Exception e) {        log.error("Exception: method ReportExporter.exportReport() - " + e, e);        return false;    } finally {        if (oStream != null) {            oStream.close();        }        if (outputStream != null) {            outputStream.close();        }    }}```

导出的模板文件Device.jrxml

<?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="AttRecord" pageWidth="1000" pageHeight="130" columnWidth="980" leftMargin="20" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true" uuid="909756e9-7e42-4320-88b9-e6960ea16436">    <property name="ireport.zoom" value="1.0"/>    <property name="ireport.x" value="0"/>    <property name="ireport.y" value="0"/>    <field name="rowNum" class="java.lang.Long"/>    <field name="name" class="java.lang.String"/>    <field name="code" class="java.lang.String"/>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="35">            <staticText>                <reportElement uuid="eb1ad22c-ecaa-40c5-a615-9ef8be85a201" x="0" y="0" width="1240" height="35"/>                <box>                    <topPen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="18" isBold="true"/>                </textElement>                <text><![CDATA[设备信息]]></text>            </staticText>        </band>    </title>    <columnHeader>        <band height="30">            <staticText>                <reportElement uuid="e0136fd9-b2f7-4827-a77c-56dda9a00a0b" x="0" y="0" width="40" height="30"/>                <box>                    <topPen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[序号]]></text>            </staticText>            <staticText>                <reportElement uuid="a33947a2-beb1-47c5-9823-8ece52a87a6b" x="40" y="0" width="150" height="30" backcolor="#33FFFF"/>                <box>                    <topPen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[设备名称]]></text>            </staticText>            <staticText>                <reportElement uuid="a33947a2-beb1-47c5-9823-8ece52a87a6b" x="190" y="0" width="150" height="30" backcolor="#33FFFF"/>                <box>                    <topPen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[管理编号]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="25" splitType="Stretch">            <textField>                <reportElement uuid="4adf333f-881b-47ad-b066-d687a8c92848" x="0" y="0" width="40" height="25"/>                <box>                    <pen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle"/>                <textFieldExpression><![CDATA[$F{rowNum}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="4adf333f-881b-47ad-b066-d687a8c92848" x="40" y="0" width="150" height="25"/>                <box>                    <pen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle"/>                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="4adf333f-881b-47ad-b066-d687a8c92848" x="190" y="0" width="150" height="25"/>                <box>                    <pen lineWidth="0.5"/>                    <leftPen lineWidth="0.5"/>                    <bottomPen lineWidth="0.5"/>                    <rightPen lineWidth="0.5"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle"/>                <textFieldExpression><![CDATA[$F{code}]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>

如何一个Excel文件中的信息导入到系统中呢？请查看后面的一篇文章 使用POI导入Excel文件信息