001/* 002 * Copyright 2002-2015 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.web.servlet.view.document; 018 019import java.util.Locale; 020import java.util.Map; 021import javax.servlet.ServletOutputStream; 022import javax.servlet.http.HttpServletRequest; 023import javax.servlet.http.HttpServletResponse; 024 025import org.apache.poi.hssf.usermodel.HSSFCell; 026import org.apache.poi.hssf.usermodel.HSSFRow; 027import org.apache.poi.hssf.usermodel.HSSFSheet; 028import org.apache.poi.hssf.usermodel.HSSFWorkbook; 029 030import org.springframework.core.io.Resource; 031import org.springframework.core.io.support.LocalizedResourceHelper; 032import org.springframework.web.servlet.support.RequestContextUtils; 033import org.springframework.web.servlet.view.AbstractView; 034 035/** 036 * Convenient superclass for Excel document views. 037 * Compatible with Apache POI 3.5 and higher, as of Spring 4.0. 038 * 039 * <p>Properties: 040 * <ul> 041 * <li>url (optional): The url of an existing Excel document to pick as a starting point. 042 * It is done without localization part nor the ".xls" extension. 043 * </ul> 044 * 045 * <p>The file will be searched with locations in the following order: 046 * <ul> 047 * <li>[url]_[language]_[country].xls 048 * <li>[url]_[language].xls 049 * <li>[url].xls 050 * </ul> 051 * 052 * <p>For working with the workbook in the subclass, see 053 * <a href="https://poi.apache.org">Apache's POI site</a> 054 * 055 * <p>As an example, you can try this snippet: 056 * 057 * <pre class="code"> 058 * protected void buildExcelDocument( 059 * Map<String, Object> model, HSSFWorkbook workbook, 060 * HttpServletRequest request, HttpServletResponse response) { 061 * 062 * // Go to the first sheet. 063 * // getSheetAt: only if workbook is created from an existing document 064 * // HSSFSheet sheet = workbook.getSheetAt(0); 065 * HSSFSheet sheet = workbook.createSheet("Spring"); 066 * sheet.setDefaultColumnWidth(12); 067 * 068 * // Write a text at A1. 069 * HSSFCell cell = getCell(sheet, 0, 0); 070 * setText(cell, "Spring POI test"); 071 * 072 * // Write the current date at A2. 073 * HSSFCellStyle dateStyle = workbook.createCellStyle(); 074 * dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); 075 * cell = getCell(sheet, 1, 0); 076 * cell.setCellValue(new Date()); 077 * cell.setCellStyle(dateStyle); 078 * 079 * // Write a number at A3 080 * getCell(sheet, 2, 0).setCellValue(458); 081 * 082 * // Write a range of numbers. 083 * HSSFRow sheetRow = sheet.createRow(3); 084 * for (short i = 0; i < 10; i++) { 085 * sheetRow.createCell(i).setCellValue(i * 10); 086 * } 087 * }</pre> 088 * 089 * This class is similar to the AbstractPdfView class in usage style. 090 * 091 * @author Jean-Pierre Pawlak 092 * @author Juergen Hoeller 093 * @see AbstractPdfView 094 * @deprecated as of Spring 4.2, in favor of {@link AbstractXlsView} and its 095 * {@link AbstractXlsxView} and {@link AbstractXlsxStreamingView} variants 096 */ 097@Deprecated 098public abstract class AbstractExcelView extends AbstractView { 099 100 /** The content type for an Excel response */ 101 private static final String CONTENT_TYPE = "application/vnd.ms-excel"; 102 103 /** The extension to look for existing templates */ 104 private static final String EXTENSION = ".xls"; 105 106 107 private String url; 108 109 110 /** 111 * Default Constructor. 112 * Sets the content type of the view to "application/vnd.ms-excel". 113 */ 114 public AbstractExcelView() { 115 setContentType(CONTENT_TYPE); 116 } 117 118 /** 119 * Set the URL of the Excel workbook source, without localization part nor extension. 120 */ 121 public void setUrl(String url) { 122 this.url = url; 123 } 124 125 126 @Override 127 protected boolean generatesDownloadContent() { 128 return true; 129 } 130 131 /** 132 * Renders the Excel view, given the specified model. 133 */ 134 @Override 135 protected final void renderMergedOutputModel( 136 Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception { 137 138 HSSFWorkbook workbook; 139 if (this.url != null) { 140 workbook = getTemplateSource(this.url, request); 141 } 142 else { 143 workbook = new HSSFWorkbook(); 144 logger.debug("Created Excel Workbook from scratch"); 145 } 146 147 buildExcelDocument(model, workbook, request, response); 148 149 // Set the content type. 150 response.setContentType(getContentType()); 151 152 // Should we set the content length here? 153 // response.setContentLength(workbook.getBytes().length); 154 155 // Flush byte array to servlet output stream. 156 ServletOutputStream out = response.getOutputStream(); 157 workbook.write(out); 158 out.flush(); 159 } 160 161 /** 162 * Creates the workbook from an existing XLS document. 163 * @param url the URL of the Excel template without localization part nor extension 164 * @param request current HTTP request 165 * @return the HSSFWorkbook 166 * @throws Exception in case of failure 167 */ 168 protected HSSFWorkbook getTemplateSource(String url, HttpServletRequest request) throws Exception { 169 LocalizedResourceHelper helper = new LocalizedResourceHelper(getApplicationContext()); 170 Locale userLocale = RequestContextUtils.getLocale(request); 171 Resource inputFile = helper.findLocalizedResource(url, EXTENSION, userLocale); 172 173 // Create the Excel document from the source. 174 if (logger.isDebugEnabled()) { 175 logger.debug("Loading Excel workbook from " + inputFile); 176 } 177 return new HSSFWorkbook(inputFile.getInputStream()); 178 } 179 180 /** 181 * Subclasses must implement this method to create an Excel HSSFWorkbook document, 182 * given the model. 183 * @param model the model Map 184 * @param workbook the Excel workbook to complete 185 * @param request in case we need locale etc. Shouldn't look at attributes. 186 * @param response in case we need to set cookies. Shouldn't write to it. 187 */ 188 protected abstract void buildExcelDocument( 189 Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) 190 throws Exception; 191 192 193 /** 194 * Convenient method to obtain the cell in the given sheet, row and column. 195 * <p>Creates the row and the cell if they still doesn't already exist. 196 * Thus, the column can be passed as an int, the method making the needed downcasts. 197 * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0) 198 * @param row the row number 199 * @param col the column number 200 * @return the HSSFCell 201 */ 202 protected HSSFCell getCell(HSSFSheet sheet, int row, int col) { 203 HSSFRow sheetRow = sheet.getRow(row); 204 if (sheetRow == null) { 205 sheetRow = sheet.createRow(row); 206 } 207 HSSFCell cell = sheetRow.getCell(col); 208 if (cell == null) { 209 cell = sheetRow.createCell(col); 210 } 211 return cell; 212 } 213 214 /** 215 * Convenient method to set a String as text content in a cell. 216 * @param cell the cell in which the text must be put 217 * @param text the text to put in the cell 218 */ 219 protected void setText(HSSFCell cell, String text) { 220 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 221 cell.setCellValue(text); 222 } 223 224}