第一步、导入依赖
<!--生成excel文件--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-examples</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-excelant</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.9</version></dependency>
第二步，编写实体类
public class Zhi {private String url; //地址private String Requestbody; //请求体private String Responsebody; //响应体@Overridepublic String toString() {return "Zhi{" +"url='" + url + '\'' +", Requestbody='" + Requestbody + '\'' +", Responsebody='" + Responsebody + '\'' +'}';}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getRequestbody() {return Requestbody;}public void setRequestbody(String requestbody) {Requestbody = requestbody;}public String getResponsebody() {return Responsebody;}public void setResponsebody(String responsebody) {Responsebody = responsebody;}}
第三步、编写表格生成工具
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class ExcelUtil {/*** 导出Excel* @param sheetName sheet名称* @param title 标题* @param values 内容* @param wb HSSFWorkbook对象* @return*/public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){// 第一步，创建一个HSSFWorkbook，对应一个Excel文件if(wb == null){wb = new HSSFWorkbook();}// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet sheet = wb.createSheet(sheetName);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制HSSFRow row = sheet.createRow(0);// 第四步，创建单元格，并设置值表头 设置表头居中HSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式//声明列对象HSSFCell cell = null;//创建标题for(int i=0;i<title.length;i++){cell = row.createCell(i);cell.setCellValue(title[i]);cell.setCellStyle(style);}//创建内容for(int i=0;i<values.length;i++){row = sheet.createRow(i + 1);for(int j=0;j<values[i].length;j++){//将内容按顺序赋给对应的列对象row.createCell(j).setCellValue(values[i][j]);}}return wb;}}
第四步、编写controller层
import cn.kgc.pojo.Account;import cn.kgc.pojo.Zhi;import cn.kgc.service.impl.ExcelUtil;import cn.kgc.service.impl.SalaryServiceImpl;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import javax.annotation.Resource;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.jsp.tagext.PageData;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import java.util.ArrayList;import java.util.List;import java.util.Random;@Controller@RequestMapping(value = "/report")public class ReportFormController{//@Resource(name = "reportService")//private ReportManager reportService;@Resource(name = "aoo")SalaryServiceImpl salaryService;/*** 导出报表* @return*/@RequestMapping(value = "/export")@ResponseBodypublic void export(HttpServletRequest request, HttpServletResponse response) throws Exception {//获取数据List<Zhi> list = new ArrayList<Zhi>();Zhi zhi=null;/*生成随机数*/Random random = new Random();for(int i=0;i<100;i++){int a = (int)(random.nextInt(900))+100;zhi=new Zhi();zhi.setUrl("/NGCRMPF_GS_CGSHQGETFULLUSRINFO_POST");zhi.setRequestbody("{\"params\":{\"object\":\"ServiceNo:13919892"+a+"\"}}");zhi.setResponsebody("{\"rtnCode\":\"0\",\"rtnMsg\":\"成功!\",\"bean\":{},\"beans\":[],\"object\":\"0~success~0~75~0~正常~01~58元新飞享套餐~1~0931\"}");list.add(zhi);}//excel标题String[] title = {"请求地址","请求体","响应体"};//excel文件名String fileName = "学生信息表"+System.currentTimeMillis()+".xls";//sheet名String sheetName = "学生信息表";String [][] content = new String[list.size()][];for (int i = 0; i < list.size(); i++) {content[i] = new String[title.length];Zhi obj = list.get(i);content[i][0] = obj.getUrl()+"";content[i][1] = obj.getRequestbody()+"";content[i][2] = obj.getResponsebody()+"";}//创建HSSFWorkbookHSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);//响应到客户端try {this.setResponseHeader(response, fileName);OutputStream os = response.getOutputStream();wb.write(os);os.flush();os.close();} catch (Exception e) {e.printStackTrace();}}//发送响应流方法public void setResponseHeader(HttpServletResponse response, String fileName) {try {try {fileName = new String(fileName.getBytes(),"ISO8859-1");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}response.setContentType("application/octet-stream;charset=ISO8859-1");response.setHeader("Content-Disposition", "attachment;filename="+ fileName);response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");} catch (Exception ex) {ex.printStackTrace();}}}
wdqqqqqqqqqqqqqqq
第五步编写jsp部分调用一下即可
<a href="${pageContext.request.contextPath}/report/export">导出Excel表格</a>
