CODEWITHSUNDEEP
javamysqlexcelspringhibernate
Joni
Joni

Reputation: 11

Export mysql table to excel using spring hibernate

am trying to export data in mysql table to excel using spring hibernate. My excel export has no data after export. Your help will be appreciated. This is my code below.Thanks in advance

ExcelExport.java

@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
@SuppressWarnings("unchecked")
List<MOH> mohs = (List<MOH>) model.get("mohs");
HSSFSheet sheet = workbook.createSheet("MOH Form")
HSSFRow header = sheet.createRow(0);

header.createCell(0).setCellValue("ID");
header.getCell(0).setCellStyle(style);
header.createCell(1).setCellValue("Data");
header.getCell(1).setCellStyle(style);

int rowCount = 1;         
    for (MOH aBook : mohs) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(aBook.getSurvey_id());
        aRow.createCell(1).setCellValue(aBook.getName());

Controller.java

@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
public ModelAndView downloadExcel() {
ModelAndView mav = new ModelAndView("showMOH");
List<MOH> mohs = new ArrayList<MOH>();
return new ModelAndView("excelView", "mohs", mohs);

Upvotes: 1

Views: 6065

Answers (2)

Ibrahima Timera
Ibrahima Timera

Reputation: 707

it's work

In your controller

@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
public ModelAndView downloadExcel(Model model) {

    List<String> usersGateways = uDAO.GetGwRoleUser();

    List<User> users = gatewayManagedDAO.findAll();
    return new ModelAndView(new ExcelView(), "users ", users );
    }
}

In your ExcelView 

public class ExcelView extends AbstractXlsView{

@Override
public void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // TODO Auto-generated method stub


    // change the file name
    response.setHeader("Content-Disposition", "attachment; filename=\"my-exported-file.xls\"");

    @SuppressWarnings("unchecked")
    List<User> users= (List<GatewayManage>) model.get("users");

    // create excel xls sheet
    Sheet sheet = workbook.createSheet("Users Detail");
    sheet.setDefaultColumnWidth(30);

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    //style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //font.setBold(true);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);


    // create header row
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("First Name");
    header.getCell(0).setCellStyle(style);
    header.createCell(1).setCellValue("Last Name");
    header.getCell(1).setCellStyle(style);
    header.createCell(2).setCellValue("Number");
    header.getCell(2).setCellStyle(style);
    header.createCell(3).setCellValue("Age");
    header.getCell(3).setCellStyle(style);



    int rowCount = 1;
    for(User user : users){
        Row userRow =  sheet.createRow(rowCount++);
        gatewayRow.createCell(0).setCellValue(user.getFirstName());
        gatewayRow.createCell(1).setCellValue(gateway.getLastName());
        gatewayRow.createCell(2).setCellValue(gateway.getNumber());
        gatewayRow.createCell(3).setCellValue(gateway.getAge());

        }

}
}

You can replace my User class by yours (Studen, aBook ....) and it's work!

Upvotes: 0

Viraj Nalawade
Viraj Nalawade

Reputation: 3225

Probably having lot more options for you :-
1. With hibernate you do this using Apache POI. Here is a tutorial with example that will guide you through the process.
2.You can also use BIRT and generate your database to any format you want.
3. You can export your data directly from MySQL to Excel using the ResultSet. See here how to export ResultSet to Excel.
4.And if you are using struts2 you can use datagrid to export it to CSV or Excel.

Upvotes: 2

Related Questions