Click here to Skip to main content
14,828,250 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to export data to excel file from jsp page by using spring mvc application with hibernate bt when i run the application it generates excel file with headers only.
I am writing the code :

QueryListExcelView.java--------->

package com.rec.excelUtil;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsView;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import com.rec.model.Admin;
import com.rec.model.Discom;
import com.rec.model.StateDiscom;

public class QueryListExcelView extends AbstractXlsxView {
	private static Logger log= Logger.getLogger(QueryListExcelView.class);
	

	@Override
	protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		try
		{
		response.setHeader("Content-Disposition", "attachment; filename=\"queryTracking.xlsx\"");

		List<Discom> AdminPanel = (List<Discom>) model.get("AdminPanel");
		 Sheet sheet = workbook.createSheet("QueryTracking Report");
		 Row header = sheet.createRow(0);
		 header.createCell(0).setCellValue("userId");
		 header.createCell(1).setCellValue("Name of the State");
		 header.createCell(2).setCellValue("Name of the Discom");
		 header.createCell(3).setCellValue("Employee Name");
		 header.createCell(4).setCellValue("Query No");
		 header.createCell(5).setCellValue("Facility Name");
		 header.createCell(6).setCellValue("Description");
		 header.createCell(7).setCellValue("Query Registered on");
		 header.createCell(8).setCellValue("Status");
		 header.createCell(9).setCellValue("QUERY COMPLIANCE DATE");
		 header.createCell(10).setCellValue("Remarks");
		 int row_count=1;
		 for(Discom records:AdminPanel)
		 {
			 log.info("records are: Id:\n"+records.getUserId()+"Discom name: \n"+records.getDiscomName());
			 Row datarow = sheet.createRow(row_count++);
			 datarow.createCell(0).setCellValue(records.getUserId());
			 datarow.createCell(1).setCellValue(records.getState());
			 datarow.createCell(2).setCellValue(records.getDiscomName());
			 datarow.createCell(3).setCellValue(records.getUsername());
			 datarow.createCell(4).setCellValue(records.getQueryNo());
			 datarow.createCell(5).setCellValue(records.getFacility());
			 datarow.createCell(6).setCellValue(records.getQueryDescription());
			 datarow.createCell(7).setCellValue(records.getQueryRegistered());
			 datarow.createCell(8).setCellValue(records.getAdmin().getStatus());
			 datarow.createCell(9).setCellValue(records.getAdmin().getQueryComplianceDate());
			 datarow.createCell(10).setCellValue(records.getAdmin().getRemarks());
			 
			 
		 }
		 
		}
		catch (Exception e) {
			log.info(e);
			e.printStackTrace();
			e.getMessage();
			// TODO: handle exception
		}
		 
		
		
		
		
		
		
		
		
		
		
	}

}

ExcelPdfController-------->
package com.rec.controller;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.rec.dao.Admin_DiscomDao;
import com.rec.service.Admin_DiscomService;

@Controller
public class ExcelPdfController {
	
	@Autowired
	SessionFactory sf;
	
	
	@Autowired
	Admin_DiscomService adminDiscomService;
	
	private static Logger logger = Logger.getLogger(ExcelPdfController.class);
	
	@RequestMapping(value = "/excel",method = RequestMethod.GET)
	public ModelAndView generateExcel() throws Exception
	{
	
		logger.info("******************Generate Excel**********************"); 
		
			Session session= sf.openSession();
			List<Object[]> queryData = adminDiscomService.searchAll();
			
		
		
		return new ModelAndView("QueryExcelView","AdminPanel",queryData);
		
	
	}

}


it generates the folowing exceptions:
java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.rec.model.Discom
	at com.rec.excelUtil.QueryListExcelView.buildExcelDocument(QueryListExcelView.java:46)


What I have tried:

I have tried it by extending AbstractXlsViewer class.
Posted
Updated 27-Jan-21 1:51am
v2
Comments
Richard MacCutchan 27-Jan-21 5:49am
   
Where is the line referred to in the error message: QueryListExcelView.java:46 ?
Member 14639038 27-Jan-21 5:59am
   
for(Discom records:AdminPanel) // this is the line refering to line 46
Richard MacCutchan 27-Jan-21 6:17am
   
The message is telling you that AdminPanel is not a collection of com.rec.model.Discom objects. Which seems a bit odd considering how you get the list in the first place.
Member 14639038 27-Jan-21 6:25am
   
can you help me in this . please suggest another solution
Richard MacCutchan 27-Jan-21 6:49am
   
Sorry, I don't have a solution. You will need to do some debugging to find out what actual values the AdminPanel object contains.
Member 14639038 27-Jan-21 6:53am
   
it contains null
Richard MacCutchan 27-Jan-21 6:55am
   
Well there is your answer; the call to model.get("AdminPanel") is not returning a List as you expect.
Member 14639038 27-Jan-21 7:03am
   
how to get a collection of com.rec.model.Discom??????????
Richard MacCutchan 27-Jan-21 7:28am
   
Sorry, I have no idea what your code is supposed to do. You need to use the debugger to find out why it is going wrong. If this is not code that you wrote, but something you copied from the internet, then you need to ask the person who wrote it.
Member 14639038 27-Jan-21 23:48pm
   
i wanna ask you that will i need databaase to export jsp data to excel
Richard MacCutchan 28-Jan-21 4:31am
   
Sorry I do not know. But as I already said, you need to find out why the model.get("AdminPanel") is not returning a List. Once you have fixed that then it may well do what you are expecting.
Member 14639038 27-Jan-21 7:05am
   
pls tell me another way to export data from jsp page to excel.................
Please
Member 14639038 27-Jan-21 6:33am
   
will i need to have database access to export data to excel from jsp??

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900