如何在Spring Boot中创建Apache POI Excel View配置 [英] How to create Apache POI Excel View Configuration in Spring Boot

问题描述

当我想使用spring boot web将我的数据导出到excel时，我有问题。

I have problem when i want to export my data to excel using spring boot web.

我正在使用Thymeleaf作为模板引擎（通过弹簧引导自动配置）。但是当我在其他配置中添加XmlViewResolver时，XmlViewResolver解决了我的thymeleaf视图，这绝对无法解决。我尝试通过创建新类来修复这个问题，扩展WebMvcConfigurerAdapter并在那里重新配置thymeleaf模板解析器。但我的模板不能解决，因为我的模板位置找不到。我把它放在：

I am using Thymeleaf as template engine (auto configured by spring boot). But when I add XmlViewResolver in my additional configuration, my thymeleaf view resolved by XmlViewResolver which is absolutely will fail to resolve. I try fix that problem by create new class thant extends WebMvcConfigurerAdapter and reconfigure thymeleaf template resolver there. But my template cant be resolved, becase my template location not found. I put it in :

 /resources/template/ 

请帮助我。

推荐答案

使用Spring Boot，您不需要任何额外的配置来生成excel文件。

With Spring Boot you don't need any extra configuration to generate an excel file.

创建一个使用AbstractExcelView返回ModelAndView的控制器：

Create a controller returning a ModelAndView with an AbstractExcelView:

@Controller
public class MyController {

    @RequestMapping(value="/myexcel", method=RequestMethod.GET)
    public ModelAndView getMyData(HttpServletRequest request, HttpServletResponse response) throws SQLException{
        Map<String, Object> model = new HashMap<String, Object>();
        //Sheet Name
        model.put("sheetname", "TestSheetName");
        //Headers List
        List<String> headers = new ArrayList<String>();
        headers.add("Column1");
        headers.add("Column2");
        headers.add("Column3");
        model.put("headers", headers);
        //Results Table (List<Object[]>)
        List<List<String>> results = new ArrayList<List<String>>();
        List<String> l1 = new ArrayList<String>();
        l1.add("A1");
        l1.add("B1");
        l1.add("C1");
        results.add(l1);
        List<String> l2 = new ArrayList<String>();
        l2.add("A2");
        l2.add("B2");
        l2.add("C2");
        results.add(l2);
        model.put("results",results);
        response.setContentType( "application/ms-excel" );
        response.setHeader( "Content-disposition", "attachment; filename=myfile.xls" );         
        return new ModelAndView(new MyExcelView(), model);
    }

然后构建您的AbstractExcelView，如：

Then build your AbstractExcelView like:

public class MyExcelView extends AbstractExcelView
{
    @SuppressWarnings("unchecked")
    protected void buildExcelDocument(Map<String, Object> model,
            HSSFWorkbook workbook,
            HttpServletRequest request,
            HttpServletResponse response)
    {
        //VARIABLES REQUIRED IN MODEL
        String sheetName = (String)model.get("sheetname");
        List<String> headers = (List<String>)model.get("headers");
        List<List<String>> results = (List<List<String>>)model.get("results");
        List<String> numericColumns = new ArrayList<String>();
        if (model.containsKey("numericcolumns"))
            numericColumns = (List<String>)model.get("numericcolumns");
        //BUILD DOC
        HSSFSheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth((short) 12);
        int currentRow = 0;
        short currentColumn = 0;
        //CREATE STYLE FOR HEADER
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        HSSFFont headerFont = workbook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont); 
        //POPULATE HEADER COLUMNS
        HSSFRow headerRow = sheet.createRow(currentRow);
        for(String header:headers){
            HSSFRichTextString text = new HSSFRichTextString(header);
            HSSFCell cell = headerRow.createCell(currentColumn); 
            cell.setCellStyle(headerStyle);
            cell.setCellValue(text);            
            currentColumn++;
        }
        //POPULATE VALUE ROWS/COLUMNS
        currentRow++;//exclude header
        for(List<String> result: results){
            currentColumn = 0;
            HSSFRow row = sheet.createRow(currentRow);
            for(String value : result){//used to count number of columns
                HSSFCell cell = row.createCell(currentColumn);
                if (numericColumns.contains(headers.get(currentColumn))){
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(NumUtils.extractDoubleOrZero(value));
                } else {
                    HSSFRichTextString text = new HSSFRichTextString(value);                
                    cell.setCellValue(text);                    
                }
                currentColumn++;
            }
            currentRow++;
        }
    }
}

这篇关于如何在Spring Boot中创建Apache POI Excel View配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！