top-image

LATEST ARTICLES

Hello,

After my previous post concerning the SSL, PorteCle Generate KeyStore, self-signed certificate, tool PorteCle, concerning the generation of server’s KeyStore, self-signed certificate…, in this post, i would expose the case of a server SERVER1 which communicates with an other server securized SERVER2 (like Jboss, Tomcat,…), so, now, we are speaking about the TrustStore of server.

A keystore contains private keys, and the certificates with their corresponding public keys. A truststore contains certificates from other parties that you expect to communicate with, or from Certificate Authorities that you trust to identify other parties.

So it’s necessary to add the certificate of SERVER1 contained in its KeyStore, into the TrustStore of SERVER2.

Create a new TrustStore
First, execute the portecle and create a new KeyStore/TrustStore which will contain the certificate:
article-portcle-3
… select the type Java Keystore:
article-portcle-4


Import a trusted certificate
Importation of a new certificate (or Key Pair) in the previous created TrustStore for the SERVER2:
article-portcle-3-1
…then, select the *.cer certificate file of SERVER1:
article-portcle-3-2
…give the same name than the *.cer file to the imported certificate:
article-portcle-3-3
article-portcle-3-4
…finally, save the TrustStore of SERVER2 with the name myTrustStore.ks on the local disk with a specific password myPass123:
article-portcle-3-5
article-portcle-3-6
article-portcle-3-7



Tomcat Server Configuration
Copy this TrustStore on SERVER2 file system and modify the “Java Options” of Tomcat server in order to add the following options (Configure Tomcat or Eclipse server environment):

-Djavax.net.ssl.trustStore="..../myTrustStore.ks"
-Djavax.net.ssl.trustStorePassword="myPass123"

That’s all!!!!!

Huseyin OZVEREN

Hello,

After my first post concerning the SSL : self-signed certification generation and the installation on Tomcat server, I would expose a new tool PorteCle allowing the generation of KeyStore, self-signed certificate instead of Keytool supported in the JDK / JRE 1.5.

A simple reminder concerning the types of keys:

  • symmetric type: the sender and the recipient have the same key,
  • asymmetric type: the sender and the recipient haven’t the same key i.e. there are a private key and a public key,

Case of data transmission: ACTOR1 sends crypted data to ACTOR2

  • ACTOR1 uses the public key of ACTOR2 in order to encrypt and send the data to ACTOR2,
  • ACTOR2 uses its private key in order to decrypt the received data from ACTOR1,

Case of signature: ACTOR1 signs a document

  • ACTOR1 uses its private key in order to sign the document,
  • ACTOR2 (recipient of this document) uses the public key of ACTOR1 in order to authenticate the document signed by ACTOR1,

Note: The sent document could be also encrypted.

During the navigation of a secured website, the installation of the certificate on the client poste doesn’t asked explicitly because the certificate has been certified by a certification authority (CA). When creating the certificate (in the keystore on server side), it’s possible to precise if the certificate is dedicated to be diffused (classic case) or not be diffused.



The principle of SSL
The securization of transactions via SSL is based on the exchanges of keys between client and server. The transaction securized via SSL is done by the following model:

  • First, the client connects to the server secured by SSL and asks it the authentication. The client sends also the list of supported cryptosystems, sorted by the descending order according to the length of keys.
  • Then, the server sends a certificate to the client, containing its public key (server side) signed by a certification authority (CA) or its self-signed public key. This certificate contains the name of the most complex client’s compatible cryptosystem from the previous list: the length of the encryption key (40 bits or 128 bits) will be that of the common cryptosystem with the largest key size.
  • The client creates random secret key, encrypts this key using the public key of server, then sends the result (session key) to server.
  • So, the server is capable of decrypting the client’s session key with its private key (server key). Thus, the 2 entities have a common key known only by these 2 parts. The rest of the transactions can be done using this session key, ensuring the integrity and confidentiality of the data exchanged.

For example, the generation of certificate is possible via the keytool:

keytool.exe -genkey -dname "cn=JavaBlog.fr, ou=JAVA/LU, o=Blog JavaBlog.fr, c=LU"
-alias tomcatKey 
-keypass 123456
-keystore D:\keytool\javablogkeystore.ks
-storepass 123456
-validity 365

..but there are other tools like PorteCle.



Installation of PorteCle
First, download portecle-1.7.zip from http://sourceforge.net/projects/portecle/ and unzip it.
Then, execute the portecle.jar file with the Java Platform SE binary:
article-portcle-1
…this is PorteCle:
article-portcle-2



Create a new KeyStore
The KeyStore contains the keys/certificates:
article-portcle-3
… select the type Java Keystore:
article-portcle-4



Create a new certificate
Creation of a new certificate (or Key Pair) in the previous created KeyStore:
article-portcle-5
…the Key Algorithm will be RAS and with a Key Size of 2048:
article-portcle-6
…and the détails of our certificate will be:

  • Validity: 365 days
  • CN (Common Name): HUO
  • OU (Organisation Unit): JAVABLOG.FR
  • O (Organisation Name): JAVA.LU
  • L (Locality Name): LU
  • ST (State Name): LU
  • C (Country): LU

article-portcle-7
…it is necessary to fill in an alias and a password for the certificate in the KeyStore (here huo and 123456):
article-portcle-8
article-portcle-9
…and double-click on the certificate to display its détails:
article-portcle-10

…finally, save the KeyStore on the local disk with a specific password 123456. In Tomcat, the private key password and keystore password should be the same. See the official documentation http://tomcat.apache.org/tomcat-6.0-doc/ssl-howto.html: You MUST use the same password here as was used for the keystore password itself. This is a restriction of the Tomcat implementation.
article-portcle-11

article-portcle-12

article-portcle-13

article-portcle-14



Export the certificate to *.cer file
Here, it is possible to export a “*.cer” file in order to install directly the certificate for example on client computer/PC. In the case of this article, it is not necessary, however, the installation will not have any impacts.
article-portcle-15

article-portcle-16

article-portcle-17

article-portcle-18

article-portcle-19

Important point : the certificate trusted by a certification authority (CA) doesn’t need to be installed on client side, because the most of these certificates have a root certificate which is already installed on user PC (example : UNIPASS).
To install the certificate on client PC and view the détails of certificate : double click on HUO.cer file
article-portcle-20

article-portcle-21

article-portcle-22
In this last screenshot, you could observe that the certificate has not been trusted by a certification authority (CA) (we have generated a self-signed certificate).

Install the certificate on client side:
article-portcle-23



Certificates Manager
After certificate installation on client side, it is visible in the certificates manager.
Execute the command certmgr.msc:
article-portcle-24

Then, you could find the newly installed certificate:
article-portcle-25

article-portcle-26



Tomcat Server Configuration : configuration of SSL and tests
Activate the following connector in the file “server.xml” of “conf” folder, to use the https protocol targeting the “C:\MyFiles\Development\Java\tools\sslcertificats” keystore with the password filled above “javablog.fr”: (keystore=”C:\MyFiles\Development\Java\tools\myKeyStore.ks” keystorePass=”123456″):

    <!-- Define a SSL HTTP/1.1 Connector on port 8443
         This connector uses the JSSE configuration, when using APR, the 
         connector should be using the OpenSSL style configuration
         described in the APR documentation -->
    <Connector port="8443" protocol="HTTP/1.1" SSLEnabled="true"
              maxThreads="150" scheme="https" secure="true"	
              keystore="C:\MyFiles\Development\Java\tools\myKeyStore.ks" keystorePass="123456"               
              clientAuth="false" sslProtocol="TLS" />

…don’t modify the connector because, per default, the 8080 port is redirected to the 8443 port:

<Connector connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/>

…and when the tomcat server is started:

15 juil. 2014 01:09:28 org.apache.coyote.http11.Http11Protocol init
INFO: Initialisation de Coyote HTTP/1.1 sur http-8080
15 juil. 2014 01:09:29 org.apache.coyote.http11.Http11Protocol init
INFO: Initialisation de Coyote HTTP/1.1 sur http-8443
15 juil. 2014 01:09:29 org.apache.catalina.startup.Catalina load
...
15 juil. 2014 01:09:32 org.apache.coyote.http11.Http11Protocol start
INFO: D�marrage de Coyote HTTP/1.1 sur http-8080
15 juil. 2014 01:09:32 org.apache.coyote.http11.Http11Protocol start
INFO: D�marrage de Coyote HTTP/1.1 sur http-8443

Note: If the Tomcat starts with the below error, it’s because in Tomcat, the private key password and keystore password should be the same. So, set the key and keystore to the same password.

15 juil. 2014 00:57:27 org.apache.coyote.http11.Http11Protocol init
INFO: Initialisation de Coyote HTTP/1.1 sur http-8080
15 juil. 2014 00:57:27 org.apache.coyote.http11.Http11Protocol init
GRAVE: Erreur � l'initialisation du point de contact
java.io.IOException: Cannot recover key
	at org.apache.tomcat.util.net.jsse.JSSESocketFactory.init(JSSESocketFactory.java:427)
	at org.apache.tomcat.util.net.jsse.JSSESocketFactory.createSocket(JSSESocketFactory.java:125)
	at org.apache.tomcat.util.net.JIoEndpoint.init(JIoEndpoint.java:496)
...

If we check our configuration by access to application deployed on tomcat server (for example: https://localhost:8443/test_extJs_1/index2.html):
article-portcle-27



Advanced tests
In order to verify that the connection is secured, we will use the TCP/IP Monitor view in Eclipse to display the content of messages sent and received between the client (browser) and server.

Configure a local monitoring port 8444 redirected to the SSL secure port 8443 on localhost server:
article-portcle-28

So, access to the securized site via the port 8444 (https://localhost:8444/test_extJs_1/index2.html) and check the crypted data in the configured proxy:
article-portcle-29

Best regards,

Huseyin OZVEREN

Hi,

In this article, I will try to expose a Tomcat server installed like Windows service.
We will use Tomcat6, Windows7 and Jdk 6.

First, our Tomcat server is installed in the “C:\MyFiles\Development\Java\tools\TomCat” folder with the following executables:
article-tomcat-windows-service-1

– Tomcat6 is a service application for running Tomcat6 as NT service.
– Tomcat6w is a GUI application for monitoring and configuring Tomcat services.



Before to expose Tomcat server like Windows service, below the Tomcat server configuration in Eclipse:
article-tomcat-windows-service-3

article-tomcat-windows-service-4

article-tomcat-windows-service-5

article-tomcat-windows-service-6

article-tomcat-windows-service-7



First, we will start the cmd.exe like administrator:
article-tomcat-windows-service-2

So, we will expose the Tomcat server like Windows server with the below command (IS = Install Service):

tomcat6 //IS//Tomcat6HUO --DisplayName="Apache Tomcat JAVABLOG.FR" --Install="C:\MyFiles\Development\Java\tools\TomCat\bin\tomcat6.exe" 
--Description="This is my Tomcat server as windows service" 
--Jvm="C:\Program Files\Java\jre6\bin\server\jvm.dll" 
--StartMode=jvm 
--StartClass=org.apache.catalina.startup.Bootstrap 
--StartPath="C:\MyFiles\Development\Java\tools\TomCat"
--StopMode=jvm 
--StopClass=org.apache.catalina.startup.Bootstrap 
--StopPath="C:\MyFiles\Development\Java\tools\TomCat"
--Classpath="C:\Program Files\Java\jdk1.6.0_32\lib\tools.jar;
C:\MyFiles\Development\Java\tools\TomCat\lib\servlet-api.jar;
C:\MyFiles\Development\Java\tools\TomCat\bin\bootstrap.jar;
C:\MyFiles\Development\Java\tools\TomCat\bin\tomcat-juli.jar" 
--StdOutput="C:\MyFiles\Development\Java\tools\TomCat\logs\catalina.out" 
--StdError="C:\MyFiles\Development\Java\tools\TomCat\logs\stderr.log" 
++StartParams=start 
++StopParams=stop 

Then, it is possible to update the Windows service configuration (US = Update Service):

tomcat6 //US//Tomcat6HUO 
++JvmOptions="-Xrs;-Dcatalina.base=C:\MyFiles\Development\Java\tools\TomCat;
-Dcatalina.home=C:\MyFiles\Development\Java\tools\TomCat;
-Dwtp.deploy=C:\MyFiles\Development\Java\tools\TomCat\webapps;
-Djava.endorsed.dirs=C:\MyFiles\Development\Java\tools\TomCat\endorsed;
-Djava.security.auth.login.config=C:\MyFiles\Development\Java\tools\TomCat\conf\jaas.config;
-Dcom.sun.management.jmxremote.port=18080;
-Dcom.sun.management.jmxremote.authenticate=false"

For information, it is possible to use environnement variables:

C:\MyFiles\Development\Java\tools\TomCat\bin>echo %JAVA_HOME%
C:\Program Files\Java\jdk1.6.0_32

For example, we could use the JAVA_HOME variable:

--Classpath=%JAVA_HOME%\lib\tools.jar;
or 
--Classpath="C:\Program Files\Java\jdk1.6.0_32\lib\tools.jar;"

Of course, a service could be delete with the command (DS = Delete Service):

tomcat6 //DS//Tomcat6HUO



The result should be :
article-tomcat-windows-service-82

The properties of Tomcat service are:
article-tomcat-windows-service-9

…and when we try to start the service, we obtain the following error:
article-tomcat-windows-service-10

==> I don’t found the solution of this problem, have you already encountered the same error?

For more information, read the page : http://tomcat.apache.org/tomcat-6.0-doc/windows-service-howto.html

Thanks in advance,

Huseyin OZVEREN

Hello,

After my previous article Example of mail generation (MSGParser, Apache POI-HSMF) concerning the generation of a mail file on server side, I would expose you an example of XLS (Excel) generation by using the Apache POI.

Introduction
The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. The Apache POI project is celebrating its 10th anniversary.

The notice in Apache POI jar precises:

This product contains the DOM4J library (http://www.dom4j.org).
Copyright 2001-2005 (C) MetaStuff, Ltd. All Rights Reserved.

This product contains parts that were originally based on software from BEA.
Copyright (c) 2000-2003, BEA Systems, <http://www.bea.com/>.

This product contains W3C XML Schema documents. Copyright 2001-2003 (c)
World Wide Web Consortium (Massachusetts Institute of technology, European
Research Consortium for Informatics and Mathematics, Keio University)

This product contains the Piccolo XML Parser for Java
(http://piccolo.sourceforge.net/). Copyright 2002 Yuval Oren.

This product contains the chunks_parse_cmds.tbl file from the vsdump program.
Copyright (C) 2006-2007 Valek Filippov (frob@dl.ru)

…so, in our example we will export data Dvd entities in 2 formats : CSV and Excel (XLS). For the first format, we use directly
the java standard class (FileWriter), but for the second format, we use the Apache library POI.



Steps by steps

Create a new project named test_poi with the following libraries in test_poi\lib folder and added in the project’s build path:

  • poi-3.10-FINAL.jar the POI library,
  • log4j-1.2.15.jar for the logging needs,

…the Eclipse project will have the following structure:
article-apache-poi-excel-csv-1

…and the test_poi\src source folder with the log4j.properties:

! log4j.debug
log4j.rootCategory=INFO , console

! WRITE TO CONSOLE (stdout or stderr)
log4j.appender.console.Threshold=INFO
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%d{HH:mm:ss}]%-5p - %m (%F:%L) %n

!-----------------------------------------------------------------------------
!                       PATTERN FORMATS GLOSSARY
!-----------------------------------------------------------------------------
! %n - newline
! %m - your log message
! %p - message priority (FATAL, ERROR, WARN, INFO, DEBUG of custom)
! %r - millisecs since program started running
! %% - percent sign in output
!
! ------------------------- SOME MORE CLUTTER IN YOUR LOG --------------------
! %c - name of your category (logger), %c{2} will outputs last two components
! %t - name of current thread
! %x - Nested Diagnostic Context (NDC) (you supply it!)
!
! ------------------------- SLOW PERFORMANCE FORMATS -------------------------
! %d - date and time, also %d{ISO8601}, %d{DATE}, %d{ABSOLUTE},
!			%d{HH:mm:ss, SSS}, %d{dd MMM yyyy HH:mm:ss,SSS} and so on 
! %l - Shortcut for %F%L%C%M
! %F - Java source file name
! %L - Java source line number
! %C - Java class name, %C{1} will output the last one component
! %M - Java method name
!
! ------------------------- FORMAT MODIFIERS ---------------------------------
! %-any_letter_above - Left-justify in min. width (default is right-justify)
! %20any_letter_above - 20 char. min. width (pad with spaces if regd.)
! %.30any_letter_above - 30 char. max. width (truncate beginning if regd.)
! %-10.10r - Example. Left-justify time elapsed within 10-wide filed.
!		Truncate from beginning if wider than 10 characters.
! ----------------------------------------------------------------------------



POJO
Create a POJO class named DvdEntity in the new package com.ho.apache.poi.test. This class will contain the objects to export.


/**
 * Entity containing the Dvd object.
 * @author huseyin
 *
 */
public class DvdEntity {
	
	// -------------------------------------- ENUMERATION
	public static enum DvdCategory{
		POLAR,
		THRILLER,
		ANIMATION,
		SCIENCE_FICTION,
		;
	}
	
	// ------------------------------- PRIVATE ATTRIBUTES
	// ID
	private Integer id;
	
	// NAME
	private String name;
	
	// PRICE
	private Double price;
	
	// NB OF VIEWING
	private Integer numberOfViewing;
	
	// LAST VIEWING DATETIME
	private Calendar lastViewingDateTime;
	
	// ACTORS
	private String[] actors;
	
	// DVD CATEGORY
	private DvdCategory dvdCategory;
	
	// COMMENTS
	private String comments;
	
	
	// -------------------------------------- CONSTRUCTOR
	public DvdEntity(){}
	
	// ----------------------------------- PUBLIC METHODS
	@Override
	public String toString(){
		return this.getClass().getName() 
				+"["
				+ "id="+id 
				+ ", name="+name
				+ ", price="+price
				+ ", numberOfViewing="+numberOfViewing
				+ ", lastViewingDateTime="+lastViewingDateTime
				+ ", dvdCategory="+dvdCategory
				+ ", comments="+comments
				+"]";
	}
	
	
	public Color getColor(){
		Color color = null;
		
		if(dvdCategory == DvdEntity.DvdCategory.ANIMATION){
			color = Color.BRIGHT_GREEN;
		}else if(dvdCategory == DvdEntity.DvdCategory.POLAR){
			color = Color.ORANGE;
		}else if(dvdCategory == DvdEntity.DvdCategory.SCIENCE_FICTION){
			color = Color.BLUE;
		}else if(dvdCategory == DvdEntity.DvdCategory.THRILLER){
			color = Color.DARK_YELLOW;
		}else{
			color = Color.BLACK;
		}
		return color;
	}

	// ----------------------------------- GET / SET TERS
	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}
//...
}



CSV File Exporter
– Create a new package com.ho.standard.exporter.csv,
– Create a new class named NullSafeCSVFileWriter extending java.io.FileWriter which is a NUllSave implementation of FileWriter in order to calling write with a null argument won’t throw any exception, but nothing will be written.

This class also provides utility methods to write Calendar, formated as timestamp, time, date, according to the DateFormats provided when creating the instance. Same goes for Double / Integer values.

public class NullSafeCSVFileWriter extends FileWriter{

	// ---------------------------------------- VARIABLES
	private NumberFormat decimalFormat;
	private String separator;

	// -------------------------------------- CONSTRUCTOR
	public NullSafeCSVFileWriter (File file, NumberFormat decimalFormat, String separator) throws IOException{
		super(file);
		this.separator = separator;
		this.decimalFormat = decimalFormat;
	}
	
	// ----------------------------------- PUBLIC METHODS
	public void write(Calendar cal, DateFormat format) throws IOException{
		if(cal != null){
			super.write(format.format(cal.getTime()));
		}
	}

	public void writeField(Calendar cal, DateFormat format) throws IOException{
		if(cal!=null){
			this.write(cal, format);
		}
		this.write(separator);
	}
//....
}

– Create a new class named DvdCSVExporter in the package com.ho.apache.poi.test which provides utility methods specific to our example (write headers, write Dvd entity,..) by using the previous class NullSafeCSVFileWriter. So, this class handles the generation of the CSV file via private methods to write headers and dvd entity.

public class DvdCSVExporter {
	
	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(DvdCSVExporter.class);
	private final TimeZone timeZone = TimeZone.getTimeZone("GMT");
	public static final String NEW_LINE = "\n";
	public static final String SEPARATOR = ";";
	private static final String[] COLUMN_LIST = {"Id","Name","Price","Number of Viewing","Last Viewing Datetime","Actors/Actress","Category","Comment"};
	
	// ----------------------------------- PUBLIC METHODS
	/**
	 * Exports the list of entities into the specified file.
	 * @param dvdList
	 * @param exportFile
	 * @throws Exception
	 */
	public void exportFile(List<DvdEntity> dvdList, File exportFile) throws Exception{
		
		LOG.info("Exporting CSV File: "+ dvdList.size() + " entries to export to the file " + exportFile.getPath());
		
		// Set up the formatters
		final NumberFormat decimalFormat = DecimalFormat.getInstance(Locale.ENGLISH);
		decimalFormat.setMaximumFractionDigits(8);
		decimalFormat.setGroupingUsed(false);
		//
		final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		//
		final SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		timestampFormat.setTimeZone(timeZone);
		//
		final SimpleDateFormat timeFormat = new SimpleDateFormat("hh:mm:ss");
		timeFormat.setTimeZone(timeZone);
		
		NullSafeCSVFileWriter fw = null;
		try{
			fw = new NullSafeCSVFileWriter(exportFile, decimalFormat, SEPARATOR);
			
			//Write header
			writeHeader(fw, timestampFormat, Calendar.getInstance());
			
			//Write content
			for (DvdEntity dvd : dvdList) {
				writeDvd(dvd, fw, decimalFormat, dateFormat, timestampFormat, timeFormat);
			}//end-for
			
			//Write last line : number of lines
			fw.write("File generated with standard classes");
			fw.write(SEPARATOR);
			fw.write(NEW_LINE);
			
			LOG.info("Exporting CSV File: Done");
			
		}finally{
			if(fw != null){
				fw.close();
			}
		}
	}

	// ---------------------------------- PRIVATE METHODS
	/**
	 * Write the headers in the CSV file.
	 * @param fw
	 * @param timestampFormat
	 * @param creationDate
	 * @throws IOException
	 */
	private void writeHeader(final NullSafeCSVFileWriter fw, final DateFormat timestampFormat, final Calendar creationDate) throws IOException{
		fw.write("File created at "+timestampFormat.format(creationDate.getTime()));
		fw.write(NEW_LINE);
		
		// Write CSV header now
		for (int i = 0; i < COLUMN_LIST.length - 1; i++) {
			fw.write(COLUMN_LIST[i]);
			fw.write(SEPARATOR);
		}//end-for
		
		// Write last column "Comment" without trailing seperator
		fw.write(COLUMN_LIST[COLUMN_LIST.length-1]);
		
		// Write the new line seperator
		fw.write(NEW_LINE);

	}
//...
}



XLS File Exporter
– Create a new package com.ho.apache.poi.exporter.xls,
– Create new interface IXLSFileBuilder provides utility methods to write headers, Calendar, formatted as timestamp, time, date to an excel file.

public interface IXLSFileBuilder {

	/**
	 * Define the headers of the file. This does not prevent you from calling addHeader() in addition
	 * (before, after or both).
	 * 
	 * @param headers : Header labels
	 */
	public void setHeaders(String... headers);

	/**
	 * Add a single header, after the last one defined
	 * @param header : Header label
	 */
	public void addHeader(String header);

	/**
	 * Set value at the given column, for the current row of data.
	 * @param col : Index of the column (0-based)
	 * @param value : Value to store.
	 */
	public void setDatavalue(int col, Calendar value);
	public void setDatavalue(int col, String value);
	public void setDatavalue(int col, Double value);
	public void setDatavalue(int col, Integer value);
	public void setDatavalue(int col, boolean value);

	/**
	 * Change the current row of data.
	 */
	public void nextRow();
//...
}

– Create a new class XLSFileBuilder implementing previous interface IXLSFileBuilder.
This class is the heart class of using Apache POI in order to create new sheet, add headers, add new row, set values in cell, set style to cell…etc.

public class XLSFileBuilder implements IXLSFileBuilder {

	// ---------------------------------------- VARIABLES
	protected final Workbook workbook = new HSSFWorkbook();
	protected Sheet sheet;
	protected final CellStyle normalStyle;
	protected final CellStyle dateStyle;
	protected final CellStyle timestampStyle;
	protected final CellStyle integerStyle;
	protected final CellStyle doubleStyle;
	protected final CellStyle boolStyle;
	protected final CellStyle headerStyle;
	protected int rowNum = 1;
	protected int headerCol = 0;
	
	// ------------------------------- PRIVATE ATTRIBUTES
	private Map<Class<?>, HashMap<Color, CellStyle>> cellStyleMap;
	private HSSFPatriarch drawingPatriach;
	private CreationHelper createHelper;
	
	// -------------------------------- PUBLIC ATTRIBUTES
	public enum Color{
		BLACK(HSSFColor.BLACK.index),
		RED(HSSFColor.RED.index),
		ORANGE(HSSFColor.ORANGE.index),
		LIGHT_ORANGE(HSSFColor.LIGHT_ORANGE.index),
		BLUE(HSSFColor.BLUE.index),
		YELLOW(HSSFColor.YELLOW.index),
		TURQUOISE(HSSFColor.TURQUOISE.index),
		DARK_YELLOW(HSSFColor.DARK_YELLOW.index),
		BRIGHT_GREEN(HSSFColor.BRIGHT_GREEN.index),
		;
		
		private short index;
		private Color(short index){
			this.index = index;
		}
		public short getIndex(){
			return this.index;
		}
	}
	
	// -------------------------------------- CONSTRUCTOR
	public XLSFileBuilder(){
		// Create styles for cell values
		normalStyle = workbook.createCellStyle();
		//
		timestampStyle = workbook.createCellStyle();
		timestampStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		//
		dateStyle = workbook.createCellStyle();
		dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
		//
		integerStyle = workbook.createCellStyle();
		integerStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		//
		doubleStyle = workbook.createCellStyle();
		doubleStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
		//
		boolStyle = workbook.createCellStyle();
		
		// Style for the header
		headerStyle = workbook.createCellStyle();
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
		headerStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
		
		// Header font
		final Font font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(font);
		
		// Error font
		final Font errorFont = workbook.createFont();
		errorFont.setColor(HSSFColor.WHITE.index);
		errorFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		errorFont.setFontHeightInPoints((short)10);
		
		// Initialize the cellStyleMap
		cellStyleMap = new HashMap<Class<?>, HashMap<Color, CellStyle>>();
		cellStyleMap.put(String.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Calendar.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Integer.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Double.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Boolean.class, new HashMap<Color, CellStyle>());
		cellStyleMap.put(Timestamp.class, new HashMap<Color, CellStyle>());
		
		
		// Store the mapping for normal fields
		cellStyleMap.get(String.class).put(null, this.normalStyle);
		cellStyleMap.get(Calendar.class).put(null, this.normalStyle);
		cellStyleMap.get(Integer.class).put(null, this.normalStyle);
		cellStyleMap.get(Double.class).put(null, this.normalStyle);
		cellStyleMap.get(Boolean.class).put(null, this.normalStyle);

		// Add format for datetime
		{
			final CellStyle timestampStyle = workbook.createCellStyle();
			timestampStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
			cellStyleMap.get(Timestamp.class).put(null, timestampStyle);
		}
		
		
		// Store the mapping for colored fields
		for(Color color : Color.values()){
			CellStyle normalColorStyle = copyAndColor(normalStyle, errorFont, color);
			cellStyleMap.get(String.class).put(color, normalColorStyle);
			
			CellStyle dateColorStyle = copyAndColor(dateStyle, errorFont, color);
			cellStyleMap.get(Calendar.class).put(color, dateColorStyle);
			
			CellStyle integerColorStyle = copyAndColor(integerStyle, errorFont, color);
			cellStyleMap.get(Integer.class).put(color, integerColorStyle);

			CellStyle doubleColorStyle = copyAndColor(doubleStyle, errorFont, color);
			cellStyleMap.get(Double.class).put(color, doubleColorStyle);

			CellStyle boolColorStyle = copyAndColor(boolStyle, errorFont, color);
			cellStyleMap.get(Boolean.class).put(color, boolColorStyle);

			CellStyle timestampColorStyle = copyAndColor(timestampStyle, errorFont, color);
			cellStyleMap.get(Timestamp.class).put(color, timestampColorStyle);
		}//end-for
		
		createHelper = workbook.getCreationHelper();
		if(sheet!=null){
			drawingPatriach = (HSSFPatriarch) sheet.createDrawingPatriarch();
		}
	}
	
	/**
	 * Constructs a new excel file builder, adding automatically a new sheet.
	 * The given sheet name is cleaned (removing forbidden characters: /,\,*,?,[,],:,!) 
	 * and truncated if necessary (max length = 31 characters).
	 * 
	 * @param sheetName
	 */
	public XLSFileBuilder(String sheetName){
		this();
		newSheet(sheetName);
		if(sheet!=null){
			drawingPatriach = (HSSFPatriarch) sheet.createDrawingPatriarch();
		}
	}
//...
}

– Create a new class named DvdXLSExporter in the package com.ho.apache.poi.test which provides utility methods specific to our example (write headers, write Dvd entity,..) by using the previous class XLSFileBuilder. So, this class handles the generation of the XLS file via private methods to write headers and dvd entity.

public class DvdXLSExporter {

	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(DvdXLSExporter.class);
	private static final String[] COLUMN_LIST = {"Id","Name","Price","Number of Viewing","Last Viewing Datetime","Actors/Actress","Category","Comment"};
	
	// ----------------------------------- PUBLIC METHODS
	/**
	 * Exports the list of entities into the specified file.
	 * @param dvdList
	 * @param exportFile
	 * @throws Exception
	 */
	public void exportFile(List<DvdEntity> dvdList, File exportFile) throws Exception{
		
		LOG.info("Exporting XLS File: "+ dvdList.size() + " entries to export to the file " + exportFile.getPath());

		final XLSFileBuilder excelFileBuilder = new XLSFileBuilder("My Dvds");
		excelFileBuilder.setHeaders(COLUMN_LIST);
		
		for(DvdEntity dvd : dvdList){
			writeDvd(dvd, excelFileBuilder);
			excelFileBuilder.nextRow();
		}//end-for
		
		// Resize the columns to fit the content
		excelFileBuilder.autoSizeColumns();
		
		excelFileBuilder.save(exportFile);
		LOG.info("Exporting XLS File: Done");
	}

	private void writeDvd(final DvdEntity dvd, final XLSFileBuilder excelFileBuilder) throws IOException{
		int col = 0;
		// Integer id
		excelFileBuilder.setDataValue(col++, dvd.getId(), dvd.getColor(), null);
		// String name
		excelFileBuilder.setDataValue(col++, dvd.getName(), dvd.getColor(), dvd.getComments());
		// Double price
		excelFileBuilder.setDataValue(col++, dvd.getPrice(), dvd.getColor(), null);
		// Integer numberOfViewing
		excelFileBuilder.setDataValue(col++, dvd.getNumberOfViewing(), dvd.getColor(), null);
		// Calendar lastViewingDateTime
		excelFileBuilder.setDataValue(col++, dvd.getLastViewingDateTime(), true, dvd.getColor(), null);
		// String[] actors
		excelFileBuilder.setDataValue(col++, dvd.getActors(), dvd.getColor(), null);
		// DvdCategory dvdCategory
		excelFileBuilder.setDataValue(col++, dvd.getDvdCategory().toString(), dvd.getColor(), null);
		// String comments
		excelFileBuilder.setDataValue(col++, dvd.getComments(), dvd.getColor(), null);
	}
}



Main method
Finally, create a new class named TestExporter in the package com.ho.apache.poi.test containing the main method to launch the DvdCSVExporter and DvdXLSExporter in order to create the files CSV and XLS of DVD entities.

public class TestExporter {

	// ---------------------------------------- VARIABLES
	private static final Logger LOG = Logger.getLogger(TestExporter.class);

	// -------------------------------------- CONSTRUCTOR
	private TestExporter(){ }
	

	// -------------------------------------- MAIN METHOD
	public static void main(String[] args) {
		int exitStatus = -1;
		try{
			// Parse parameters, load properties and mark the batch as started.
			String defaultFilename = "data/test_poi_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(Calendar.getInstance().getTime());
			File xlsExport = new File(defaultFilename + ".xls");
			File csvExport = new File(defaultFilename + ".csv");
			List<DvdEntity> entities = new ArrayList<DvdEntity>();
			//
			int idCounter = 1;
			for (int i = 0; i < DvdCategory.values().length; i++) {
				for (int j = 0; j < 5; j++, idCounter++) {
					DvdEntity dvd = new DvdEntity();
					dvd.setId(idCounter);
					dvd.setName("Dvd "+idCounter);
					dvd.setPrice((Math.random()%10));
					dvd.setNumberOfViewing((int)(Math.random()%10));
					dvd.setLastViewingDateTime(GregorianCalendar.getInstance());
					dvd.setActors(new String[]{"Actor "+idCounter+j, "Actress "+idCounter+j});
					dvd.setDvdCategory(DvdCategory.values()[i]);
					dvd.setComments("a comment for the dvd "+idCounter);
					entities.add(dvd);
				}//end-for
			}//end-for
			
			// Run the batch
			{
				// Export the dvds as Excel file
				final DvdXLSExporter dvdXLSExporter = new DvdXLSExporter();
				dvdXLSExporter.exportFile(entities, xlsExport);
				
				// Export the dvds as CSV file
				final DvdCSVExporter dvdCSVExporter = new DvdCSVExporter();
				dvdCSVExporter.exportFile(entities, csvExport);
			}
			
			exitStatus = 0;
			
		}catch(Exception ex){
			exitStatus = 2;
			LOG.error("Exception caught:", ex);
		}
		System.exit(exitStatus);
	}	
}

The outputs are:

[00:52:19]INFO  - Exporting XLS File: 20 entries to export to the file data\test_poi_20140521_005219.xls (DvdXLSExporter.java:32) 
[00:52:21]INFO  - Exporting XLS File: Done (DvdXLSExporter.java:46) 
[00:52:21]INFO  - Exporting CSV File: 20 entries to export to the file data\test_poi_20140521_005219.csv (DvdCSVExporter.java:41) 
[00:52:21]INFO  - Exporting CSV File: Done (DvdCSVExporter.java:73) 

article-apache-poi-excel-csv-2

article-apache-poi-excel-csv-3

article-apache-poi-excel-csv-4

That’s all!!!

Download: test_poi.zip

Best regards,

Huseyin OZVEREN

Page 1 of 30:1 2 3 4 »Last »
bottom-img
Copyright ® 2012 Huseyin Ozveren. No reproduction, even partial, can be used from this site and all its contents including text, documents, images, etc.. without the express permission of the author.
Jeux gratuit | flash jeux gratuits