前端请求为： window.open(path+‘/researchlist/getAllListByExcel.html?id=‘+id);
使用window.open()方式进行请求
用的jar包：
package com.mydoip.kx.research.service.impl;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.awt.Color;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//单元格样式
private XSSFCellStyle getTitleStyle(XSSFWorkbook workBook) {
XSSFCellStyle commonStyle = workBook.createCellStyle();//公共样式
commonStyle.setWrapText(true);//自动换行
commonStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
XSSFCellStyle style2 = workBook.createCellStyle();//第二行表头的样式
style2.cloneStyleFrom(commonStyle);
XSSFFont font2 = workBook.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 11);
style2.setFont(font2);
return style2;
}
//数据样式
private XSSFCellStyle getContentStyle(XSSFWorkbook workBook) {
XSSFCellStyle styleContent = workBook.createCellStyle();//数据的样式
XSSFCellStyle commonStyle = workBook.createCellStyle();//公共样式
commonStyle.setWrapText(true);//自动换行
commonStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
styleContent.cloneStyleFrom(commonStyle);
XSSFFont font3 = workBook.createFont();
font3.setFontName("宋体");
font3.setFontHeightInPoints((short) 11);
styleContent.setFont(font3);
return styleContent;
}
/**
* 导出excel （HSSFWorkbook）
* @throws Exception
*/
public void exportExcel(HttpServletRequest request, HttpServletResponse response,String id) throws Exception {
XSSFWorkbook workBook = new XSSFWorkbook();
CellStyle styleContent=getContentStyle(workBook);
CellStyle style2=getTitleStyle(workBook);
String tableName="调研计划";
//创建sheet
XSSFSheet sheet = workBook.createSheet(tableName);
//标题
{
//创建表头，第一行
XSSFRow row = sheet.createRow(0);
row.setHeight((short) 700);
//第一行的第一列：表头独占一行
XSSFCell cell = row.createCell(0);
cell.setCellValue("调研计划情况一览表");
cell.setCellStyle(style2);
// 合并
//0代表第一行起始值，0代表第一行结束值，0代表第一行第一列,8代表第一行共有7个单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
//创建表头，第二行
XSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 700);
//每列的title
String[] row_first = {"调研对象", "调研对象类型","调研主题", "调研方式", "调研地点", "建议出席领导", "发起处室", "参与处室", "发起时间"};
for (int i = 0; i < row_first.length; i++) {
XSSFCell cell1 = row1.createCell(i);
//赋值给第二行的每列
cell1.setCellValue(row_first[i]);
cell1.setCellStyle(style2);
//注意：给每个单元格赋值不用合并操作
//sheet.addMergedRegion(new CellRangeAddress(1, 1, i, i+1));
}
Map<String,Object> map = new HashMap<String,Object>();
List<ResearchCommonModelForList> tableList =researchListDao.getAllListByExcel(id);
List<String> list=new ArrayList<String>();
Map<Integer,List<String>> maps=new HashMap();
int rowNum=2;
for(int i=0;i<tableList.size();i++) {
//循环数据的长度，并依次创建数据长度的行数并给予赋值
XSSFRow rows = sheet.createRow(rowNum++);
rows.setHeight((short) 700);
for(int j=0;j<9;j++) {
XSSFCell cells0= rows.createCell(j);
cells0.setCellStyle(style2);
//每一个列值对应一个列标题
switch(j) {
case 0:
cells0.setCellValue(tableList.get(i).getPeopleName());
break;
case 1:
cells0.setCellValue(tableList.get(i).getPeopleTypeName());
break;
case 2:
cells0.setCellValue(tableList.get(i).getTitle());
break;
case 3:
cells0.setCellValue(tableList.get(i).getWayName());
break;
case 4:
cells0.setCellValue(tableList.get(i).getAddress());
break;
case 5:
cells0.setCellValue(tableList.get(i).getLdName());
break;
case 6:
cells0.setCellValue(tableList.get(i).getCreateDeptName());
break;
case 7:
cells0.setCellValue(tableList.get(i).getCsName());
break;
case 8:
if(tableList.get(i).getCompleteDate()!=null) {
String Sdate= tableList.get(i).getCompleteDate().toString();
String s=Sdate.split(" ")[0];
s.replace("\\", "-");
//list.add(s+"\t");
cells0.setCellValue(s);
}
break;
}
}
}
//下载的表名
String fileName = tableName+".xlsx";;
//请求的固定格式
String nameStr = new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "";
if (request.getHeader("User-Agent").toUpperCase()
.indexOf("MSIE") > 0) {
nameStr = URLEncoder.encode(fileName, "UTF-8");// IE浏览器
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-Disposition", "attachment; filename=" + nameStr);
workBook.write(os);
os.flush();
os.close();
}
}