JExcel API

In my last assignment, i got requirment to write data into excel file.I got the solution JExcel API.I got solution in the form of JExcel.Today, i will try to demonstrate JExcel API.
JExcelApi allows developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically.It also contains a mechanism which allows java applications to read in a spreadsheet, modify some cells and write out the new spreadsheet.

Download JExcelApi JAR files from

http://jexcelapi.sourceforge.net/

Benifit of this API is:
Any operating system which can run a Java virtual machine (i.e., not just Windows) can both process and deliver Excel

spreadsheets. Because it is Java, the API can be invoked from within a servlet, thus giving access to Excel spreadsheets over

internet and intranet web applications.

Demo Application:

imported packages are following:
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public ByteArrayOutputStream generateExcelReport() throws IOException, WriteException {
/* Stream containing excel data */
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

/* Create Excel WorkBook and Sheet */
WritableWorkbook workBook = Workbook.createWorkbook(outputStream);

or writing data in to generated xls
//Workbook workbook = Workbook.getWorkbook(new File(“Deepak.xls”));
WritableSheet sheet = workBook.createSheet("Project List", 0);

/* Generates Headers Cells */
WritableFont headerFont = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
headerCellFormat.setBackground(Colour.PALE_BLUE);
sheet.addCell(new Label(1, 1, "Project Id", headerCellFormat));
sheet.addCell(new Label(2, 1, "Project Name", headerCellFormat));

/* Generates Data Cells */
WritableFont dataFont = new WritableFont(WritableFont.TAHOMA, 12);
WritableCellFormat dataCellFormat = new WritableCellFormat(dataFont);

int currentRow = 2;
for (User user : getUsers()) {
sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
currentRow++;
}

/* Write & Close Excel WorkBook */
workBook.write();
workBook.close();

return outputStream;
}

public List getUsers() {

try{
Class.forName("com.mysql.jdbc.Driver");
Connection

con=DriverManager.getConnection("jdbc:mysql://localhost/pmsdatabase2?user=root&password=mysql");
PreparedStatement ps=con.prepareStatement("select project_code,project_name from project");
ResultSet rs=ps.executeQuery();
while(rs.next()){
users.add(new User(rs.getString(1),rs.getString(2)));
}

}catch(Exception e){
System.out.println("Coming here!!"+e);
}
return users;
}

COde for User.java:

class User{

private String firstName;
private String lastName;
//getter and setter of firstName and lastName
public User(){}

public User(String lastName,String firstName){
this.firstName=firstName;
this.lastName=lastName;

}

}

Call this method by using any servlet.
A complete document is available on sourceforge or other sites.

Thanks!
Deepak

A basic application for iBatis use

Last month i got chance to learn some new techonologies like GWT (Google Web ToolKit) and iBatis. Firstly i would like to demonstrate application on iBatis. In upcoming posts, i would cover GWT.

I found iBatis very useful because With Ibatis I find myself getting the Job done faster, I create any POJO any set of tables, and I link them, much flixability in that field, but little extra work, building Queries and managing Maps (Little time required here). I never worked or got chance to learn hibernate.So bit difficult for me to tell that which is much better.But anyway here i developed a small application which only tells you that how you can use iBatis in your projects.

Download iBatis.jar
http://ibatis.apache.org/javadownloads.html

iBatis:

Step1: create SQLMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
<settings useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:mysql://localhost:3306/mhrddemo"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value="mysql"/>
</dataSource>
</transactionManager>
<sqlMap resource="DataBean.xml"/>
</sqlMapConfig>

you can create property file and then use that values in this config file.

2)create another xml where you write query:
DataBean.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">
<!--- Showing all data of table -->



<!--- Showing all data of table -->
<select id="getAll" resultClass="ibdeep.DataBean">
select university_id,entity_type,entity_description from entity_type
</select>

<resultMap id="result" class="ibdeep.DataBean">
<result property="university_id" column="university_id"/>
<result property="entity_type" column="entity_type"/>
<result property="entity_description" column="entity_description"/>

</resultMap>
<select id="getById" resultMap="result">
select * from entity_type where entity_type=#entity_type#
</select>


</sqlMap>

3) create a java file which has getter and setter of table fields or properties.

package ibdeep;

class DataBean
{

private String university_id;

private String entity_type;

private String entity_description;

public DataBean(){

}

public DataBean(String university_id,String entity_type,String entity_description){

this.university_id=university_id;
this.entity_type=entity_type;
this.entity_description=entity_description;

}

public String getUniversity_id(){

return university_id;

}

public void setUniversity_id(String university_id){

this.university_id=university_id;

}

public String getEntity_type(){

return entity_type;

}

public void setEntity_type(String entity_type){

this.entity_type=entity_type;

}

public String getEntity_description(){

return entity_description;

}

public void setEntity_description(String entity_description){

this.entity_description=entity_description;

}

}

Step 4): Create another java file
package ibdeep;

import com.ibatis.common.resources.Resources;

import com.ibatis.db.sqlmap.SqlMap;

import com.ibatis.sqlmap.client.*;

import java.io.Reader;

public class SqlMapManager {
private static SqlMap sqlMap = null;
static SqlMapManager smm;

public static SqlMapClient getSqlMapClient() {
try {
Reader reader = Resources
.getResourceAsReader("ibdeep/SqlMapConfig.xml");
SqlMapClient sqlMapper = SqlMapClientBuilder
.buildSqlMapClient(reader);
reader.close();

return sqlMapper;
} catch (Exception e) {
System.out.println("Map Exception");
e.printStackTrace();
throw new RuntimeException(e.getMessage(), e);
}
}


public void setSqlMap(SqlMap sqlMap) {
SqlMapManager.sqlMap = sqlMap;
}


public static SqlMap getSqlMap() {
return sqlMap;
}
}

Step 5) create properties file.you can use it.You can pass these values directly

url=jdbc:mysql://localhost:3306/mhrddemo
login=root
password=mysql

Step 6) Finally create java file which uses

package ibdeep;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.sql.SQLException;
import java.util.*;

class IBatisExample
{
public static void main(String[] args)
throws IOException,SQLException{
Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
SqlMapClient sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);

//Output all contacts

System.out.println("All Contacts");
List<DataBean> contacts = (List<DataBean>)
sqlMap.queryForList("Contact.getAll",null);
DataBean contact = null;
for (DataBean c : contacts) {
System.out.print(" " + c.getUniversity_id());
System.out.print(" " + c.getEntity_type());
System.out.print(" " + c.getEntity_description());
contact = c;
System.out.println("");
}
}
}

I hope it is useful.

A Simple Apllication with DWR:

A Simple Apllication with DWR:

DWR: Direct Web Remoting-Easy Ajax for java

Link for DWR tutorial and download is:
http://directwebremoting.org/dwr/index.html

I found it very useful for my project PMS(Project Management System).
That's why I would like to demonstrate a simple application.

Just create webproject and put dwr.jar into lib.

Make changes in web.xml:
"<servlet>
<servlet-name>dwr-invoker</servlet-name>
<servlet-class>uk.ltd.getahead.dwr.DWRServlet</servlet-class>
<init-param>
<param-name>debug</param-name>
<param-value>true</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>dwr-invoker</servlet-name>
<url-pattern>/dwr/*</url-pattern>
</servlet-mapping>"

create dwr.xml and put following entries
"<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 0.4//EN" "http://www.getahead.ltd.uk/dwr/dwr.dtd">

<dwr>
<allow>
<create creator="new" javascript="DwrDemo">
<param name="class" value="deep.DwrDemo"/>
</create>

</allow>
</dwr>"
value of class where you write your fuction which you want to call on any event.
and this javascript (Here DwrDemo) willbe created corresponding to that class.

Now we try to use it in a simple JSP page.
Add these lines in you JSP.

" <script type='text/javascript' src='dwr/engine.js'></script>
<script type='text/javascript' src='dwr/util.js'></script>
<!-- This JavaScript file is generated specifically for your application -->
<script type='text/javascript' src='dwr/interface/DwrDemo.js'></script>
<script type="text/javascript">

function demo() {
var name = DWRUtil.getValue("name");

DwrDemo.demo(name,function(val)
{
DWRUtil.setValue("first",val);
}
);
}
</script>

This javascript function calls demo() method which is inside DwrDemo method.
val is value which is returned by function demo().

<body>
<select id="name" name="name" onchange="demo();">
<option value="Deepak">Deepak</option>
<option value="Anil">Anil</option>
<option value="Akash">Akash</option>
<option value="Mayank">Mayank</option>
</select>

<input type="text" id="first"/>
</body> "

Class deep.DwrDemo is given below.
package deep;

public class DwrDemo {

public String demo(String name)
{
if(name.equals("Deepak"))
return "Hello "+name+" Pandey";
else
return "Hello "+name;
}

}

Now Run your application.

Project Management System

Hi,

Last time i wrote about Project Management System(PMS). URL for PMS is

http://pms.iitk.ernet.in/

Thanks!!
Deepak

Add Graphs by Using JFree Chart

Recently i want to add graphs in PMS application. There are too many ways to do this.One of them is JFree Chart.Steps for this are following..

Step1: Download Demo Application of JfreeChart demo and get library jar.

Step2: Add following lines in your code and import files which is required.

JFreeChart chart = ChartFactory.createPieChart("Pie Chart created by Deepak Pandey", pieDataset, true, true,true);
BufferedImage bi = chart.createBufferedImage(500, 500);
chart.setBackgroundPaint(new Color(173, 230, 163));
BufferedImage buf = chart.createBufferedImage(500, 500, null);
try {
JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(response.getOutputStream());
JPEGEncodeParam param = encoder.getDefaultJPEGEncodeParam(buf);
param.setQuality(0.75f, true);
encoder.encode(buf, param);

} catch (Exception e) {
System.out.println("Exception in JSP"+e);
}

Step3: Run you application

You can create this chart on a frame.
Create JFrame Object. and then add JFreeChart object and make this JFrame Object visible.

More Information of this JFreeChart,access following link
http://www.jfree.org

Thanks!!!