How To Export Database Data To Excel File In Java

I am here to tell you how can we export database data into excel file and then download it in servlet, you can also put this logic in core java and any framework in java.

one important thing dont forget to download apache poi jar and put into lib, by this API we can export data easily.



import java.io.File;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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

/**
 * Servlet implementation class DatabaseToExcel
 */
@WebServlet("/DatabaseToExcel")
public class DatabaseToExcel extends HttpServlet {
private static final long serialVersionUID = 1L;

/**
* @see HttpServlet#HttpServlet()
*/
public DatabaseToExcel() {
super();
// TODO Auto-generated constructor stub
}


@SuppressWarnings("deprecation")
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {

Connection connection;
Statement stmt;
ResultSet rs;

try {
connection = com.Database.nee.DatabaseConnect.getConnection();
stmt = connection.createStatement();
String query = "Select C.TYPE,C.CUSTOMER,C.MOBILE, P.PURCHASE_DATE,P.DEAL_ID from customer C "
+ "INNER JOIN productinfo P ON C.C_ID=P.C_ID";
rs = stmt.executeQuery(query);

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("lawix10");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell((short) 0).setCellValue("TYPE");
rowhead.createCell((short) 1).setCellValue("CUSTOMER");
rowhead.createCell((short) 2).setCellValue("MOBILE");
rowhead.createCell((short) 3).setCellValue("PURCHASE_DATE");
rowhead.createCell((short) 4).setCellValue("DEAL_ID");

int i = 1;

while (rs.next()) {

HSSFRow row = sheet.createRow((short) i);
// row.createCell((short)
// 0).setCellValue(Integer.toString(rs.getInt("TYPE")));
row.createCell((short) 0).setCellValue(rs.getString("TYPE"));
row.createCell((short) 1)
.setCellValue(rs.getString("CUSTOMER"));
row.createCell((short) 2).setCellValue(rs.getString("MOBILE"));
row.createCell((short) 3).setCellValue(
rs.getString("PURCHASE_DATE"));
row.createCell((short) 4).setCellValue(rs.getString("DEAL_ID"));
i++;
System.out.println(i);

}

String filename = "bajaj.xls";
String upload = "/folderCsv/";
String filepath = request.getServletContext().getRealPath(upload);
File folder = new File(filepath);
if (!folder.exists()) {

folder.mkdir();
}
String exactPath = filepath + File.separator + filename;
FileOutputStream filOut = new FileOutputStream(exactPath);
workbook.write(filOut);

filOut.close();

} catch (SQLException | IOException e) {

e.printStackTrace();
}



String message = "<span style='color:green;'>"
+ " Data Is Exported Successfully"
+ "</span>";
request.setAttribute("message", message);
request.getRequestDispatcher("/exportContacts.jsp").forward(request,
response);


}

protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}


Here i have created database connection according to me but you can do it according to you. and i think you know how to play with jsp.



Thanks

Keep Happy Learning !