In Oracle TRUNC(data_time_var) returns only the date portion of a date_time data type.

In MySQL to truncate time part from date we have to use

SELECT DATE_FORMAT(date_time_var, '%Y-%m-%d')
Try different date formate.

Further date time function in mysql can be found in
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

|

1. public class MethodName {  
2.    
3.  public String methodOne() {  
4.   return new Exception().getStackTrace()[0].toString();  
5.  }  
6.    
7.  public String methodTwo() {  
8.   return Thread.currentThread().getStackTrace()[2].toString();  
9.  }  
10.   
11.  public static void main(String[] args) {  
12.   MethodName name = new MethodName();  
13.     
14.   long start = System.currentTimeMillis();  
15.   for (int i = 0; i < 100000; i++) {  
16.    name.methodOne();  
17.   }  
18.   System.out.printf("First method - new Exception() - in %d millis\n",  
19.      System.currentTimeMillis() - start);  
20.     
21.   start = System.currentTimeMillis();  
22.   for (int i = 0; i < 100000; i++) {  
23.    name.methodTwo();  
24.   }  
25.     
26.   System.out.printf("Second method - Thread.currentThread() - in %d millis\n",  
27.      System.currentTimeMillis() - start);  
28.  }  
29. }  

|

Sample code that will export JasperReport(.jasper) to excel, pay attention to the bold code
that's all the tricks. The sample servlet will accept two parameter the date and the name of the report.


/*
* JasperToExcel.java
*/

import bean.report.RowStatistics;
import java.io.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import javax.servlet.*;
import javax.servlet.http.*;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.data.JRMapCollectionDataSource;
import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;

/**
*
* @author amontejo
* @version
*/
public class JasperToExcel extends HttpServlet {

public static final String REPORT_DIRECTORY = "/reports";

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();
String reportName = request.getParameter("reportname");
String selectedyear = request.getParameter("SelectedYear");
InputStream reportStream = getServletConfig().getServletContext().getResourceAsStream("/" + REPORT_DIRECTORY + "/" + reportName + ".jasper");

ServletContext context = this.getServletConfig().getServletContext();
JasperPrint jasperPrint = null;
HashMap parameterMap = new HashMap(); 

parameterMap.put("parayear", new String(selectedyear));

try {
ServletOutputStream servletOutputStream = response.getOutputStream(); 
JRDataSource dataSource = createReportDataSource(request, selectedyear, reportName);
jasperPrint = JasperFillManager.fillReport(reportStream, new HashMap(), dataSource);
generateXLSOutput(reportName, jasperPrint, response);
} catch (Exception e) {
}
}

private String tagreport(String string) {
java.util.Calendar calendar = java.util.Calendar.getInstance();
return string + calendar.get(calendar.MONTH) + calendar.get(calendar.DAY_OF_MONTH) + calendar.get(calendar.YEAR);
}

private void generateXLSOutput(String reportname,
JasperPrint jasperPrint,
HttpServletResponse resp)
throws IOException, JRException {
String reportfilename = tagreport(reportname) + ".xls";
JExcelApiExporter exporterXLS = new JExcelApiExporter();

exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, resp.getOutputStream());
resp.setHeader("Content-Disposition", "inline;filename=" + reportfilename);
resp.setContentType("application/vnd.ms-excel");

exporterXLS.exportReport();
}


// 
/** Handles the HTTP GET method.
* @param request servlet request
* @param response servlet response
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

/** Handles the HTTP POST method.
* @param request servlet request
* @param response servlet response
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

/** Returns a short description of the servlet.
*/
public String getServletInfo() {
return "Short description";
}
// 
}

|

There are few methods to alternate background colour of rows in jasperreport.

Use conditional style.

A conditional style has two elements: a Boolean condition expression and a style. The
style is used only if the condition evaluates to true.

We have following code snippet to get alternating row color.

<style name="alternateStyle" fontName="Arial" backcolor="red">
<conditionalStyle>
<conditionExpression>
new Boolean($V{REPORT_COUNT}.intValue() % 2 == 0)
</conditionExpression>

<style backcolor="blue"/>
</conditionalStyle>
</style>



In this example, element with this style will get red backcolor. But the presence of conditional style changes the behaviour and when rendered on an even row, the same element will have blue backcolor.

A conditional style override the properties of parent style.



OR

just put the a rectangle with a different background color and set the printwhenexpression to :

new Boolean(($V{REPORT_COUNT} mod 2) == 0)

OR

Set up a new java.lang.Integer variable called BACKGROUND. Set its Calculation Type to DistinctCount, Reset typeto None, Increment type to None, Variable Expression to a field that will always contain unique values (eg. $F{PortNumber} ), and an Initial Value Expression of new java.lang.Integer(0). That will give you an auto-incrementing variable.


In the Detail section make sure all the existing elements are set to Transparent.


Now in the Detail section of your report create a new Static Text box. Size it to cover the full extents of the detail section. If you are using multiple columns size it to the full extent of just one column. Set its background colour to be whatever you want it to be. Put a single space in the Static Text area.


To make it toggle on and off you use a Print When expression based on your BACKGROUND variable. Set it to be new java.lang.Boolean(($V{BACKGROUND}.intValue() % 2)==1)


Now all you need to do is send it to the back. Under the Format Menu choose the Send to Back option, and your done.

|