spring里组合POI生成EXCEL

spring里结合POI生成EXCEL

spring里支持从数据动态生成PDF/XSL，PDF的很多例子了，而Spring可以结合Apache的POI包，来生成EXCEL的。
首先到http://jakarta.apache.org/poi/index.html去下载poi包，将其放到工程目录的WEB-INF\LIB下。

POI的具体操作例子可以到http://jakarta.apache.org/poi/hssf/quick-guide.html这里去看。
其实讲到底是利用了spring的ResourceBundleViewResolver的。

下面列一个一本书上的例子，以供备考

song.java
package springxls;

public class Song {
?private String singer;
?private String title;

?public Song(String singer, String title) {
??this.singer = singer;
??this.title = title;
?}
?public String getSinger() {
??return singer;
?}
?public String getTitle() {
??return title;
?}
}

控制器SongPageController .java

public class SongPageController extends AbstractController {
?private String successView;
?public void setSuccessView(String successView) {
??this.successView = successView;
?}

?protected ModelAndView handleRequestInternal(HttpServletRequest request,
??????????????????????????????????????????? HttpServletResponse response)
??throws Exception {
??? Map map = new HashMap();
??? List songList = new ArrayList();

??? songList.add(new Song("James Blunt", "Goodbye My Lover"));
??? songList.add(new Song("MC HotDog", "我行我素"));

??? map.put("songlist", songList);

??? return new ModelAndView(successView, map);

?}
}



生成Xsl文件
mport java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class SongXlsPage extends AbstractExcelView {
?public void buildExcelDocument(
???Map model, HSSFWorkbook workbook,
???HttpServletRequest request, HttpServletResponse response)
???throws Exception {

??int sheetRowNum = 0;

??//取得模型数据
??List songs = (List)model.get("songlist");
??Iterator<Song> iter = songs.iterator();
??//创建工作表和标题
??? HSSFSheet sheet = workbook.createSheet("MySong");
??? HSSFRow titleRow = sheet.createRow((short) sheetRowNum++);
??? HSSFCell titleCell = titleRow.createCell((short) 0);
??? titleCell.setCellValue("My Songs");
??? //创建一个空行
??? sheet.createRow(sheetRowNum++);
??//创建数据表头
??String[] titles = {"Title", "Singer"};
??? HSSFRow dataTitleRow = sheet.createRow((short) sheetRowNum++);
??? for (int i = 0; i < titles.length; i++) {
????? HSSFCell cell = dataTitleRow.createCell((short)i);
????? cell.setCellValue(titles[i]);
??? }
??//数据模型转换:创建表格数据
??? for (int i = sheetRowNum; i < songs.size() + sheetRowNum; i++) {
??? ?if (iter.hasNext()) {
????? ?Song song = iter.next();
??????? HSSFRow dataRow = sheet.createRow((short)(i));
??????? HSSFCell cell1 = dataRow.createCell((short)0);
??????? //中文显示指定编码
??????? cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
??????? HSSFCell cell2 = dataRow.createCell((short)1);
??????? cell1.setCellValue(song.getTitle());
??????? cell2.setCellValue(song.getSinger());
??? ?}
??? }
?}
}

web.xml
<servlet>
??? <servlet-name>frontcontroller</servlet-name>
??? <servlet-class>
????? org.springframework.web.servlet.DispatcherServlet
??? </servlet-class>
??? <load-on-startup>1</load-on-startup>
? </servlet>
? <servlet-mapping>
??? <servlet-name>frontcontroller</servlet-name>
??? <url-pattern>*.do</url-pattern>
? </servlet-mapping>

froncontroller-servlet.xml<beans>
? <bean id="viewResolver"
? value="xlsSong"/>
? </bean>
</beans>