@RequestMapping(value = "downloadExcel", method = RequestMethod.GET) public String download(HttpServletRequest request,HttpServletResponse response) t ...
@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; }
