I am trying to display barcode in excel sheet using API's like Apache POI but didn't found any solution.
I also tried using JasperReports (as we can generate barcode in it) and then convert the compiled report to excel using JRXlsExporter.
Below is my jrxml for the excel export
<?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="customer_export" language="groovy" pageWidth="755" pageHeight="842" columnWidth="715" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<parameter name="siteId" class="java.lang.String">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="companyId" class="java.lang.String">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT c.NAME ,c.EXTERNAL_CODE ,a.ADDRESS,CASE WHEN c.LOCAL_MAIL IS NOT NULL THEN c.LOCAL_MAIL when C.MAIL IS NOT NULL then c.MAIL when c.MAIL IS Null then NULL when c.LOCAL_MAIL is NULL then NULL ELSE NULL END as MAIL, ru.BARCODE, c.PHONE FROM CUSTOMER c, SITE csite, RACK_USER ru, ADDRESS a WHERE c.COMPANY_SITE_ID = csite.ID_SITE AND c.RACK_USER_ID = ru.ID_RACK_USER AND ru.ID_RACK_USER = a.RACK_USER_ID AND csite.ID_SITE=$P{companyId} AND c.SITE_ID=$P{siteId} AND a.MAIN_FOR_COMPANY_SITE=1 AND a.COMPANY_SITE_ID =$P{companyId} ORDER BY c.NAME ASC]]>
</queryString>
<field name="NAME" class="java.lang.String"/>
<field name="EXTERNAL_CODE" class="java.lang.String"/>
<field name="ADDRESS" class="java.lang.String"/>
<field name="MAIL" class="java.lang.String"/>
<field name="BARCODE" class="java.lang.String"/>
<field name="PHONE" class="java.lang.String"/>
<title>
<band height="20">
<staticText>
<reportElement mode="Opaque" x="0" y="0" width="119" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Customer Name]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="119" y="0" width="112" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Customer Code]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="231" y="0" width="157" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Address]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="388" y="0" width="121" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Email]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="509" y="0" width="100" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Barcode]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="609" y="0" width="106" height="20" backcolor="#CCCCCC"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Phone Number]]></text>
</staticText>
</band>
</title>
<detail>
<band height="24" splitType="Stretch">
<textField>
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="119" height="24"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
</textField>
<textField>
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="119" y="0" width="112" height="24"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{EXTERNAL_CODE}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="231" y="0" width="157" height="24"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{ADDRESS}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="388" y="0" width="121" height="24"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{MAIL}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="609" y="0" width="106" height="24"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{PHONE}]]></textFieldExpression>
</textField>
<componentElement>
<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="509" y="0" width="100" height="24"/>
<jr:barbecue xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" type="Code39" drawText="true" checksumRequired="false" evaluationTime="Report">
<jr:codeExpression><![CDATA["1234"]]></jr:codeExpression>
</jr:barbecue>
</componentElement>
</band>
</detail>
Java code to export excel:
JRXlsExporter exporterXLS = new JRXlsExporter();
exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jprint);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, reportos);
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET, 50000);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_FILE, filename);
exporterXLS.exportReport();
But in Barcode column I'm getting empty when excel is exported.
Is there any attribute I need to apply in JasperReports or in Java or anything else?
Or is there any other API's or can we display barcode using Apache POI?
Using the barcode4j or barbecue you can generate the barcode as an image in java and then pass it to jasper-reports
Java code
public class MyBarcodeGenerator {
public static Image getGS1_128(String code) {
if (code==null){
return null;
}
EAN128Bean c = new EAN128Bean();
c.setChecksumMode(ChecksumMode.CP_AUTO);
c.setOmitBrackets(false);
c.setCodeset(Code128Constants.CODESET_C);
c.setMsgPosition(HumanReadablePlacement.HRP_BOTTOM);
c.doQuietZone(true);
c.setQuietZone(5);
c.setFontSize(2d);
int dpi = 200;
boolean antiAlias = false;
int orientation = 0;
BitmapCanvasProvider canvas = new BitmapCanvasProvider(dpi, BufferedImage.TYPE_BYTE_BINARY, antiAlias, orientation);
c.generateBarcode(canvas, code);
return canvas.getBufferedImage();
}
}
jrxml, displaying the image
<image scaleImage="Clip" hAlign="Center" vAlign="Middle" isUsingCache="false" onErrorType="Blank">
<reportElement positionType="Float" x="112" y="67" width="315" height="95" uuid="b90b1ec8-1483-4677-8db4-4556ecdad6b3">
<printWhenExpression><![CDATA[new Boolean($F{GS1_128}!=null)]]></printWhenExpression>
</reportElement>
<imageExpression><![CDATA[my.package.MyBarcodeGenerator.getGS1_128($F{GS1_128})]]></imageExpression>
</image>
For correct image size change the dpi settings or use the batik libraries to generate svg images, it's a bit more complex but with much better resolution see barcode4j svg dom