@RequestMapping(value = "downloadExcel", method = RequestMethod.GET)
public String download(HttpServletRequest request,HttpServletResponse response) throws IOException{
String fileName="excel文件";
//填充projects数据
ExcelUtil excelUtil = new ExcelUtil();
List<Project> projects = excelUtil.createData();
List<Map<String,Object>> list = excelUtil.createExcelRecord(projects);
String columnNames[]={"ID","项目名","销售人","负责人","所用技术","备注"};//列名
String keys[] = {"id","name","saler","principal","technology","remarks"};//map中的key
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "utf-8"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bos.flush();
} catch (final IOException e) {
logger.error("导出Excel异常:",e);
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
return null;
}
/**
* 导出Excel
* @param request
* @param response
*/
@RequestMapping(value = "ProcessRequestExcel", method = RequestMethod.GET)
public void ProcessRequest(HttpServletRequest request,HttpServletResponse response){
java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyyMMddhhmmss");
String now = format.format(new Date());
String exportFileName = "信息导出_"+now+".xls";//导出文件名
List<Basicinfo> list = getInfoList();
HSSFWorkbook workBook = null;
String[] cellTitle = {"序号", "姓名", "性别", "部门"};
try {
workBook = new HSSFWorkbook();//创建工作薄
HSSFSheet sheet = workBook.createSheet();
workBook.setSheetName(0, "订单信息");//工作簿名称
HSSFFont font = workBook.createFont();
font.setColor(HSSFFont.COLOR_NORMAL);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle = workBook.createCellStyle();//创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建第一行标题
HSSFRow titleRow = sheet.createRow((short) 0);//第一行标题
for(int i = 0,size = cellTitle.length; i < size; i++){//创建第1行标题单元格
sheet.setColumnWidth(i,4000);
HSSFCell cell = titleRow.createCell(i,0);
cell.setCellStyle(cellStyle);
cell.setCellValue(cellTitle[i]);
}
//从第二行开始写入数据
for(int i=1,size = list.size();i<size;i++){
HSSFRow row = sheet.createRow((short) i);
Basicinfo entity = list.get(i);
for (int j = 0,length=cellTitle.length; j < length; j++) {
HSSFCell cell = row.createCell(j, 0);// 在上面行索引0的位置创建单元格
cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
switch(j){// 在单元格中输入一些内容
case 0:
cell.setCellValue(String.valueOf(i));
break;
case 1:
cell.setCellValue(entity.getName());
break;
case 2:
cell.setCellValue(entity.getSex());
break;
case 3:
cell.setCellValue(entity.getDepart());
break;
}
}
}
// 表示以附件的形式把文件发送到客户端
response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName).getBytes(), "ISO8859-1"));//设定输出文件头
response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型
// 通过response的输出流把工作薄的流发送浏览器形成文件
OutputStream outStream = response.getOutputStream();
workBook.write(outStream);
outStream.flush();
outStream.close();
}catch(IOException e){
System.out.println("IO 异常！"+e.getMessage());
e.printStackTrace();
}finally{
}
}
/**
* 模拟数据库获取信息
* @return
*/
@SuppressWarnings("unchecked")
public List<Basicinfo> getInfoList(){
List<Basicinfo> list = new ArrayList();
for(int i=1;i<101;i++){
Basicinfo entity = new Basicinfo();
entity.setName("员工"+i);
entity.setSex(i%2==1?"男":"女");
entity.setDepart(i>80?"销售部":"财务部");
list.add(entity);
}
return list;
}