Export to excel converter text to scientific number solution

 you can continue using the SQL approach to include the zero-width space before the loanId values:

SELECT UNISTR('\200B') || loanId AS loanId FROM loan;

This SQL query will add an invisible zero-width space before each loanId value, preventing Excel from automatically converting the values to scientific notation when exported.

Jasper report - Export to excel force cell to convert as text.

Issue: some cell having numeric value like field name is TRANSACTION_ID and suppose value is like 1545789348775194 then it auto convert to 4548766+15 something like scientific notation once you click on cell and focus out so below jasper export to excel property help to set the cell property type = text.

The issue is now fixed and will be part of the next JasperReports release.

To have cells set as Text type in Excel, you need to set the following property for that element:

<property name="net.sf.jasperreports.export.xls.pattern" value="@"/>

Also, if the net.sf.jasperreports.export.xls.detect.cell.type property is set to false, the cell type will be set as Text.

how to create a multi/separate worksheet excel - Search Criteria in separate sheet

Just do copy past it will work. Even if not work just keep gap top of the summary band and first element to make sure it consider break.

set below jasper property in top of the report.

<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First 

sheet/Second sheet/Third sheet" />

Add break to see that part in separate worksheet. 

<summary>
    <band height="110">
        <printWhenExpression><![CDATA[$V{MAIN_REPORT_TABLE1_COUNT}>=1]]></printWhenExpression>
<textField>
        <reportElement X="0" y="0" width="1000" height="40" >
                <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
 </reportElement>
<textElement> <font size="24" isBold="true"/></textElement> 
<textFieldExpression><![CDATA["Search Criteria"]]></textFieldExpression>
</textField>
</band>
</summary>    

https://stackoverflow.com/questions/34808741/how-to-export-to-multiple-sheets-in-excel-from-jasper-report#:~:text=sheet%3D%22true%22%20%2C%20created,master%20report%2CAnd%20this%20worked%20!

 

How to use IN/NOT IN Using Parameter

Example

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN($P{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Not Work As Considering As A Single Value.

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ($P!{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Work As Per Expected

SELECT * FROM ORDERS WHERE $X{IN,EMP_NAME,P_STUDENT_NAME} 
Work As Per Expected But Need To Pass Array

$P!{P_STUDENT_NAME} : This will not be treated as a SQL parameter. 
JasperReports will consider this parameter value will be a part to sql statement.
So you can also pass the query part from parameter.

Example: How to make order by as a parameterised.
select * from employee ORDER BY $P!{P_STUDENT_NAME}

Pass Parameter Value as : EMP_NAME DESC 
Note : Without " or ' sign as there is no quate in query.

JasperServer: How to generate/export report using url

http://<host>:<port>/<context>/flow.html?_flowId=viewReportFlow&reportUnit=/supermart/Details/XXX&employee=123&output=pdf

&output=pdf : Add this parameter in url to get a pdf file.
&output=xls  : Add this parameter in url to get a xls file.

Tags: 

Export PDF
Export Excel

Jasperserver.

Custom JQUERY in Jasper Server

You can add your custom JQUERY code with below syntax.
i.e. jQuery('#back').hide();

Follow the below steps to test JQUERY code. After implement below steps you can hide back button with JQUERY custom code.

Step 1: Open \\apache-tomcat\webapps\jasperserver\scripts\commons.main.js

Step 2: Add JQUERY code in below function.      

           domReady(function(){
 //Add the below jquery to hide back button
 jQuery('#back').hide();
            });

Step 3: You can see that back button is not display.

Below is result.

Tags: 

JQUERY
Customization

Jasperserver.

How to use JSON File with Table Component.

 Please Refer Attached JSON File and understand that below is my structure.

You can download the JSON Viewer Freeware Software from below link.
http://tomeko.net/software/JSONedit/index.php?lang=en

Below image display that how we can display JSON data in Table component.

Step 1 : Create connection with JSON File.

Step 2 : Create one data source and define Json query language path.

Step 3: Take Table Component and set parameter.

SAMPLE JSON FILE: Two.json

{"company" : "abc",
 "dbinformation" : {
      "company_detail" : "the description",
      "company_id" : 1538,
      "division" : {
         "product" : [
            {
               "proddetail" : {
                  "description" : "abc mobile",
                  "id" : 1,
                  "type" : "mobile"
               },
               "amount" : 6.0,
               "rate" : 4000
            },
            {
               "proddetail" : {
                  "description" : "xyz tablet",
                  "id" : 2,
                  "type" : "tablet"
               },
  "amount" : 2.0,
               "rate" : 5000
            }
         ],
         "product_supply_detail" : [
            {
               "address" : {
                  "description" : "Pune",
                  "pincode" : 38001
               },
            "productid" : 1,
     "quantity"  : 3
            },
            {
               "address" : {
                  "description" : "Ahmedabad",
                  "pincode" : 38001
               },
            "productid" :1,
     "quantity" : 4
            }
         ]}
   },
   "date" : 15022015
}

Tags: 

JSON
iReport

Jasper Studio
JSPN With Table Component