Create Excel File in java

Create Excel File in java 

In this example I select data from database and write that data into a Excel file.


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.hpsf.HPSFException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.sql.*;



public class tntWriteTOExcel {

    public static void excel() throws HPSFException {
      try
      {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
       
        int no=OtherFunctions.RandomNumber();
               
        ArrayList data = new ArrayList();
        ArrayList headers = new ArrayList();

        File file123 = new File("E:\\MemberList.xls");

        headers.add("Student ID");
        headers.add("CardID");
        headers.add("FirstName");
       
        headers.add("Middle Name");
        headers.add("Last Name");
        headers.add("DOB");
       
           
         connection = ConnectionConfiguration.getConnection(); //This my database connection.

         preparedStatement = connection.prepareStatement("SELECT * FROM studentregistration");
         resultSet = preparedStatement.executeQuery();  

        //for (int i = 0; i <= 5; i++) {
       
         while(resultSet.next())
         {
            ArrayList cells = new ArrayList();
            cells.add(resultSet.getString("Stu_ID"));
            cells.add(resultSet.getString("Stu_CardID"));
            cells.add(resultSet.getString("Stu_FirstName"));
           
            cells.add(resultSet.getString("Stu_MidName"));
            cells.add(resultSet.getString("Stu_LastName"));
            cells.add(resultSet.getString("Stu_DOB"));
           
         
           
            data.add(cells);
        }

        exportToExcel("Test", headers, data, file123);
    }
   
    catch(Exception Ex)
    {
        System.out.println("Export to Excel "+Ex);
    }
            }
    public static void exportToExcel(String sheetName, ArrayList headers,
            ArrayList data, File outputFile) throws HPSFException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);

        int rowIdx = 0;
        short cellIdx = 0;

        // Header
        HSSFRow hssfHeader = sheet.createRow(rowIdx);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (Iterator cells = headers.iterator(); cells.hasNext();) {
            HSSFCell hssfCell = hssfHeader.createCell(cellIdx++);
            hssfCell.setCellStyle(cellStyle);
            hssfCell.setCellValue((String) cells.next());
        }
        // Data
        rowIdx = 1;
        for (Iterator rows = data.iterator(); rows.hasNext();) {
            ArrayList row = (ArrayList) rows.next();
            HSSFRow hssfRow = sheet.createRow(rowIdx++);
            cellIdx = 0;
            for (Iterator cells = row.iterator(); cells.hasNext();) {
                HSSFCell hssfCell = hssfRow.createCell(cellIdx++);
                hssfCell.setCellValue((String) cells.next());
            }
        }

        wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_COMPRESSED_UNICODE);
        try {
            FileOutputStream outs = new FileOutputStream(outputFile);
            wb.write(outs);
            outs.close();
            System.out.println("success");
        } catch (IOException e) {
          throw new HPSFException(e.getMessage());
        }
    }
   

    }

Continue reading →

MySQL Database Connection

MySQL Database Connection 



import java.sql.*;
/**
 *
 * @author Rajkumar
 */
public class ConnectionConfiguration {
     //public static final String URL = "jdbc:mysql://localhost:3306/passerin_crm";
   public static final String URL = "jdbc:mysql://localhost:3306/demopasserine_crm";
//    /**
//     * In my case username is "root" *
//     */
    public static final String USERNAME = "root";
//    /**
//     * In my case password is "1234" *
//     */
    public static final String PASSWORD = "root";
//
  //  public static final String URL = "jdbc:mysql://passerinegroup.com:3306/passerin_crm";
    /**
     * In my case username is "root" *
     */
 //   public static final String USERNAME = "passerin_crm";
    /**
     * In my case password is "1234" *
     */
  //  public static final String PASSWORD = "crm@!@#";
//////
   
    public static Connection getConnection() {
        Connection connection = null;

        try {
             Class.forName("com.mysql.jdbc.Driver");
             System.out.println("Connectinggggggg.....");
             connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             System.out.println("Connected..");
           
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return connection;
    }

}

Continue reading →