Read Write to excel using JAVA

In this video, I have shown how you can create an excel file using java.

1. Project structure.

2. DB_Connection.java, This connection class is used to connect with database.
You will need to update the host,dbname,username and password according to your project.

package jinuclass;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection_HR {
  public Connection getConnection() {
    Connection connection = null;
    System.out.println("Connection called");
    try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hr", "root", "root");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return connection;
  }
}

3. Below class will help you to read files from Excel.
Read_From_Excel.java

package jinuclass;
import java.io.FileInputStream; 
import java.util.Iterator;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Read_From_Excel { 
public static void main( String [] args ) {
	String fileName="C:\\Users\\MIRITPC\\Desktop\\test\\excel\\test.xls"; 
	//Read an Excel File and Store in a Vector
    Vector dataHolder=readExcelFile(fileName);
    //Print the data read
    printCellDataToConsole(dataHolder);
}
public static Vector readExcelFile(String fileName)
{
    /** --Define a Vector
        --Holds Vectors Of Cells
     */
    Vector cellVectorHolder = new Vector();
    try{
    /** Creating Input Stream**/
    //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName );
    FileInputStream myInput = new FileInputStream(fileName);
    /** Create a POIFSFileSystem object**/
    POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
    /** Create a workbook using the File System**/
     HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
     /** Get the first sheet from workbook**/
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    /** We now need something to iterate through the cells.**/
      Iterator rowIter = mySheet.rowIterator();
      while(rowIter.hasNext()){
          HSSFRow myRow = (HSSFRow) rowIter.next();
          Iterator cellIter = myRow.cellIterator();
          Vector cellStoreVector=new Vector();
          while(cellIter.hasNext()){
              HSSFCell myCell = (HSSFCell) cellIter.next();
              cellStoreVector.addElement(myCell);
          }
          cellVectorHolder.addElement(cellStoreVector);
      }
    }catch (Exception e){e.printStackTrace(); }
    return cellVectorHolder;
}
private static void printCellDataToConsole(Vector dataHolder) {
	try {  
    for (int i=0;i<8000; i++){
               Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
        for (int j=0; j < cellStoreVector.size();j++){
            HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
            String stringCellValue = myCell.toString();
            System.out.print(j+" "+stringCellValue+"\t");
        }
        System.out.println();
    }
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
}

4. The code below will help you to write to excel file directly.
Write_to_excel_file_directly.java

package jinuclass;
import java.io.File;
import jxl.Workbook;
import jxl.write.*;
public class Write_to_excel_file_directly {
  public static void main(String[] args) throws InterruptedException {
    WritableWorkbook wworkbook;
    try {
      wworkbook = Workbook.createWorkbook(new File("C:\\Users\\MIRITPC\\Desktop\\test\\excel\\write_to_excel_test.xls"));
      WritableSheet wsheet = wworkbook.createSheet("First Sheet", 0);
      Label label = new Label(0, 2, "A label record");
      wsheet.addCell(label);
      int i = 0;
      int j = 1;
      i = 0;
      label = new Label(i++, j, j + "");
      wsheet.addCell(label);
      label = new Label(i++, j, "11");
      wsheet.addCell(label);
      label = new Label(i++, j, "12");
      wsheet.addCell(label);
      label = new Label(i++, j, "13");
      wsheet.addCell(label);
      j++;
      i = 0;
      label = new Label(i++, j, j + "");
      wsheet.addCell(label);
      label = new Label(i++, j, "21");
      wsheet.addCell(label);
      label = new Label(i++, j, "22");
      wsheet.addCell(label);
      label = new Label(i++, j, "33");
      wsheet.addCell(label);
      j++;
      wworkbook.write();
      wworkbook.close();
      System.out.println("fineshed");
    } catch (Exception e) {
      System.out.println(e);
    }
  }
}

5. The code below will connect mysql database and select the table data and write to excel file as per the table.
Write_to_excel_file_from_my_sql_database.java

package jinuclass;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jxl.Workbook;
import jxl.write.*;
public class Write_to_excel_file_from_my_sql_database {
  public static void main(String[] args) throws InterruptedException {
    WritableWorkbook wworkbook;
    try {
      wworkbook = Workbook.createWorkbook(new File("C:\\Users\\MIRITPC\\Desktop\\test\\excel\\absent_details_JAN.xls"));
      DBConnection_HR obj_DBConnection_LMC = new DBConnection_HR();
      Connection connection = obj_DBConnection_LMC.getConnection();
      PreparedStatement ps = null;
      ResultSet rs = null;
      String query = "select * from attendence_table where date like '%oct-2015%'";
      ps = connection.prepareStatement(query);
      System.out.println(ps);
      rs = ps.executeQuery();
      WritableSheet wsheet = wworkbook.createSheet("First Sheet", 0);
      Label label = new Label(0, 2, "A label record");
      wsheet.addCell(label);
      int i = 0;
      int j = 1;
      while (rs.next()) {
        i = 0;
        label = new Label(i++, j, j + "");
        wsheet.addCell(label);
        label = new Label(i++, j, rs.getString("employee_name"));
        wsheet.addCell(label);
        label = new Label(i++, j, rs.getString("date"));
        wsheet.addCell(label);
        label = new Label(i++, j, rs.getString("branch"));
        wsheet.addCell(label);
        j++;
      }
      wworkbook.write();
      wworkbook.close();
      System.out.println("fineshed");
    } catch (Exception e) {
      System.out.println(e);
    }
  }
}

6. The JSP page below will call the download_source.jsp and download the excel file in with web browser.
download_excel.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
	<a href="Download_source.jsp">Click Here to Download</a>
</body>
</html>

7. This code will create the excel file in the browser from the client side and force download the excel file.
Download_source.jsp

<%@page import="java.text.DecimalFormat"%>
<%@page import="java.util.HashMap"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%> 
<%@page import="org.apache.poi.ss.util.CellRangeAddress"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%><%@page import="java.io.*" %>
<% 
// create a small spreadsheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(
        0, //first row (0-based)
        0, //last row  (0-based)
        0, //first column (0-based)
        6  //last column  (0-based)
));
 row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
 cell = row.createCell(0);
 cell.setCellValue("Leave Requests");
 row = sheet.createRow(1);
 cell = row.createCell(0);
 cell.setCellValue("#");
cell = row.createCell(1);
cell.setCellValue("Name");
cell = row.createCell(2);
cell.setCellValue("Subject");
cell = row.createCell(3);
cell.setCellValue("Requested From Date");
cell = row.createCell(4);
cell.setCellValue("Requested To Date");
cell = row.createCell(5);
cell.setCellValue("Approved From Date");
cell = row.createCell(6);
cell.setCellValue("Approved To Date");
cell = row.createCell(7);
cell.setCellValue("Last Leave Date");
cell = row.createCell(8);
cell.setCellValue("Last Rejoin Date");
cell = row.createCell(9);
cell.setCellValue("Requested Number of Days");
cell = row.createCell(10);
cell.setCellValue("Approved Number of Days");
cell = row.createCell(11);
cell.setCellValue("Reliever");
cell = row.createCell(12);
cell.setCellValue("Nationality");
cell = row.createCell(13);
cell.setCellValue("Native Address");
cell = row.createCell(14);
cell.setCellValue("Comments");
cell = row.createCell(15);
cell.setCellValue("Qatar Contact Number");
cell = row.createCell(16);
cell.setCellValue("Native Number");
cell = row.createCell(17);
cell.setCellValue("Requested On");
cell = row.createCell(18);
cell.setCellValue("Status");
row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellValue("2#");
cell = row.createCell(1);
cell.setCellValue("2Name");
cell = row.createCell(2);
cell.setCellValue("2Subject");
cell = row.createCell(3);
cell.setCellValue("2Requested From Date");
cell = row.createCell(4);
cell.setCellValue("2Requested To Date");
cell = row.createCell(5);
cell.setCellValue("2Approved From Date");
cell = row.createCell(6);
cell.setCellValue("2Approved To Date");
cell = row.createCell(7);
cell.setCellValue("2Last Leave Date");
cell = row.createCell(8);
cell.setCellValue("2Last Rejoin Date");
cell = row.createCell(9);
cell.setCellValue("2Requested Number of Days");
cell = row.createCell(10);
cell.setCellValue("2Approved Number of Days");
cell = row.createCell(11);
cell.setCellValue("2Reliever");
cell = row.createCell(12);
cell.setCellValue("2Nationality");
cell = row.createCell(13);
cell.setCellValue("2Native Address");
cell = row.createCell(14);
cell.setCellValue("2Comments");
cell = row.createCell(15);
cell.setCellValue("2Qatar Contact Number");
cell = row.createCell(16);
cell.setCellValue("2Native Number");
cell = row.createCell(17);
cell.setCellValue("2Requested On");
cell = row.createCell(18);
cell.setCellValue("2Status");
// write it as an excel attachment
ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);
byte [] outArray = outByteStream.toByteArray();
response.setContentType("application/ms-excel");
response.setContentLength(outArray.length); 
response.setHeader("Expires:", "0"); // eliminates browser caching
response.setHeader("Content-Disposition", "attachment; filename=Leave Details.xls");
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();
%>

8. The code will preview the excel file as table in client side.
preview_excel.jsp.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.io.FileInputStream"%>
<%@page import="jxl.Workbook"%>
<%@page import="jxl.Sheet"%> <html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
				<table border="" width="100%">
						<tr>
								<td>
											#
								</td>
								<td>
											col 1
								</td>
								<td>
											col 2
								</td>
								<td>
											col 3
								</td>
						</tr>
						<%
						try{
					   String FilePath = "C:\\Users\\MIRITPC\\Desktop\\test\\excel\\test.xls";
						  //  String FilePath = Common_Things.document_upload_path+"/"+name;
						FileInputStream fs = new FileInputStream(FilePath);
					Workbook wb = Workbook.getWorkbook(fs);
					// TO get the access to the sheet
					Sheet sh = wb.getSheet("Sheet1");
					// To get the number of rows present in sheet
					int totalNoOfRows = sh.getRows();
					// To get the number of columns present in sheet
					int totalNoOfCols = sh.getColumns();
					for (int row = 0; row < 8000; row++) {
					%>
					<tr>
					<%
						for (int col = 0; col < totalNoOfCols; col++) {
							//out.println(sh.getCell(col, row).getContents()+ "-----------" );
					%>
							<%
									if(col==0){
						 	%>
								<td>
											<%=sh.getCell(col, row).getContents() %>
								</td>
							<%			 
									}
							%>  
							<%
									if(col==1){
						 	%>
								<td>
											<%=sh.getCell(col, row).getContents() %>
								</td>
							<%			 
									}
							%>  
							<%
									if(col==2){
						 	%>
								<td>
											<%=sh.getCell(col, row).getContents() %>
								</td>
							<%			 
									}
							%>  
							<%
									if(col==3){
						 	%>
								<td>
											<%=sh.getCell(col, row).getContents() %>
								</td>
							<%			 
									}
							%>  
					<%	
						}
					%>
					</tr>
					<%
					}
					}catch (Exception e) {
						System.out.println(e);
					}
						%>
				</table>
</body>
</html>

9. We are using 4 jar files to run the codes. You can downlaod each below.

poi-3.9-20121203

dom4j

jxl

mysql-connector-java-5.0.0-beta-bin

10. Download the complete project here

JavaExcel