CODEWITHSUNDEEP
javaspring-mvc
Breon
Breon

Reputation: 31

How to export to Excel from Spring MVC

I want to export some data which I have retrieved from the database to the controller level. From the controller I need to export this data to an Excel file without using a view.

I wrote:

        ReportSearchVO searchL = formL.getObjReportSearchG();

        loggerG.info("Resource List:" + searchL.getResourceListG());

        projDetailReportL = reportServiceG.createProjectDetailReport(formL);

        formL.setProjDetailReport(projDetailReportL);
        formL.setReportTypeEnum(ReportTypeEnum.PROJECTDETAILREPORT);
        formL.setObjReportSearchG(searchL);

        requestR.setAttribute("resLevelForm", formL);
        returnModelAndView = new ModelAndView(
            ViewConstants.FINAL_VIEW_PROJECT_DETAILS_REPORT, "reportForm",
            formL);

but this uses a view.

Upvotes: 3

Views: 34298

Answers (7)

BaiJiFeiLong
BaiJiFeiLong

Reputation: 4635

A complete example for serving XLSX generated in memory:

package io.github.baijifeilong.excel;

import lombok.SneakyThrows;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

/**
 * Created by [email protected] at 2019-08-20 16:36
 */
@SpringBootApplication
@RestController
public class ExcelApp {

    public static void main(String[] args) {
        SpringApplication.run(ExcelApp.class, args);
    }

    @SneakyThrows
    @GetMapping(value = "/")
    public void index(HttpServletResponse response) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("OK");
        response.addHeader("Content-Disposition", "attachment; filename=world.xlsx");
        workbook.write(response.getOutputStream());
    }
}

Upvotes: 1

Ajay Kumar
Ajay Kumar

Reputation: 3250

Prepare your Excel sheet like this (using apache poi). And then (for example) in your Controller you can easily write it to the body :

@GetMapping(value = "/alluserreportExcel")
public ResponseEntity<InputStreamResource> excelCustomersReport() throws IOException {
    List<AppUser> users = (List<AppUser>) userService.findAllUsers();
    ByteArrayInputStream in = GenerateExcelReport.usersToExcel(users);
    // return IO ByteArray(in);
    HttpHeaders headers = new HttpHeaders();
    // set filename in header
    headers.add("Content-Disposition", "attachment; filename=users.xlsx");
    return ResponseEntity.ok().headers(headers).body(new InputStreamResource(in));
}

The full blown example is here.

Upvotes: 0

ares
ares

Reputation: 4413

without using a view

To not use a view, you have to make the return type of your request mapping method to void

@Controller
public class MyController{

  @RequestMapping("/xyz")
  public void getExcel(HttpServletRequest request, HttpServletResponse response){
     // 1. Fetch your data
     // 2. Create your excel
     // 3. write excel file to your response.
  }

}

I believe you've already done part 1. Part 2 is completely differnt thing and you have to use some third party api to do that. Apache POI is very simple and effective. https://poi.apache.org/spreadsheet/. Their quickguide is nice to sart with.

Once you have created your file, now you need to write it to response so that it can be downloaded to client end. Here's how you can do that. Lets say the excel you created is xyz.xls

    response.setContentType("application/octet-stream");    // set content attributes for the response

    FileInputStream inputStream = new FileInputStream(new File("xyz.xls"));

    OutputStream outputStream = response.getOutputStream();             // get output stream of the response

    byte[] buffer = new byte[1024];
    int bytesRead = -1;
    while ((bytesRead = inputStream.read(buffer)) != -1) {  // write bytes read from the input stream into the output stream
        outputStream.write(buffer, 0, bytesRead);
    }

    outputStream.flush();

Upvotes: 0

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

user3358994
user3358994

Reputation: 31

Using AbstractExcelView and ModalAndView its possible in SpringMVC. Refer below for more details

http://learnfromexamples.com/generate-excel-in-spring-mvc-application-using-apache-poi/

Upvotes: 3

Vishnu
Vishnu

Reputation: 1009

Apache POI is inhertly supported by spring, it provides AbstractExcelView to provide excel downloads.

Sample code:

public class ExcelBuilder extends AbstractExcelView {

    @Override
    protected void buildExcelDocument(Map<String, Object> input,
            HSSFWorkbook workbook, HttpServletRequest arg2, HttpServletResponse response)
            throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"sample.xls\"");
         // create a new Excel sheet        
         HSSFSheet sheet = workbook.createSheet("Test");        
         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(CellStyle.SOLID_FOREGROUND);       
         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);       
         font.setColor(HSSFColor.WHITE.index);      
         style.setFont(font);         
         // create header row       
         HSSFRow header = sheet.createRow(0);                
         header.createCell(0).setCellValue("Title");       
         header.getCell(0).setCellStyle(style);             
         header.createCell(1).setCellValue("col2");     
         header.getCell(1).setCellStyle(style);             
         header.createCell(2).setCellValue("col3");       
         header.getCell(2).setCellStyle(style);              
         header.createCell(3).setCellValue("col4");   
         header.getCell(3).setCellStyle(style);                
         header.createCell(4).setCellValue("col 5");      
         header.getCell(4).setCellStyle(style);
//Your data goes here
        }
    }

If you just want the excel download without poi, is just set the content dispostion header in your jsp and from controller direct return the view that suggests the jsp. Be warned that when you do like this you are just pasting the content of jsp as html in the excel (a valid file can be opened on Microsoft excel too), so no macro's or function work with that.

Upvotes: 0

Brett Walker
Brett Walker

Reputation: 3576

In the past when I have need to generate Excel Documents I have used Apache POI to create the file.

Upvotes: 0

Related Questions