How can export data in excel from MYSql server.
Dear All ,
I am new in Java and know about how to export data from mysql to export
My code as below :-
database code:-
private void setDynamicDate(LoginCountForm l_objForm) throws Exception {Connection l_objConnection = DataAccess.connectToDatabase();
Statement l_objStatement = l_objConnection.createStatement();
Statement l_objStatement1 = l_objConnection.createStatement();
String l_strQuery = "";
int m_noofdays = 0;
String d = "";
LoginCountBean bean = null;
LoginCountBean1 bean1 = null;
ArrayList lst = new ArrayList();
ArrayList lst1 = new ArrayList();
ArrayList date = new ArrayList();
List completeList = new ArrayList();
String m_strStartDate = SDCommonUtil.convertSDFormatToDBDate(l_objForm
.getM_strStartDate());
String m_strEndDate = SDCommonUtil.convertSDFormatToDBDate(l_objForm
.getM_strEndDate());
l_strQuery = "Select timestampdiff(day,'" + m_strStartDate + "','"
+ m_strEndDate + "')";
l_objStatement.executeQuery(l_strQuery);
ResultSet l_objRes = l_objStatement.getResultSet();
while (l_objRes.next()) {
m_noofdays = l_objRes.getInt(1);
m_noofdays = m_noofdays + 1;
}
for (int i = 0; i < m_noofdays; i++) {
l_strQuery = "select DATE_ADD('" + m_strStartDate + "',INTERVAL '"
+ i + "' DAY) as d";
l_objStatement.executeQuery(l_strQuery);
l_objRes = l_objStatement.getResultSet();
if (l_objRes != null) {
while (l_objRes.next()) {
bean = new LoginCountBean();
String date1 = l_objRes.getString("d");
bean.setM_strDynamicDate(date1);
date.add(date1);
lst.add(bean);
bean = null;
}
}
l_objForm.setL_lstDynamicDate(lst);
}
for (int i = 0; i < date.size(); i++) {
l_strQuery = "SELECT e.pernr,t.count FROM sales.empdetails e left join (SELECT pernr,count FROM sales.loginncntmst where date="
+ SDCommonUtil.convertBlankToNull(String.valueOf(date
.get(i)), true) + ") t on e.pernr=t.pernr";
System.out.println(l_strQuery);
l_objStatement.executeQuery(l_strQuery);
l_objRes = l_objStatement.getResultSet();
while (l_objRes.next()) {
bean1 = new LoginCountBean1();
bean1.setM_strDynamicDateCount(l_objRes.getString("count"));
if (bean1.getM_strDynamicDateCount() == null) {
bean1.setM_strDynamicDateCount("0");
}
bean1.setM_strEmployeeName(SDCommonUtil.convertValuesForValueAndID(l_objStatement1,
"sales.empdetails", "pernr", "ename",
SDCommonUtil.convertNullToBlank(l_objRes.getString("pernr"), false), true));
bean1.setM_strDepartment(SDCommonUtil
.convertValuesForValueAndID(l_objStatement1,
"sales.empdetails", "pernr", "department",
SDCommonUtil.convertBlankToNull(l_objRes
.getString("pernr"), false), true));
lst1.add(bean1);
bean1 = null;
}
completeList.add(lst1);
lst1 = new ArrayList();
}
l_objForm.setL_lstLoginCount(completeList);
l_objConnection.close();
}
in the above code i was receive 379 row from table know i want to handle in excel my excel code are as beloe :-
public void exportToExcel(LoginCountForm l_objForm,
HttpServletResponse response) throws Exception {
ArrayList dataList = new ArrayList<LoginCountBean>();
ArrayList dataList1 = new ArrayList<LoginCountBean1>();
ArrayList dataList2 = new ArrayList<LoginCountBean1>();
ArrayList dataList3 = new ArrayList<LoginCountBean1>();
ArrayList headers = new ArrayList();
// File file123 = new File("/tmp/Employee Leave Report.xls");
String contextPath = getServlet().getServletContext().getRealPath("/");
File file123 = new File(contextPath + "/Login Count Day Wise.xls");
// File file123 = new File("E://Vip//Employee Leave Report.xls");
String m_strFileName = "Login Count Day Wise.xls";
headers.add("Employee Name");
headers.add("Department");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Employee Login Count Daywise");
int rowIdx = 0;
int cellIdx = 0;
int a[] = new int[headers.size()];
HSSFRow hssfHeader = sheet.createRow(rowIdx);
sheet.setColumnWidth(0, 4200);
sheet.setColumnWidth(1, 4200);
HSSFFont font = wb.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 11);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFont(font);
int m = 0;
for (Iterator cells = headers.iterator(); cells.hasNext();) {
String data = (String) cells.next();
HSSFCell hssfCell = hssfHeader.createCell(cellIdx++);
hssfCell.setCellStyle(cellStyle);
hssfCell.setCellValue(data);
}
for (int i = 0; i < l_objForm.getL_lstDynamicDate().size(); i++) {
LoginCountBean reportBean = (LoginCountBean) l_objForm
.getL_lstDynamicDate().get(i);
ArrayList<Object> beanObj = new ArrayList<Object>();
beanObj.add(reportBean.getM_strDynamicDate());
dataList.add(beanObj);
}
cellIdx = 0;
int x = 1;
for (Iterator cells = dataList.iterator(); cells.hasNext();) {
x++;
sheet.setColumnWidth(x, 4200);
String data = String.valueOf(cells.next());
HSSFCell hssfCell = hssfHeader.createCell(x);
hssfCell.setCellValue(data);
hssfCell.setCellStyle(cellStyle);
}
for (int i = 0; i < l_objForm.getL_lstLoginCount().size(); i++) {
List<LoginCountBean1> listOfReportBean = (List<LoginCountBean1>) l_objForm
.getL_lstLoginCount().get(i);
// Arjun now you need to use your earlier data to iterate and
// pull the data
for (int listi = 0; listi < listOfReportBean.size(); listi++) {
if(i==0){
ArrayList<Object> beanObj = new ArrayList<Object>();
dataList1.add(beanObj);
System.out.println("At index :"+i+"item is "+dataList1.get(i));
}
}
}
cellIdx=0;
int z=0;
x=2;
int y=0;
for (Iterator cells=dataList1.iterator(); cells.hasNext();){
HSSROW row= sheet.createRow(x);
}
try {
FileOutputStream outs = new FileOutputStream(file123);
wb.write(outs);
outs.close();
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=\""
+ m_strFileName + "\"");
FileInputStream fis = new FileInputStream(file123);
ServletOutputStream out = response.getOutputStream();
int j = 0;
while ((j = fis.read()) != -1) {
out.write(j);
}
fis.close();
out.flush();
file123.delete();
} catch (IOException e) {
throw new HPSFException(e.getMessage());
}
}
so how can handel the data in excel pls help me if some one know it.