根据模板导出EXCEL

function downloadHref(){
	//kk('http://localhost:8080/CGR/PS103/downExels?chk=123');
	var param = "PS103/downExels?chk=123";
	kk=$("base").attr("href")+param;
}
/**
	 * <TT><PRE>
	 * <p> 下载処理.</p>
	 * @param request
	 * @param response
	 * @throws Exception
	 * </PRE></TT>
	 */
	@RequestMapping("downExels")
	@ResponseBody
	public Object download(HttpServletRequest request, HttpServletResponse response,Cgr_Evaluate cgr_Evaluate,String chk,HttpSession session) throws Exception {

		Map<String, Object> retMap=new HashMap<>();
		if(chk==null){
			//月份处理
			int years=Integer.valueOf(cgr_Evaluate.getEvaluateMonth());
			years+=1;
			cgr_Evaluate.setYeaersDateFrom(cgr_Evaluate.getEvaluateMonth()+"-04");
			cgr_Evaluate.setYeaersDateTO(years+"-03");
			
			List<Cgr_Evaluate> list=ps103Service.findBody(cgr_Evaluate);
			if(list.size()<1){
				retMap.put("result", "erro");
				return retMap;
			}else{
				retMap.put("result", "succes");
				session.setAttribute("sessionList",list );
				return retMap;
			}
		}	
		String templateFolder = request.getSession().getServletContext().getRealPath("excel");
		String fullPathName = templateFolder + "/" + "年经销商管理表.xlsx";
		// Excel初期化
		Workbook wb = makeWorkBook(fullPathName);
		
		//数据处理
		@SuppressWarnings("unchecked")
		List<Cgr_Evaluate> dataList=this.getDateList((List<Cgr_Evaluate>)session.getAttribute("sessionList"));
		if (dataList != null && !dataList.isEmpty()) {
			
			Sheet sheet = wb.getSheetAt(0);
			Row srcRow = sheet.getRow(3);
			int rowIndex = 3;
			int i=0;
			for (Cgr_Evaluate map : dataList) {
				if (rowIndex > 3) {
					copyRows(rowIndex, sheet, srcRow, false);
				}
				setCellValue(sheet, rowIndex, 0, ++i);
				
				setCellValue(sheet, rowIndex, 1, map.getBareaName());
				setCellValue(sheet, rowIndex, 2, map.getOfficeName());
				setCellValue(sheet, rowIndex, 3, map.getDealerName());
				setCellValue(sheet, rowIndex, 4, map.getDealerTypeBs());
				setCellValue(sheet, rowIndex, 5, map.getDealerTypeCp());
				setCellValue(sheet, rowIndex, 6, "");
				setCellValue(sheet, rowIndex, 7, map.getUserName());
				setCellValue(sheet, rowIndex, 8, map.getCkCount4()==null?"":getInt(map.getCkCount4()));
				setCellValue(sheet, rowIndex, 9, map.getRank4());
				setCellValue(sheet, rowIndex, 10, map.getCkCount5()==null?"":getInt(map.getCkCount5()));
				setCellValue(sheet, rowIndex, 11, map.getRank5());
				setCellValue(sheet, rowIndex, 12, map.getCkCount6()==null?"":getInt(map.getCkCount6()));
				setCellValue(sheet, rowIndex, 13, map.getRank6());
				setCellValue(sheet, rowIndex, 14, map.getCkCount7()==null?"":getInt(map.getCkCount7()));
				setCellValue(sheet, rowIndex, 15, map.getRank7());
				setCellValue(sheet, rowIndex, 16, map.getCkCount8()==null?"":getInt(map.getCkCount8()));
				setCellValue(sheet, rowIndex, 17, map.getRank8());
				setCellValue(sheet, rowIndex, 18, map.getCkCount9()==null?"":getInt(map.getCkCount9()));
				setCellValue(sheet, rowIndex, 19, map.getRank9());
				setCellValue(sheet, rowIndex, 20, map.getCkCount10()==null?"":getInt(map.getCkCount10()));
				setCellValue(sheet, rowIndex, 21, map.getRank10());
				setCellValue(sheet, rowIndex, 22, map.getCkCount11()==null?"":getInt(map.getCkCount11()));
				setCellValue(sheet, rowIndex, 23, map.getRank11());
				setCellValue(sheet, rowIndex, 24, map.getCkCount12()==null?"":getInt(map.getCkCount12()));
				setCellValue(sheet, rowIndex, 25, map.getRank12());
				setCellValue(sheet, rowIndex, 26, map.getCkCount1()==null?"":getInt(map.getCkCount1()));
				setCellValue(sheet, rowIndex, 27, map.getRank1());
				setCellValue(sheet, rowIndex, 28, map.getCkCount2()==null?"":getInt(map.getCkCount2()));
				setCellValue(sheet, rowIndex, 29, map.getRank2());
				setCellValue(sheet, rowIndex, 30, map.getCkCount3()==null?"":getInt(map.getCkCount3()));
				setCellValue(sheet, rowIndex, 31, map.getRank3());
				setCellValue(sheet, rowIndex, 32, map.getCkCountSum());
				setCellValue(sheet, rowIndex, 33, map.getRankSum());
				
					
				rowIndex++;
			}
		}

		// 配送派单信息_时间
		String tempOutPath = request.getSession().getServletContext().getRealPath("temp/download");

		String outFullName = tempOutPath + "/" + "年经销商管理表" + "_" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";;
	//	wb.setForceFormulaRecalculation(true);
		try {
			OutputStream os = new FileOutputStream(outFullName);
			wb.write(os);
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		download(outFullName, request, response);
		return null;
	}
	/**
	 * 下载excel(导出)
	 *
	 * @param path
	 * @param response
	 */
	public void download(String path, HttpServletRequest request, HttpServletResponse response) {
		try {
			File file = new File(path);
			String fileName = file.getName();
			InputStream fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			response.reset();
			String outName = "";

			String agent = request.getHeader("USER-AGENT");
			if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent.indexOf("Trident")) {// ie
				String name = java.net.URLEncoder.encode(fileName, "UTF8");
				outName = name;
			} else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等
				outName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
			}

			response.addHeader("Content-Disposition", "attachment;filename=" + outName);
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * <TT><PRE>
	 * <p>makeWorkBook. </p>
	 * @param fullPathName 模板文件
	 * @return 結果
	 * @throws Exception 例外
	 * </PRE></TT>
	 */
	protected Workbook makeWorkBook(String fullPathName) throws Exception {
		FileInputStream inStream = null;
		Workbook wb = null;

		// 模板文件
		File excelFile = new File(fullPathName);
		// 该模板文件不存在时,
		if (!excelFile.exists()) {
			throw new Exception("模板文件不存在");
		}

		// 模板文件读取
		inStream = new FileInputStream(excelFile);
		wb = WorkbookFactory.create(inStream);
		inStream.close();
		return wb;
	}

	/**
	 * <TT><PRE>
	 * <p>行拷贝. </p>
	 * @param startRow 开始行
	 * @param currentSheet 模板文件
	 * @param sourceRow 模板文件
	 * @param isCopyVal 值拷贝判断标识
	 * </PRE></TT>
	 */
	protected void copyRows(int startRow, Sheet currentSheet, Row sourceRow, boolean isCopyVal) {

		int columnCount = sourceRow.getLastCellNum();
		Row newRow = currentSheet.createRow(startRow);
		newRow.setHeight(sourceRow.getHeight());
		for (int j = 0; j < columnCount; j++) {
			Cell templateCell = sourceRow.getCell(j);
			if (templateCell != null) {
				Cell newCell = newRow.createCell(j);
				copyCell(templateCell, newCell, isCopyVal);
			}
		}
	}

	/**
	 * <TT><PRE>
	 * <p>单元格拷贝. </p>
	 * @param currentSheet 模板文件
	 * @param srcRowIdx 拷贝元行
	 * @param srcColIdx 拷贝元列
	 * @param distRowIdx 目标单元格行
	 * @param distColIdx 目标单元格列
	 * @param isCopyVal 值拷贝判断标识
	 * </PRE></TT>
	 */
	protected void copyCell(Sheet currentSheet, int srcRowIdx, int srcColIdx, int distRowIdx, int distColIdx, boolean isCopyVal) {
		Row srcRow = currentSheet.getRow(srcRowIdx);
		Row distRow = currentSheet.getRow(distRowIdx);
		Cell srcCell = srcRow.getCell(srcColIdx);
		// cell未生成时,先生成cell对象
		if (srcCell == null) {
			srcCell = srcRow.createCell(srcColIdx);
		}
		Cell distCell = distRow.getCell(distColIdx);
		// cell未生成时,先生成cell对象
		if (distCell == null) {
			distCell = distRow.createCell(distRowIdx);
		}
		copyCell(srcCell, distCell, isCopyVal);
	}

	/**
	 * <TT><PRE>
	 * <p>单元格拷贝. </p>
	 * @param srcCell 拷贝元
	 * @param distCell 目标单元格
	 * @param isCopyVal 值拷贝判断标识
	 * </PRE></TT>
	 */
	protected void copyCell(Cell srcCell, Cell distCell, boolean isCopyVal) {
		distCell.setCellStyle(srcCell.getCellStyle());
		if (isCopyVal) {
			if (srcCell.getCellComment() != null) {
				distCell.setCellComment(srcCell.getCellComment());
			}
			int srcCellType = srcCell.getCellType();
			distCell.setCellType(srcCellType);
			if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
				if (DateUtil.isCellDateFormatted(srcCell)) {
					distCell.setCellValue(srcCell.getDateCellValue());
				} else {
					distCell.setCellValue(srcCell.getNumericCellValue());
				}
			} else if (srcCellType == Cell.CELL_TYPE_STRING) {
				distCell.setCellValue(srcCell.getRichStringCellValue());
			} else if (srcCellType == Cell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
				distCell.setCellValue(srcCell.getBooleanCellValue());
			} else if (srcCellType == Cell.CELL_TYPE_ERROR) {
				distCell.setCellErrorValue(srcCell.getErrorCellValue());
			} else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
				distCell.setCellFormula(srcCell.getCellFormula());
			}
		}
	}

	/**
	 * <TT><PRE>
	 * <p> 项目值出力函数. </p>
	 * @param sheet 表
	 * @param rowIndex 行番号
	 * @param colIndex 列番号
	 * @param value 値
	 * </PRE></TT>
	 */
	protected void setCellValue(Sheet sheet, int rowIndex, int colIndex, Object value) {
		if (value == null) {
			return;
		}

		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}

		Cell cell = row.getCell(colIndex);
		if (cell == null) {
			cell = row.createCell(colIndex);
		}

		if (value instanceof String) {
			cell.setCellValue(value.toString());
		} else if (value instanceof Double) {
			cell.setCellValue((Double) value);
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} else if (value instanceof Float) {
			cell.setCellValue((Float) value);
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof java.util.Date | value instanceof java.sql.Date) {
			cell.setCellValue((Date) value);
		}

	}

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券