TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Arjun Dhilod
802
812
106.5k
How can export data in excel from MYSql server.
Oct 29 2013 5:42 AM
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.
Reply
Answers (
0
)
code correction @no of lines in file ? just one line
Division of two numbers using array list ? help