导出Excel

导出功能

Action层

/**
	 * 导出 生命周期 报表
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String exportAssetsLifeReport() {
		String fileName = "";
		String sheetName = "";
		String reportName = "";
		String finalFileName = "";
		String projectName = "";
		String deptName="";
		if ("0".equals(deptId)) {
			deptName = "全部机构";
		}else {
			deptName = deptService.getRecord(deptId).getDeptName();
		}
		try {
			HttpServletRequest request = ServletActionContext.getRequest();
			HttpServletResponse response = ServletActionContext.getResponse();
			final String userAgent = request.getHeader("USER-AGENT");
			if (StringUtil.isNotNull(cid)) {
				assetsLifeReport.setCid(cid);
			}
			PageDto page = assetsLifeReportService.getAssetsLifeReport(this.getPageInfo(), assetsLifeReport, key);
			PageDto total = assetsLifeReportService.getTotal(this.getPageInfo(), assetsLifeReport, key);
			
			List<AssetsLifeReportDto> assetsLifeDtoList = (List<AssetsLifeReportDto>)page.getRecordList();
			List<AssetsLifeReport> assetsLifeList = (List<AssetsLifeReport>)total.getRecordList();
			if (assetsLifeList!=null && assetsLifeList.size()!=0) {
				reportName = assetsLifeDtoList.get(0).getReportName();
			}else {
				return ConstantUtil.ACTION_ERROR;
			}
			sheetName = deptName;
			fileName = reportName +" - "+ sheetName +".xls";
			if (StringUtils.contains(userAgent, "Edge")) {// Edge浏览器
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			} else if (StringUtils.contains(userAgent, "Trident")) {// IE浏览器
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
				finalFileName = new String(fileName.getBytes(), "ISO8859-1");
			} else {
				finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
			}
			response.setContentType("application/x-execl");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ finalFileName);
			ServletOutputStream outputStream = response.getOutputStream();
			assetsLifeReportService.exportAssetsLifeReport(assetsLifeDtoList,assetsLifeList, outputStream,reportName,sheetName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

Service层

public void exportAssetsLifeReport(List<AssetsLifeReportDto>assetsLifeDtoList,List<AssetsLifeReport> assetsLifeList, ServletOutputStream outputStream, String reportName, String sheetName) {
		try {
			// 声明一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet(sheetName);
			/* ********************* 画对角线 start ******************* */
			// 加入一条对角线
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			//合并
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 12));
			//添加验证数据
			//sheet.addValidationData(new HSSFDataValidation(null, null));
			HSSFClientAnchor anchor = new HSSFClientAnchor();
			//试着改变第二个参数和第六个参数来改变线的位置
			anchor.setAnchor((short) 0, 2, 0, 0, (short) 1, 3, 0, 0);
			HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
			line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
			line.setLineStyle(HSSFShape.LINESTYLE_SOLID);
			// 在NPOI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。
			line.setLineWidth(6350);
			/* ********************* 画对角线 end ******************* */
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			HSSFCellStyle rightHeader = workbook.createCellStyle();
			HSSFCellStyle header = workbook.createCellStyle();
			HSSFCellStyle content = workbook.createCellStyle();
			// 读取准备好的样本excel的格式
			String filePath = ContextUtil.getMsgByKey("FILE_UPLOAD_PATH");
			FileInputStream is = new FileInputStream(new File(filePath+"templets/report/资产生命周期.xls"));
			HSSFWorkbook wb_in = new HSSFWorkbook(is);
			HSSFSheet sheet_in = wb_in.getSheetAt(0);
			// 标题样式
			titleStyle.cloneStyleFrom(sheet_in.getRow(0).getCell(0).getCellStyle());
			rightHeader.cloneStyleFrom(sheet_in.getRow(2).getCell(0).getCellStyle());
			header.cloneStyleFrom(sheet_in.getRow(2).getCell(1).getCellStyle());
			content.cloneStyleFrom(sheet_in.getRow(3).getCell(0).getCellStyle());
			for (int i = 0; i < 10; i++) {
				sheet.setColumnWidth(i, sheet_in.getColumnWidth(i));
			}
			sheet.setDefaultRowHeightInPoints(sheet_in.getDefaultRowHeightInPoints());
			// 设置表格默认列宽度为15个字节
			
			CellRangeAddress cellRangeAddress0 =new CellRangeAddress(0, 0, 0, 9);
			CellRangeAddress cellRangeAddress1 =new CellRangeAddress(1, 1, 0, 9);
			//在sheet里增加合并单元格
			sheet.addMergedRegion(cellRangeAddress0);
			sheet.addMergedRegion(cellRangeAddress1);
			
			// 生成一个样式 居中
			HSSFCellStyle style1 = workbook.createCellStyle();
			HSSFCellStyle style2 = workbook.createCellStyle();
			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 生成一个字体 加粗
			HSSFFont font = workbook.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style1.setFont(font);
			
			String[] titles = new String[10];
			titles[0] = "            状态    类型";
			titles[1] = "使用中";
			titles[2] = "已出库";
			titles[3] = "维护中";
			titles[4] = " 返修";
			titles[5] = "测试中";
			titles[6] = "终止";
			titles[7] = "报废";
			titles[8] = "未知";
			titles[9] = "总计";

			
			//生成第一行和第二行
			Row row0 = sheet.createRow(0);
			Row row1 = sheet.createRow(1);
			Cell first = row0.createCell(0);
			Cell second = row1.createCell(0);
			first.setCellValue(reportName);
			first.setCellStyle(style1);
			first.setCellStyle(titleStyle);
			second.setCellValue(sheetName);
			second.setCellStyle(style1);
			second.setCellStyle(titleStyle);
			
			// 第三行，表头
			HSSFRow row2 = sheet.createRow(2);
			for (int i = 0; i < titles.length; i++) {
				HSSFCell cell_row2 = row2.createCell(i);
				cell_row2.setCellValue(titles[i]);
				cell_row2.setCellStyle(style1);
				if(i == 0 ){
				cell_row2.setCellStyle(rightHeader);
				}else{
				cell_row2.setCellStyle(header);
				}
			}
			// 数据行
			for (int i = 0; i < assetsLifeList.size(); i++) {
				AssetsLifeReport report = assetsLifeList.get(i);
				Row row_data = sheet.createRow(i+3);
				Cell cell0 = row_data.createCell(0);// "状态类型"
				cell0.setCellValue("资产总数");
				cell0.setCellStyle(style1);
				cell0.setCellStyle(content);
				Cell cell1 = row_data.createCell(1);// "使用中
				cell1.setCellValue(report.getUse());
				cell1.setCellStyle(style1);
				Cell cell2 = row_data.createCell(2);// "已出库
				cell2.setCellValue(report.getWarehouseOut());
				cell2.setCellStyle(style1);
				Cell cell3 = row_data.createCell(3);// "维护中";
				cell3.setCellValue(report.getMaintenance());
				cell3.setCellStyle(style1);
				Cell cell4 = row_data.createCell(4);//  返修"
				cell4.setCellValue(report.getRepair());
				cell4.setCellStyle(style1);
				Cell cell5 = row_data.createCell(5);// "测试中
				cell5.setCellValue(report.getTest());
				cell5.setCellStyle(style1);
				Cell cell6 = row_data.createCell(6);// "终止";
				cell6.setCellValue(report.getTermination());
				cell6.setCellStyle(style1);
				Cell cell7 = row_data.createCell(7);//"报废
				cell7.setCellValue(report.getDiscard());
				cell7.setCellStyle(style1);
				Cell cell8 = row_data.createCell(8);// "未知"
				cell8.setCellValue(report.getUnknow());
				cell8.setCellStyle(style1);
				Cell cell9 = row_data.createCell(9);// "总计
				cell9.setCellValue(report.getUse()+report.getWarehouseOut()+report.getMaintenance()+report.getRepair()+report.getTest()+
									report.getTermination()+report.getDiscard()+report.getUnknow());
				cell9.setCellStyle(style1);
			}
			
			for (int i = 0; i < assetsLifeDtoList.size(); i++) {
				AssetsLifeReportDto report = assetsLifeDtoList.get(i);
				Row row_data = sheet.createRow(i+4);
				Cell cell0 = row_data.createCell(0);// "状态类型"
				cell0.setCellValue(report.getTypeName());
				cell0.setCellStyle(style1);
				cell0.setCellStyle(header);
				Cell cell1 = row_data.createCell(1);// "使用中
				cell1.setCellValue(report.getUse());
				cell1.setCellStyle(style1);
				Cell cell2 = row_data.createCell(2);// "已出库
				cell2.setCellValue(report.getWarehouseOut());
				cell2.setCellStyle(style1);
				Cell cell3 = row_data.createCell(3);// "维护中";
				cell3.setCellValue(report.getMaintenance());
				cell3.setCellStyle(style1);
				Cell cell4 = row_data.createCell(4);//  返修"
				cell4.setCellValue(report.getRepair());
				cell4.setCellStyle(style1);
				Cell cell5 = row_data.createCell(5);// "测试中
				cell5.setCellValue(report.getTest());
				cell5.setCellStyle(style1);
				Cell cell6 = row_data.createCell(6);// "终止";
				cell6.setCellValue(report.getTermination());
				cell6.setCellStyle(style1);
				Cell cell7 = row_data.createCell(7);//"报废
				cell7.setCellValue(report.getDiscard());
				cell7.setCellStyle(style1);
				Cell cell8 = row_data.createCell(8);// "未知"
				cell8.setCellValue(report.getUnknow());
				cell8.setCellStyle(style1);
				Cell cell9 = row_data.createCell(9);// "总计
				cell9.setCellValue(report.getUse()+report.getWarehouseOut()+report.getMaintenance()+report.getRepair()+report.getTest()+
						report.getTermination()+report.getDiscard()+report.getUnknow());
				cell9.setCellStyle(style1);
			}
			workbook.write(outputStream);
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

下一篇：导出Excel