Skip to main content

apache poi example

what is apache poi?
 Apache POI is a Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc.

 Lets take an example.

 Tools:
 Java JDK 1.5 or above,apache poi latest jar,mysql connector jar and eclipse.

 create a table name it city and insert two data example:

 create table `city` (

 `id` bigint (20),

 `city_id` varchar (12),

 `city_name` varchar (60)

 );

 insert into `city` (`id`, `city_id`, `city_name`) values('1','KOL','Kolkata');

 insert into `city` (`id`, `city_id`, `city_name`) values('2','SIL','Siliguri');


 Now create a java project in eclipse and write a class PoiExample.java where data retrieve from database and create a excel file to show the data.

 PoiExample.java

 package com.test;
 import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;


 import java.io.OutputStream;



 import java.sql.Connection;



 import java.sql.DriverManager;



 import java.sql.PreparedStatement;



 import java.sql.ResultSet;



 import java.sql.SQLException;



 import java.util.ArrayList;



 import java.util.List;







 import org.apache.poi.hssf.usermodel.HSSFCellStyle;



 import org.apache.poi.hssf.usermodel.HSSFFont;



 import org.apache.poi.hssf.usermodel.HSSFSheet;



 import org.apache.poi.hssf.usermodel.HSSFWorkbook;



 import org.apache.poi.hssf.util.HSSFColor;







 public class PoiExample {







 /**



 * @param args



 */



 public static void main(String[] args) {



 PoiExample poiex=new PoiExample();



 byte[] outputByte =poiex.getExcel();



 OutputStream outt = new ByteArrayOutputStream() ;



 try {



 outt.write(outputByte);



 if(outt!=null)



 outt.flush();



 } catch (Exception e) {







 }







 }



 public Connection getConnection()



 {



 Connection connect=null;



 try{



 Class.forName("com.mysql.jdbc.Driver");



 // Setup the connection with the DB



 connect = DriverManager



 .getConnection("jdbc:mysql://localhost/poidatabase?"



 + "user=root&password=root");



 }catch (Exception e) {



 // TODO: handle exception



 }



 return connect;







 }



 public byte[] getExcel()



 {



 List<City> cityList=new ArrayList<City>();



 City city=null;



 Connection connection = getConnection();



 PreparedStatement pst = null;



 ResultSet rs = null;



 String query = null;



 try {







 query = " select * from city ";



 pst = connection.prepareStatement(query);



 rs = pst.executeQuery();







 while (rs.next()) {



 city = new City();



 city.setId(rs.getString("city_id"));



 city.setCity_name(rs.getString("city_name"));



 cityList.add(city);



 }



 } catch (Exception e) {



 e.printStackTrace();



 } finally {



 try {



 connection.close();



 } catch (SQLException e) {



 // TODO Auto-generated catch block



 e.printStackTrace();



 }



 }



 FileOutputStream outputStream = null;



 HSSFWorkbook wb=new HSSFWorkbook();



 HSSFSheet sheet = wb.createSheet("City");



 boolean returnValue = true;



 ByteArrayOutputStream bos=null;



 String currentfilename=null;



 //////////////////////Excel Header Style/////////////////////////



 HSSFCellStyle headerlabelcs = wb.createCellStyle();



 headerlabelcs.setFillForegroundColor(HSSFColor.PALE_BLUE.index);



 headerlabelcs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);



 headerlabelcs.setBorderLeft((short)1);



 headerlabelcs.setBorderRight((short)1);







 HSSFFont headerlabelfont = wb.createFont();



 headerlabelfont.setFontHeightInPoints((short)12);



 headerlabelfont.setFontName("Calibri");



 headerlabelfont.setColor(HSSFColor.BLACK.index);



 headerlabelfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);



 headerlabelcs.setFont(headerlabelfont);



 //////////////////////Excel Header Style/////////////////////////



 //////////////////////Excel Error Style/////////////////////////



 HSSFCellStyle errlabelcs = wb.createCellStyle();



 errlabelcs.setFillForegroundColor(HSSFColor.TAN.index);



 errlabelcs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);



 errlabelcs.setBorderLeft((short)1);



 errlabelcs.setBorderRight((short)1);







 HSSFFont errlabelfont = wb.createFont();



 errlabelfont.setFontName("Calibri");



 errlabelfont.setColor(HSSFColor.BLACK.index);



 errlabelfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);



 errlabelcs.setFont(errlabelfont);



 //////////////////////Excel Header Style/////////////////////////



 //////////////////////Excel Success Style/////////////////////////



 HSSFCellStyle successlabelcs = wb.createCellStyle();



 successlabelcs.setFillForegroundColor(HSSFColor.GREEN.index);



 successlabelcs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);



 successlabelcs.setBorderLeft((short)1);



 successlabelcs.setBorderRight((short)1);







 HSSFFont successlabelfont = wb.createFont();



 successlabelfont.setFontName("Calibri");



 successlabelfont.setColor(HSSFColor.BLACK.index);



 successlabelfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);



 successlabelcs.setFont(successlabelfont);



 //////////////////////Excel Header Style/////////////////////////



 int rowIndex = 0;



 sheet.createRow(rowIndex);



 sheet.getRow(rowIndex).createCell(0).setCellValue("City Id");



 sheet.getRow(rowIndex).getCell(0).setCellStyle(headerlabelcs);



 sheet.getRow(rowIndex).createCell(1).setCellValue("City Name");



 sheet.getRow(rowIndex).getCell(1).setCellStyle(headerlabelcs);







 try {



 for(City ci: cityList){



 rowIndex++;



 sheet.createRow(rowIndex);



 sheet.getRow(rowIndex).createCell(0).setCellValue(ci.getId());



 sheet.getRow(rowIndex).createCell(1).setCellValue(ci.getCity_name());







 }



 //Automatically Column Size Set



 for (short col = 0; col < 10; col++)sheet.autoSizeColumn((short)col);







 bos = new ByteArrayOutputStream();



 wb.write(bos);



 outputStream = new FileOutputStream("city.xls");



 wb.write(outputStream);



 wb = null;



 outputStream.close();



 } catch (Exception e) {



 System.out.println("error="+rowIndex+" "+e.getMessage());



 returnValue = false;



 e.printStackTrace();



 }



 return bos.toByteArray();











 }







 }







 <strong>City.java</strong>







 package com.test;







 public class City {







 public String id;



 public String city_name;



 public String getId() {



 return id;



 }



 public void setId(String id) {



 this.id = id;



 }



 public String getCity_name() {



 return city_name;



 }



 public void setCity_name(String city_name) {



 this.city_name = city_name;



 }


 }


 Now run the project and refresh the project .















Comments

Popular posts from this blog

jdbc mysql example in java

Here’s an example to show you how to connect to MySQL database via a JDBC driver.To connect to MySQL from Java, you have to use the JDBC driver from MySQL. You find the latest MySQL JDBC driver under the following URL:http://dev.mysql.com/downloads/connector/j.
Create a example database and named it test.Now create a a table name it user. Create table user ((userid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL, PRIMARY KEY (userid)); Now add the record in your table; Now create a project in eclipse and mysql-connector jar in its classpath.package com.test;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; publicclass JavaJDBCExample { private Connection connect = null; private Statement statement = null; private ResultSet resultSet = null; publicvoid readDatabase() { try { // this will load the MySQL driver Class.forName("com.mysql.jdbc.Driver"); // setup the connection wi…

singleton design pattern

Java Singleton design pattern is one of the design pattern which suggest that only one instance of a Singleton object is created by the JVM. This is useful when exactly one object is needed to coordinate actions across the system Example: public class Singleton{ privatestatic Singleton singleton = null; private Singleton(){ } publicstaticsynchronized Singleton getInstance( ) { if(singleton == null) { singleton = new Singleton(); } returnsingleton; } publicvoid test( ) { System.out.println("test"); } @Override protected Object clone() throws CloneNotSupportedException { // TODO Auto-generated method stub returnnew CloneNotSupportedException(); } } publicclass SingletonTest { publicstaticvoid main(String[] args){ Singleton singleton = Singleton.getInstance(); try{ Singleton

Tomcat configuration

1. How to deploy web application in tomcat 
 Create a war file and deploy it in tomcat->webapps folder.Restart tomcat and write localhost:8080/YOURWEBAPPNAME. 
 2. Add jndi in tomcat 
 <Context>
 <Resource name="jdbc/myDB" auth="Container"
 type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000"
 username="admin" password="admin"
 driverClassName="com.mysql.jdbc.Driver"
 url="jdbc:mysql://localhost:3306/javatest"/> 
 </Context>
 in web.xml 
 <resource-ref>
 <description>DB Connection</description>
 <res-ref-name>jdbc/myDB</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 </resource-ref> 
 3. Add user with manager role in tomcat   Tomcat-users.xml 
 <tomcat-users>
 <role rolename="manager"/>
 <user username="admin"…