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…

lucene example

In simple words: it parses a text and without saving the text the framework can search inside it. It keeps track of terms used inside the text. Of course you can optionally save the text into your <a href="http://www.phloxblog.in/lucene-indexing-and-searching-in-multiple-tables-conceptuual-representaion/" data-cke-saved-href="http://www.phloxblog.in/lucene-indexing-and-searching-in-multiple-tables-conceptuual-representaion/">lucene index</a>.

 Lets take an example.

 I have created a database and a table named it 'city'.

 create table `city` (

 `id` bigint (20),

 `city_name` varchar (765)

 );

 Now I have added two record Kolkata,Delhi in the database.

 Now create a java project and add lucene-analyzers-common-4.0.0.jar,lucene-core-4.0.0.jar,lucene-queries-4.0.0.jar,lucene-queryparser-4.0.0.jar and mysql-connector-java-5.1.22-bin.jar in the project.

 create a java class named LuceneTest  and write the following.

LuceneTest .java


 package com.test;

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