如何在Spring Boot中创建Apache POI Excel View配置

Z时代
2024-01-10
分类：问答

我想使用Spring Boot Web将数据导出到excel时遇到问题。

我正在使用Thymeleaf作为模板引擎（由Spring

Boot自动配置）。但是，当我在其他配置中添加XmlViewResolver时，由XmlViewResolver解析的胸腺视图绝对是无法解析的。我尝试通过创建新类来解决该问题，然后扩展WebMvcConfigurerAdapter并在那里重新配置thymeleaf模板解析器。但是我的模板无法解析，因为找不到我的模板位置。我把它放在：

/resources/template/

请帮我。

回答：

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

创建一个返回带有AbstractExcelView的ModelAndView的控制器：

@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：

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++;
        }
    }
}