//controller
package com.jun.controller;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.jun.entity.Book1;
import com.jun.util.ExcelUtil;
import com.jun.util.ResponseUtil;
/**
* poi测试
* @author wangjun
*
*/
@RestController
@RequestMapping("/excel")
public class TestPoiController {
/**
* 导出Excel
* @param response
* @return
*/
@RequestMapping("/exportExcel")
public String PoiTest(HttpServletResponse response){
Workbook wb = null;
List<Book1> book = new ArrayList<Book1>();
// 模拟数据
for (int i = 0; i < 1000; i++) {
Book1 book1 = new Book1();
book1.setName("图书名称"+i);
book1.setAuthor("张三"+i);
book1.setPrice(123+i);
book.add(book1);
}
try {
// 获取一个工作簿
wb = ExcelUtil.fillExcelDataWithTemplate("book.xls");
// 获取sheet页
Sheet sheet=wb.getSheetAt(0);
// 获取模板列数
int rowIndex=1;
// 创建Excel行
Row row;
// 塞入数据
if(book != null){
for (Book1 book1 : book) {
row = sheet.createRow(rowIndex++);
row.createCell(0).setCellValue(book1.getName());
row.createCell(1).setCellValue(book1.getAuthor());
row.createCell(2).setCellValue(book1.getPrice());
row.createCell(3).setCellValue("测试");
}
}
final String fileName="图书表格.xls";
ResponseUtil.export(response, wb, fileName);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
// util 工具类
package com.jun.util;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelUtil {
/**
* 获取模板
* @param re
* @param templateFileName 模板名称 请把模板放入指定目录
* @throws Exception
*/
public static Workbook fillExcelDataWithTemplate(String templateFileName)throws Exception{
//获取模板
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/jun/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
return wb;
}
}
// ResponseUtil
package com.jun.util;
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}