Export mysql table to excel using spring hibernate

Question

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);

Use simple sql query to export a table to a file "SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename. fiename can be any type txt, sql, xl. I didn't know how to do it in hibernate. — SatyaTNV
– SatyaTNV, Commented Jul 15, 2015 at 6:25

Community · Accepted Answer · 2017-05-23 12:21:48Z

2

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.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jul 15, 2015 at 7:17

Viraj Nalawade

3,2353 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ibrahima Timera · Accepted Answer · 2018-01-17 16:31:03Z

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!

Collectives™ on Stack Overflow

Export mysql table to excel using spring hibernate

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related