Protecting Excel sheets using Apache POI

Hi All,

In this article I will show how to protect excel sheet using Apache POI, Apache POI, a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

The below programme will demonstrate on Excel :

public static void main(String arg[]) {
try{
//Specify the name of the outout file
FileOutputStream out=new FileOutputStream(new File(“protected.xls”));

//Create  the workbook object
HSSFWorkbook hssfworkbook = new HSSFWorkbook();

/* Creates a sample sheet with name as Sheet 1 */
HSSFSheet sheet = hssfworkbook.createSheet(“Sheet1”);

String CELL_DATA = “First cell data”;

/* Creates first row */
HSSFRow row = sheet.createRow(0);


/* Creates a first cell */

HSSFCell cell =row.createCell((short)0);

/* Sets the value in first cell */
cell.setCellValue(
CELL_DATA );

/* Sets the password for the sheet */
sheet.protectSheet(“myPassword”);

/* Writes to the File Output stream */
hssfworkbook.write(out);
out.close();

} catch(Exception e){
e.printStackTrace();
}

}

Hope this helps.


Thanks
R Vashi

Advertisements

Jar scanner for finding jars

Hi All,

I am very happy to share a tool written by me, I have named it Archive Scanner, As the name suggest this tool will help to search for Java classes jar names. Sometime we spend lots of time searching for a jar name, and looking here and there. I can understand this pain as I have faced so many times. So it is a sort of tool I have gifted to myself and all you out there.

This tool(jar) is self executable, only things is that you need to set up a java environment to use that.
It works on JDK.1.5 or later. Please download from the below file.

Tool DownLoad:
archive scanner

Steps to Use:
– First of all save the Jar it to some location. e.g c:\myTool
– Open command Console and change the directory to the location where Jar has been saved
– Set Java Home (ignore if already defined)

– Run the below java command
– java -jar <<Name of jar>> –searchType DSF –dirName C:\\bea\\modules\\ –fileName Classwriter.class
or
– java -jar <<Name of jar>> –searchType DSF –dirName C:\\bea\\modules\\ –fileName Classwriter
or
– java -jar <<Name of jar>> –searchType DSF –dirName C:\\bea\\modules\\ –fileName com/bea/objectweb/asm/ClassWriter
or
– java -jar <<Name of jar>> –searchType DSF –dirName C:\\bea\\modules\\ –fileName com.bea.objectweb.asm.ClassWriter
or
– java -jar <<Name of jar>> –searchType DSF –dirName C:\\bea\\modules\\ –fileName com.bea.objectweb.asm.ClassWriter.class

Hope this help, Please give your suggestions or your experience with this tool, this would help me to apply any sort of improvements further.

Happy learning.


Thanks
R Vashi

read a file inside a Jar file

Hi All,

In this article I will explain how to read a text or csv or prop file from jar. This is very helpful when you export  files and other resources into JAR files to distribute your Java application. So many time we face issues like FileNotFound while accessing the resources which are inside Jar file.

Always use ClassLoader to load any resource from the jar, the reason is thea whenever we are trying to invoke a class from jar, main jvm looks only to the relative position because it tries to load the same class into there, so by using the class loader means we have requested to extract more resources from the jar.
(Please correct me If any of you have some better explanation).

The below example will detailed.
Step 1: Add help.txt in the code base. e.g c:\mytest\help.txt, and add some data into it

Step 2: Write a class and add the below method

public void readFromFile() {
BufferedReader bufferedReader = null;
try {
//Construct Input Stream object, and use the java class classloader to find the resource
InputStream is = getClass().getResourceAsStream(“help.txt“); // pass the resource name
//[NOTE]: Beware that ClassLoader#getResourceAsStream returns null if the resource is not found. This will throw an ugly NPE. So always put a null check before going forward.

//Create a InputStream Reader Object from Input Stream
InputStreamReader isr = new InputStreamReader(is);

// Now pass the Input Stream into Buffered Reader, and read the input and display on the console
bufferedReader = new BufferedReader(isr);
String line = null;
while ((line = bufferedReader.readLine()) != null) {
System.out.println(line);
}

} catch (FileNotFoundException ex) {
ex.printStackTrace();

} catch (IOException ex) {
ex.printStackTrace();

} finally {
//Close the BufferedReader
try {
if (bufferedReader != null)
bufferedReader.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}

Note [Structure of Jar for this Example]
myjar.jar
|
– META-INF
– MyClass.class
– help.txt

Hope this helps.


Thanks
R Vashi

Configuring System property in weblogic

Hi All,

Sometime we need to configure few properties in server which we can easily read, without reading property files in java code/JSP code/Servlet.

Weblogic gives a very nice way to do that. Only thing we need to do is to configure the property with key value in server start options (i.e java options). We also called theses memory arguments. Just login to admin console, and go to server properties and add the below property.

-Dkey=MyValue

After setting the memory argument simply restart the server and access the property in your JSP/Java Code/Servlet

String keyValue=System.getProperty(“key”);
System.out.pritnln(“************** “+keyValue);

//output
// **************MyValue

Hope this helps.


Thanks
R Vashi

Displaying Column names fromJDBC Resultset

Hi All,

The below example will demonstrate how to display column name from ResultSet object, This is very useful when we are building something very generic to display data on the UI. By using ResultSetMetaData , which  contains information about the columns of the ResultSet object that will be returned, It’s an Interface defined inside java.sql. Package.

The ResultSetMetaData object can be obtained by calling the getMetaData() method in the ResultSet object obtained when a SQL query is executed. After getting a OracleResultSetMetaData object, you can use the methods in it to get details about the columns in the ResultSet.All the methods in OracleResultSetMetaData return boolean, int or String.

  • getColumnCount() method returns number of columns in the ResultSet as an int.
  • getColumnName() method returns the column name as a String.
  • getColumnTypeName() method returns the column type as a String.
  • isNullable() method returns if the the column can be NULL. This method returns an int which can be any one of the

following constants:

  • columnNoNulls                – indicates that a column does not allow NULL values
  • columnNullable                – indicates that a column allows NULL values
  • columnNullableUnknown – indicates that the nullability of a column’s values is unknown

OracleResultSetMetaData interface does not implement the getSchemaName() and getTableName() methods because underlying protocol does not make this feasible.

//Get the connection
Connection connection = DriverManager.getConnection
(url, username, password);
//Create a statement object
Statement stmt = connection.createStatement();
// Run the Query
ResultSet rs = stmt.executeQuery("SELECT * FROM mysql_all_table");

// Create a Resulsetmetadata object which will Hold the Table schema information
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
for (int i = 1; i < numColumns + 1; i++) {
String columnName = rsmd.getColumnName(i);
String tableName = rsmd.getTableName(i);
}
}


Thanks
R Vashi

java.lang.OutOfMemoryError: PermGen space

Hi All,

Few days back I was reading one of the article of my all time favorite guru(Jaysen sharma), So I would like to share that in this article.

In case of OutOfMemoryError in PermGen Sapace …increasing only the Heap Size will not solve anything…

Formula:

(OS Level)Process Size = Java Heap Native Space (2-3% OS related Memory)

PermSize : It’s a Native Memory Area Outside of the Heap, Where ClassLoading kind of things happens…. In an operating System like Windows Default Process Size is 2GB (2048MB) default (It doesn’t matter How much RAM do u have 2GB or 4GB or more)…until we don’t change it by setting OS level parameter to increase the process size..Usually in OS like Solaris/Linux we get 4GB process size as well.

Now Lets take the default Process Size=2GB (Windows), Now if you have set the -Xmx512M, we can assume that rest of the memory 1536 Mb is available for Native codes.

(ProcessSize (-) HeapSize) = Native (+) (2-3% OS related Memory)

2048 MB (-) 512 MB = 1536 MB

THUMB RULES:

MaxPermSize = (Xmx/3) —- Very Special Cases (One Third of maximum Heap Size)

MaxPermSize = (Xmx/4) —- Recommended (One Fourth Of maximum Heap Size)

So finally you need to increase the PermGen Size… like -Xmx1024m -Xms1024m -XX:MaxPermSize256m

But again these are not the Final values … It depends on ther Environments

Thanks

R Vashi.

Using PreparedStatement to set Null for char/string columns JDBC

Hi All,

sometime we need to pass null values to DB procedure parameters, or query columns. There is a very simple way to achieve that.

conn = getConnection(); // gets connection form DB factory
String query = “insert into table(id,string_column, int_column) values(?, ?, ?)”;

pstmt = conn.prepareStatement(query); // create PrepareStatement object
pstmt.setString(1, id);
pstmt.setNull(2, java.sql.Types.VARCHAR);
pstmt.setNull(3, java.sql.Types.INTEGER);

// execute query, and return number of rows created
int rowCount = pstmt.executeUpdate();
System.out.println(“rowCount=” + rowCount);


Thanks
R Vashi

set java heap size in Tomcat

Hi All,

Stop Tomcat server, set environment variable CATALINA_OPTS, and then restart Tomcat. Look at the file tomcat-install/bin/catalina.sh or catalina.bat for how this variable is used. For example,

set CATALINA_OPTS=-Xms512m -Xmx512m  (Windows, no "" around the value)
setenv CATALINA_OPTS "-Xms512m -Xmx512m"  (tcsh/csh, "" around the value)
export CATALINA_OPTS="-Xms512m -Xmx512m"  (ksh/bash, "" around the value)

In catalina.bat or catallina.sh, you may have noticed CATALINA_OPTS, JAVA_OPTS, or both can be used to specify Tomcat JVM options. What is the difference between CATALINA_OPTS and JAVA_OPTS? The name CATALINA_OPTS is specific for Tomcat servlet container, whereas JAVA_OPTS may be used by other java applications (e.g., JBoss). Since environment variables are shared by all applications, we don’t want Tomcat to inadvertently pick up the JVM options intended for other apps. I prefer to use CATALINA_OPTS.

Note: Always make sure all the java objects should garbage collected, specially JDBC objects.


Thanks
R Vashi.

Bootstrap servlet or Startup servlet

Hi,

Sometime we need some services to be initialized when application gets started, Few application servers like weblogic provides handy feature where you can add startup classes when server gets started. There is a very nice and easy way where we can add bootstrap classes when application gets started. These classes we can use to init some services like logger,email agents, service agents, db factories etc. The below example will show how to do that using a simple servlet.

Step 1: write a simple servlet

public class StartupService extends HttpServlet{

/**
*
*/
private static final long serialVersionUID = 1L;

/**
*
*/
public void init(ServletConfig config) throws ServletException {

/*
ADD Your logic here…
*/
System.out.println(“Startup class initialized successfully”);
}

}

Step2:  Add the below mapping entry in web.xml

<servlet>
<servlet-name>StartupService</servlet-name>
<servlet-class>com.example.StartupService</servlet-class>

<!– Container/server will start the servlet when app gets start –>

<load-on-startup>1</load-on-startup>
</servlet>

Step 3: Start the server. you might will see “Starup class initialized successfully”.


Thanks
R Vashi

Cropping Image in Java Servlet/JSP using JQuery

Hi,

Below example shows how to crop an Image using JQuery crop plugin using Java Servlet

Step 1

Create JSP/Html Page

<script src=”jquery.min.js”></script>
<script src=”jcrop_demos.js”></script>
<link rel=”stylesheet” href=”jquery.Jcrop.css” type=”text/css” />
<link rel=”stylesheet” href=”demo.css” type=”text/css” />
<script language=”Javascript”>
// Remember to invoke within jQuery(window).load(…)
// If you don’t, Jcrop may not initialize properly
jQuery(document).ready(function(){
jQuery(‘#cropbox’).Jcrop({
onChange: showCoords,
onSelect: showCoords
});
});

// Our simple event handler, called from onChange and onSelect
// event handlers, as per the Jcrop invocation above
function showCoords(c)
{
jQuery(‘#x’).val(c.x);
jQuery(‘#y’).val(c.y);
jQuery(‘#x2’).val(c.x2);
jQuery(‘#y2’).val(c.y2);
jQuery(‘#w’).val(c.w);
jQuery(‘#h’).val(c.h);
};

</script>
<!– http://localhost:8080/TestJ2EE/cropTest?t=50&l=60&w=90&h=50&i=logo_plain.jpg&f=jpg –>
<a href=”<%=request.getContextPath()%>/testDownload”>downlaod</a>
<!– This is the image we’re attaching Jcrop to –>

<img src=”logo_plain.jpg” id=”cropbox” />
<form action=”http://localhost:8080/TestJ2EE/cropTest1&#8243; method=”get”>
<label>X1 <input type=”text” size=”4″ id=”x” name=”l” /></label>
<label>Y1 <input type=”text” size=”4″ id=”y” name=”t” /></label>
<label>W <input type=”text” size=”4″ id=”w” name=”w” /></label>
<label>H <input type=”text” size=”4″ id=”h” name=”h” /></label>
<input type=”hidden” size=”4″ id=”f” name=”f” value=”jpg” />
<input type=”hidden” size=”4″ id=”i” name=”i” value=”logo_plain.jpg” />
<input type=”submit” />
</form>

———————————————–

Step 2
Create a Servlet

/**
*
*/
package com.test;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;

import javax.imageio.ImageIO;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
* Crop
* @author RVashi
*
*/
public class MyOwnCropServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException,ServletException{

int t=Integer.parseInt(req.getParameter(“t”));
int l=Integer.parseInt(req.getParameter(“l”));
int w=Integer.parseInt(req.getParameter(“w”));
int h=Integer.parseInt(req.getParameter(“h”));
String imagePath=getServletContext().getRealPath(“/”)+req.getParameter(“i”);
BufferedImage outImage=ImageIO.read(new File(imagePath));
BufferedImage cropped=outImage.getSubimage(l, t, w, h);
ByteArrayOutputStream out=new ByteArrayOutputStream();
ImageIO.write(cropped, req.getParameter(“f”), out);

ImageIO.write(cropped, req.getParameter(“f”), new File(getServletContext().getRealPath(“”)+System.getProperty(“file.separator”)
+”cropped.jpg”)); // save the file with crop dimensions

//res.setContentType(“image/jpg”);
ServletOutputStream wrt=res.getOutputStream();
wrt.write(out.toByteArray());
wrt.flush();
wrt.close();

}

}

Thanks

R Vashi