如何使用Spring，Hibernate和PostgreSQL将数据导出到Excel? [英] How do I Export Data to Excel using Spring, Hibernate, and PostgreSQL?

问题描述

我有一个项目，该项目具有一个模型类，即Person，具有3列:id，名称和国家(地区)，可以正确地保留在PostgreSQL中.结果可以在List对象中正确找到.但是我想将List数据(显示在页面上)导出到Excel.

I have a project which has one model class, Person with three Columns: id, name, and country, which correctly persists to PostgreSQL. The results are correctly found in List objects. But I want to export the List data (which displays on the page) to Excel.

我制作了一个名为PersonExcelView的类:

I made a class named PersonExcelView:

public class PersonExcelView extends AbstractExcelView {
    private PersonService personService;
    @Override
    protected void buildExcelDocument(Map<String, Object> arg0, HSSFWorkbook arg1, HttpServletRequest arg2,
            HttpServletResponse arg3) throws Exception {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet excelSheet = workbook.createSheet("PersonList");
            setExcelHeader(excelSheet);

        List personList = (List) personService.getPersonList();
        setExcelRows(excelSheet,personList);

    }

    public void setExcelHeader(HSSFSheet excelSheet) {
        HSSFRow excelHeader = excelSheet.createRow(0);
        excelHeader.createCell(0).setCellValue("Id");
        excelHeader.createCell(1).setCellValue("Name");
        excelHeader.createCell(2).setCellValue("Country");
    }

    public void setExcelRows(HSSFSheet excelSheet, List personList){
        int record = 1;
        for (Person person : personService.listPersons()) {
            HSSFRow excelRow = excelSheet.createRow(record++);
            excelRow.createCell(0).setCellValue(person.getId());
            excelRow.createCell(1).setCellValue(person.getName());
            excelRow.createCell(2).setCellValue(person.getCountry());

    }

}}

这是我的PersonServiceImpl课:

@Service
public class PersonServiceImpl implements PersonService {

    private PersonDAO personDAO;

    public void setPersonDAO(PersonDAO personDAO) {
        this.personDAO = personDAO;
    }

    @Override
    @Transactional
    public void addPerson(Person p) {
        this.personDAO.addPerson(p);
    }

    @Override
    @Transactional
    public void updatePerson(Person p) {
        this.personDAO.updatePerson(p);
    }

    @Override
    @Transactional
    public List<Person> listPersons() {
        return this.personDAO.listPersons();
    }

    @Override
    @Transactional
    public Person getPersonById(int id) {
        return this.personDAO.getPersonById(id);
    }

    @Override
    @Transactional
    public void removePerson(int id) {
        this.personDAO.removePerson(id);
    }

    @Override
    public List<Person> getPersonList() {
        // TODO Auto-generated method stub
        return listPersons();
    }

}

这是我的控制器代码:

 @RequestMapping(value = "/export", method = RequestMethod.POST)
    public ModelAndView getExcel() {
        List personList = (List) personService.getPersonList();
        return new ModelAndView("PersonExcelView", "personList", personList);
    }

这是JSP:

<a href="SpringMVCHibernate/export">Export</a></h3>

当我单击导出"时，它不会将数据导出到Excel.错误在哪里?

When I click export, it does not export the data into Excel. Where is the error?

推荐答案

使用以下代码将数据导出到excel工作表，并根据您的代码要求进行更改.

Use below code for exporting data to excel sheet and do changes as per your code requirement.

 XSSFWorkbook workbook = new XSSFWorkbook();           
              XSSFSheet spreadsheet = workbook.createSheet("PersonList");         
              XSSFRow row;      
                     for (Object obj : objectArr)// for (Person person : personService.listPersons()) 
                     {
                        Cell cell = row.createCell(cellid++);
                        cell.setCellValue((String)obj);
                     }               
                for(int columnIndex = 0; columnIndex < 24; columnIndex++) {
                      spreadsheet.autoSizeColumn(columnIndex);
                    }

                      if(List.size()!=0)
                      {
                          FileOutputStream out;                      
                          out = new FileOutputStream(new File(file_path));                     
                          workbook.write(out);
                          out.close();
                      }

这篇关于如何使用Spring，Hibernate和PostgreSQL将数据导出到Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！